Calculating a field by lookup in related table
I've spent quite a while experimenting with FMP to find a solution to this problem, and even longer searching the forums, but no success yet, so I'm hoping somebody might be able to help.
I need to calculate the exact number of hours between timestamps held in related tables.
The earlier timestamp (joinDate) and the calculated field (elapsedHours) are stored in the Members table.
A related table Events appears as a portal, and contains a list of events for each Member, with fields eventDate and eventType. eventType is a text field with options such as "alarm", "ping" etc. The Events table is sorted by eventDate.
I need elapsedHours to calculate the time difference between joinDate and eventDate for the earliest occurrence of eventType="ping". The first "ping" event could be anywhere in the Events table, or not at all.
I've tried using List(Events::eventType) and this produces a list of events in the correct order, but I can't work out how to determine the position of the first "ping", then use this position to abstract the date from List(Events::eventDate). I'm not even sure if this is the right way to do it.
Your advice would be greatly appreciated.