Aggregate functions: Sum, Average, Count, Max, Min in context

Intermediate

Apply FileMaker's aggregate functions correctly by understanding how context, table occurrences, and empty values affect their results.

What you'll learn

How each aggregate function handles empty values
Why Count() and CountValues() produce different results
How to use a second table occurrence for a filtered aggregate
How aggregate functions behave differently in a parent vs. portal context

Sum, Average, Count, Max, and Min are FileMaker's built-in aggregate functions. In a calculation field, they operate across all related records in a table occurrence matched by the current record's relationship. Their behavior is determined by which table occurrence you reference, which records are related, and how empty values are handled.

1/4

Aggregate function reference

Each aggregate function operates on all non-empty values in the referenced field across related records.

FileMaker Script

// All of these operate on LineItems related to the current Invoice:
Sum     ( LineItems::Amount )    // total of all non-empty Amount values
Average ( LineItems::Amount )    // mean of non-empty Amount values
Count   ( LineItems::id )        // count of records where id is non-empty
Max     ( LineItems::Amount )    // largest Amount value
Min     ( LineItems::Amount )    // smallest Amount value

GetSummary() across relationships Date calculations: age, duration, and working days