Formulas in forms

Formulas allow you to apply arithmetic operations and functions to data fields within a custom form. This is similar to the formulas you can write in a cell of an Excel spreadsheet. Currently, formulas can only be applied to the field of the same record (or "row"). 

When creating a form, it is possible to specify a type of field that is a Formula.  This feature enables you to add the values of fields within a form, create data strings based on fields, and specify a value based on a particular condition in your form.

To add a Formula field to your form:

1. You must be in the creation panel for your form. 
2. You will see all the different type of fields to the left of the screen and you can drag and drop the type of field Formula to the position within your form where you wish to enter it.
3. Now enter the title that you wish to give the field.
4. You need to SAVE your form so the available fields appear in the Formula box. But do not PUBLISH until after you have set your formula
5. Click on the pencil icon to edit the parameters of your formula
6. You will enter the formula in the Formula box
7. You will see a list of the available fields for your formula. The name of the field is generated automatically by the system using the title of the field and a unique code.
8. Click on the field you want, this will copy it into your clipboard.
9. Then click on the formula box and paste the name of the field into the box, this will enter the desired field into the box. Arguments contain the information you want to calculate.

Arithmetic operators:

The following arithmetic operations are available: 

• + (addition) 
• - (subtraction) 
• * (multiplication) 
• / (division)

Relational and logical operators

It is possible to use the following relational and logical operators

• ==  (equal; converts the data type to compare values)
• ===  (identical; does not convert data type. "1" string is not similar to 1 number)
• !=  (not equal)
• !==  (not identical)
• <  (less than)
• >  (greater than)
• <=  (less than or equal to)
• >=  (greater than or equal to)
• &&  (AND)
• ||  (OR)

Currently, the following functions are available: 

1. CONCAT
2. MID
3. UPPER
4. LOWER
5. IF
6. RECORDID()
7. INT
8. IFS
9. BETWEEN
10. DATEDIFF

UPPER and LOWER

These functions will convert text into uppercase and lowercase

CONCAT

Use CONCAT to join two or more values (text, date, numbers, etc.) into one string. This function enables you, for example, to create a unique participant ID using information collected in your form. 

Example:

CONCAT(UPPER(MID(surname, 1, 3)),UPPER(MID(first_name, 1, 1)), date_of_birth)

With this formula, you would need to have fields titled "surname," "first name," and "date of birth" of the participant.  In this example, the formula would then create a new string of the first letters of the surname, the first letter of the first name and the date of birth. Note that another "MID" function is in use here that allows selecting segments of a string (ex, first three letters).

IF

The IF function allows you to compare a field's value and what you expect logically. Use this formula to "flag" specific information in your record. 

Example 1: 

If you wish to know whether a participant succeeded in the the training, you can add an IF function as follows: 

IF(math_results+history_results>50,"Approved","Failed")

The first section is the condition that must be met. In this case, the participant's overall results must be over 50. 

If this condition is met, the system will populate the field with the label Approved.

If this condition is not met, the system will populate the field with the Failed label. 

Example 2:

IF((number_positive_3662/number_of_tests_3663*100)>50, "True," "False")

Example 3:

Imagine a form with 3 different criteria. Based on these criteria I wish to apply a condition that will determine the content of my Formula field. In my example the criterion to determine whether a partner is eligible for my survey is that they need to be at the same time: Women led, autonomous and not for profit. In my form I have 3 yes/no fields for each of these criteria and I wish to add a 4th field to determine whether or not the partner is eligible

• IF Women led = "Yes" AND
• IF Autonomous = "Yes" AND
• IF Not for profit = "Yes"
• THEN, "Eligible"
• If not, "Not eligible."

The correct formulation for this in LogAlto is: (The numbers after the name of the field are automatically formulated by the system based on my form)

 IF(women_led_1716=="yes"&&autonomous_1715=="yes"&&not_for_profit_1714=="yes","Eligible","Not eligible")

IFS

The IFS() function checks whether one or more conditions are met, and returns a value that corresponds to the first TRUE condition. IFS can take the place of multiple nested IF statements, and is much easier to read with multiple conditions.

IFS(var == 1, "First", var == 2, "Two") // var = 1 => "First"
IFS(var == 1, "First", var == 2, "Two") // var = 2 => "Two"
IFS(var == 1, "First", var == 2, "Two", true, "Default") // var = 5 => "Default"

BETWEEN

Return whether the `value` is included between the `left` and `right` numbers.

BETWEEN(9, 10, 20) // false
BETWEEN(10, 10, 20) // true
BETWEEN(15, 10, 20) // true
BETWEEN(20, 10, 20) // true
BETWEEN(20.5, 10, 20) // false
````

DATEDIFF

Use the DATEDIF function when you want to calculate the difference between two dates. First put a start date in a cell, and an end date in another. 

DATEDIFF("2024-01-17", "2024-01-01", 'DAY') // 16
DATEDIFF("2025-01-17", "2024-01-01", 'MONTH') // 12
DATEDIFF("2025-01-17", "2024-01-01", 'YEAR') // 1

INT

The function INT() can be used to convert a numeric value to an integer.

This function will generate a unique ID for a specific record. This ID can be used for different scenarios, such as identification, tracking data points, etc.

Addition of field values: 

You can add data within two or more different fields. If you have the following fields in your form:

Number of female participants +  Number of male participants

This will give you the Total number of participants

Use formulas to prioritize risks:

You can also use this feature in your risk register if you calculate the level of a risk by multiplying its probability and impact in your project.

Have in your form a field that is Probability (Scale of 1-5). Add a field that is Impact (Scale of 1-5) 

Add a formula and make Probability  * Impact  this will give you your risk's level.

Create a unique ID:

You can generate a unique ID with the RECORDID() function.  This could be used to assign identifying IDs to each record.

For example if you have a form that gathers the following information :

- Location of clinic - Date of test

CONCAT(UPPER(MID(location, 1, 3)), date_of_test, RECORDID())

Currently the Formula feature is limited to records. This means that you can add a formula and apply it only to data within the same record. In blue in the example below:

For the time being you can use the Analytics module for your column-based calculation needs. In orange in the example below: