Record context

Formulas can be evaluated in the context of a form record. This includes:

• Calculated field formulas
• Serial number prefix formulas
• Relevance rules
• Validation rules
• Record locks
• Record-level permissions

In each of these cases, the formula is evaluated in the context of each of the form's records. In this context, you can refer to the values of the record's fields, related records and sub-records, and metadata related to the record.

Since the formula is calculated record-by-record, this context is not where you want to calculate aggregates like the percentage of participants who are women. Such statistics can be calculated using pivot tables.

Field values

In a record context, you can refer to fields, using the field code, the field's label, or the field's internal id and the symbol syntax. A symbol is written in formulas without quotes.

For example, if you have a text field with the label "Number of students" and code NUM_STUDENTS, then you could calculate the required number of teachers, assuming a desired ratio of 30:1, using any of the following formulas:

• Students / 30
• NUM_STUDENTS / 30
• csr4lu5lfl1n0swr / 30

The text csr4lu5lfl1n0swr is the field's internal id assigned by ActivityInfo, which can be found through the API.

If a field's label contains spaces or other special characters, the symbol must be escaped using brackets. For example, if the field above had the label "Number of students", then you must refer to it as [Number of students] in the formula.

Note that field labels are not required to be unique within a form. It is common to have multiple fields with the label "Other", for example. If you refer to a field by label and there is more than one field with this label, the value of the first field will be used.

For this reason, we recommend assigning codes to all fields if you plan to use formulas.

Text fields

Text, Multi-line, Serial Numbers and Barcode Fields all have a text formula type in the record context.

Select fields

Single- and multiple-select fields have a formula type of "struct" and struct list respectively.

If your form includes reference fields, you can use include the values of fields in related records in formulas.

Reference fields

When you add a reference field to a form, ActivityInfo stores the built-in record ID of the selected record as part of the field.

For example, let's say that you add a reference to the Afghan District form in the public Geodatabase with the code DISTRICT . During data entry or import, ActivityInfo will present the key fields of the related record to the user to help them select the correct record:

However, when the record is saved, ActivityInfo actually stores the record id of the related record. This ensures that even if the name of the district changed, the relationship between the two records is preserved.

If you reference DISTRICT in a formula, the result will be the record id, like "z0000454985":

Related fields

In most cases, the related record id will not be very useful. Instead, you might want to reference the district's name, or its population. For this, you can use the dot notation to look up the related value:

DISTRICT.NAME

If the related form also has reference fields, you can use multiple dots to follow the path. In this case, the District form has a PROVINCE reference field with its own NAME field. To find the name of the related Province, you could use the formula:

DISTRICT.PROVINCE.NAME

Geographic Point Fields

Geographic Point fields have a "struct" formula type. This means that each Geographic Point has two values: latitude and longitude. You can refer to these individual components using dot syntax:

CONCAT(
  IF(LOCATION.Latitude > 0, "North", "South"), " ",
  IF(LOCATION.Longitude > 0, "East", "West"), " 
Hemisphere")

Subform Fields

While Susbform fields do not themselves have a value, you can refer to sub record fields in the record context, as long as they are aggregated.

For example, if you have a HOUSEHOLDS_MEMBERS subform with the fields NAME and AGE, you cannot simply refer to HOUSEHOLD_MEMBERS.NAME , because there may be more than one name. You can however, use aggregate functions like COUNT, SUM, AVERAGE, MIN, and MAX to compute a single value from all of a record's subrecords. For example, you could find the number of members using the formula:

COUNT(HOUSEHOLD_MEMBERS.NAME)

And you could test for the presence of a child under five using the formula:

MIN(HOUSEHOULD_MEMBERS.AGE) < 5

Record metadata

In the record context, you can also refer to metadata about the record:

Note that the _lastEditTime refers to the last time that an update reached the server. A record may have been added or edited offline much earlier.