1 Reply Latest reply on Sep 11, 2013 4:39 PM by philmodjunk

    Calculating a field by lookup in related table

    JDB

      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

        • 1. Re: Calculating a field by lookup in related table
          philmodjunk

               My best guess is that you have this relationship:

               Members::__pkMemberID = Events::_fkMemberID

               Though your field names may be different.

               Option 1:

               Set up a relationship that matches to events by both memberID AND also by event type and you can match to the earliest "ping" event.

               A calculation field constPing might return the quoted text "Ping" and then a new table occurrence for Events can be linked like this:

               Members::__pkMemberID = Events|Ping::_fkMemberID AND
               Members::constPint = Events|Ping::EventType

               (And instead of a calculation, a text field can be used in place of constPing so that by selecting different values, you match to different event types.)

               Option 2:

               Instead of adding a new table occurrence, FileMaker 12, can also extract this value using ExecuteSQL by setting up a SQL query that extracts this value.