Ways to find "most recent record"

Lounge (Discussions)
1

I frequently find the need to update data in a parent table with data from a related child table, but using only the most recent record from the child table.

Here are a few ways I've done it:

  1. child table has self relation sorted by date (descending) with a calculation field IsLatest = If(self_relation_by_date_desc::serial = me::serial; 1 ; "")
  2. same as #1 except the field is a stored auto-enter calc field (so it can be indexed and be much faster). Disadvantage: You have to script this to be updated whenever new records are added to the child table.
  3. parent table related to child table by child date, sorted descending (though be careful, as discussed here Confusing 1 to Many Find behavior with relationship sorted by Date - #7 by bdbd finds through relationships may ignore sorts
  4. parent table related to child table by a global date field in addition to ID. Pro: very fast. Con: only works if your child data has predictable patterns (e.g. records only updated once a month)

What techniques do you like?

2

Here we want one record to update when a related record updates. No auto-entry solution can work in this case as the wrong record is being updated, therefore the wrong record's auto-entries trigger. A scripted approach is needed.

A child record is presumably the most recent record when it is being committed. I would attach an OnRecordCommit trigger script to the layouts that allow child record modifications or a subscript to scripts that modify the child records. This trigger script or subscript would then update the parent. No need for a calculation.

I would add a "most recent child ID" field and a change flag field to the parent table if I needed the ability to update the parent record at a time after the most recently modified child was modified. Finds would then be limited to the flag field of the parent record. The ID would link the parent record to the most recently modified child record.

The child would be responsible to update the ID in the parent and set the flag. The above trigger script or subscript would take care of the child's updates. The script that updates the parent would be responsible to clear the flag when it updates its own data.

Hope this makes sense.

1 Like
3

If the relationship is 1:N then targeting the parent record from any of the N child records is trivial. You simply set the fields in the parent record, as there is only one, and the relationship is valid, you will set the correct record.

In your situation, if you start from the Parent.

  1. Go to Related Records ( show only related records )
  2. Go to Record Last
  3. push the data into Parent
  4. Go to layout (original)
1 Like
4

Depending on how many related records in the child table, you could get the record data also using ESQL in the context of TO ParentTable:

Let ( [
_latest = ExecuteSQL ( "
SELECT MAX(NUMVAL(changeDate)) 
FROM ChildTable 
WHERE fk=?" 
; "" ; "" ; ParentTable::pk )
] ;
ExecuteSQL ( "
SELECT * FROM ChildTable 
WHERE fk=? AND NUMVAL(changeDate)=?"
; "" ; "" ; ParentTable::pk ; _latest )
)
1 Like
5

We wouldn’t recommend sorting within the relationship due to the performance overhead. However, you could have a (hidden) 1 record portal sorted in reverse by timestamp on the parent layout and update this using script triggers.

1 Like
6

I'm loving all these creative solutions, thank you.