GetNthRecord() patterns and pitfalls

Beginner

Use GetNthRecord() to compare records within a found set, build running calculations, and understand the sort-dependency that makes it fragile in production.

What you'll learn

How GetNthRecord addresses records within the current found set
How to build a "previous record" comparison using Get(RecordNumber)
Why GetNthRecord is always an unstored calculation
When to use a Summary field instead of GetNthRecord

GetNthRecord(fieldName; recordNumber) returns the value of a field from the nth record in the current found set. It is the only native calculation function that can look at sibling records without a relationship. That power comes with a major caveat: the result changes whenever the found set changes or the records are re-sorted, making GetNthRecord inherently unstored and sort-dependent.

1/4

Comparing to the previous record

Use Get(RecordNumber) to identify the current record's position, then subtract 1 to reference the previous record. Guard against record 1 with an If().

FileMaker Script

// Show the change in Amount from the previous record
Let ( n = Get ( RecordNumber ) ;
  If ( n > 1 ;
    Transactions::Amount - GetNthRecord ( Transactions::Amount ; n - 1 ) ;
    Transactions::Amount  // first record has no previous
  )
)

JSONFormatElements and JSON pretty-printing TextFormatRemove() and text style manipulation