1 Reply Latest reply on Mar 16, 2011 9:13 AM by philmodjunk

    Calculations involving field in different records



      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]

      Copy [Select]

      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

        • 1. Re: Calculations involving field in different records

          Look up the GetNthRecord function in FileMaker help.

          As long as your records are always correctly sorted and you can use an unstored calculation for this:

          GetNthRecord (Events::EventEnd ; get ( RecordNumber ) - 1 )

          In some circumstances, (experiment and see), you can use the "value from last visited record" to auto-enter the value. That option enables you to use a stored, indexed field (needed in some cases and faster for searches and sorts in others) as well as allowing you to directly edit the value in the field should you ever need to.

          Final note: There are much better ways to move data from one place to another than copy and paste. Copy and paste wipe out anything that the user has copied to the clipboard and also will silently fail to work if the field they copy from or paste to is not present on the current layout. Thus, scripts that use copy/paste not only irritate/confuse the user by mysteriously changing the clipboard contents, a future edit of a layout can result in a script that then mysteriously fails to work as expected. You can, instead, use set variable to copy the value and Set field to "paste" the value from the variable to the field and avoid both issues.