00:00:00 Introduction

Good afternoon everyone. This is Alex Bertram coming to you from The Hague. This session is going to be a deep dive into ActivityInfo formulas. This will be part of our advanced course that we are going to be offering in the fall, but we thought we would share a little bit now because we have done some really interesting development around these features in the last couple of months.

We try to make ActivityInfo as easy to use as possible, but sometimes you are going to encounter situations that are not simple. Formulas are a way to deal with those complexities and situations that you encounter in the real world. We hope that for 90% of the things you do with ActivityInfo, it is very easy, but for that last 10%, you can solve the problem with formulas.

We are going to focus on the setup: how you can use formulas to set up data collection, how to set up forms to meet your needs, and how to use formulas for analyzing the data that you have collected. We are going to go through four stages, starting off really simple and adding a bit more as we go along.

00:03:57 Assigning field codes

A formula is a way of expressing a calculation or a rule using symbols. It can be used for data validation, analysis, and permissions. The first thing we are going to look at is how to prepare for this by assigning your field codes. It is possible to use formulas without codes, but it will make your life much easier.

I have set up a very simple example database for today regarding an emergency NFI distribution. I have set up a simple form to collect data about each of these distributions including the date of alert, date of distribution, the village, and two indicators: the number of women and the number of men who received a kit.

I am going to start by assigning codes to these fields. A code is a much shorter version of the field's name or label. For "Date of alert," I will put date_alert. Codes cannot contain any spaces; they can only include letters and numbers, and they can only include Latin letters and numbers. Keeping these codes short will make your life easier when writing formulas.

00:09:45 Validation and relevance rules

Let's look at how we can use formulas for validation and relevance conditions. In my emergency program, the distribution is always going to come after the alert. I am going to make the alert field required and set a validation rule. For simple validation, you don't need a formula; I can just say the date has to be after the date my program starts.

For the "Date of distribution," I want that date to be after the alert date. I can choose "Formula is true" and enter the formula: date_dist > date_alert. If the distribution can happen on the same day as the alert, then you can use the greater than or equal to operator. This ensures that if someone tries to put the date of the distribution before the alert, they will get an error and will not be allowed to proceed.

You can also use these formulas in relevance conditions. For example, we want to understand gender imbalances. We can add a multi-line text question asking, "Why were women underrepresented?" However, we only want to ask this question if there was actually a problem. We can set a formula to be true based on a ratio. If the ratio of women to men drops below a certain number, like 0.9, we make this question relevant. This prompts the field staff for an explanation only when necessary.

00:17:40 Formulas for data analysis

We can use formulas to calculate indicators for your programs or for reporting purposes. There are several places where you can use formulas in ActivityInfo. The first is adding calculations on an ad hoc basis directly in the Table View. This opens the formula editor, which lists functions on the left and fields on the right. For example, we can calculate the total beneficiaries by adding Men + Women. We can also calculate the ratio or percentage of women beneficiaries.

The second place you can use formulas is directly within a Pivot Table. If you want to see a total by village, you can add a calculated field within the report settings. For instance, you can add Women + Men to get a total column in your report. When you save the report, the formula is saved along with it.

The third place is in the Form Designer itself. If you are using the same formulas repeatedly, you can add a "Calculated field" in the form design. These formulas are saved as part of the form and do not appear during data entry, but they are available for analysis later. You can also use calculated fields in other calculations. For example, if you calculate the total individuals, you can then use that total to estimate the number of households by dividing by an average household size.

00:26:54 Using functions

Functions allow for more complex calculations. A function starts with the function name, followed by parentheses containing arguments. For example, MIN(1, 3) would return 3. We have tried to take all our formula functions from Excel so that users familiar with Excel feel at home.

One useful application is calculating the time between dates. We can use the DAYS function to find the number of days between the distribution and the alert. Note that because we follow Excel's standards, you must put the end date first in the DAYS function to avoid a negative number. This allows you to analyze metrics like median response time in your Pivot Tables.

Another versatile function is the IF function. It takes three arguments: a condition, a value if true, and a value if false. For example, you can categorize age groups. If age < 18, the result is "Minor", otherwise "Adult". You can nest IF statements to create multiple categories, such as "Minor", "Adult", and "Elderly".

You can also use functions to build scores, such as a vulnerability index. By combining boolean checks with addition, you can assign points based on criteria. For example, if the head of household is a minor, add 5 points; if the household has more than 5 children, add 3 points. This results in a calculated vulnerability score for each record.

00:56:30 Working with subforms

When you have a form and a subform, it means that for every record in your parent form, you have zero or more subform records. You cannot simply use a field from a subform in a formula in the parent form because it doesn't have just one value. You must use subform fields as part of an aggregate or statistic function, such as SUM or COUNT.

For example, in a household vulnerability assessment, instead of asking for a simple number of children, you might have a subform for "Other household members" where you collect individual data like date of birth. You can calculate the age of each member within the subform. Then, in the parent form, you can create a calculated field that counts the number of children by using a formula that counts how many records in the subform meet the age criteria.

01:05:47 Using reference fields

A reference field is the opposite of a subform; a specific record references one other record in a reference form. You can use fields from that referenced record for data validation and analysis using dot notation. For example, if you have a reference field pointing to a "Province" form, you can access the province's population using province.population.

This is useful for validation. If you are collecting data on the "Population with access to water" in a specific district, you can set a validation rule ensuring the number entered is not greater than the total population of the referenced province. You can also use reference data in relevance rules, such as only showing a question about water treatment facilities if the referenced province's population is greater than 1 million. Finally, you can calculate indicators, like the percentage of the population with access to water, by dividing the assessed number by the total population pulled from the reference form.

01:14:00 Q&A and conclusion

Can we do a formula or validation to prevent the user from using decimal values? Yes, you can use the FLOOR function. The FLOOR function returns the closest integer. You can set a validation rule where the input must equal the FLOOR of the input. If someone enters 3.5, it will not equal 3, and they will get an error.

Can we see the formula used in data analysis? If you add a formula directly to a Pivot Table, you can see it there. However, if it is a calculated field from the Form Designer, you currently have to go back to the Form Designer to view the formula.

Is help available offline? Yes, you can download PDFs of the manual from our help site to use offline.

Does the date change with the time zone? The TODAY() function currently returns the date based on Greenwich Mean Time (GMT). We are looking at ways to allow you to specify alternate time zones. In general, be careful using TODAY() because the value will change over time; it is often better to use a fixed reference date like "Date of Assessment."

Thank you for joining us for this deep dive into formulas. We hope this helps you unlock more possibilities in ActivityInfo.