Aggregate functions in related and portal contexts

Expert

Use Sum, Count, Average, Min, and Max across related records and understand the difference between portal-row context and parent-record context.

What you'll learn

How aggregate functions resolve when placed on the parent vs. portal-row layout
How to distinguish between portal aggregate and all-records aggregate
How to use a second table occurrence to aggregate a subset of related records

FileMaker's aggregate functions (Sum, Count, Average, Min, Max) operate across all records in a related set when given a field reference from a related table occurrence. The result depends on which table occurrence context the calculation lives in — a detail line sees the whole set, but a portal row sees all rows, not just itself.

1/3

Aggregate from the parent record

A calculation in the Invoices table that references LineItems::Amount will aggregate across all related line items for the current invoice. This is the most common use.

FileMaker Script

// In an Invoices calculation field:
Sum ( LineItems::Amount )      // Total of all line items
Count ( LineItems::id )        // Number of line items
Average ( LineItems::Amount )  // Mean line item amount

Date arithmetic and calendar calculations Stored vs. unstored calculations: performance impact