Self-Referential Hierarchies in FileMaker

Beginner

Model parent-child hierarchies within a single table using self-joins, and navigate multi-level trees using recursive relationship patterns and utility scripts.

What you'll learn

How to create and use a self-join TO
How to model a parent-child tree with fixed depth using multiple self-join TOs
How to write a recursive script to traverse an arbitrary-depth hierarchy
When to consider flattening the hierarchy with a computed path field

An employee table where each employee has a ManagerID pointing to another employee's EmployeeID is a self-referential hierarchy. FileMaker handles one level of this well with a self-join, but multi-level traversal (grandparent, great-grandparent) requires either multiple self-join TOs or a recursive script. Understanding the limits of the self-join approach and when to reach for scripting is the key skill.

1/4

The self-join TO

Create a second TO of the same table and define a relationship between the two. This gives you a "parent" view and a "children" view of the same table.

TEXT

// Table: Employees
// Fields: EmployeeID, Name, ManagerID (FK -> Employees::EmployeeID)

// Self-join TOs in the relationship graph:
// Employees (anchor)
// Employees_Manager    -> Employees::ManagerID = Employees_Manager::EmployeeID
//                        (the manager record for any given employee)
// Employees_DirectReports -> Employees::EmployeeID = Employees_DirectReports::ManagerID
//                          (all direct reports for any given employee)

// On Employees layout:
// Employees_Manager::Name shows the employee's manager
// Portal of Employees_DirectReports shows all direct reports

Many-to-Many Relationships with Attributes Cross-File Lookups and Reference Data