Help please: Calculating elapsed time from related records
Hi there, and thanks for your help! I'm new to FMP and trying it out.
I'm trying to calculate the length of time that an item spends in a location but having difficulties because I'm using sorted portal rows to perform the calculation (I think).
Table: Items, contains item ID (unique) and name.
Table: Locations, contains item ID (link), movement ID (unique), movement date, location and duration.
I want to calculate how long an item spends in each location. The start date is the date in the same record but the end date is the date in the next record if it exists, sorted by movement date in descending order, or the current date.
Here's my calculation:
If ( Get ( RecordNumber )=1 ;
Get ( CurrentDate )-Movement Date ;
GetNthRecord ( Movement Date ; Get ( RecordNumber )-1 ) - Movement Date )
1. It works in the layout with the sorted portal when it's unstored but not when it's stored. It think this is because when it's stored it performs the calculation based on the unsorted table rather than the sorted portal.
2. It doesn't work _reliably_ in the table, sorted or otherwise, because the first record for one item may not be the first record in the table.
Do I need to a script triggered by entering a new movement? Do I need to use a self-join in Locations using item ID and Movement Date?