Many-to-many relationships with a join table

Expert

Implement many-to-many relationships in FileMaker using a join table and understand how to create, read, and delete join records.

What you'll learn

The join table structure for a many-to-many relationship
How to create and delete join records from both sides
How to display related records through a join table in a portal

A direct many-to-many relationship (e.g., students can enroll in many courses; courses can have many students) cannot be expressed in a single relationship in FileMaker. The standard approach is a join table that stores one row per pair, with foreign keys to both parent tables.

1/3

Join table structure

Create a join table with at minimum: a primary key id, a foreign key for table A, and a foreign key for table B. Additional columns can store relationship metadata (date enrolled, role, etc.).

FileMaker Script

// Tables:
// Students (id, Name)
// Courses (id, Title, Code)
// Enrollments (id, StudentId, CourseId, EnrolledAt)

// Relationships:
// Students → Enrollments: Students::id = Enrollments::StudentId
// Courses → Enrollments: Courses::id = Enrollments::CourseId

// To show a student's courses: portal on Students via
// Students → Enrollments → Enrollments_Courses

Table occurrence naming conventions Lookups vs. related calculations: choosing correctly