...
Addition (+)
Subtraction (-)
Multiplication (*)
Division (/)
Equality (==)
Inequality (!=)
Less than (<)
Greater than (>)
Less than or equal to (<=)
Greater than or equal to (>=)
Logical AND (&&)
Logical OR (||) (jump to example)
Logical NOT (!)
Ternary Operator ( ? )
Operands
Operands are the values or expressions that operators act upon. They can be numbers, strings, dates, or references to other cells or fields.
Functions
Functions are built-in routines that perform specific tasks, such as calculations, data manipulation, and text processing. They can take arguments (inputs) and return values (outputs).
...
Performing Null Checks
Null checks ensure that formulas handle missing or empty values appropriately. Common functions for null checks include:
ISNULL(): Checks if a value is null.
ISBLANK(): Checks if a value is null or an empty string.
COALESCE(): Replaces a null value with a specified alternative value.
...
Formula Building Techniques
...
Performing Null Checks
Null checks ensure that formulas handle missing or empty values appropriately. Common functions for null checks include:
ISNULL(): Checks if a value is null.
ISBLANK(): Checks if a value is null or an empty string.
COALESCE(): Replaces a null value with a specified alternative value.
...
Using the select() Function to Select Values Based on Cases
Select() Function Syntax
The select() function is used to select a value from a set of possible values based on a given case. It takes three arguments:
...
ROUND(): Rounds a number to the nearest specified number of decimal places.
ROUNDUP(): Rounds a number up to the nearest specified number of decimal places.
ROUNDDOWN(): Rounds a number down to the nearest specified number of decimal placesdecimal places.
Contact Phone Property Access
Access contact phone-related properties using the new array properties: phoneNumber, phoneType, and phoneExtension. You can now access these properties directly without using array notation.
For example, instead of CONTACT.phoneNumber[0], you can use CONTACT.phoneNumber or first(CONTACT.phoneNumber).
To display a "home" phone number, use the following formula:
| Code Block |
|---|
lookup(`CONTACT`.phoneType=='Home', `CONTACT`.phoneNumber) |
The phones property is deprecated. Replace any references to phones with phoneNumber, phoneType, or phoneExtension in your formulas.
Formatting Dates
Date formats control how dates are displayed. The FORMAT() function enables formatting dates according to various styles.
...
Question | Formula | ||||||||
Display when answer = Blue (The text selection is case sensitive) |
| ||||||||
Display when answer > 10 (num function converts the value to a number) |
| ||||||||
Display when answer = Yes |
| ||||||||
Display when answer = A Or D
|
| ||||||||
Display when Question 1 = M OR Question 2 = X |
| ||||||||
Display when sum of Question 1 and Question 2 > 100 (num function converts the value to a number) |
| ||||||||
Display when the count of items >= 3 |
| ||||||||
Display when the text contains the word “show” (toLowerCase converts the text to all lower case and then in this example compares to the lower case value “show”) |
| ||||||||
Display when the text contains does not contain the word “show” (toLowerCase converts the text to all lower case and then in this example compares to the lower case value “show”) |
| ||||||||
Display when checkbox is checked |
| ||||||||
Display when checkbox is not checked |
|
Advance Conditional Formula Examples
...