What is an efficient way to determine the most recent modification timestamp across a primary record and all its related records? For example, if the primary record was modified on 11/1/24, and one related record was modified on 11/15/24 while others were modified earlier, I want to automatically identify 11/15/24 as the latest modification date.
Here are some options, in no specific order:
- you could sort the relationship on modification date descending and grab the first record across that relationship
- you could use an aggregate formula like this:
max ( relatedTableOccurrence::modificationDate ) - You could use a transaction when editing child records to also edit a field in the parent record that would store the last modification date of the child record you are editing
When you are asking for efficiency, it depends how you measure that. The 3rd option, storing data in the parent record would give you the information without having to compute anything, but you may have to undergo a lot of changes to make sure you can enforce data integrity (depends on how many different contexts allow for the child records to be modified). The 2nd option would be of little cost to you, but computing that will require having all the child records available to the calc engine in order to be able to give the answer. If you have a high count of child records, or child records that are whide (many fields) or heavy (fields with lots of data in them), this will not perform well.
Take your worst case scenario, bump that up, and test with that if you want to know how efficient or taxing the design you go with can be for your users.
4 Likes
Thank you for the ideas. I was working on the second suggestion already so I will finish that and see how it works. But I like your third suggestion that might work really well.
Be very (very) careful when using "Modified" dates.
• Many things can modify a record when you are not expecting it ... e.g., server side script, developer account, imports, if someone adds or removes a space in a field ... all could trigger a standard "modified date" field.
• I often use a special "record_dated" field that ony updates if certain fields are changed ... and it is set via a script - usually a trigger on a layout or field(s).
• you can querry wich fields have been modified before the record is saved ... do a pattern count to see if any of the "important" fields are modified, and if so, then you set the "record_updated" field
• then use that field track your most recently modified record
Note: this does not help with fields where the person adds/removes a space or makes an "insignificant" change ... any change to an "important field" will change the date of the "record_updated" field ... but I find this better than a generic "Modified Date" field.
3 Likes
Interesting idea and one I might look into in the future. For now I am happy with any change.
+1 @Leish
Preserving accurate metadata is hard and often overlooked.
1 Like
in 21.1 there is a new option on on the Commit Transaction script step to ignore auto-enter calculations. While possibly useful for suppressing mod timestamps in this way, it does apply to all auto enter calculations which is a shame - often times you just want to suppress one or two at a time...
For mod timestamp suppression we often use a global variable that can be set via scripted actions such as $$SUPPRESS_AE
When on, specified auto enter calcs are not run, this has to be done within the auto enter calculation itself, e.g.:
if ( $$SUPPRESS_AE = 1 ; self ; <rest of code> )
Obviously this doesn't work well if you're using an auto enter option other than the calculation, such as the modification options, however you can work around this using a little trick I first learned from @jwilling
Let ( ~trigger = GetField () ; <code here> )
The GetField() function without any parameter is triggered when any field in the table is changed, and so you can use it to reproduce the modified auto enter property within a calculation, so putting it all together...
Let (
~trigger = GetField()
;
if ( $$SUPPRESS_AE = 1 ; self ; Get ( CurrentHostTimestamp ) )
)
This calculation triggers when any field in the table is modified. If the suppress flag is set, the original timestamp is retained, otherwise it is updated.
4 Likes
You could also split your table in two (or more) tables with one to one relationships to manage the groups of fields that affect timestamps.
1 Like
Get out of here! How many FileMaker functions have hidden features?
Too true. I did wonder whether the Modification auto-enter option is just a nice front-end display for what is actually underneath it all an auto-enter calc of GetField() ...
Another one I saw recently was concerning use of SerialIncrement instead of Right function to pad a number with zeroes...
Right ( "000" & 3 ; 3 ) = 003
and
SerialIncrement ( "000" ; 3 ) = 003
I think both have their qualities, while Right you can be very specific about the length of the result. With SerialIncrement you do have to first specify the length by way of the amount of zeroes you give, but it is a little more compact.
A cheat that I have is for Go to Related Records. You can use it as a layout switcher and search tool. When the layout that you go to is based on the same Layout Table Occurrence ( in other words - same table - not a "related" table ) GTRR will transfer the found set from the current layout.
For frequently used double-digit or treble numbers, I have two Custom Functions for best readability: TwoDigit ( _number ) and ThreeDigit ( _number ) basing on "Right (…)".
[EDIT:]
I have an additional CF for anything else where I need to pad a string or number with a leading character to a certain length: LeadingChars ( _char ; _length ; _value )
I just realized that my previous name for the CF was misleading: nLeadingChars (...).
From now on I will just call it LeadingChars(...).
1 Like