00:00:19 Introduction and housekeeping

Hello everyone. Thank you so much for being here today. Welcome to today's webinar, "Using formulas in ActivityInfo." My name is Jane and I'm a marketing specialist at ActivityInfo. Together with Mr. Alexander Bertram, the Technical Director and founder of ActivityInfo, we will be hosting this webinar, broadcasting from The Hague, the Netherlands.

You may all be familiar with Alex, who founded ActivityInfo as a simplified platform for M&E data collection after his frustration with the time required to build a collection system for each new program while working with UNICEF in Congo.

Now it's time for the housekeeping rules. Your microphone is muted and you should all be able to see the shared screen. We will try our best to upload the recording of the webinar on the website and also send you an email with the link after the webinar. On our website, you can also find the recordings of previous webinars on M&E and also on using the software ActivityInfo. During the webinar, you can send us your questions via the Q&A session, which you can find in the toolbar at the bottom of the screen. This makes it easier for us to keep track of all the questions. Now I would like to give the floor to Alex.

00:03:02 Agenda and overview

Thanks, Jane. I'm excited to present this. We do a couple of these trainings on formulas every once in a while. We have new features to add this time around, but I do hope that you'll share your questions and the kind of things that you're trying to solve with formulas in ActivityInfo so that we can dive into them as well.

The brief overview of today's training: we're going to start with an introduction to the ActivityInfo formula language, how it works, and general principles. Then we'll look at where you can use formulas in ActivityInfo. In the application, we have a consistent formula language that is used throughout, so that's a useful tool. Then we'll spend a lot of time on examples, just walk through step-by-step on different ways to use formulas. We'll open up for questions and I'll share some of the stuff that's on our roadmap for this year to improve formulas.

00:04:04 Introduction to formula theory

To start from the beginning, a formula is a way to express a calculation or a rule using symbols. You're probably familiar with building these rules with the graphic Rule Builder or the user interface Rule Builder. For example, in the form designer, you can build rules using simple date or sum values. But formulas are a different way of expressing these rules that give you much more flexibility. They give you the power to address the complexity that you might find in real-world data. Formulas in ActivityInfo can be used for data validation, for analysis, and even for permissions, such as defining the rules that filter records for partners or for specific topics.

Let's take a quick look at the simplest kinds of formulas. When we added formulas to ActivityInfo many years ago, this was the extent of the language: basically taking an existing field and doing some basic arithmetic. For example, if I have a quantity field for 'Number of women' and 'Number of men', I can add a code to help reference these fields. I can then add a calculated field using the formula men + women. You can combine operations. So maybe you want to see the percentage of women that your program is reaching. You could do that by taking women, dividing by the total, and multiplying by 100.

Formulas are useful not only for calculating new values but for defining rules or true/false values, known technically as Boolean logic. You can use formulas to set a rule, for example, that a field has to be less than 18. You can combine rules together using the ampersand symbol for 'and'. For example: age > 0 && age <= 18. I can also work with strings or text by putting it in quotes.

In addition to basic operators like plus, minus, divide, multiply, and comparison operators, we also have functions. Mathematical functions have a name followed by parentheses containing a list of arguments. For example, the MIN function takes the minimum of its arguments. One quite useful function is the IF function. It takes three arguments: the first is a Boolean expression (true or false), and the second two arguments determine the value based on the condition. This is an example of how you can take a number and turn it into a category, like 'minor' or 'adult'.

00:10:08 Working with specific field types

In ActivityInfo, you have different kinds of fields. We've looked at text and quantity fields, but you also have select fields. A safe way to refer to select fields is using the dot notation. If you have a gender field with an option 'male', the expression gender.male will be true if 'male' is selected. Square brackets are used in the formula language if the name of the field or select item has spaces or special characters.

If you have a subform, like a list of household members, you may have many values for a field. You cannot use that subform's field directly in a formula because there could be more than one value. You need to use one of the aggregate functions like SUM, AVERAGE, MEDIAN, or COUNT. These functions will take multiple values in your subform and turn them into a single number that you can use in your formula.

Looking at reference fields, these are where you can select a record from another form. You can refer to those values in your formulas. For example, if I have a reference field called 'Province' and I want to find the name of the selected province, I can use the dot notation to follow that link: Province.Name. You can do this over multiple levels of indirection. If you are familiar with SQL, this is similar to a left join. ActivityInfo essentially defines this join when you add a reference field to the form, so you just have to use the dots to follow those joins.

00:17:09 Formulas in the table view

Let's look at using formulas in the Table View. You can add calculated fields directly to this Table View if you just need to do a quick calculation. If you click on "Select columns," you can add a calculated field. This opens up the formula editor, which helps you write formulas. On the left-hand side, you'll see a list of all the available functions with help icons. On the right-hand side, you have the fields available in this context.

For example, if I want to find the total number of people, I can click on 'Number of men' and 'Number of women' to add them to my formula. This will be calculated automatically for each record. This context is for ad hoc or quick formulas because if I refresh the page, I lose my selection.

00:19:41 Adding calculated fields to forms

If you want to save formulas so that they are a permanent part of your form, you can add a calculated field in the form designer. First, I recommend adding codes to your fields to make them easier to refer to. Then, add a calculated field, such as 'Percentage of women participating'. You can type your formula directly or use the Formula Editor.

