Key Field is a setting you can select when designing a Form. This article explains the role of Key fields in ActivityInfo, and how to select Key fields for your Forms.
Selecting one or more Key fields is an important step in setting up your Database with related Forms.
Introduction
When designing a Form, you can add Fields with special settings. When you mark them a Field as Key field in your Form, it provides the following benefits:
Key fields play several roles, including:
- Providing a way to label records in the user interface
- Enforcing record uniqueness during data entry
- Helping users to look up related records during data entry
- Matching related records when importing
- Indicating to which fields date-based locks apply
We strongly recommend selecting at least one key field for each form you design.
Without key fields
Each record in ActivityInfo is automatically assigned a unique, auto-generated ID that never changes. The Record ID is used to store the relationship between two records, and is essential to preserving referential integrity, but is not very user friendly. Key Fields are a way for form designers to provide alternative, easy-to-read identifiers for records.
Record IDs are normally hidden from the user, but you can display them in the Form View by clicking “Select Columns” and then checking “Record ID”:
If you don’t select a Key Field, then ActivityInfo uses the Record ID, which may confuse your users. For example, if you reference the Province form without selecting a Key Field for the Province Form, you’ll have to select a Province by finding its Record ID:
This is probably not what you want.
Selecting a Key Field
When you select a Key Field in your Form, you provide a readable alternative to the Record ID for your form. The Record ID is still present, but ActivityInfo displays the Key Field to users instead.
For example, if you have a Province form with a list of provinces in Afghanistan, the “Name” field is the obvious choice for a Key Field:
No two provinces have the same name, so selecting the Name field as a Key Field is a good choice. If you try to add a record with same value for the “Name” field as an existing record, you receive an error message:
In addition to duplicate checking, the Key field is used by ActivityInfo to “name” each record. For example, if you select multiple records, ActivityInfo uses the value of this field to show you which records have been selected:
Key fields become even more important when you reference this Form in another Form. For example, if I have a Clinic Form with a Reference Field referring to the Province Form, the Province’s Name field will be used for the dropdown in the data entry form:
The Key Field is also displayed in the table view instead of the Record ID to indicate in which Province each Clinic is located.
By default, key fields are used to lookup values in the reference Form. Alternatively, you can select the fields you wish to use for lookup or add your own custom formulas. For more information, see Adding a Reference field.
If you import a list of clinics, the Key Field is used to find the Record ID of the Province to match:
Multiple Key Fields
Often, there is no single field that uniquely identifies a record. In our example of the clinics above, imagine that there are two clinics with the name “MSF Trauma Center”: one in Kunduz, and one in Kabul:
In this case, we can’t make the Clinic’s Name Field the only Key Field, because then ActivityInfo wouldn’t let us add the two records with the same name, even though they are in different provinces.
For this case, we should make both the Name and the Province field Key fields:
Now, ActivityInfo allows any unique combination of the two Key fields. For example, I would be able to add another “MSF Trauma Center” in Nuristan province, but not another “MSF Trauma Center” in Kabul because that combination already exists.
If we had a third form that referenced Clinics, such a “Supply distribution”, then both the Name and the Province field would be included as cascading lookup fields:
Both Key Fields are displayed in the Table View as well. Even though “Clinic” is a single Field, it will be displayed by default as two columns in the Table View because it has two Key Fields.
Serial Number Fields
In some cases, there is no combination of fields that can uniquely identify a record. For example, when tracking a small group of beneficiaries, using First Name, Last Name, and Birth Date fields as Key Fields might be sufficient as it is unlikely to find someone with the same name and date of birth.
However, as the population grows, this becomes untenable. You will eventually find two “John Smiths” with the same birth date.
You may still want a user-friendly way to label and identify records. In this case, you can add a Serial Number field, which assigns a sequential number to each new record.
This Serial Number is then be the form’s only Key Field and is also used for looking up records. For example, if we had a “Health Checkup” form that references the Beneficiary form, we would need to select a Beneficiary ID:
It is not possible to combine a Serial Number field with other Key Fields in the same form. However, you can use Serial Number Prefix formulas to include meaningful information in the code to make the serial numbers easier to work with.