00:00:00 Introduction and poll

Welcome everyone. We value your feedback from our previous webinars, and we are hoping that we can get some more feedback to better understand your needs and determine the extent and nature of support that we can provide to you. Please take a moment to answer the questions appearing on your screen. They will help us tailor our support to meet your requirements. We want to know how you use ActivityInfo, how often you use subforms, and how familiar you are with the relational database model. Lastly, we want to understand if you feel you have optimally utilized the reverse reference field feature, which is one of our latest features from 2023.

I see that some of you design forms and databases, some just report data, some analyze, and some do not use ActivityInfo yet. We are glad that you are able to join. We see a number of people who are currently using subforms in their data model and those who feel they have not explored it to its full potential. Hopefully, at the end of this session, you will understand the reasons why you should use some of the relational models within ActivityInfo or the features that enhance the relational model.

00:02:13 Webinar agenda

We are going to be exploring how to establish relationships in ActivityInfo and the differences between subforms and reference forms. For those who mentioned that they are already using subforms, we are also going to explore reference forms and how they differ in their usage. We will discuss their context, common use cases, and best practices. Hopefully, you gain knowledge that will help you rethink how you can apply the relational data model features available to you in ActivityInfo.

00:03:07 Understanding relationships in ActivityInfo

Generally, ActivityInfo supports a relational database model which allows you as form designers or administrators to create links or associations between data sets in different forms. There are two methods available to link data sets in your database: reference fields and subforms.

Reference fields allow you to create links between weakly related data sets. Here, records from one form may reference records in other forms without necessarily owning them. Reference fields have the flexibility to support a number of use cases where you are dealing with loosely coupled data.

Subforms allow you to create links between strongly related data sets where a parent record may own multiple child sub-records. Subforms are powerful in the sense that they help you keep data integrated and coherent.

00:05:30 Context and use cases for subforms

A subform is simply a form within a form. ActivityInfo offers a highly adaptable form designer that enables you to create diverse structures to meet your project and organizational needs. A subform is nested within another form; it is a child to a parent form. It is possible to include subforms within subforms. You can add up to 30 subforms in a single form, though the maximum depth level for subforms is limited to ten.

Subforms serve the purpose of enabling data collection that requires constant monitoring over a period of time or collecting data multiple times around a particular attribute in a project. It can be utilized to group various types of information that need to be collected together.

Let's look at some common use cases. In a humanitarian context, subforms can help monitor relief distribution. For instance, if an organization is distributing relief items like food, water, and shelter across different communities, a subform can be used within the main distribution form to track multiple rounds of distribution to the same community or household over time. This subform can capture details such as the date, the month, the items shared, the quantity, and beneficiary details.

In healthcare service provisions, a subform can be employed to record multiple beneficiary or patient encounters, either within a single visit or on a recurring basis. Within a main form capturing general patient information and medical history, a subform can document each consultation, including vital signs and diagnoses, allowing you to monitor health status over time.

For education program monitoring, a subform can document various activities conducted within each program session. Within a main form capturing overall program attendance and demographics, a subform can log details of each session, such as the number of participants, topics covered, materials used, and feedback.

Finally, in community needs assessments, a subform can assist in collecting detailed information about various needs identified within a community to monitor changes in vulnerability over time. Rather than creating endless columns in one form, you use subforms to collect information in a structured manner.

00:12:10 Relevance rules in subforms

Relevance rules can be applied to subforms similar to other fields within the parent form. Advanced rules can even be created using formulas. Relevance rules allow you to customize when a field will appear in the data entry page based on specific conditions. This saves time and avoids confusion for users adding records.

For example, if I ask "Do you cultivate maize?" and you say "No," the subform related to maize cultivation is hidden. If you say "Yes," the subform becomes available. If a subform field satisfies the relevance rule, it will be displayed. If it does not meet the rule but contains existing records, it will remain visible but marked as invalid. If it does not meet the rule and has no existing records, it will be hidden. Note that records are not automatically deleted when a subform becomes irrelevant; they are marked as invalid, and you must acknowledge a warning to delete them.

00:15:35 Best practices for subforms

For those who feel they have not optimized the subform feature, here are some best practices. First, maintain a clear and consistent structure to ensure ease of data entry and interpretation. Second, avoid excessive nesting or overly complex structures. If a subform within a subform within a subform doesn't serve a real purpose, it creates challenges in data management and analysis. Limit the depth and prioritize simplicity.

Third, use purposeful segmentation. Subforms should logically segment related data within a main form, allowing for a focused approach to specific themes or categories. Each subform should serve a distinct purpose and contribute to the overall data collection objective, facilitating analysis and report writing.

00:18:16 Understanding reference forms

Reference forms are designed to contain reference data, allowing for the connection of forms to other forms or the built-in geographic database within ActivityInfo. Reference data refers to fixed or slowly changing data used within your project to provide a standard point of reference. This could include locations, partners, lists of countries, or lists of caseworkers.

Reference forms store information known in advance that is unlikely to change quickly. They are useful for storing information needed across multiple data collection forms. Instead of generating a long list of selection options in every form—for example, entering 70 provinces manually in multiple forms—you enter that data once in a reference form and link it using a reference field.

A key advantage is the ability to make corrections. If you make a mistake in a province name, you edit it once in the reference form, and it reflects automatically across all linked forms.

00:22:09 Key fields and serial numbers

To establish a link between a record of one form and another, it is essential to utilize a common reference. This is achieved through key fields or serial number fields. Key fields serve as a unique identifier for each record. By designating certain fields as key fields in the source form, you can establish a connection between corresponding records.

