Selecting one or more “Key” fields is an important step in setting up an ActivityInfo database with related forms. This article explains what role Key fields play in ActivityInfo, and how to select Key fields for your forms.
Introduction
Key fields play several roles in ActivityInfo:
- Key fields provide a way to label records in the user interface
- Key fields enforce record uniqueness during data entry
- Key fields help users to look up related records during data entry
- Key fields are used to match related records when importing
- Key fields indicate which 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, user-friendly 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 will fallback to using 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 will display 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 will 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 will 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 will use 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.
If you import a list of clinics, the Key Field will be 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 “MSF Trauma Centers” because they have 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 will allow 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 will be 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 will 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 birthdate in a small population.
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 will assign a sequential number to each new record.
This Serial Number will then be the form’s only Key Field and will also be 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.