Reference fields store a reference to a record in another Form. Reference fields allow you to define many-to-one relationships between forms. An individual registration Form might have a Field that references the household to which they belong, or to the village in which they live.
Design
When adding a reference field, you must select the Form to reference. After adding the reference field, you cannot change the referenced Form.
By default, the value that is used to lookup referenced records is the Key field, if it has been set.
Optionally, you can configure what fields on a reference form may be useful for lookup. These may or may not be Key fields, or may be just a subset of Key fields.
To enable and configure the Lookup fields feature, follow these steps:
- Navigate to the Database settings.
- Click on the Form you wish to add a lookup field to.
- Click on the Field.
- Within the Field settings, scroll down to the Lookup fields section and toggle the “Use custom lookup fields” button.
You can also create a lookup formula. To do so, follow these steps:
- Click the “Create formula lookup field” button.
- Add your own custom Formula in the Formula designer.
Data entry
Desktop
During data entry, the user is provided with one or more dropdown fields in order to select the record from the Referenced field. The data entry Form shows the specified Lookup field(s). The lookup field may be the Key field or some other Field, depending on how the Form designer customized it.
When adding a Record with a lookup field, there will be a column displayed in the Table view for each lookup field that has been specified.
Record IDs vs Lookup Fields
Every record stored in ActivityInfo is assigned a unique, immutable identifier (ID) that look like c57rfl7lqqqfwio7 or cillbi2lqr48es8q .
During data entry, users are shown the record’s lookup fields rather than this ID (if a lookup field has been set), to help them select a Record. If either a Key field or a custom Lookup field has been set (even without the referenced form having a Key), then users are shown the corresponding field rather than the ID.
However, it is the record ID that is actually stored as the value of Reference fields. This ensures that even if a record’s Key field is changed, for example, a spelling mistake in a name is corrected, the link between the two records is not broken.
Note: If the referenced Form does not have any lookup fields set, then the record ID is used as the lookup value.
Importing
When importing records with a Reference field, the importer expects a column for one or more Key fields.
Matching is case insensitive, but otherwise must be exact.
If multiple records in the referenced Form match, the importer considers it an error.
Formulas
In formulas, the value of a Reference field is the autogenerated Record ID.
In the example above, the formula CONCAT([Last Name], Location) might evaluate to "Smith c57rfl7lqqqfwio7".
However, you can the dot notation to reference more useful fields in the selected record. For example, the formula CONCAT(Location.Name, ", ", Location.Province.Name) might evaluate to “Khanabad, Kunduz”.