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)

Replaces part of a text string with a different text string, based on the position and number of characters you specify.

REPLACE(old_text, start_num, num_chars, new_text)    

REPLACE("abcdef", 2, 3, "XYZ") → returns "aXYZef"
REPLACE("2025-09-12", 6, 2, "15") → returns "2025-15-12"

Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string.

SUBSTITUTE(text, old_text, new_text, [instance_num])   

SUBSTITUTE("apple, apple, apple", "apple", "pear") → "pear, pear, pear"
SUBSTITUTE("apple, apple, apple", "apple", "pear", 2) → "apple, pear, apple"

The OR operator allows you to return a result when at least one condition is true.

In formulas, OR is written using ||   .

condition1 || condition2

The formula returns true if at least one condition is true.

Example in a conditional formula

IF(Status = "Approved" || Status = "Pending", "Valid", "Invalid")

This means:

• If the Status is "Approved" OR "Pending", the result will be "Valid".
• Otherwise, the result will be "Invalid".

Use the DIVIDE function to divide one numeric value by another. This function is useful when calculating ratios, percentages, averages, or indicators based on two fields.

DIVIDE(number1, number2)  

DIVIDE(total_budget, number_of_participants)

With this formula, the system will divide the value in the total_budget   field by the value in the number_of_participants   field.

Example:

DIVIDE(100, 4) // 25
DIVIDE(25, 5) // 5

Note:

• Both values must be numeric.
• If the divisor is 0, the formula may return an error or empty value.

LEFT returns the first characters from a text string, based on the number of characters specified.

LEFT(text, num_chars)  

LEFT(first_name, 3)

With this formula, the system will return the first 3 characters of the first_name   field.

Example:

LEFT("Jonathan", 4) // "Jona"
LEFT("LogAlto", 3) // "Log"

This function is useful for generating abbreviations, prefixes, or unique identifiers.

RIGHT returns the last characters from a text string, based on the number of characters specified.

RIGHT(text, num_chars)  

RIGHT(phone_number, 4)

With this formula, the system will return the last 4 characters of the phone_number   field.

Example:

RIGHT("Jonathan", 3) // "han"
RIGHT("2025-001", 3) // "001"

This function is useful for extracting suffixes, codes, or the last digits of identifiers.

This function returns the creation date of the current record.

RECORD_CREATED_ON()  

RECORD_CREATED_ON(): Date|Empty

The returned value corresponds to the exact date and time when the record was created in the system.

Example:

RECORD_CREATED_ON() // "2024-05-17"

The SELECTED function verifies whether a specific value is selected in a multiple choice or multi-select field.

SELECTED(field, value)  

SELECTED(
 field,
 value,
 [ value_1 ],
 [ value_2 ]
): Bool

Examples:

SELECTED(["Hello", "World"], "World") // returns true
SELECTED(["Hello", "World"], "N/A") // false

This function is useful for checking whether one or multiple values are selected in a multi-select field.

This function generates a universally unique identifier (UUID) for a record.

UUID()  

A UUID is a randomly generated identifier designed to be globally unique.

Example output:

"550e8400-e29b-41d4-a716-446655440000"

This function can be used for:

• Creating secure unique identifiers
• Synchronizing records between systems
• Avoiding duplicate IDs across multiple databases

Unlike RECORDID()  , UUID values are not sequential and cannot be predicted.