Calculations involving field in different records
‘Events’ is a table tracking the start and the end times of events. It has timestamp fields ‘EventStart’ and ‘EventEnd. For each given record, I need to know the time elapsed since the end of the previous event. Not skilled enough to directly perform a calculation between fields in two different records, I added a field called ‘PreviousEventEnd’ which I populate with the following script:
SortRecords [Restore; No dialog]
Go to Record/Request/Page [Previous]
Go to Field [Select/perform; Events::EventEnd]
Go to Record/Request/Page [Next]
Paste [Select; No style; Events::PreviousEventEnd]
I then use a calculated field (EventEnd – PreviousEventEnd) to get what I need. Note that events never overlap, so when the table is sorted by ‘EventStart’ all the records unambiguously follow each other in the right chronological sequence.
The system works fine, but since I am very new to FileMaker, I am afraid that it is suboptimal and that I could do much better. For instance, is there a way to avoid a script altogether and make ‘PreviousEventEnd’ a simple calculation field? More generally, what are the best ways to perform calculations involving different records in a given table, as if it were a spreadsheet?
If scripting is the only way to go, is there a way to improve my creation? Again, I’m really new to FM so please excuse me if I’m missing something obvious.
Any help will be greatly appreciated. Thanks in advance. W