GetNthRecord: accessing other records in a calculation

Expert

Use GetNthRecord to read field values from records other than the current one within a calculation field.

What you'll learn

How GetNthRecord addresses records within the found set
How to calculate a running total using GetNthRecord
The limitations of GetNthRecord (unstored, sort-dependent)

GetNthRecord(fieldName; n) returns the value of a field in the nth record of the current found set. It is the only native way to reference a sibling record from within a calculation, enabling running totals, comparisons to the previous row, and sequential ranking — all without scripts.

1/3

Basic GetNthRecord usage

GetNthRecord references a field in the context of a specific record number in the found set. Record 1 is the first record in the current found set order.

FileMaker Script

// In a LineItems calculation — get the amount from the previous row
Let ( n = Get ( RecordNumber ) ;
  If ( n > 1 ;
    GetNthRecord ( LineItems::Amount ; n - 1 ) ;
    0
  )
)

Stored vs. unstored calculations: performance impact Evaluate() for dynamic expressions