Formula functions

Available functions

The following functions are available: 

MID

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)

UPPER and LOWER

These functions will convert text into uppercase and lowercase

UPPER(field)

LOWER(field) 

UPPER(surname)

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. 


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

RECORDID

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.


RECORD(field)

NOTE 1: The IDs order is chronological per platform. This means the ID is provided to a new record for any form. Record IDs might not be sequential within a single form if other forms had created records in the meantime.

IF

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

Syntax Notes for IF functions: 

  • When you're writing your formula, there shouldn't be any spaces in the names of the fields. Notice that we entered underscores for the area Attended_all_trainings. The system automatically does this when you select the domain for your formula.
  • The arguments always need to be "between quotes."

  • For saying that a particular condition must be met IF a equals b, the correct syntax would be: IF(a==b, "Yes", "No")

    Note the two equal signs.

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

Note:

  • LogAlto requires 2 equal signs (==) and 2 ampersands (&&) for the formula to work
  • The conditions must be between quotation marks (" ")

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

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(date_field1, date_field2, unit) 

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

INT

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

INT(field) 

INT(Number_field)

ROUND

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

Related Articles