Make a dumb function smart and elegant!

[innodat]

I'm looking for someone to make a dumb (but useful) function much smarter and more elegant:

Case ( 

GetNthRecord ( DriverPortal_Driverlog::Kilometer From ; 5 ) < 1; GetNthRecord ( DriverPortal_Driverlog::Kilometer From ; 6 ); 

GetNthRecord ( DriverPortal_Driverlog::Kilometer From ; 4 ) < 1; GetNthRecord ( DriverPortal_Driverlog::Kilometer From ; 5 ); 

GetNthRecord ( DriverPortal_Driverlog::Kilometer From ; 3 ) < 1; GetNthRecord ( DriverPortal_Driverlog::Kilometer From ; 4 ); 

GetNthRecord ( DriverPortal_Driverlog::Kilometer From ; 2 ) < 1; GetNthRecord ( DriverPortal_Driverlog::Kilometer From ; 3 ); 

GetNthRecord ( DriverPortal_Driverlog::Kilometer From ; 1 ) < 1; GetNthRecord ( DriverPortal_Driverlog::Kilometer From ; 2 ); 

GetNthRecord ( DriverPortal_Driverlog Daily AllForDriver::Kilometer From ; 1 )

)

The function works, but it's far from elegant... and has limitations. It pulls data from the last record in a sorted relationship that has data in a certain field, thus omitting records with no data in the "Kilometer From" field.

What I'm looking for is a function that skips as many records as needed and always shows data from the first record from the sorted relationship that has data in a specified field.

Maybe a recursive function?

[comment]

I think these two statements contradict each other:

12 minutes ago, innodat said:

data from the last record in a sorted relationship that has data in a certain field,

13 minutes ago, innodat said:

data from the first record from the sorted relationship that has data in a specified field.

 

IIUC, there is only one field involved in this. To get the value from the last related record that has a value in this field, you can use the Last() function - in your example, that would be simply:

Last ( DriverPortal_Driverlog::Kilometer From )

There is no equivalent function to get the value from the first related record that has a value in this field, but you could do:

GetValue ( List ( DriverPortal_Driverlog::Kilometer From ) ; 1 )

 

Both of these are rather simple. A much more difficult problem would be getting data from another field in the first/last related record that has a value in some field. But as I noted above, that seems not to be the case here.

 

[innodat]

1 hour ago, comment said:

GetValue ( List ( DriverPortal_Driverlog::Kilometer From ) ; 1 )

 

Many thanks for your reply, comment. Nice to see you’re still around!

 

I was considering the List () function, but ruled it out due to performance concerns.

Do you happen to know the inner workings of combining GetValue and List?

Will your example above send the first value only to the client, or does the entire list have to be cached before the first value is can be returned?

I’m asking because there are well over 1 Mio records in the related table. We only access data from it with very restrictive relationships.

Edited April 13, 2022 by innodat

[comment]

1 hour ago, innodat said:

Will your example above send the first value only to the client

I don't know. Probably not - but AFAIK sorting the relationship also happens at the client, so I guess all the related records will be fetched anyway.

 

1 hour ago, innodat said:

I’m asking because there are well over 1 Mio records in the related table. We only access data from it with very restrictive relationships.

I don't understand this part. If the relationship is "very restrictive". then you should not have "well over 1 Mio records". At least not related records - which I think is what counts here.

 

If you prefer to iterate using GetNthRecord() until a non-empty value is encountered, try it along these lines:

While ([ 
result = "" ;
i = 1 
] ; 
IsEmpty ( result ) and i ≤ Count ( Child::ParentID ) ; 
[ 
result = GetNthRecord ( Child::Value ; i ) ;
i = i + 1
] ; 
result
)

But I don't know if it's better performance-wise. You will have to run your own tests.

 

[innodat]

10 hours ago, comment said:

I don't understand this part. If the relationship is "very restrictive". then you should not have "well over 1 Mio records". At least not related records - which I think is what counts here.

The App is being used in the field. We are working with relationships such as today’s records for one user. So that’s no more than 20 out of 1 Mio filtered @ the relationship.

For this function to be bulletproof and always return a value it should look at more records than just a day’s worth.  

But I guess we could limit data to a month or so which would be a list of about 600 values. That should return a result in 99% of the cases and performance should be acceptable.

thanks again for your valuable input!

[comment]

Perhaps you should look at a different approach, where the data entry is script-driven and every time a new child record is created and the value field populated, the script will also update the parent record.

But now the topic changes from "smart and elegant function" to "optimization" - a topic on which I am much less comfortable offering advice.

 

[innodat]

Thanks again comment, I implemented the following solution which performs just fine:

1. Sorted relationship: ChildTable::Date  >  AnkerTable::Backdate, sorted descending by Date (and Time)

2. Scripted entry of: AnkerTable::Backdate --> Get (CurrentDate) - 30

3. Scripted entry of: AnkerTable::TargetField --> GetValue ( List ( ChildTable::SourceField ) ; 1 )

Edited April 14, 2022 by innodat

[comment]

I don't see the big picture here. Is the relationship sorted for this purpose only? If so, you could skip this part and use Last() as suggested earlier (this is assuming that records are entered in chronological order).

Moreover, if you're scripting this, then you don't the relationship at all. You could simply perform a find or - possibly even better in your situation - use ExecuteSQL() to get the desired value.

 

[innodat]

1 hour ago, comment said:

I don't see the big picture here. Is the relationship sorted for this purpose only? If so, you could skip this part and use Last() as suggested earlier (this is assuming that records are entered in chronological order).

Moreover, if you're scripting this, then you don't the relationship at all. You could simply perform a find or - possibly even better in your situation - use ExecuteSQL() to get the desired value.

 

They are not guaranteed to be entered chronologically, hence the sorted RS.

SQL crossed my mind too, but once again I don‘t know enough about performance. It has to be translated to the Draco engine for sure, therefore I doubt that a SELECT command works like a „view“ in SQL that only loads selective data?

Maybe someone reading this knows what needs to be cached in order for an SQL function to be executed?

Wim actually wrote a good article on SQL performance with links for deep-dive: https://www.soliantconsulting.com/blog/executesql-filemaker-performance/ but it doesn’t directly answer this question.

Edited April 14, 2022 by innodat