FileMaker Pro 9: The Missing Manual

Aggregate Calculations

The min and max functions you learned about on Building the Calculation Fields are part of a collection of functions called aggregate functions. Aggregate functions work differently from other FileMaker functions when you use related fields because they look at all the related records. For example, consider this calculation:

Round(Line Items::Price, 2)

It uses the round function to round a related Price field’s value to two decimal places. If you have three related line item records, this calculation returns the rounded value from only the first record. After all, it really only makes sense to round a single value. But aggregate functions are, by their nature, all about working with lots of values. This calculation returns the largest price across all three related line item records:

	Max(Line Items::Price)

Min and max are two of the most common aggregate functions, but FileMaker offers several, from the obvious, to the obscure, to the unexpected. You can see them all by selecting "Aggregate functions” from the View pop-up menu in the Specify Calculation dialog box.

Numerical Aggregate Functions

The most basic aggregate functions operate on numbers and perform fairly common operations. The common ones include:

• Min returns the smallest of the values passed to it.

• Max returns the largest value instead.

• Average calculates the statistical average of all the values. (In other words, it adds the values, counts them, and divides the total by the count.)

• Sum adds all the values ...