2. PHPRunner General questions
  3. [SOLVED] Concatenate two or more API result fields into a new one
This topic is locked
[SOLVED]

Concatenate two or more API result fields into a new one

 		12/6/2022 9:32:42 AM
PHPRunner General questions
V
 valdaure author

I am using PHPRunner 10.5 Build 36451.

I am building a restaurant reservations management application that enhances the rudimentary one provided by our restaurant till supplier. My application accesses our restaurant's reservations via the till supplier's API.

Here is my concrete problem: I would have expected the reservation date/time data to be stored in a date/time field, but this is not the case. For example, a reservation for 6 December 2022 at 20:00, is stored in three separate fields as follows: "booking_date":"2022-12-06","hours":20,"minutes":0

On my List page, I'm displaying the reservation date and time in two separate columns. I'm using the Fields Custom View As to format the dates and times as I find most appropriate (Tue - 06/12/2022 20:00).

However, sorting in ascending order on booking_date still leaves the time data in no particular order, meaning for a given date the guest arrival times are not sorted ascending.

If it were possible in the Source PHP Mode to sort the Json result on three 'columns', this should solve my problem. But I haven't found a way to do that. My other idea was to duplicate the booking_date field, call it booking_stamp, and then change its value to a concatenation of booking_date, hours and minutes (2022-12-06 20;00) for easy sorting. But I haven't found a way to do that either.

Any suggestions for either: 1) sorting the Json result on more than one field after it is converted to a recordset; or 2) inserting a new field through concatenation that combines the values of two or more fields. Obviously, any new field would have to enter the recordset in order to be manipulated on the Fields screen, etc.

Thanks in advance for any help.
Admin 12/6/2022

Something like this is possible but it will be all custom coding and not really sure what kind of advice you looking for here.

Normally, problems like this are solved on the API side of things. It will be much easier for everyone if your supplier returns datetime values in a proper format that can be sorted using built-in options.

V
 		valdaure author 12/7/2022

I agree entirely. I requested that they provide date/time in a single field, but they preferred to leave things unchanged.

Would it be possible to sort the recordset on two or more columns? Or does sortRecordset require specifying just one column? Some sample code for how to perform the sort on two more columns would be greatly appreciated.

Thanks again for all your help.
Admin 12/7/2022

Unfortunately, we do not have such sample code. We can develop this kind of functionality as a custom solution. Contact support directly if you are interested.
V
 		valdaure author 12/7/2022

OK. Thanks.
Should this post be marked as Solved? Just wondering because people seeing the title might assume a solution can be found here.
V
 		valdaure author 1/3/2023

Fortunately, I was able to persuade the API provider to make a proper date/time field available. That solved it. Thanks.