Description
The MAX function finds the maximum value in a given set of values. The given set of values can be Fields in a Form, or a Field in related records (i.e. when using a Subform or a Reverse Reference field).
Usage
MAX(VALUE1, VALUE2, VALUE3, ...)
MAX(SUBFORM_FIELD.FIELD_NAME)
MAX(REVERSEREFERENCE_FIELD.FIELD_NAME)
Remarks
When used with Subform or Reverse Reference fields, the argument to MAX() must include a reference to the form's Subform or Reverse Reference field and then the field within the related Form, using the dot notation.
The MAX function accepts arguments with the following types:
- Number
- String
- Instant
- Local date
- Week
- Month
When comparing text, the comparison is case sensitive. That means that "ABC" does not equal "abc".
Please note that the way the aggregation is performed heavily depends on the type of the input.
For example, MAX(2000, 300) will return 2000 because the arguments are in a Number type. However, MAX(“2000”, “300”) will return 300 because the arguments are in a String type and are compared alphabetically.
Examples
Find the date of the last project update
The Indicators Tracking with Global M&E database template has a Projects form with a Monthly reports sub form.
You could add a calculated field to the Projects form to find the latest monthly report for each project using the following formula:
MAX([Monthly reports].Month)
This gives you an overview of the reporting progress per project:
