Multiple reference fields store a reference to multiple Records in another Form.
Multiple reference fields are similar to Reference fields in a way that both Field types create relationships between Records. Multiple reference fields store a reference to multiple Records in another Form.
For example, your organization needs to aggregate project results for donors that contribute to specific projects. In your organization, donors can fund multiple projects, and each project can be funded by multiple donors. In your _Projects _ form, you want to see each project being connected to all the donors that fund that project. This is a many-to-many relationship, that can be set up using Multiple reference fields.
Design
When adding a Multiple reference field, you must select the Form to reference. After adding the reference field, you cannot change the referenced Form.
To add a Multiple reference field, follow these steps:
- Navigate to your Database.
- Click “Database settings.”
- Select the Form to which you want to add the Multiple reference field. If the Form is in a Folder, click on the Folder to reveal the Forms it contains.
- Click on the “+” to reveal the available Field types.
- Select “Multiple reference.”
- Select a Form to reference and click “Continue.”
- Fill in the required fields and settings and click “Done.”
Data entry
During data entry, the user is provided with one or more dropdown fields in order to select the record from the Multiple referenced field (in this example, “All the cities you have lived in”). The data entry Form shows the specified Lookup field as well. The lookup field may be the Key field or some other Field, depending on customization.
When adding a Record with a lookup field, there is a column displayed in the Table view for each lookup field that has been specified.
Formulas
The Multiple reference field consolidates all the associated Records into a set. You can then perform various calculations on this set of associated Records.
Imagine you have a Form titled Individuals that contain information about the countries an individual has lived in. In this Form, a Multiple reference field linked to the Countries form has been added to allow individuals to be associated with multiple countries. You can do a few calculations on this data set.
First, you can aggregate values in the referenced records by using functions like COUNT. For example,COUNT(Countries.code) will count the code values across all the countries referenced by a record for an individual. This formula will therefore return the total number of countries that an individual has lived in like in the table below.
Another calculation you can do is test to see whether a certain value is contained among the set of associated records. To determine whether an individual has lived in a specific country, you can use the following Formula:
ISNUMBER(SEARCH("Venezuela", TEXTJOIN(", ", TRUE, Countries.name)))
This Formula returns results like this:
The following actions are not possible with Multiple reference fields:
- Setting a default value
- Adding new reference records in-place when entering data
- Setting a custom lookup field