Many-to-many relationships with a junction table

Intermediate

Model many-to-many data — such as students enrolled in courses — using a junction table that turns one complex relationship into two simple one-to-many relationships.

What you'll learn

Why direct many-to-many relationships are not possible and how the junction table solves this
How to design and connect a junction table
How to add fields to the junction table to store relationship-specific data

A many-to-many relationship cannot be expressed directly in FileMaker — you need a junction (join) table that sits between the two main tables and holds one record per association.

1/3

Design the three tables

Create the two main tables and a third junction table. The junction table holds two foreign keys — one for each main table — plus any fields that describe the association itself.

FileMaker Script

Students                     Enrollments (junction)      Courses
──────────                   ──────────────────────      ──────────
StudentID ───────────────── StudentID (FK)              CourseID ─────────────── CourseID (FK)
Name                         CourseID  (FK)  ────────── Title
Email                        EnrolledOn                 Credits
                             Grade
                             EnrollmentID (PK)

One-to-many relationships: parent, child, and foreign key Self-join relationships for hierarchical data