# Ways to find "most recent record"

**URL:** <https://the.fmsoup.org/t/ways-to-find-most-recent-record/3141>\
**Category:** Lounge (Discussions)\
**Tags:** calculation-engine\
**Created:** [October 7, 2022, 8:20pm UTC](https://the.fmsoup.org/t/ways-to-find-most-recent-record/3141 "2022-10-07T20:20:11Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![xochi](https://avatars.discourse-cdn.com/v4/letter/x/0ea827/32.png) [@xochi](https://the.fmsoup.org/u/xochi)\
**Post date:** [October 7, 2022, 8:20pm UTC](https://the.fmsoup.org/t/ways-to-find-most-recent-record/3141/1 "2022-10-07T20:20:11Z")

</div>

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](https://the.fmsoup.org/t/confusing-1-to-many-find-behavior-with-relationship-sorted-by-date/3139/7) 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?

---

<div class="post-metadata">

**Author:** ![bdbd](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/bdbd/32/620_2.png) [@bdbd](https://the.fmsoup.org/u/bdbd)\
**Post date:** [October 7, 2022, 8:46pm UTC](https://the.fmsoup.org/t/ways-to-find-most-recent-record/3141/2 "2022-10-07T20:46:34Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Malcolm](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/malcolm/32/196_2.png) [@Malcolm](https://the.fmsoup.org/u/Malcolm)\
**Post date:** [October 8, 2022, 4:39am UTC](https://the.fmsoup.org/t/ways-to-find-most-recent-record/3141/3 "2022-10-08T04:39:38Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![Otmar](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/otmar/32/703_2.png) [@Otmar](https://the.fmsoup.org/u/Otmar)\
**Post date:** [October 8, 2022, 9:00am UTC](https://the.fmsoup.org/t/ways-to-find-most-recent-record/3141/4 "2022-10-08T09:00:05Z")

</div>

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:

```auto
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 )
)

```

---

<div class="post-metadata">

**Author:** ![AndyHibbs](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/andyhibbs/32/3089_2.png) [@AndyHibbs](https://the.fmsoup.org/u/AndyHibbs)\
**Post date:** [October 8, 2022, 3:30pm UTC](https://the.fmsoup.org/t/ways-to-find-most-recent-record/3141/5 "2022-10-08T15:30:33Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![xochi](https://avatars.discourse-cdn.com/v4/letter/x/0ea827/32.png) [@xochi](https://the.fmsoup.org/u/xochi)\
**Post date:** [October 8, 2022, 10:25pm UTC](https://the.fmsoup.org/t/ways-to-find-most-recent-record/3141/6 "2022-10-08T22:25:32Z")

</div>

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