00:00:05 Introduction

Good afternoon. My name is Alex Bertram, the Technical Director here at BeDataDriven, and I'm here with my colleague Faye Kanellari. Faye works on the development team and helps new and existing customers get started using ActivityInfo. I'm really excited to share some of the advanced features we have regarding formulas in relevance rules and validation rules.

This session will cover a short introduction to formulas in ActivityInfo, including how our formula language works and where you can use it. We will look at basic validation and relevance rules to get a quick start. Then, we will examine using reference fields in relevance rules, working with select and multi-select fields, and finally, we will have time for questions and specific use cases.

00:02:43 Introduction to formulas

A formula expresses a calculation or a rule using symbols. You might be familiar with using formulas in Excel to summarize things or add cells together. We have based our formula language as much as possible on Excel's language because we know many people are familiar with that. Formulas can be used for data validation, which we are covering today, but also for analysis and permissions. I strongly recommend setting up your forms with codes, as they will help you more easily read and write formulas.

You can refer to a field by its code, ID, or label, but using the field code is the easiest way. You can do arithmetic to calculate new values, but you can also use formulas to calculate true or false values. This is what we are focusing on today because if a formula is either true or false, it can be used for relevance and validation rules. For example, a formula can check if age is less than 18, or if age is greater than or equal to 10 AND less than or equal to 18. You can also use equality and inequality to check text fields.

Functions are very important and have the same syntax as they do in math, Excel, and other programming languages. The name of the function comes first, followed by arguments in parentheses. For example, the MIN function takes multiple arguments separated by commas to find the smallest number. The IF function takes three arguments: a true or false formula, the value if true, and the value if false.

For select and multi-select fields, you can use dot notation to see whether an individual item is selected. For example, gender.male will be true if "male" is selected. If a select option has spaces in the name, you must use brackets. You can also use dot notation for reference fields, similar to joins in SQL. If you have a reference field named "Province," you can write .name to look up the name of the referenced province, or .population to look up the population field in that province record.

00:09:46 Basic relevance rules

Let's start with the simplest cases using a health status form. I have a single select field for sex (Male/Female) with the code "sex". If this is a questionnaire for individuals, we might have questions only relevant to specific genders. This is sometimes called skip logic, but in ActivityInfo, we call them relevance rules.

For example, if we add a question "Are you currently pregnant?", it is not relevant for all respondents. We can set a relevance rule using the Rule Editor to say this is only relevant if the sex is female. When filling out the form, if "Female" is chosen, the question appears; if "Male" is chosen, it does not. Under the hood, this is a formula. If you click on the Formula Editor, you can see the underlying formula, which is a true/false statement based on whether the item "Female" is selected.

00:12:50 Basic validation rules

Now let's look at a simple validation rule. I will add a date field for the date of birth with the code "dob". We don't want just any date; perhaps we are working with a population expected to be born after 1920. We can set a validation rule to ensure the date is after 1920. If I try entering 1882, it tells me it is not a valid date. However, if I enter a future date like 2023, the rule accepts it, which is incorrect.

To fix this, we need to strengthen the validation rule to ensure the date is also in the past. We can use the Formula Editor to combine conditions. We can use the TODAY() function, which returns the current date, to ensure the date of birth is less than today. This ensures that data is checked at the point of collection.

00:17:34 Advanced relevance rules

Returning to the pregnancy question, we might want to avoid asking this for certain age groups. We can do this because we have the date of birth. We can say this question is only relevant if the subject is female AND they are of reproductive age, for example, using 12 as a cutoff.

In the Formula Editor, we can use the AND operator. To calculate age, we can use the YEARFRAC function, which returns the difference between two dates as a fraction of a year. We can write a formula that checks if the fraction of years between the date of birth and today is greater than 16. This ensures we ask the question only for female respondents of reproductive age.

One thing to be careful about is that the value of the TODAY() function changes. If you use TODAY() to calculate age for a relevance rule (e.g., "Are you currently in school?" only for those under 18), the record might become invalid if you edit it a year later when the person has turned 18. In such cases, it is better to use a static "Date of Registration" field rather than TODAY() to preserve the validity of the record over time.

00:25:06 Using reference fields

Let's look at how things change when using reference fields. Reference fields link forms together. For example, we might have an Activity form listing activities like Vaccination or Water Trucking, and a Partner form listing partners and the sectors they work in (e.g., Health, WASH).

If we are creating a "Monthly Reports" form, we can add a reference field for the Partner. We might have specific questions, like "Number of liters delivered," that are only relevant if the partner works in the WASH sector. We can set a relevance rule that looks up the partner selected and checks if they are working in the WASH sector. The formula uses dot notation to look up the partner, find the sector, and check if "WASH" is selected. This allows the form to dynamically display questions based on the attributes of the referenced partner.

00:31:06 Validation with reference fields

We can also use validation rules to filter reference options. For example, in a "Water Point Assessment" form, we want to record the implementing partner. However, not all partners work in water and sanitation. To reduce error, we can add a validation rule to the Partner reference field.

Using the Formula Editor, we can write a true/false formula that checks the referenced partner's sector. We look up the implementing partner and ensure that "WASH" is true. This means that when a user tries to select a partner in the Water Point Assessment form, they will only see partners who are active in the WASH sector.

00:34:12 Complex example: 3W form

Let's look at a more complex example: a 3W form (Who does What, Where). We have a list of Partners, a list of Activities, and a list of Indicators linked to those activities. In the 3W form, we select a Partner ("Who"), an Activity ("What"), and a Location ("Where").

We can add a subform to track indicator values. In this subform, we select an indicator. We want to ensure the selected indicator relates to the Activity selected in the parent form. We can set a validation rule on the indicator field stating that the indicator's activity must equal the activity selected in the parent form. Furthermore, if the indicator requires disaggregation (e.g., by sex), we can set a relevance rule on the "Number of men" and "Number of women" fields to only appear if the referenced indicator has "Sex" selected in its disaggregation field. This allows you to capture complex relationships in a single form structure.

00:40:51 Working with select and multi-select fields

Finally, let's look at a tactic for multiple-select fields. In an assessment, you might check for facilities like "Latrines" or "Water Source." However, you might also have options like "No facilities" or "No information available." It is illogical to select "No facilities" AND "Latrines" at the same time.

To prevent this, we can add a validation rule. We want to ensure that if "No facilities" is selected, then "Latrines" and "Water Source" must NOT be selected. The formula would look something like: Either "No facilities" is NOT selected, OR (if it is selected) then "Latrines" and "Water Source" must be false. This ensures mutually exclusive options are respected during data entry.

00:47:27 Q&A

Can we design this in Excel and load it to ActivityInfo? Yes, for large questionnaires, this is useful. You can use the "Export fields" option to get a CSV file of your form structure. You can then edit the rows, add new questions, and copy validation/relevance logic down the columns. Once done, you can copy the spreadsheet content and use the "Paste field list from spreadsheet" feature in the Form Designer to update or create the form.

Is it possible to edit a question from single-select to multiple-select? Not directly if data has already been entered. You would need to add a new multiple-select field and remove the old one. If you have existing data, you would need to migrate that data to the new field, potentially using the importers.

Is there validation to restrict a telephone number entry? Yes. You can use an input mask for simple text fields to limit input to numbers and specific lengths. For more complex validation, you can use the REGEXMATCH function in the Formula Editor. This allows you to define a Regular Expression pattern (e.g., starting with specific digits followed by a set number of digits) that the input must match.

How do we express project duration in months? You can use YEARFRAC or the DAYS function. YEARFRAC calculates the fraction of a year between two dates, which you can multiply by 12. Alternatively, you can use DAYS to get the number of days between a start and end date and divide by 30. You can wrap these in a FLOOR function to round down to a whole number.

Can we write a formula to sum two separated forms located in different folders? Formulas discussed today are for record-level calculations and validation. To sum data across multiple forms, you should use the Reports feature. Reports allow you to aggregate data from different forms and summarize them using formulas.

01:01:18 Conclusion

We have covered how formulas work, basic and advanced validation and relevance rules, and how to work with reference and select fields. If you have more questions, please email us at support@activityinfo.org. We have upcoming webinars on sample sizes and the new Collection Link features, which allow anonymous data collection. Thank you for joining us.