Description
The FIRST function finds the earliest occurrence of a value in a subform’s field when sorted by another field in the same subform.
Usage
FIRST([Subform field].ValueField, [Subform field].SortField)
- Subform field: The subform containing the fields
- ValueField: the field whose value to find
- SortColumn: the field containing the values on which the sorting will be based
Examples
Tracking household health status over time
Suppose you are tracking the number of sick family members across households over time. You have a parent form called "Households" and a subform "Health Check-ins":
| Family | Health Check-ins |
|---|---|
| Ahunna | 2 |
| Bilal | 2 |
The subform contains a list of all the health check-ins, along with the date of the check-in.
| Family | Number of sick family members | Date of reporting |
|---|---|---|
| Ahunna | 3 | 2023-01-15 |
| Bilal | 1 | 2023-02-10 |
| Ahunna | 4 | 2023-01-20 |
| Bilal | 2 | 2023-01-15 |
You can add a calculated field to the parent household form shows the earliest reported number of sick family members for each family as a baseline using the following formula:
FIRST([Health check-ins].[Number of sick family members],
[Health check-ins].[Date of reporting])