Relationships
Portals, relationships, lookups, and the Relationships Graph — how FileMaker connects data.
Bring the messy part
Relationships making a weird face at you?
Book consulting when the lesson makes sense, but your actual file has opinions. We can untangle the design, debug the snag, or map the next build.
One-to-many relationships: parent, child, and foreign key
Understand the most common FileMaker relationship pattern — the one-to-many — by connecting a parent table to a child table through a shared key field.
Many-to-many relationships with a junction table
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.
Self-join relationships for hierarchical data
Relate a table to itself to model hierarchical structures like employee-manager chains, category-subcategory trees, or task dependencies.
Using global fields as relationship match fields
Leverage global (storage: global) fields as relationship match fields to create session-scoped relationships that change based on user input without modifying any record.
Multi-key relationships with return-delimited match fields
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.
Adding a portal to display related records
Place a portal on a FileMaker layout to display and edit rows from a related table, and configure its rows, fields, and basic appearance.
Filtering portal rows with a calculation
Use the portal's Filter Records option to show only a subset of related records based on a Boolean calculation, reducing noise without changing the underlying relationship.
Sorting portal rows: relationship sort vs portal sort
Control the order of portal rows by configuring a sort within Portal Setup, and understand how that differs from the sort order defined on the relationship itself.
Cascade deletes: deleting related records with a parent
Enable "Delete related records when a record in this table is deleted" on a relationship to automatically remove child records, and understand the risks involved.
Allow creation of records via a relationship
Enable "Allow creation of records via this relationship" so users can create new child records directly from a portal row, and understand what FileMaker sets automatically.
Lookup fields vs live related fields
Understand the difference between a lookup field (which copies a value at a point in time) and a live related field (which always reflects the current related value), and when to use each.
The Anchor-Buoy methodology for the Relationships Graph
Organize your Relationships Graph using the Anchor-Buoy pattern to keep it readable, predictable, and maintainable as your solution grows.
Table occurrences: what they are and how to name them
Understand that FileMaker's Relationships Graph shows table occurrences, not tables — and learn why a single table can and should appear multiple times in the graph.
Relationship-based value lists and the second-field display
Build value lists that draw values from a related table so the list stays current automatically, and use the second-field option to show a human-readable label while storing an ID.
Cross-file relationships with External Data Sources
Relate tables across separate FileMaker files using External Data Sources, allowing layouts in one file to display and edit data from another file.
The Cartesian product (×) relationship operator
Use the × (cross-join) operator in the Relationships Graph to return all records from a related table regardless of key values — useful for reporting and utility layouts.
Relationship operators: =, ≠, <, >, ≤, ≥, and range
Go beyond the default equality operator to build range-based and inequality relationships that return records matching a condition rather than an exact key match.
Primary keys and foreign keys: UUID vs serial number
Choose the right primary key strategy for your FileMaker tables — and understand why UUID (Get(UUID)) is safer than an auto-incrementing serial number.
How layout context determines related records
Understand how the current layout table occurrence and current record together define which related records are visible — and how context shifts when you navigate or run scripts.
Organizing the Relationships Graph: layout, colors, and notes
Keep the Relationships Graph navigable as your solution grows by applying a consistent visual layout, using color coding, and adding notes to document your design decisions.
Multi-predicate relationships
Define relationships with more than one match field to create precise joins that filter related records by multiple criteria.
Self-join relationships and hierarchical data
Create a table occurrence of the same table to build organizational charts, parent-child hierarchies, and next/previous record navigation.
Cartesian joins and cross-product relationships
Use the × (Cartesian) join operator to relate every record in one table to every record in another, enabling matrix lookups and global popups.
Global fields in relationships: dynamic joins
Use global fields as relationship predicates to build user-driven filters and popups that update instantly without scripted finds.
Go to Related Record: navigation and found sets
Use Go to Related Record to navigate to related data and understand how "Show only related records" controls the destination found set.
Cascading deletes and relationship delete options
Configure relationships to automatically delete related records when the parent is deleted, and understand the risks of enabling this option.
Table occurrence naming conventions
Apply consistent naming conventions for table occurrences (TOs) so the Relationships Graph stays navigable as the solution grows.
Many-to-many relationships with a join table
Implement many-to-many relationships in FileMaker using a join table and understand how to create, read, and delete join records.
Lookups vs. related calculations: choosing correctly
Understand when to copy data into a record with a lookup vs. when to display a live related field, and the long-term data integrity implications.
Relationship performance and indexing
Ensure relationships use indexed fields, understand when FileMaker creates and uses indexes, and avoid performance traps in complex graphs.
Unstored calculations across relationship chains
Understand how chain depth and unstored fields compound into slow solutions, and how to break calculation chains with stored intermediate fields.
Organizing and documenting the Relationships Graph
Apply layout, grouping, and color conventions to the Relationships Graph so complex solutions remain navigable.
Modifying related records through portals
Understand the commit behavior, locking, and validation rules that apply when users edit related records through portal rows.
Summary fields vs. aggregate calculations across relationships
Choose between Summary fields and calculation aggregate functions for cross-record totals, and understand when each approach is correct.
External FileMaker data sources
Connect to tables in a separate FileMaker file as external data sources, enabling cross-file relationships and shared reference data.
The Anchor-Buoy Method
Master the anchor-buoy layout pattern for organizing the relationship graph -- the industry-standard approach to keeping FileMaker graphs readable and maintainable at scale.
Table Occurrence Naming Conventions in Depth
Design a TO naming system that scales to hundreds of occurrences and survives team handoffs, covering prefixes, suffixes, versioning, and documentation.
Relationship Performance Optimization
Identify and fix the most common relationship-related performance problems: slow portals, unindexed match fields, and calculation fields used as relationship keys.
Avoiding and Resolving Circular Relationships
Understand why circular relationships cause infinite loops and data anomalies in FileMaker, and learn the detection and resolution strategies.
Multi-File Relationships and External Sources
Connect multiple FileMaker files as external data sources, understand the implications for relationship performance and portability, and manage cross-file dependencies.
Portal Filtering with Relationship Keys
Control which related records appear in a portal using relationship predicates, global match fields, and filtered portals -- and understand when each approach is appropriate.
Relationships and Find Mode Scope
Understand how find requests interact with relationships -- which table is searched, how related field finds work, and how to perform finds across multiple tables.
Aggregate Calculations Across Relationships
Use Sum, Count, Average, Min, Max, and List functions across relationships to compute totals, counts, and summaries without scripts or temporary records.
Conditional and Context-Driven Relationships
Build relationships that change which records they match based on context -- the current user, a date, a mode flag -- using global fields and calculation match fields.
Table Occurrence Groups and Context Management
Design table occurrence groups (TOGs) to manage multiple execution contexts in a single FileMaker solution, covering group isolation, cross-group navigation, and context switching.
Many-to-Many Relationships with Attributes
Model many-to-many relationships using join tables that carry their own attributes -- the only correct approach for relationships that have data beyond the foreign keys.
Self-Referential Hierarchies in FileMaker
Model parent-child hierarchies within a single table using self-joins, and navigate multi-level trees using recursive relationship patterns and utility scripts.
Cross-File Lookups and Reference Data
Use FileMaker external data sources for shared reference data, understanding when lookups across files make sense and how to keep reference data in sync.
Dynamic Relationship Keys with Global Fields
Build dynamic relationships that show different related records based on context by using global fields as match keys -- including multi-key globals for complex filtering.
Relationship Graph Refactoring
Safely restructure a tangled FileMaker relationship graph -- merging redundant TOs, applying anchor-buoy, and removing obsolete connections -- without breaking layouts or scripts.
Portals Within Portals -- Nested Related Data
Display multi-level related data by nesting related fields from grandchild tables inside portal rows, and understand the performance and layout limitations of deep nesting.
Relationship-Based Security
Use FileMaker relationships and privilege sets to enforce row-level data security, limiting which records each user can see and edit based on their account and group membership.
Optimizing Portal Performance
Measure and improve portal loading times by tuning relationship predicates, indexing, row limits, sort orders, and layout object counts.
Enterprise Data Modeling in FileMaker -- Relationship Design
Apply entity-relationship modeling principles to FileMaker solutions at enterprise scale, covering normalization, surrogate keys, and graph organization for hundreds of tables.
Complete Relationship Design Review
Audit and score a FileMaker relationship architecture against a comprehensive checklist covering normalization, naming, performance, security, and maintainability.
Enterprise Data Modeling with FileMaker
Apply entity-relationship theory, domain-driven design, and bounded contexts to FileMaker solutions serving hundreds of users and millions of records.
Normalized vs. Denormalized Design in FileMaker
Evaluate when to normalize FileMaker data for integrity and when to deliberately denormalize for query performance, reporting speed, or API simplicity.
Migrating Legacy Databases to FileMaker Relationships
Plan and execute a migration from a flat, denormalized legacy FM database (or external data source) to a properly normalized relational structure.
Relationship Design for Reporting
Design FileMaker table structures and relationships that support efficient sub-summary reports, summary fields, and dashboard aggregations without scripting.
Temporal Data Patterns -- Effective Dates in FileMaker
Model time-varying data with effective dates, track record history, and query "as of" states using FileMaker relationships and calculation fields.
Versioning Records via Relationships
Implement record versioning in FileMaker -- tracking every change to a record over time -- with efficient storage, retrieval, and comparison of historical versions.
Managing Relationship Graph Complexity
Techniques for keeping a large FileMaker relationship graph readable and maintainable as it grows -- visual organization, graph audits, and complexity metrics.
Relationship Documentation
Create and maintain comprehensive documentation for a FileMaker relationship architecture -- within the solution itself, in external tools, and in team knowledge bases.
Multi-Tenant Data with FileMaker Relationships
Design a multi-tenant FileMaker solution where different organizations share the same database while keeping their data completely isolated.
Complete Relationship Design Review -- Synthesis
Apply all relationship concepts in a comprehensive final review, assessing a FileMaker solution's data architecture for enterprise readiness.