Logical
AND
Returns true if all of the provided arguments are logically true, and false if any of the provided arguments are logically false.
Syntax
AND(logical_expression1, [logical_expression2, ...])
- logical_expression1 - An expression or field containing a value that represents some logical value, i.e. TRUE or FALSE, or an expression that can be coerced to a logical value.
- logical_expression2, ... - [ OPTIONAL ] - Additional expressions or fields containing values representing some logical values, i.e. TRUE or FALSE, or expressions that can be coerced to logical values.
Sample Usage
AND(firstName = "foo", lastName = "bar")
AND(TRUE, FALSE, TRUE)
Notes
- The number 0 is logically false; all other numbers (including negative numbers) are logically true.
FALSE
Returns the logical value FALSE.
Syntax
FALSE()
Sample Usage
FALSE()
IF
Returns one value if a logical expression is TRUE and another if it is FALSE.
Syntax
IF(logical_expression, value_if_true, value_if_false)
- logical_expression: An expression or field containing an value that represents some logical value, i.e. TRUE or FALSE.
- value_if_true: The value the function returns if logical_expression is TRUE.
- value_if_false: The value the function returns if logical_expression is FALSE.
Sample Usage
IF(name = "foo", "Name is foo", "Name is not foo")
IF(property, "Property was true", "Property was FALSE")
IF(TRUE, 4, 5)
Notes
- Ensure that value_if_true and value_if_false are provided to the function, and in the correct order.
IFS
Evaluates multiple conditions and returns a value that corresponds to the first true condition.
Syntax
IFS(logical_expression, value_if_true, logical_expression2, value_if_true2)
- logical_expression: The first condition to be evaluated. This can be a boolean, a number, an array, or a reference to any of those.
- value_if_true: The returned value if logical_expression is TRUE.
- logical_expression2, value_if_true2, … -: Additional conditions and values if the first one is evaluated to be false.
Sample Usage
IFS(name = "foo", "Name is foo", name = "bar", "Name is bar")
IFS(TRUE, 4)
Notes
- If all conditions are FALSE, #N/A error is returned.
COALESCE
The COALESCE function evaluates its arguments in order and returns the first value that isn't undefined.
Syntax
COALESCE(value1, [value2, ...])
- value1: The first value to evaluate and return if it is not undefined.
- value2, ... - [OPTIONAL]: Additional values to continue evaluating and potentially return if value1 is undefined.
Sample Usage
COALESCE(property1, property2)
- Returns property2 if property1 is undefined, otherwise it returns property1.
COALESCE(ANS(), TODAY())
- Keeps the creation date regardless of how many times the value gets recomputed.
NOT
Returns the opposite of a logical value: NOT(TRUE) returns FALSE, NOT(FALSE) returns TRUE.
Syntax
NOT(logical_expression)
- logical_expression: An expression or reference to a property holding an expression that represents some logical value, i.e. TRUE or FALSE.
Sample Usage
NOT(property)
OR
The OR function returns TRUE if any of the provided arguments are logically TRUE, and FALSE if all of the provided arguments are logically FALSE.
Syntax
OR(logical_expression1, [logical_expression2, ...])
- logical_expression1: An expression or reference to a property containing an expression that represents some logical value, i.e. TRUE or FALSE, or an expression that can be coerced to a logical value.
- logical_expression2, ... - [ OPTIONAL ]: Additional expressions or references to properties containing expressions representing some logical values, i.e. TRUE or FALSE, or expressions that can be coerced to logical values.
Sample Usage
OR(firstName = "foo", lastName = "bar")
OR(TRUE, FALSE, TRUE)
OR(0, 1, 2, 3)
Notes
- The number 0 is logically false; all other numbers (including negative numbers) are logically true.
ISBLANK
Checks whether the referenced property is empty.
Syntax
ISBLANK(value)
- value: Reference to the property that will be checked for emptiness.
- ISBLANK returns TRUE if value is empty or a reference to an empty property, and FALSE if it contains data or a reference to data.
Sample Usage
ISBLANK(property)
IF(ISBLANK(numericProperty),,5/numericProperty)
Notes
- ISBLANK returns FALSE if the referenced property has any content, including spaces, the empty string (""), and hidden characters. In case of unexpected FALSE results, try clearing the property again to remove any hidden characters.
- This function is most often used in conjunction with IF in conditional statements.
TRUE
Returns the logical value TRUE.
Syntax
TRUE()
Sample Usage
TRUE()
SWITCH
Tests an expression against a list of cases and returns the corresponding value of the first matching case, with an optional default value if nothing else is met.
Syntax
SWITCH(expression, case1, value1, [case2, value2, ...], [default])
- expression: Any valid values.
- case1: The first case to be checked against expression.
- value1: The corresponding value to be returned if case1 matches expression.
- case2, value2, … - [OPTIONAL]: Additional cases and values if the first one doesn’t match the expression.
- default - [OPTIONAL]: An optional value, specified as the last parameter, to be returned if none of the cases match the expression.
Sample Usage
SWITCH(property, 0, “No”, 1, “Other”)
SWITCH(property, 4, “Four”, 8, “Eight”)