Multi-key relationships with return-delimited match fields

Expert

Use a return-delimited list of values as a relationship match field to relate one record to multiple records at once without a junction table.

What you'll learn

How FileMaker evaluates a return-delimited match field in a relationship
When multi-key relationships are appropriate and when they are not
How to build and maintain a multi-key field safely

When a relationship match field contains a return-delimited list of values, FileMaker treats each line as a separate key and returns every record that matches any of them — a powerful but dangerous pattern.

1/3

Understand how the multi-key is evaluated

If a match field contains "101¶104¶107", FileMaker runs the equivalent of: WHERE RelatedTable.ID IN (101, 104, 107). Every record whose key matches any line is included in the related set.

FileMaker Script

# Invoices table
ProjectIDs   — Calculation (text):
  Projects::ProjectID & ¶ &
  Projects_2::ProjectID & ¶ &
  Projects_3::ProjectID

# Relationship:
Invoices::ProjectIDs ──────────────< Timesheets::ProjectID
# Returns all Timesheet records where ProjectID matches ANY
# of the three project IDs stored in the Invoices::ProjectIDs field

Using global fields as relationship match fields Adding a portal to display related records