The FIRST function finds the earliest occurrence of a value in a subform’s field when sorted by another field in the same subform.


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


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])
Next item
OR (||)