Trouble with SORT( SELECT( Expression

Cubinche · 12-28-2019 06:17 AM

I’ve spent the last two days wrestling with a perplexing problem associated with the following expression, which is designed to retrieve the latest DateTime column [SchedDateTime] in a passenger transportation app. The ultimate purpose is to add an hour to that ‘pickup’ DateTime, thereby automating creation of the subsequent ‘drop-off’ DateTime entry:

ANY( TOP( SORT( SELECT( Schedule[SchedDateTime] , AND( ( [TripID] = [_THISROW].[TripID] ) , ( [TripPart] = “PICKUP” ) ) ) , TRUE ) , 1 ) )

The formula works flawlessly both in detail view of any given record and in AppSheet’s formula workspace ‘test’ feature. It fails however, when in form view to create the subsequent drop-off record. In that view, it appears that either the SORT( or TOP( portion of the formula fails to retrieve the latest recorded [SchedDateTime], apparently pulling the first such record, which is the earliest - not the latest - timestamp.

What am I doing wrong?

Thanks in advance!

Suvrutt_Gurjar

Hi @Ed_Benet,

I believe you may wish to mention what you mean by subsequent drop off time.

Is it in the same record that you open from the detail view?

Cubinche

Thanks for your interest and speedy reply, Suvrutt.

[SchedDateTime] can be either a pickup time or a drop-off time - each record being a pickup or dropoff record as stipulated by [TripPart].

Trips can be either one way or round trip. One way trips share two records, one for pickup and one for drop-off, and each of those records share a common [TripID], which of course is not unique or a key.

Round trip records include two pickup records and two drop-offs - one corresponding to each direction of travel.

I have a LINKTOFORM action “CompleteTrip”, which is available to any record that’s missing its corresponding pickup or drop-off record as identified by [TripID]. It pre-populates the next trip record with virtually all the necessary information, including the default one hour added to the latest pickup time recorded to determine probable drop-off time. I need to identify the latest pickup time for instances of round trip travel, which will necessarily have two pickup records in the end.

Answering your question therefore, pickup and drop-off times are always the same column but in different records. The “CompleteTrip” action fails to correctly retrieve the latest [SchedDateTime], apparently adding the default one hour to the earliest rather than latest timestamp of records that share the given [TripID].

Suvrutt_Gurjar

Hi @Ed_Benet,

Thank you for all details. So is it correct to say the aim is to copy the [SchedDateTime] from the current record to the new record being created through LINKTOFORM() action. This new record is necessarily paired record of the record being copied and the [TripID] is common for both the records. The record types [TripPart] alternate between" Pickup" or “Drop” depending on the record being copied.

Also if so, could you please add what you mean by the “earliest” or “first such” record? Is it the first record in the table itself irrespective of the [TripID]?

Cubinche

Thanks, Suvrutt.

You asked:

No, [TripID] is vital to search only those records related to the same trip, which can have up to four records as determined by [TripType], which is an ENUM that can be either “ONE WAY” or “ROUND TRIP”.

If [TripType] = “ONE WAY”, there will ultimately be two records sharing the same [TripID] - one a pickup record and the other a drop-off.

If [TripType] = “ROUND TRIP”, there will ultimately be four records sharing the same [TripID]: one pickup and one drop-off record for the first leg of travel and another pair of pickup and drop-off records representing the return trip.

This is almost true. The LINKTOFORM() action populates [TripID] in the new record. [SchedDateTime] is populated by that column’s own ‘Initial Value’ property with the results of 'latest [TripType]=“PICKUP” [SchedDateTime] entry for the record that shares the same [TripID], + “001:00:00” ', or said another way: the latest pickup [SchedDateTime] record + one hour.

It is crucial to select the latest pickup record’s [SchedDateTime] in cases where [TripType] = “ROUND TRIP”, since there will ultimately be two pickup records for any given [TripID] whose [TripType] = “ROUND TRIP”.

Suvrutt_Gurjar

Hi @Ed_Benet,

Thank you for your details and patience. I am sorry if I have still not understood the app configuration but could you update why the current record’s [SchedDateTime] cannot be simply copied to the new record?

As per my understanding so far , the record being copied will always be latest pick up record irrespective of trip type?

Cubinche

Thanks for your continued help, Suvrutt.

You asked:

No. The “CompleteTrip” action button is available on any record whose total count of records with the same [TripID] is <2 (in the case of [TripType] = “ONE WAY”), or < 4 (in the case of [TripType] = “ROUND TRIP”). Virtual Columns track the count of identical [TripID]s. The button creates new records to complete the segments of each trip that is missing required trip segments. Since the button is attached to all of the records with the same [TripID] missing completed trip segments, one cannot automatically copy [SchedDateTime] from the originating record to the new one because each record’s [SchedDateTime] will necessarily be different from the others.

[SchedDateTime]'s Initial Value property looks at [TripType] (“ONE WAY” or “ROUND TRIP”) and the last pickup time if [TripPart] = “DROPOFF” to perform the calculation of the drop-off record’s proposed drop-off time.

Suvrutt_Gurjar

Thank you @Bahbus for your useful insights.

Hi @Ed_Benet,

Could you please use an expression something like

INDEX( SORT( SELECT(Table1[SchedDateTime] , AND( ( [TripID] = [_THISROW].[TripID] ) , ( [TripPart] = “PICKUP” ) ) ) , TRUE ) , 1 )

My testing showed that somehow the order of TOP() reverses between detail view and form view and as such the form view shows earliest value rather than the latest value.

Please take a look at all the step by step capture of constituent subexpressions of your expression and please take a look at TOP() expression.

Your expression : ANY( TOP( SORT( SELECT( Schedule[SchedDateTime] , AND( ( [TripID] = [_THISROW].[TripID] ) , ( [TripPart] = “PICKUP” ) ) ) , TRUE ) , 1 ) )

@Steve, @Aleksi : May I request your insights into why this could be happening.
Detail View

Form View

Bahbus

I don’t know if it will end up helping at all, but I personally prefer ORDERBY() rather than SORT(), and in your case, INDEX() over TOP(), which will also eliminate the ANY() call.

With all that said… I think it would be easier to have many of these things happen via a series of grouped actions and/or workflows. For example, when you create the higher level Trip ID, you could use a workflow when that form is saved to add 2 or 4 rows prefilled with appropriate information.

Anywho, if nothing helps on your own, please take screenshots of how you have the column in question set up, any tables/columns associated with the expression, the form failure, and anything else that may visually help us troubleshoot.

Cubinche

Thanks for the great insights, Dave. Very helpful!

Cubinche

That did it, Suvrutt. Thanks much!!