Formula functions

The following functions are available: 

MID returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify.

MID(field, start_num, char_num)

MID(surname, 1, 3)

These functions will convert text into uppercase and lowercase

UPPER(field)

LOWER(field)

UPPER(surname)

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. 

CONCAT(field1, field2, ...)

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).

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

RECORDID()

Use the IF function to return one value if a condition is true and another value if it's false.

IF(logical_test, value_if_true, [value_if_false])

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")

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"

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

BETWEEN(field, min_num, max_num)

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
````

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. 

DATEDIF(date_field1, date_field2, unit)

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

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

INT(field)

INT(Number_field)

• Rounds a number to a specified number of digits.
• To always round up (away from zero), use the ROUNDUP function.
• To always round down (toward zero), use the ROUNDDOWN function.

ROUND(field, num)

ROUNDUP(field, num)

ROUNDDOWN(field, num)

ROUND(Number_field, 3)