Lookups vs. related calculations: choosing correctly

Expert

Understand when to copy data into a record with a lookup vs. when to display a live related field, and the long-term data integrity implications.

What you'll learn

When to use a lookup to lock in a value vs. a live related field
How lookups behave when the source record changes
Common business scenarios where each approach is correct

A lookup copies a value from a related record into the current record at a point in time and stores it permanently. A related field (or calculation referencing a related field) always shows the current live value from the related record. Choosing between them is a fundamental data-modeling decision with lasting consequences.

1/3

Lookup: copy and store

A lookup field copies a value from a related record when the match field changes (or when triggered by Relookup Field Contents). The value is stored and does not change if the source record later changes.

FileMaker Script

// Invoice line item: price at the time of order
// Field: LineItems::UnitPrice (lookup)
// Lookup from: Products::Price
// When InvoiceId match field changes → copy Products::Price

// If the product's price changes next month,
// the historical invoice still shows the original price.

Many-to-many relationships with a join table Relationship performance and indexing