In addition to using a reference field, you can employ multiple key fields in the reference form to create a cascading effect. This allows you to capture and display relevant information associated with each option, providing a more comprehensive user experience.

00:23:44 Reference fields vs selection fields

Both reference fields and selection fields create lists of options, but they have different functionalities. A selection field allows you to create a list of options directly within the form itself. A reference field is used to reference another form where each option is a separate record.

Reference fields allow you to include extra details about each choice. However, to capture a basic list of options that do not require additional information or are not lengthy—such as age category or biological sex—it is recommended to use a single select field.

00:25:45 Choosing between subforms and reference forms

To summarize: use a subform when you are trying to create a parent-child relationship where the data is dependent on another entity (e.g., information about a person's children).

Use a reference form if the data is known in advance, needs to be managed centrally, is referenced across multiple forms, is shared among multiple entities, or is used for categorization. Reference forms are best for managing long lists of data and ensuring updates are reflected globally.

00:28:00 Case study: Tracking aid delivery

Let's look at a case study where we are tracking aid delivery to beneficiaries in a crisis intervention following a natural disaster. A humanitarian organization is tasked with tracking aid delivery to affected households.

We have specific analysis requirements: a long list of caseworkers, a list of geographical locations, and partners used across multiple forms. We want to collect details about the head of the household, their vulnerability, and displacement situation on a monthly basis. We also want to differentiate between displaced and extremely vulnerable beneficiaries versus those who are not displaced but vulnerable to determine appropriate follow-up.

The approach is to use reference and subforms and implement relevance rules. This will enable automatic differentiation of beneficiaries.

00:30:00 Data model overview

In our data model, we have reference forms for Caseworkers (capturing names and contact info), Countries (capturing locations), and Partners. In the main form, the "Crisis Intervention Model Vulnerability Assessment Tool," the main entity is the Head of Household.

We have a subform for "Vulnerability Assessment" to gather information about vulnerability attributes. We have a second subform for "Displacement Assessment" to collect details about displacement status. We apply a relevance rule here: only those who are displaced and extremely vulnerable will see the displacement assessment subform. Those who are not displaced but vulnerable will be saved the confusion of filling out an irrelevant form.

00:32:21 Demo: Database structure and data entry

Let's look at the database. We have the reference data folder containing Caseworkers, Country, and Partners. In the Caseworker form, the "Name" is a key field. In the main form, we have fields referencing these forms.

When adding data, if we select a specific location and residence type, we can save the record. Regarding the relevance rule: if we select "Household at risk of displacement" or "Household displaced in the past 12 months," the system shows the option to add a record in the Displacement Assessment subform. However, if we select "Head of house not at risk of displacement," the Displacement Assessment subform is hidden, and only the Vulnerability Assessment form is visible.

00:36:26 Reverse reference fields

A reverse reference field enables you to keep track of where form records have been referenced in other forms. This allows you to maintain visibility into the connections between data across forms. Unlike other fields, reverse reference fields need to be intentionally added.

You use this to navigate from one form to another to access related information, aid analysis, and report on data relationships. For example, in our Caseworker reference form, we can add a reverse reference field to see all the records in the main Vulnerability Assessment form that have referenced a specific caseworker. This allows us to easily navigate to the records associated with that caseworker.

00:38:26 Duplicating reference datasets

The duplication feature allows you to easily recreate existing relationships in a new database. You can duplicate the entire database or specific forms and folders. This is useful if you have built a reference form in Phase 1 of a project and want to use the same reference forms in a new database.

Note that when duplicating a database, users and their permissions are not automatically duplicated; you must invite them separately. To duplicate, you select "Duplicate database," give it a name, choose whether to copy the schema only or schema and data, and select specific folders (like your Reference Data folder). This creates a new database with the selected reference forms and data populated, saving you from starting from scratch.

00:42:34 Q&A session

Q: For the subform, is there any possibility of creating a relevant formula to avoid more answers than those required? For example, if I state there are 3 people in the household, can I limit the subform to 3 entries?

A: You can use the default value feature or relevance formulas to manage this, depending on how you structure your data. You can set rules to avoid people filling in more information than necessary, but it depends heavily on the specific data structure.

Q: Is there a possibility to move sub-records from one parent record to another while keeping the same record ID?

A: You can copy the information in your sub-record and paste it back into ActivityInfo in the new location.

Q: Suppose I use subforms for monthly reporting. Is there a way to notify users about missing collection points or a report to spot missing collections when the number of activities is high?

A: You can make fields required to ensure data is filled in. Regarding spotting missing collections, reverse reference fields can be very helpful here. If you have a reverse reference field between the form where entries are collected and the caseworkers, you can see the count of records entered for each caseworker. If you expect 100 entries and see only 80, you can easily identify who is missing data.

Q: I am an admin of two databases. I would like to duplicate a form from the old to the new database. How can I go about it?

A: You can use the duplication process we demonstrated to create a new database with the forms you are interested in. ActivityInfo also supports R scripts which can enable migration from one database to another.

Q: I have no rights to duplicate a database. I guess I am left with the R script?

A: If you have no rights to duplicate, you should contact the administrator of your database. There might be internal considerations regarding permissions, so it is best to discuss this with your team.

Q: How can we update a list that changes every quarter while keeping the list from previous forms?

A: You can update lists within your reference form. If the information changes constantly, you need to evaluate if it should be a reference form or a subform based on the requirements we discussed earlier.

Q: What is the difference between reference and reverse reference fields?

A: A reference field is the initial link you create. For example, Form B references Form A (e.g., a project form references a country list). A reverse reference happens from the other side. Form A (the country list) uses a reverse reference to look back at Form B and see all the records that referenced it. The reference field creates the connection; the reverse reference allows you to view that connection from the source form.