Once added, this calculated field becomes part of the form. It can be displayed in the table and used in analysis. You have options to hide this in the data entry form or hide it in the table. If you show it in the data entry form, the calculation will update live as you type, which can guide users through the process.

00:23:02 Reference fields and subforms in practice

Let's look at how we can include calculated reference fields in data entry. In a 3W form, I might have reference fields for Partners, Activities, and Location. When selecting a partner, ActivityInfo only shows the key field by default. However, the partner form might have other fields like 'Full Name'. I can add a calculated field to the 3W form that references the 'Full Name' field of the selected partner using dot notation. I can also use functions like CONCAT to create a string like "The partner's full name is [Name]".

We can also use subform fields in formulas. For example, if I have a subform for indicator values, I can add a calculated field to the parent form to find the average indicator value. Using the AVERAGE function on the subform field will aggregate the values entered in the subform into a single number.

00:27:58 Data validation rules

We can use formulas to help enforce data quality. For example, I can ensure that each partner can only enter indicators that are relevant to them. I can set a validation rule on the indicator reference field that filters the choices based on the activity selected in the parent form. This functions as a filter, showing only the valid choices.

You can also use validation rules for quantities. For example, if gender breakdown is provided, we want to make sure the total value equals the sum of men and women. However, if the breakdown is not provided, we don't want to enforce this. We can use the ISBLANK function to create a complex rule: if both men and women fields are blank, the field is valid. If either is provided, the value must equal the sum of men and women. This type of complex logic is harder to express with simple drop-down rule builders.

00:33:23 Relevance rules

Relevance rules determine whether a field is relevant and should be shown to the user. A classic example is pregnancy status. We might have a rule that the 'Pregnant' question is only relevant if the sex is female. We can narrow this further by calculating the age. Using the YEARFRAC function (similar to Excel), we can calculate the age based on the date of birth and today's date.

We can then combine these rules: the field is only relevant if the sex is female AND the calculated age is greater than 16. If a user selects 'Female' and a date of birth that results in an age over 16, the question appears. If they change the sex to 'Male', the question disappears and the value is cleared to maintain data integrity.

You can also use relevance rules with reference fields. For example, a 'Number of men' field might only be relevant if the selected indicator requires disaggregation by sex. We can use dot notation to check properties of the selected indicator record to determine if the breakdown fields should be shown.

00:41:31 Formulas in reports and pivot tables

Finally, let's look at using formulas in pivot tables and analysis. It is important to note a limitation: calculated fields added to a pivot table are calculated at the record level first, and then aggregated. For example, if you calculate a percentage for each record and then sum those percentages in a pivot table, you will get a nonsense value.

To calculate an aggregate percentage (e.g., total women divided by total participants across all records), you currently need a multi-step approach. First, create a pivot table that sums the men and women. Then, create a second pivot table using the first one as a source. In the second table, you can add a calculated field that performs the division on the aggregated totals. We are looking at adding more sum functions from DAX to make these multi-step calculations easier in the future.

00:46:52 Q&A: Relevance logic and complex conditions

Mira: I have a question regarding the pregnancy example. Can we adjust the age limit, for example, for women younger than 16?

Alex: Yes, that is for you to define. In the formula, I set it to greater than 16, but you can change that number or remove the age condition entirely depending on what is culturally or medically appropriate for your program.

Mira: Can we chain questions? For example, if we have a question about symptoms, can we add follow-up questions based on specific criteria?

Alex: Yes, that is the beauty of formulas. You can make them as complex as you like. For example, you could ask about symptoms like dizziness or headaches. Then, you could have a follow-up question about cardiac health that only appears if the respondent is male, over 40, and has experienced specific symptoms. You can chain these together so that answering "yes" to one question triggers the next relevant question.

00:53:55 Regular expressions and text validation

I want to show an example using regular expressions (Regex), which we added last year. This is great for validating text fields like phone numbers or IDs. I recommend using text fields for phone numbers rather than quantity fields.

You can set a validation rule using the REGEXMATCH function. For example, to validate a Dutch phone number, you can write a pattern that checks for specific prefixes (like 06, 070, 020) followed by a specific number of digits. If the entered text matches the pattern, the field is valid.

You can also use Regex in calculated fields to extract information. For instance, you could determine the "Type of phone" by checking if the number starts with "06" (mobile) or another prefix (fixed line). Other useful text functions include LEFT and RIGHT to extract parts of a string, and CONCAT to combine strings. We also have date functions like ADD_DAYS to calculate future dates, and COALESCE, which is useful for finding the first non-blank value among a list of fields.

01:01:58 Q&A: Linking fields and SUMIF

Parvez: Can we link one field to another, for example, to automatically determine if a woman is of reproductive age based on the age field?

Alex: Yes, exactly. You can use a calculated field with an IF statement to output "Reproductive" or "Not Reproductive" based on the age range.

Parvez: Can we do a SUMIF function? For example, to calculate the salary for men only?

Alex: To add up records, you generally use a pivot table. However, to achieve a SUMIF logic, you can add a calculated field that says: IF(gender.male, salary, 0). This creates a "Male Salary" field. Then, in your pivot table, you can sum this specific field to get the total salary for men.

01:05:13 Conclusion

I think we'll wrap up for today. I hope it was useful. Feel free to send me more questions as we go forward. I'm going to be working on updating our formula manual this coming week, so I am happy to include those examples in the updated manual. I wish everybody a good afternoon, good evening, and a good weekend. Take care. Bye-bye.