AnsweredAssumed Answered

Calculating a field by lookup in related table

Question asked by JDB on Sep 11, 2013
Latest reply on Sep 11, 2013 by philmodjunk

Title

Calculating a field by lookup in related table

Post

     Evening everyone,

     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.

     Many thanks,

     JDB

Outcomes