Functions to help choose one value over another. Many of these functions exist in lieu of conditional statements
Functions
case(condition1, result1, conditionN, resultN, defaultResult) → {object}
The case function will choose a value from a number of options. The arguments for the function
are alternating condition and value expressions with a final default expression
if none of the conditions are met. A formal case statement may look like this:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;
This functions will just encase each expression case(condition1, result1, condition2, result2, conditionN, resultN, result)
All results must be of a comparable data type
Parameters:
| Name | Type | Description |
|---|---|---|
condition1 |
boolean |
Required. The first condition expression that will evaluate to a true or false |
result1 |
object |
Required. The first value to use if the first condition is met |
conditionN |
boolean |
Optional. A set of optional expression that will evaluate to a true or false |
resultN |
object |
Optional. A set of optional values to use if the condition is met |
defaultResult |
object |
Optional. If no conditions are met, then return this value instead |
Returns:
- Type:
- object
The first value that had a condition that was met, or the default result value
Example
// This case example will create a datetime from now + 2, 7, 30 or 60 days depending on the value of the field priority
case((Priority == 'P1'), (date_add(now(), 2, 'day')), (Priority == 'P2'), (date_add(now(), 14, 'day')), (Priority == 'P3'), (date_add(now(), 30, 'day')), (date_add(now(), 60, 'day')))coalesce(value1, valueN) → {object}
Return the first non-null value in the arguments list
coalesce is shorthand for a case statement where all conditions checks if the value is null
All values must be of a comparable data type
Parameters:
| Name | Type | Description |
|---|---|---|
value1 |
object |
Required. The value to check for non-null |
valueN |
object |
Optional. Additional values to check for non null |
Returns:
- Type:
- object
Return the first non-null value in the arguments list, or null if there are none
Example
// returns the first non null contact phone number, checking for empty text with nullif
coalesce(nullif(homephone,''), nullif(workphone,''), nullif(cellphone,''), 'NA')if_else(condition, value1, value2) → {object}
The if_else function will choose the first value if the condition is true, else choose the second value
if_else is shorthand for a case with a single condition, value and default value
All values must be of a comparable data type
Parameters:
| Name | Type | Description |
|---|---|---|
condition |
boolean |
Required. The value to check for non null |
value1 |
object |
Required. Additional values to check for non null |
value2 |
object |
Required. Additional values to check for non null |
Returns:
- Type:
- object
Return the first value if the condition is true else it will return the second value
Example
// This will return the current datetime if status is completed else it will return the completed date
if_else((Status == 'Completed'), now(), CompletedDate)max(x, y) → {object}
The max() method returns the number with the highest value.
All values must be of a comparable data type
Parameters:
| Name | Type | Description |
|---|---|---|
x |
object |
Required. A number |
y |
object |
Required. A number |
Returns:
- Type:
- object
Returns the highest value between x an y
min(x, y) → {object}
The min() method returns the number with the lowest value.
All values must be of a comparable data type
Parameters:
| Name | Type | Description |
|---|---|---|
x |
object |
Required. A number |
y |
object |
Required. A number |
Returns:
- Type:
- object
Returns the lowest value between x an y
nullif(value1, value2) → {object}
The nullif() function returns null if two expressions are equal, otherwise it returns the first expression.
All values must be of a comparable data type
Parameters:
| Name | Type | Description |
|---|---|---|
value1 |
object |
Required. A value |
value2 |
object |
Required. A value |
Returns:
- Type:
- object
Returns null if value1 equals value2 else return value1