This article introduces the concept of related records and referential integrity, and describes how ActivityInfo enforces this referential integrity.
Introduction
Reference fields allow you to reference one record from another record in a different form. For example, a record describing a school might reference a “Province” form, indicating its location. This creates a relationship between the School and the Province forms.
When you start adding records to the School and Province forms, you also create relationships between schools and provinces records.
In the diagram above, each school references a single province. When a record references another record, we say that the two records are related.
Referential integrity is a property of a data that states that all references are valid. This means that whenever a record references some value, there must be a reference record that exists that contains that value. In the diagram above, we can see that each school has a valid reference to a province. Thus, the two forms have referential integrity.
Subform fields also create relationships between parent and child records.
Editing records
Editing a Record in ActivityInfo does not affect referential integrity. For example, renaming the “North” province to “Northern”, does not affect the relationship with the “Kirby” and “Wellsboro” schools. This is because ActivityInfo uses an internal, autogenerated and immutable Record ID to store the relationship between records.
These internal IDs are generally hidden from the user. Instead, ActivityInfo displays the referenced record using the Key Fields of that form. However, you can display the Record ID in the Table View by clicking Select Columns and checking “Record ID”.
Deleting parent records
When you delete a record from a form that has a subform, all child records are automatically deleted, preserving referential integrity.
When you recover a deleted parent record, all of its child records are recovered as well.
Deleting referenced records
Deleting a record that is referenced by other records would compromise referential integrity. If you deleted the “South” province, then the Dalton school would no longer have a valid reference to a Province. Your data would be incomplete, and you would no longer be able to know where the schools are located.
For this reason, ActivityInfo will generally not allow a record to be deleted when it is referenced by other records.
On the other hand, the “East” province is not referenced by any of the schools, so it can be deleted without compromising the referential integrity of the two forms.
Exceptions
There are a few cases where ActivityInfo cannot or does not enforce referential integrity.
Working offline
ActivityInfo is not able to fully enforce referential integrity when data is collected or deleted offline.
For example, if a field worker adds a new school record that references the “East” province while disconnected from the server, an administrator might delete “East” province at the same time. Since there are no records on the server that reference the “East” province, ActivityInfo allows the record to be deleted.
However, when the field worker arrives back to the office after a week in the field and synchronizes, the new record will arrive to the server with a deleted reference to the deleted “East” province, violating referential integrity.
In this case, referential integrity can be restored by recovering the deleted “East” province.
References between databases owned by different billing accounts
ActivityInfo allows forms to reference forms in other databases, even in databases owned by other organizations with different billing accounts . This is a key feature that supports humanitarian coordination: many different organizations working in the same geographic area can each reference common operational datasets to support coordination.
For example, one organization, Organization A, might collect data on schools in region. A partner Organization B, might then add their own ActivityInfo database and conduct an assessment of these schools. Since the organizations are working together, Organization B references the Schools Form owned by Organization A:
In this case the School Assement Form and the School Form are owned by two different organizations. Referential integrity remains important, but so is the data owner’s right to control their own data.
If an administrator from Organization A tries to delete the school “Dalton”, they will be warned about the consequences on Organization B’s data, but they will still be allowed to proceed with the deletion.
Database Owners
Database owners are exceptionally allowed to delete records that are referenced by other records, even if this compromises referential integrity.
API
ActivityInfo’s API does not currently enforce referential integrity for referenced records.
Deleting a parent record through the API automatically deletes its subrecords as well, however.