FM Function of the Week: Min and Max

What’s better than a function of the week? Two functions of the week! This week we’ll explore the (fraternal) twin functions of Min and Max. These functions return the lowest and highest value respectively among a range of fields. But the results you get have everything to do with the kind of fields you use in the calculation. We’ll look at how Min and Max behave in the two most common usage scenarios: using a set of local fields and using a single related field.

Local Fields

The term “local fields” refers to fields that are defined in the same table that your layout is tied to. It’s important to understand that when using Min and Max with local fields, it will only assess the values in the current record. Many users assume that Min and Max will look across all the records in a found set to find that minimum or maximum value, but such is not the case when using local fields.

In this example we’ve got one record with three different fields. Our charge is to calculate the minimum and maximum values that appear among them.

three fields each with a different numerical valueGiven the above values, the following calculations will resolve as shown:

Min ( valueA ; valueB ; valueC ) = 412
Max ( valueA ; valueB ; valueC ) = 1,222

Related Fields

Things get more powerful and arguably more interesting when related values are fed to the Min and Max functions. By referring to a single related field in a Min or Max calculation, FileMaker will return the lowest or highest value in that field across all related records. Take a look at the portal below where we’ve got five related records, each with three fields.

a portal displaying 5 related records with three fields each

Min ( Related::valueB ) = 6
Max ( Related::valueB ) = 512

Here, simply referring to a single field gets us the minimum and maximum values in that field across all the related records.

Bonus Round

When using related fields, it’s perfectly OK to string them together as we did in the Local Fields example. When you do that, FileMaker will determine the lowest and highest values among all the related fields and produce the single lowest or highest among all of them. To wit:

Min (Related::valueA ; Related::valueB ; Related::valueC) = 5
Max (Related::valueA ; Related::valueB ; Related::valueC) = 588
This entry was posted in FileMaker 13, Function of the Week. Bookmark the permalink.