Self-join relationships for hierarchical data

Intermediate

Relate a table to itself to model hierarchical structures like employee-manager chains, category-subcategory trees, or task dependencies.

What you'll learn

How to create a second table occurrence of the same table for a self-join
How to model a manager-employee hierarchy with a ParentID field
How to navigate up and down the hierarchy in scripts and portals

A self-join connects two table occurrences of the same underlying table, letting each record point to another record in the same table as its parent or child.

1/3

Add a ParentID field to the table

Add a field that stores the primary key of the record's parent in the same table. For top-level records, leave it empty.

FileMaker Script

# Employees table
EmployeeID   — Text, auto-enter: Get ( UUID )
Name         — Text
Title        — Text
ManagerID    — Text  (foreign key pointing to another Employees record)

Many-to-many relationships with a junction table Using global fields as relationship match fields