Compound key calculations

Expert

Design and implement compound key fields that combine multiple values into a single match key for relationships, lookups, and de-duplication.

What you'll learn

When to use a compound key instead of multiple relationship predicates
How to construct a reliable compound key with a delimiter
How to ensure compound keys remain consistent across updates
Tradeoffs of compound keys vs. multi-predicate relationships

A compound key concatenates two or more field values into a single field to create a unique match value. FileMaker relationships can only match on equality between two fields, so compound keys are essential when a match requires combining multiple criteria -- a foreign key plus a type code, for example.

1/4

Basic compound key construction

Concatenate the component values with a delimiter that cannot appear in the components. The pipe character "|" is a common choice because it is rare in data.

FileMaker Script

// Compound key on Invoices: ClientId + Year + Month
ClientId & "|" & Year ( InvoiceDate ) & "|" & Month ( InvoiceDate )
// e.g., "C001|2025|3"

// Matching TO on a MonthlyStats table must have the same formula:
MonthlyStats::ClientId & "|" & MonthlyStats::Year & "|" & MonthlyStats::Month
// Relationship: Invoices::MonthKey = MonthlyStats::MonthKey

FilterValues patterns and list manipulation Self-referencing calculations and recursion limit awareness