Relationship Design for Reporting
BeginnerDesign FileMaker table structures and relationships that support efficient sub-summary reports, summary fields, and dashboard aggregations without scripting.
What you'll learn
- How summary fields work and which tables they belong in
- How to design relationship keys that enable sub-summary grouping
- How to use the Virtual List technique for custom reports
- How to design aggregation-friendly table structures
FileMaker's native reporting features -- sub-summary parts, summary fields, and chart objects -- require specific data structures and sort orders to function correctly. Designing the relationship structure with reporting in mind from the start avoids expensive data restructuring later. This lesson covers the relationship patterns that unlock FM's native reporting capabilities.
Summary fields and table placement
Summary fields aggregate values across the found set in a table. They are always placed in a sub-summary part, which groups by a sort field. The summary field and the layout's anchor table must be the same for native sub-summaries to work.
// Summary field: Invoices::sTotal = Total of Invoices::Amount // This field returns the total of all invoice amounts in the current found set, // OR the total for the current sub-summary group when in a sorted sub-summary report. // For a report that groups invoices by territory: // 1. Perform Find [ all invoices for the period ] // 2. Sort by Territory, then by InvoiceDate // 3. Navigate to Invoice Report layout (sub-summary by Territory) // -> Each territory group shows sTotal for just that territory
Sign in to track your progress and pick up where you left off.
Sign in to FM Dojo