# Functions

{% hint style="info" %}
**FlexiKanban** provides a rich library of built-in functions that can be used inside formulas and expressions throughout the application. These functions allow administrators to dynamically manipulate data, evaluate conditions, and build advanced logic without writing Apex code. Functions can be used in Kanban dashboards, forms, actions, filters, and styling configurations.
{% endhint %}

### <mark style="color:$primary;">SObject Functions</mark>

***

#### SOBJECTTYPE

Returns the API name of an object

{% tabs %}
{% tab title="Usage" %}
`SOBJECTTYPE(id)`

Replace `id` with the ID of an object.
{% endtab %}

{% tab title="Example" %}
`SOBJECT({Stub.Id})` will return '**Stub\_\_c**'
{% endtab %}
{% endtabs %}

#### SOBJECTDESCRIBE

Retrieves metadata for an object. Returns object (JSON).

{% tabs %}
{% tab title="Usage" %}
`SOBJECTDESCRIBE(object_api_name)`

Replace `object_api_name` with the API name of an object.
{% endtab %}

{% tab title="Example" %}
`JGET(SOBJECTDESCRIBE('Account'), 'LabelPlural')` will return '**Accounts**'
{% endtab %}
{% endtabs %}

#### SOBJECTFIELDDESCRIBE

Retrieves metadata for an object field. Returns object (JSON)

{% tabs %}
{% tab title="Usage" %}
`SOBJECTFIELD(object_api_name, field_api_name)`

Replace `object_api_name` with the API name of an object and `field_api_name` with an API name of a field to retrieve metadata for.
{% endtab %}

{% tab title="Example" %}
`JGET(SOBJECTFIELDDESCRIBE('Stub__c', 'Check__c'), 'Type')` will return '**BOOLEAN**' which is type of a Check\_\_c field.
{% endtab %}
{% endtabs %}

### <mark style="color:$primary;">Text Functions</mark>

***

#### SPECIALSYMBOLS

Allows the use of special symbols within the formula.

{% tabs %}
{% tab title="Usage" %}
Use any of the below expressions whenever you need a formula to contain special symbol(s):

`{QUOTE}` for single quote (`'`)\
`{DBL_QUOTE}` for double quote (`"`)\
`{OPEN_BKT}` for opening bracket (`(`)\
`{CLOSE_BKT}` for closing bracket (`)`)\
`{OPEN_SQR_BKT}` for opening square bracket (`[`)\
`{CLOSE_SQR_BKT}` for closing square bracket (`]`)\
`{TAB}` for tabulation\
`{BACK_SL}` for backslash (`\`)\
`{BR}` for line break\
`{RET}` for carriage return
{% endtab %}

{% tab title="Example" %}
`IF(STARTS({Request__c.Name}, 'RFQ'), '{OPEN_SQR_BKT}Request for Quote{CLOSE_SQR_BKT}', '{OPEN_SQR_BKT}Service{CLOSE_SQR_BKT}')` will return **\[Request for Quote]** if request name starts with 'RFQ', and **\[Service]** if it doesn't.
{% endtab %}
{% endtabs %}

#### CONTAINS

Compares two arguments of text and returns **true** if the first argument contains the second argument. If not, returns **false**.

{% tabs %}
{% tab title="Usage" %}
`CONTAINS(source_string, search_string)`

Replace `source_string` with the text that should be searched for a value of `search_string`.
{% endtab %}

{% tab title="Example" %}
`IF(CONTAINS({Opportunity.Product_Type__c}, 'part'), 'Parts', 'Service')` - this formula checks the content of a text field named *Product\_Type\_\_c* and returns '**Parts**' for any product with the word 'part' in it. Otherwise, it returns '**Service**'.
{% endtab %}

{% tab title="Example" %}
`IF(STARTS({Request__c.Name}, 'RFQ'), 'Request for Quote', 'Service')` - this formula checks the content of a *Name* field and returns '**Request for Quote**' for any request name starting with 'RFQ'. Otherwise, it returns '**Service**'.
{% endtab %}
{% endtabs %}

#### POS

Gets the position of a string within another string and returns it as a number.

{% tabs %}
{% tab title="Usage" %}
`POS(source_string, search_string)`

Replace `source_string` with the field or expression you want to search and `search_string` with the string you want to find.
{% endtab %}

{% tab title="Example" %}
`POS({Contact.Email}, '@')` returns the location of the `@` sign in a person's email address.
{% endtab %}
{% endtabs %}

#### ENDS

Determines whether a string ends with the characters of another string, returning **true** if it does. If not, returns **false**.

{% tabs %}
{% tab title="Usage" %}
`ENDS(source_string, search_string)`

Replace `source_string` with the text that should be checked for ending with the value of `search_string`.
{% endtab %}

{% tab title="Example" %}
`IF(ENDS({Company__c.Name}, 'TM'), 'Trademark', 'Service mark')` this formula checks the content of a *Name* field and returns '**Trademark**' for any item ending with 'TM'. Otherwise, it returns '**Service mark**'.
{% endtab %}
{% endtabs %}

#### SUBSTR

Returns a new String that begins with the character at the specified start position and extends until the character at the end position.

{% tabs %}
{% tab title="Usage" %}
`SUBSTR(source_string, start_index, end_index)`

Replace `source_string` with the string, field, or expression from which you want to get a substring, `start_index` with the position substring begins from, `end_index` the position substring ends at.

{% hint style="warning" %}
Note that position indexes start from 0.
{% endhint %}
{% endtab %}

{% tab title="Example" %}
`SUBSTR({Contact.Name}, 4, 8)` in case when *Contact.Name* value = '*Jefferson*' function will return '**erso**'.
{% endtab %}
{% endtabs %}

#### REPLACE

Replaces a sequence of characters in a string with another set of characters, and returns the resulting string.

{% tabs %}
{% tab title="Usage" %}
`REPLACE(source_string, from_string, to_string, regex_boolean_optional)`

Replace `source_string` with a string to be changed, `from_string` with characters to be replaced in `source_string`, and `to_string` with the replacement set of characters.

A User can pass a regular expression as a `from_string` argument as well. In this case, the user should add `true`' as an optional `regex_boolean_optional` argument.
{% endtab %}

{% tab title="Example 1" %}
`REPLACE({Opportunity.Unique_ID__c}, '3a', '2b')` - the *Unique\_ID\_\_c* will now contain new value with the '`3a`' replaced with '`2b`'.
{% endtab %}

{% tab title="Example 2" %}
`REPLACE('a3f45qq456', '[^0-9]', '', true)` will return **345456**.
{% endtab %}
{% endtabs %}

#### REMOVE

Removes one or more substrings from a source string.

{% tabs %}
{% tab title="Usage" %}
`REMOVE(source_string, string_parameter1, string_parameter2, ...)`

Replace `source_strin`g with the text field or expression you want to remove substrings from, and `string_parameter1`, `string_parameter2`, etc., with substrings to be removed from the source string.
{% endtab %}

{% tab title="Example" %}
`REMOVE('(555) 555-6789 ', ' ', '(', ')', '-')` will return '**5555556789**'.
{% endtab %}
{% endtabs %}

#### LEN

Returns the number of characters in a specified text string (string length).

{% tabs %}
{% tab title="Usage" %}
`LEN(string)`

Replace `string` with the field or expression whose length you want to get.
{% endtab %}

{% tab title="Example" %}
`LEN({Product__с.Code__c})` returns the length of a *Product.Code* field.
{% endtab %}
{% endtabs %}

#### SPLIT

Returns an array (a list) that contains each substring of the source string that is split on the given parameter. Substrings are placed in the array in the order in which they occur in the source string.

{% tabs %}
{% tab title="Usage" %}
`SPLIT(source_string, separator_string)`

Replace `source_string` with the text field or expression to be split, and `separator_string` with a substring to split on.
{% endtab %}

{% tab title="Example" %}
`SPLIT('AL, AK, AZ, AR', ', ')` will return an array of string type elements.
{% endtab %}
{% endtabs %}

#### JOIN

Joins the elements of an array into a single string separated by the specified separator.

{% tabs %}
{% tab title="Usage" %}
`JOIN(array, separator_string, left_string_optional, right_string_optional)`

Replace `array` argument with an array you want to join to a string, `separator_string` with a string to insert between array elements. `left_string_optiona`l and `right_string_optional` arguments are optional and can be used to modify array elements (add some text leftside or rightside of each of them) before joining.
{% endtab %}

{% tab title="Example" %}
Let *String\_\_c* field on *Stub\_\_c* object stores following string: '**Cars, Plains, Ships**'.

`{OPEN_SQR_BKT} + JOIN(SPLIT({Stub__c.String__c}, ','), ',', '{DBL_QUOTE}', '{DBL_QUOTE}') + {CLOSE_SQR_BKT}` - this formula will split field value to an array and then transform it to a following string: '**\["Cars", "Plains", "Ships"]**'.
{% endtab %}
{% endtabs %}

#### LOWER

Converts all of the characters in the string to lowercase.

{% tabs %}
{% tab title="Usage" %}
`LOWER(string)`

Replace `string` parameter with a string you need to convert to lowercase.
{% endtab %}

{% tab title="Example" %}
`LOWER({Account.Name})` will return *Account Name* value in lowercase.
{% endtab %}
{% endtabs %}

#### UPPER

Converts all of the characters in the string to uppercase.

{% tabs %}
{% tab title="Usage" %}
`UPPER(string)`

Replace `string` parameter with a string you need to convert to uppercase.
{% endtab %}

{% tab title="Example" %}
`UPPER({Account.Name})` will return *Account Name* value in uppercase.
{% endtab %}
{% endtabs %}

#### ESCAPE

Returns a string whose characters are escaped using the specified rule.

{% tabs %}
{% tab title="Usage" %}
`ESCAPE(string, JAVA|JSON|HTML|CSV|XML|UNICODE|QUOTE)`

Replace `string` parameter with a string to be escaped and specify one of the allowed escape rules as the second argument.
{% endtab %}

{% tab title="Example" %}
Let *Error\_\_c* value is '**Please, fill in "Name"**'.\
`ESCAPE({Stub__c.Error__c}, 'HTML')` will return '**Please, fill in \&quot;Name\&quot;**'\
`ESCAPE({Stub__c.Error__c}, 'JAVA')` will return '**Please, fill in \\"Name\\**"'\
`ESCAPE({Stub__c.Error__c}, 'CSV')` will return '**"Please, fill in ""Name"""**'\
`ESCAPE({Stub__c.Error__c}, 'XML')` will return '**Please, fill in \&quot;Name\&quot;**'

Let *String\_\_c* value is '**De onde você é?**'\
`ESCAPE({Stub__c.String__c}, 'UNICODE')` will return '**De onde voc\u00EA \u00E9?**'
{% endtab %}
{% endtabs %}

#### ID

Converts a string to a Salesforce object ID.

{% tabs %}
{% tab title="Usage" %}
`ID(string)`

Replace `string` argument with a value that should be treated as ID.
{% endtab %}

{% tab title="Example" %}
I`D('a006m00007sMGHjAAO')` will return **a006m00007sMGHjAAO** (some object ID).
{% endtab %}
{% endtabs %}

#### BR

Adds\
(line break) HTML tag when used.

{% tabs %}
{% tab title="Usage" %}
Add `BR()` to other functions or output text values wherever you need a line break.
{% endtab %}

{% tab title="Example" %}
`JOIN(SPLIT({Account.Account_Multy__c}, ';'), BR())` - this formula will first split field value to an array and then transform it to a following text:

```
'A
B
C'
```

{% endtab %}
{% endtabs %}

#### TEXT

Converts any data type into text.

{% tabs %}
{% tab title="Usage" %}
`TEXT(parameter)`

Replace `parameter` with the field or expression you want to convert to text format.
{% endtab %}

{% tab title="Example" %}
`IF(TEXT({Stub__c.Number__c}) = {Stub__c.Text__c}, true, false)` will return **true** when given *Stub.Number* numeric value is 25 and *Stub.Text* string value is '25'.

{% hint style="info" %}
You can't directly compare values of different types, so, such conversion may be very useful.
{% endhint %}
{% endtab %}
{% endtabs %}

#### BLOB

Casts the specified string to a Binary Large Object.

{% tabs %}
{% tab title="Usage" %}
`BLOB(string)`

Replace `string` argument with a string you need to cast to BLOB
{% endtab %}

{% tab title="Example" %}
`BLOB({Account.LongTextField__c})` will return field value as the BLOB content.
{% endtab %}
{% endtabs %}

#### TOBASE64

Converts a BLOB to a Base64-encoded String representing its normal form.

{% tabs %}
{% tab title="Usage" %}
`TOBASE64(blob_parameter)`

Replace `blob_parameter` with a BLOB content, you need to convert it to base64.
{% endtab %}

{% tab title="Example" %}
Let *Account.Name* value is '**Mary Ann'**.

`TOBASE64(BLOB({$Account.Name}))` will return '**TWFyeSBBbm4=**'
{% endtab %}
{% endtabs %}

#### FROMBASE64

Converts a Base64-encoded string to a BLOB representing its normal form.

{% tabs %}
{% tab title="Usage" %}
`FROMBASE64(string_parameter)`

Replace `string_parater` with Base64-encoded string.
{% endtab %}

{% tab title="Example" %}
`FROMBASE64('TWFyeSBBbm4=')` will return BLOB content of a '**Mary Ann**' string.
{% endtab %}
{% endtabs %}

#### TOHEX

Returns a hexadecimal (base 16) representation of the string.

{% tabs %}
{% tab title="Usage" %}
`TOHEX(string_parameter)`

Replace `string_parameter` with a string you need to convert to hex.
{% endtab %}

{% tab title="Example" %}
Let *Account.Name* value is '**Mary Ann'**.

`TOHEX({Account.Name})` will return '**4d61727920416e6e**'.
{% endtab %}
{% endtabs %}

#### FROMHEX

Converts the specified hexadecimal (base 16) value to a string.

{% tabs %}
{% tab title="Usage" %}
`FROMHEX(string_parameter)`

Replace `string_parameter` with a hex string, you need to convert it to text.
{% endtab %}

{% tab title="Example" %}
`FROMHEX('4d61727920416e6e')` will return '**Mary Ann**' string.
{% endtab %}
{% endtabs %}

#### URLENCODE

Encodes or decodes a string in application/x-www-form-urlencoded format using a specific encoding scheme.

{% tabs %}
{% tab title="Usage" %}
`URLENCODE(string_to_encode, format_optional, boolean_encode_optional)`

Replace `string_to_encode` parameter with a string to be encoded (or decoded).

Optionally: provide a specific encoding scheme in `format_optional` argument (default is **UTF-8**), and pass *false* as a `boolean_encode_optional` argument if you need to decode a string.
{% endtab %}

{% tab title="Example 1" %}
Let *String\_\_c* value is '**Test / me**'.

`URLENCODE({Stub__c.String__c})` will return '**Test+%2F+me**'.
{% endtab %}

{% tab title="Example 2" %}
Let *String\_\_c* value is '**Test+%2F+me**'.

`URLENCODE({Stub__c.String__c}, 'UTF-8', false)` will return '**Test / me**'.
{% endtab %}
{% endtabs %}

#### TOTEXTDURATION

Сonverts integer amount of minutes (numeric value) into a string in the `XXXh YYm` or `ZZZd XXh YYm` format.

{% tabs %}
{% tab title="Usage" %}
`TOTEXTDURATION(integer_minutes_parameter, hours_in_day_optional)`

Replace `integer_minutes_parameter` with an integer representing the number of minutes to get the duration in hours and minutes.

Optional `hours_in_day_optional` argument represents how many hours in a working day are set. Add it to get duration in working days, hours, and minutes.
{% endtab %}

{% tab title="Example 1" %}
`TOTEXTDURATION(6067)` will return '**101h 7m'**.
{% endtab %}

{% tab title="Example 2" %}
`TOTEXTDURATION(6067,8)` will return '**12d 5h 7m**', where each day has 8 hours (useful to calculate how many business days will take some process).
{% endtab %}

{% tab title="Example 3" %}
`TOTEXTDURATION(6067,24)` will return '**4d 5h 7m**' where each day has 24 hours (in this case you will get duration in full days).
{% endtab %}
{% endtabs %}

#### FROMTEXTDURATION

Converts string in the `XXXh YYm` or `ZZZd XXh YYm` format to an integer representing the number of minutes. Returns a numeric value.

{% tabs %}
{% tab title="Usage" %}
`FROMTEXTDURATION(string_parameter, hours_in_day_optional)`

Replace `string_parameter` with a string to be converted.

Add optional `hours_in_day_optional` argument to specify how many work hours one working day consists of.
{% endtab %}

{% tab title="Example 1" %}
`FROMTEXTDURATION('62h 34m')` will return **3754** (62\*60 + 34).
{% endtab %}

{% tab title="Example 2" %}
`FROMTEXTDURATION('3d 2h 34m')` will return **4474** (3\*2\u34;*\**\u36;0 + 2\*60 + 34).
{% endtab %}

{% tab title="Example 3" %}
`FROMTEXTDURATION('3d 2h 34m',8)` will return **1594** (3\*\u38;*\**\u36;0 + 2\*60 + 34).
{% endtab %}
{% endtabs %}

#### MATCH

Searches a string for a match against a regular expression, and returns the matches in an array.

{% tabs %}
{% tab title="Usage" %}
`MATCH(regex_string, data_string)`

Replace `regex_string` with a regular expression, and `data_string` with the string to compare to the regex.
{% endtab %}

{% tab title="Example" %}
Let *Duration\_\_c* value is '**8d**'.

`MATCH('[^abc]', {Stub__c.Duration__c})` will return following array: **(8, d)**.
{% endtab %}
{% endtabs %}

### <mark style="color:$primary;">Logical Functions</mark>

***

#### ISBLANK

Analyzes the given expression and returns:

* With one argument: a boolean value (**true** if the argument expression does not have a value (is empty or does not exist), otherwise returns **false**).
* With 2 arguments: some expression evaluation result or the first argument value.
* With 3 arguments: some expression evaluation result or some other expression evaluation result.

{% tabs %}
{% tab title="Usage" %}
`ISBLANK(parameter1)` or\
`ISBLANK(parameter1, if_true_parameter2)` or\
`ISBLANK(parameter1, if_true_parameter2, if_false_parameter3)`

Replace:

`parameter1` with a logical expression you want to evaluate;

`if_true_parameter2` with the result you want to get when `parameter1` returns **true**;

`if_false_parameter3` with the result you want to get when `parameter1` returns **false**.
{% endtab %}

{% tab title="Example 1" %}
`ISBLANK({$Variables.someDate})` will return **true** if variable *someDate* value is empty (or this variable does not exist at all), and **false** if it has a value.
{% endtab %}

{% tab title="Example 2" %}
`ISBLANK({$Variables.someDate}, '{$System.date}')` will return current system date if variable *someDate* value is empty (or this variable does not exist at all), and a value of this variable in other case.
{% endtab %}

{% tab title="Example 3" %}
`ISBLANK({$Variables.someDate}, '{$System.date}', '{Opportunity.closeDate}')` will return current system date if variable *someDate* value is empty (or this variable does not exist at all), and value of *Opportunity.closeDate* field if variable has a value.
{% endtab %}
{% endtabs %}

#### NOT

Returns **false** for TRUE and **true** for FALSE (inverts the result of the logical expression).

{% tabs %}
{% tab title="Usage" %}
`NOT(parameter)`

Replace `parameter` with a logical expression that you want to evaluate.
{% endtab %}

{% tab title="Example" %}
`NOT(CONTAINS({Opportunity.Product_Type__c}, 'part'))` will return **true** if *Product Type* value contains '*part*' string and **false** if it doesn't.
{% endtab %}
{% endtabs %}

#### IF

Determines if the condition expression is true or false. Returns a given value if true and another value if false.

{% tabs %}
{% tab title="Usage" %}
`IF(condition, result_if_true, result_if_false)`

Replace:\
`condition` with the logical expression,\
`result_if_true` with the result you want to get if the `condition` is true,\
`result_if_false` with the result you want to get if the `condition` is false.
{% endtab %}

{% tab title="Example" %}
I`F({Opportunity.Items_Number__c} > 0, 'In Progress', 'Pending')` will return 'I**n Progress**' if number of items is more than *0*, and '**Pending**' in other case.
{% endtab %}
{% endtabs %}

#### AND

Returns a boolean value: **true** if all parameter values are *true*, and **false** if one or more values are *false*.

{% tabs %}
{% tab title="Usage" %}
`AND(boolean_parameter1, boolean_parameter2, ...)`

Replace `boolean_parameter1`, `boolean_parameter2`, etc with values or expressions that you want evaluated.
{% endtab %}

{% tab title="Example" %}
`AND({Contact.Migrated__c} = 'Yes', {Contact.Trusted__c} = 'Yes')` will return **true** if both *Contact Migrated* and *Contact Trusted* values are equal to 'Yes'. Otherwise it will return **false**.
{% endtab %}
{% endtabs %}

#### OR

Returns a boolean value: true if at least one parameter is true, and false if all parameters are false.

{% tabs %}
{% tab title="Usage" %}
`OR(boolean_parameter1, boolean_parameter2, ...)`

Replace `boolean_parameter1`, `boolean_parameter2`, etc., with values or expressions you want evaluated.
{% endtab %}

{% tab title="Example" %}
`OR({$Variables.Exhibition_month__c} = 'MAR', {Partner__c.Exhibition_month__c} = 'APR', {Partner__c.Exhibition_month__c} = 'MAY')` - this formula will return **true** if *Exhibition\_month\_\_c* is set to any spring month, and **false** if it is set to any other value.
{% endtab %}
{% endtabs %}

#### CASE

Checks the given expression value against a series of case values. If the expression is equal to one of the case values, it returns the corresponding result.

{% tabs %}
{% tab title="Usage" %}
`CASE(value, case1, result1, case2, result2, ..., else_result)`

Replace:

`value` with the field or value you want compared to each specified case;

each case (`case1`, `case2`, etc.) with the data/expression/field for comparison;

each result (`result1`, `result2`, etc.) with data/expression/field that must be returned for the proper case;

`else_result` with the data/expression/field that must be returned when the expression does not equal any case.
{% endtab %}

{% tab title="Example" %}
Let *String\_\_c* = '**Plane**'.

`CASE({Stub__c.String__c}, 'Car' , 'drive', 'Plane', 'fly', 'Boat', 'sail', 'none')` will return '**fly**'.
{% endtab %}
{% endtabs %}

#### IN

Returns a boolean (**true** or **false**) result depending on whether the checked value is present in the given list.

{% tabs %}
{% tab title="Usage" %}
`IN(value, list_of_values OR [value1, value2, ...])`

Replace:

`value` with the value you need to check in the list;

`list_of_values` with the list to compare value parameter to (or pass a list of values as separate parameters `value1`, `value2`, etc).

{% hint style="info" %}
You can also pass a combination of lists and separate values as parameters.
{% endhint %}
{% endtab %}

{% tab title="Example 1" %}
`IN({Opportunity.Stage}, 'Lost', 'Won', 'Review', 'Needs Analysis')` returns **true** if *Opportunity.Stage* field value is present in the given set of strings (`'Lost', 'Won', 'Review', 'Needs Analysis'`) and **false** if it is not.
{% endtab %}

{% tab title="Example 2" %}
Let *Stub\_\_c.String\_\_c* = '**\["YESTERDAY", "NOW", "TOMORROW"]**'.

`IN('NOW', JPARSE({Stub__c.String__c}))` will return **true**.
{% endtab %}

{% tab title="Example 3" %}
Let *Stub\_\_c.String\_\_c* = '**\["YESTERDAY", "NOW", "TOMORROW"]**' and *Stub\_\_c.Area\_\_c* = '**NEXT YEAR**'.

`IN('NEXT YEAR', JPARSE({Stub__c.String__c}), {Stub__c.Area__c}, 'PREVIOUS YEAR')` will return **true**.
{% endtab %}
{% endtabs %}

#### NOTIN

Returns a boolean (**true** or **false**) result depending on whether the checked value is NOT present in the given list.

{% tabs %}
{% tab title="Usage" %}
`NOTIN(value, list_of_values OR [value1, value2, ...])`

Replace:

`value` with a value you need to check in the list;

`list_of_values` with the list to compare value parameter to (or pass a list of values as separate parameters `value1`, `value2`, etc).

{% hint style="info" %}
You can also pass a combination of lists and separate values as parameters.
{% endhint %}
{% endtab %}

{% tab title="Example 1" %}
`NOTIN({Opportunity.Stage}, 'Lost', 'Won', 'Review', 'Needs Analysis')` returns **true** if *Opportunity.Stage* field value is not present in the given set of strings (`'Lost', 'Won', 'Review', 'Needs Analysis'`) and **false** if it is present there.
{% endtab %}

{% tab title="Example 2" %}
Let *Stub\_\_c.String\_\_c* = '**\["YESTERDAY", "NOW", "TOMORROW"]**'.

`NOTIN('NOW', JPARSE({Stub__c.String__c}))` will return **false**.
{% endtab %}

{% tab title="Example 3" %}
Let *Stub\_\_c.String\_\_c* = '**\["YESTERDAY", "NOW", "TOMORROW"]**' and *Stub\_\_c.Area\_\_c* = '**NEXT YEAR**'.

`IN('NEXT MONTH', JPARSE({Stub__c.String__c}), {Stub__c.Area__c}, 'PREVIOUS YEAR')` will return **true**.
{% endtab %}
{% endtabs %}

#### INSTANCEOF

Validates if the specified value is an instance of a declared type. Returns a boolean result (**true** or **false**).

{% tabs %}
{% tab title="Usage" %}
`INSTANCEOF(value, Decimal|Boolean|String|Date|DateTime)`

Replace `value` argument with a value you want to validate and pass one of the allowed types (`Decimal`, `Boolean`, `String`, `Date` or `DateTime`) as the second argument.
{% endtab %}

{% tab title="Example" %}
`INSTANCEOF({$Variables.Result}, Decimal)` will return **true** if variable "*Result*" is a number, and **false** if it is not.
{% endtab %}
{% endtabs %}

### <mark style="color:$primary;">Numeric Functions</mark>

***

#### NUMBER

Converts a string representing a number into a decimal number.

{% tabs %}
{% tab title="Usage" %}
`NUMBER(parameter)`

Replace `parameter` With a string, you need to convert it to a decimal.

{% hint style="warning" %}
If `parameter` contains non-digit symbols, the function will throw an exception.
{% endhint %}
{% endtab %}

{% tab title="Example" %}
`NUMBER({Opportunity.Unique_ID__c})` returns decimal value of *Unique\_ID* field, e.g if *Unique\_ID* = *'1234'* function will return **1234.0**.
{% endtab %}
{% endtabs %}

#### INTNUMBER

Converts a string representing a number into an integer.

{% tabs %}
{% tab title="Usage" %}
`INTNUMBER(parameter)`

Replace `parameter` with a string, you need to convert it to an integer.

{% hint style="warning" %}
If `parameter` contains non-digit symbols, the function will throw an exception.
{% endhint %}
{% endtab %}

{% tab title="Example" %}
I`NTNUMBER({Opportunity.Unique_Value__c})` returns integer value of *Unique\_Value* field, e.g for *Unique\_Value\_\_с* = *'1234.32'* it will return **1234**.
{% endtab %}
{% endtabs %}

#### SUM

Returns an integer or decimal value representing a sum of numeric parameters.

{% tabs %}
{% tab title="Usage" %}
`SUM(list_of_values OR value1, value2, ...)`

Replace `list_of_values` argument with the list of numeric values (or pass numeric values as `value1`, `value2'`, etc). arguments.

{% hint style="info" %}
You can also pass a combination of arrays and separate numeric arguments.
{% endhint %}
{% endtab %}

{% tab title="Example 1" %}
Let *March Amount* = **5.5**, *April Amount* = **10** and *May Amount* = **15**.

`SUM({Stub__c.March_Amount__c}, {Stub__c.April_Amount__c}, {Stub__c.May_Amount__c})` will return **30.5**.
{% endtab %}

{% tab title="Example 2" %}
Let *String* field stores following value: **'8, 8, 10, 12'**.

`SUM(SPLIT({Stub__c.String__c}, ','))` will return **48**.
{% endtab %}

{% tab title="Example 3" %}
Let *March Amount* = **5.5**, *April Amount* = **10**, *May Amount* = **15** and *String* = **'8, 8, 10, 12'**.

`SUM({Stub__c.March_Amount__c}, {Stub__c.April_Amount__c}, {Stub__c.May_Amount__c}, SPLIT({Stub__c.String__c}, ','))` will return **78.5** (30.5 + 48).
{% endtab %}
{% endtabs %}

#### SUB

Returns an integer or decimal value representing the result of sequential subtraction.

{% tabs %}
{% tab title="Usage" %}
`SUB(list_of_values OR value1, value2, ...)`

Replace `list_of_values` argument with the list of numeric values (or pass numeric values as `value1`, `value2`, etc. arguments).

{% hint style="info" %}
You can also pass a combination of arrays and separate numeric arguments - they will be subtracted first, and then the results will be subtracted.
{% endhint %}
{% endtab %}

{% tab title="Example 1" %}
Let *March Amount* = **5**, *April Amount* = **10** and *May Amount* = **15**.

`SUB({Stub__c.March_Amount__c}, {Stub__c.April_Amount__c}, {Stub__c.May_Amount__c})` will return **-20** (5 - 10 - 15).
{% endtab %}

{% tab title="Example 2" %}
Let *String* field stores following value: **'8, 8, 10, 12'**.

`SUB(SPLIT({Stub__c.String__c}, ','))` will return **-22** (8 - 8 - 10 - 12).
{% endtab %}

{% tab title="Example 3" %}
Let *March Amount* = **5**, *April Amount* = **10,** *May Amount* = **15** and *String* = **'8, 8, 10, 12'**.

`SUM({Stub__c.March_Amount__c}, {Stub__c.April_Amount__c}, {Stub__c.May_Amount__c}, SPLIT({Stub__c.String__c}, ','))` will return **2** ( (5 - 10 - 15) - (8 - 8 - 10 - 12) ).
{% endtab %}
{% endtabs %}

#### MULT

Returns an integer or decimal value representing the result of multiplication.

{% tabs %}
{% tab title="Usage" %}
`MULT(list_of_values OR value1, value2, ...)`

Replace `list_of_values` argument with the list of numeric values (or pass numeric values as `value1`, `value2`, etc. arguments).

{% hint style="info" %}
You can also pass a combination of arrays and separate numeric arguments.
{% endhint %}
{% endtab %}

{% tab title="Example 1" %}
Let *March Amount* = **5**, *April Amount* = **10** and *May Amount* = **15**.

`MULT({Stub__c.March_Amount__c}, {Stub__c.April_Amount__c}, {Stub__c.May_Amount__c})` will return **750**.
{% endtab %}

{% tab title="Example 2" %}
Let *String* field stores following value: **'8, 8, 10, 12'**.

`MULT(SPLIT({Stub__c.String__c}, ','))` will return **7680** (8 \* 8 \* 10 \* 12).
{% endtab %}

{% tab title="Example 3" %}
Let *March Amount* = **5**, *April Amount* = **10**, *May Amount* = **15** and *String* =: **'8, 8, 10, 12'**.

`MULT({Stub__c.March_Amount__c}, {Stub__c.April_Amount__c}, {Stub__c.May_Amount__c}, SPLIT({Stub__c.String__c}, ','))` will return **5760000** (750 \* 7680).
{% endtab %}
{% endtabs %}

#### DIV

Returns an integer or decimal value representing the result of sequential dividing.

{% tabs %}
{% tab title="Usage" %}
`DIV(list_of_values OR value1, value2, ...)`

Replace `list_of_values` argument with the list of numeric values (or pass numeric values as `value1`, `value2`, etc. arguments).

{% hint style="info" %}
You can also pass a combination of arrays and separate numeric arguments - they will be divided first, and then the results will be divided.
{% endhint %}
{% endtab %}

{% tab title="Example 1" %}
Let *x* = **450**, *y* = **10** and *z* = **15**.

`DIV({Stub__c.x}, {Stub__c.y}, {Stub__c.z})` will return **3** (450 / 10 / 15).
{% endtab %}

{% tab title="Example 2" %}
Let *String* field stores following value: **'1200, 2, 10, 12'**.

`DIV(SPLIT({Stub__c.String__c}, ','))` will return **5** (1200 / 2 / 10 / 12).
{% endtab %}

{% tab title="Example 3" %}
Let *String* field stores following value: **'\["24", "2", "3"]'** and *Area* field stores **'\["6", "3"]'**.

`DIV(JPARSE({Stub__c.String__c}), JPARSE({Stub__c.Area__c}))` will return **2** ( (24 / 2 / 3) / (6 / 3) ).
{% endtab %}
{% endtabs %}

#### SQRT

Returns the positive square root of a given number.

{% tabs %}
{% tab title="Usage" %}
`SQRT(parameter)`

Replace `parameter` with the field or expression you want computed into a square root.
{% endtab %}

{% tab title="Example" %}
`SQRT(26.5)` returns **5.1478150704935**.
{% endtab %}
{% endtabs %}

#### ABS

Calculates the absolute value of a number (a number without its positive or negative sign).

{% tabs %}
{% tab title="Usage" %}
`ABS(parameter)`

Replace `parameter` with a field or numeric value that has the sign you want removed.
{% endtab %}

{% tab title="Example" %}
`ABS({Department__c.Expected_Revenue__c})` calculates the positive value of the *Expected Revenue* amount regardless of whether it is positive or negative.
{% endtab %}
{% endtabs %}

#### LOG

Returns the base 10 logarithm of a number.

{% tabs %}
{% tab title="Usage" %}
`LOG(parameter)`

Replace `parameter` with the field or expression from which you want the base 10 logarithm calculated.
{% endtab %}

{% tab title="Example" %}
Let *{$Variables.Concentration}* = **10^-6**.

`LOG({$Variables.Concentration})` will return **-6**.
{% endtab %}
{% endtabs %}

#### POW

Returns one value (*x*) raised to the power of another value (*y*), i.e., **x^y**.

{% tabs %}
{% tab title="Usage" %}
`POW(parameter, exponent)`

Replace `parameter` argument with an integer or double value that needs to be raised to the power of `exponent` argument.
{% endtab %}

{% tab title="Example 1" %}
`POW (8, 3)` will return **512**.
{% endtab %}

{% tab title="Example 2" %}
`POW (1000, -1/3)` will return **0.1**.
{% endtab %}
{% endtabs %}

#### MOD

Returns a remainder after a number is divided by a specified divisor.

{% tabs %}
{% tab title="Usage" %}
`MOD(parameter1, parameter2)`

Replace:

`parameter1` with the field or expression you want to be divided;

`parameter2` with the number to use as the divisor.
{% endtab %}

{% tab title="Example" %}
`MOD(123, 100)` will return **23**.
{% endtab %}
{% endtabs %}

#### MIN

Returns the lowest number from several numeric values.

{% tabs %}
{% tab title="Usage" %}
`MIN(list_of_values OR value1, value2, ...)`

Replace `list_of_values` argument with the list of numeric values (or pass numeric values as `value1`, `value2`, etc. arguments).

{% hint style="info" %}
You can also pass a combination of arrays and separate numeric arguments.
{% endhint %}
{% endtab %}

{% tab title="Example" %}
Let *String* field stores following value: **'\["12", "2", "3"]'** and *Area* field stores **'\["6", "3"]'**.

`MIN(JPARSE({Stub__c.String__c}), JPARSE({Stub__c.Area__c}))` will return **2**.
{% endtab %}
{% endtabs %}

#### MAX

Returns the highest number from several numeric values.

{% tabs %}
{% tab title="Usage" %}
`MAX(list_of_values OR value1, value2, ...)`

Replace `list_of_values` argument with the list of numeric values (or pass numeric values as `value1`, `value2`, etc. arguments).

{% hint style="info" %}
You can also pass a combination of arrays and separate numeric arguments.
{% endhint %}
{% endtab %}

{% tab title="Example" %}
Let *String* field stores following value: **'\["12", "2", "3"]'** and *Area* field stores **'\["6", "3"]'**.

`MAX(JPARSE({Stub__c.String__c}), JPARSE({Stub__c.Area__c}), 9, 13)` will return **13**.
{% endtab %}
{% endtabs %}

#### ROUND

Returns the rounded approximation of a decimal value.

{% tabs %}
{% tab title="Usage" %}
`ROUND(decimal_value, CEILING|DOWN|FLOOR|UP|HALF_DOWN|HALF_UP)`

Replace `decimal_value` with the decimal to be rounded, and pass one of the allowed rounding modes (`CEILING`, `FLOOR`, `DOWN`, `UP`, `HALF_DOWN`, `HALF_UP`).
{% endtab %}

{% tab title="Examples" %}
`ROUND(122.3456669, 'CEILING')` will return **123**\
`ROUND(-122.3456669, 'CEILING')` will return **-122**

`ROUND(122.3456669, 'FLOOR')` will return **122**\
`ROUND(-122.3456669, 'FLOOR')` will return **-123**

`ROUND(122.3456669, 'DOWN')` will return **122**\
`ROUND(-122.3456669, 'DOWN')` will return **-122**

`ROUND(122.3456669, 'UP')` will return **123**\
`ROUND(-122.3456669, 'UP')` will return **-123**

`ROUND(122.5, 'HALF_DOWN')` will return **122**\
`ROUND(-122.5, 'HALF_DOWN')` will return **-122**

`ROUND(122.5, 'HALF_UP')` will return **123**\
`ROUND(-122.5, 'HALF_UP')` will return **-123**
{% endtab %}
{% endtabs %}

#### SCALE

Returns the decimal scaled to the specified number of decimal places.

{% tabs %}
{% tab title="Usage" %}
`SCALE(decimal_value, decimal_places)`

Replace:

`decimal_value` with the decimal value you want scaled;

`decimal_places` with a number of decimal places to be left.
{% endtab %}

{% tab title="Example 1" %}
Let *Salary* value = **122.3456669.**

`SCALE({User.Salary__c}, 2)` will return **122.35**.
{% endtab %}

{% tab title="Example 2" %}
Let *Number* value = **25**.

`SCALE({Stub__c.Number__c}, 3)` will return **25.000**.
{% endtab %}
{% endtabs %}

#### RANDOM

Generates a random 19-digit number (positive or negative; a negative number will also have a sign).

{% tabs %}
{% tab title="Usage" %}
`RANDOM()`

This function doesn't require parameters.
{% endtab %}

{% tab title="Example" %}
`RANDOM()` will return a random 19-digit number like **2139744657709176245** or **-6535028942888403203**.
{% endtab %}
{% endtabs %}

#### FORMATNUMBER

Formats the decimal value for readability (adds thousands separators and removes leading zeros).

{% tabs %}
{% tab title="Usage" %}
`FORMATNUMBER(decimal_value)`

Replace `decimal_value` with the decimal value you want to be formatted.
{% endtab %}

{% tab title="Example" %}
`FORMATNUMBER(-01233534343453.566)` will return **-1,233,534,343,453.566**.
{% endtab %}
{% endtabs %}

### <mark style="color:$primary;">Date and Time Functions</mark>

***

#### MINUTES

Converts a number into a minutes value.

{% tabs %}
{% tab title="Usage" %}
`MINUTES(parameter)`

Replace `parameter` with the number value to convert it to minutes.
{% endtab %}

{% tab title="Example" %}
`{Opportunity.RFQ_Date__c} + MINUTES(30)` will add *30* minutes to *RFQ\_Date\_\_c* value.

{% hint style="warning" %}
If do not convert number to minutes, error will occur.
{% endhint %}
{% endtab %}
{% endtabs %}

#### HOURS

Converts a number into an hours value.

{% tabs %}
{% tab title="Usage" %}
`HOURS(parameter)`

Replace `parameter` with the number value to convert it to hours.
{% endtab %}

{% tab title="Example" %}
`{Opportunity.RFQ_Date__c} + HOURS(12)` will add *12* hours to *RFQ\_Date\_\_c* value.

{% hint style="warning" %}
If do not convert number to hours, error will occur.
{% endhint %}
{% endtab %}
{% endtabs %}

#### DATE

Returns a date value from year, month, and day values you enter.

{% tabs %}
{% tab title="Usage" %}
`DATE(year, month, day)`

Replace:

`year` with a four-digit year;

`month` with a two-digit month;

`day` with a two-digit day.
{% endtab %}

{% tab title="Example" %}
`DATE(2016, 10, 10)` returns **October 10, 2016** as a date value.
{% endtab %}
{% endtabs %}

#### DATETIME

Returns a date value from year, month, day, hours, minutes, and seconds values in the local time zone of the current user.

{% tabs %}
{% tab title="Usage" %}
`DATETIME(year, month, day, hours, minutes, seconds)`

Replace parameters with integer values of year, month, day, hours, minutes, and seconds.
{% endtab %}

{% tab title="Example" %}
`DATETIME(2005, 1, 2, 20, 30, 0)` returns **January 2, 2005, 8:30 PM** as a datetime value.
{% endtab %}
{% endtabs %}

#### DATETIMEGMT

Returns a date value from year, month, day, hours, minutes, seconds in a GMT datetime value.

{% tabs %}
{% tab title="Usage" %}
`DATETIMEGMT(year, month, day, hours, minutes, seconds)`

Replace parameters with integer values of year, month, day, hours, minutes, and seconds.
{% endtab %}

{% tab title="Example" %}
`DATETIMEGMT(2005, 1, 2, 20, 30, 0)` returns **January 2, 2005, 8:30 PM GMT** as a datetime value.
{% endtab %}
{% endtabs %}

#### DATEVALUE

Returns a date value for a date, datetime, or text expression in the local time zone of the current user.

{% tabs %}
{% tab title="Usage" %}
`DATEVALUE(datetime_or_string)`

Replace `datetime_or_string` with a date, datetime, or text value.
{% endtab %}

{% tab title="Example" %}
`DATEVALUE('11/15/2015')` returns **November 15, 2015** as a date value.
{% endtab %}
{% endtabs %}

#### DATETIMEVALUE

Returns a datetime value for a date, datetime, or text expression in the local time zone of the current user.

{% tabs %}
{% tab title="Usage" %}
`DATETIMEVALUE(date_or_string)`

Replace `date_or_string` with a date, datetime, or text value.
{% endtab %}

{% tab title="Example" %}
`DATETIMEVALUE('2005-11-15 17:00:00')` returns **November 15, 2005 5:00 PM** as a datetime value.
{% endtab %}
{% endtabs %}

#### DATETIMEVALUEGMT

Returns a date value for a date, datetime, or text expression in the GMT time zone.

{% tabs %}
{% tab title="Usage" %}
`DATETIMEVALUEGMT(datetime_or_string)`

Replace `datetime_or_string` with a date/datetime or text value.
{% endtab %}

{% tab title="Example" %}
`DATETIMEVALUEGMT('2015-11-15 17:00:00')` returns **November 15, 2005 5:00 PM** as a datetime value in GMT time zone.
{% endtab %}
{% endtabs %}

#### DATETIMETOGMT

Converts a datetime value from the local time zone of a context user to the Greenwich Mean Time value with the datetime type.

{% tabs %}
{% tab title="Usage" %}
`DATETIMETOGMT(datetime_object)`

Replace `datetime_object` with a datetime value in the context of the user's local time zone.
{% endtab %}

{% tab title="Example" %}
Let current user local datetime value is **6/9/2015 10:09:14 PST**.

`DATETIMETOGMT({$System.DateTime})` returns **6/9/2015 17:09:14 GMT** as a datetime value.
{% endtab %}
{% endtabs %}

#### ADDDAYS

Returns a new date or datetime value that adds the specified number of days to the value of this instance.

{% tabs %}
{% tab title="Usage" %}
`ADDDAYS(date/datetime, number_of_days, boolean_consider_weekends)`

Replace:

`date/datetime` with the date or datetime you want to add days to;

`number_of_days` with an integer number of days that needs to be added to the original value;

`boolean_consider_weekends` with *true* if calculation should exclude Saturday and Sunday, or *false* if it should not.
{% endtab %}

{% tab title="Example" %}
`ADDDAYS(DATE(2019, 02, 12), 2, false)` will return **2019-02-14 00:00:00**.
{% endtab %}
{% endtabs %}

#### ADDMONTHS

Adds the given number of months to the date or datetime value.

{% tabs %}
{% tab title="Usage" %}
`ADDMONTHS(date/datetime, number_of_months)`

Replace:

`date/datetime` with the date or datetime you want to add months to;

`number_of_months` with the integer number of months that needs to be added to the original value.
{% endtab %}

{% tab title="Example" %}
`ADDMONTHS(DATE(2019, 02, 12), 5)` will return **2019-07-12 00:00:00**.
{% endtab %}
{% endtabs %}

#### ADDYEARS

Adds the given number of years to the date or datetime value.

{% tabs %}
{% tab title="Usage" %}
`ADDYEARS(date/datetime, number_of_years)`

Replace:

`date/datetime` with the date or datetime you want to add years to;

`number_of_years` with the integer number of years that needs to be added to the original value.
{% endtab %}

{% tab title="Example" %}
`ADDYEARS(DATE(2019, 02, 12), 5)` will return **2024-02-12 00:00:00**.
{% endtab %}
{% endtabs %}

#### ADDINTERVAL

Adds a time interval to the datetime based on the business hours for the current Salesforce organization.

{% hint style="info" %}
To access business hours records, navigate to Setup > Company Profile > Business Hours.
{% endhint %}

{% tabs %}
{% tab title="Usage" %}
`ADDINTERVAL(datetime, interval_string, business_hours_id_optional)`

Replace:

`datetime` parameter with a datetime value;

`interval_sting` with time interval in the *'1d 1h 1m'* format ;

`business_hours_id_optional` with an ID of a business hours record used in your organization.

{% hint style="warning" %}
Note: a `business_hours_id_optional` parameter is optional.

If a pass an empty string or null, an interval will be added using a 24-hour day, a 7-day week, and a 365-day year.
{% endhint %}
{% endtab %}

{% tab title="Example" %}
Let current system datetime value is **Tuesday, 04/01, 8 AM** and business hours are set to **8 AM to 11 AM, Monday to Friday**.

`FORMAT(ADDINTERVAL({$System.DateTime}, '5h 30m', 'id_of_business_hours_record'))` will return **Wednesday, 04/02, 10:30 AM** value.
{% endtab %}
{% endtabs %}

#### SUBTRACTINTERVAL

Subtracts the time interval from the datetime based on the business hours for the current Salesforce organization.

{% hint style="info" %}
To access business hours records, navigate to Setup > Company Profile > Business Hours.
{% endhint %}

{% tabs %}
{% tab title="Usage" %}
`SUBTRACTINTERVAL(datetime, interval_string, business_hours_id_optional)`

Replace:

`datetime` parameter with a datetime value;

`interval_sting` with time interval in the *'1d 1h 1m'* format;

`business_hours_id_optional` with an ID of a business hours record used in your organization.

{% hint style="warning" %}
Note: a `business_hours_id_optional` parameter is optional.

If you pass an empty string or null, the interval will be subtracted using a 24-hour day, a 7-day week, and a 365-day year.
{% endhint %}
{% endtab %}

{% tab title="Example" %}
`FORMAT(SUBSTRACTINTERVAL(DATETIME(2019, 03, 12, 11, 0, 0), '3h 30m'))` will return **3/12/2019 7:30 AM**.
{% endtab %}
{% endtabs %}

#### NORMALIZEDATE

Moves a date or datetime to the next working day within business hours.

{% hint style="info" %}
To access business hours records, navigate to Setup > Company Profile > Business Hours.
{% endhint %}

{% tabs %}
{% tab title="Usage" %}
`NORMALIZEDATE(date/datetime, business_hours_id_optional)`

Replace:

`date/datetime` parameter with the value you need to normalize;

`business_hours_id_optional` with an ID of a business hours record used in your organization.

{% hint style="warning" %}
Note: a `business_hours_id_optional` parameter is optional.

If you pass an empty string or null, the default business hours setting for the organization will be used to calculate the date or datetime.
{% endhint %}
{% endtab %}

{% tab title="Example" %}
Let current system datetime value is **Saturday, 03/16, 2 PM** and business hours are set to **8 AM to 11 AM, Monday to Friday**.

`NORMALIZEDATE({$System.DateTime}, id_of_business_hours_record)` will return a datetime value for **Monday, 3/18 8:00 AM**.
{% endtab %}
{% endtabs %}

#### STARTOFDAY

Moves a date/datetime to the start of the working day according to business hours.

{% hint style="info" %}
To access business hours records, navigate to Setup > Company Profile > Business Hours.
{% endhint %}

{% tabs %}
{% tab title="Usage" %}
`STARTOFDAY(date/datetime, business_hours_id_optional)`

Replace:

`date/datetime` parameter with the value you need to calculate;

`business_hours_id_optional` with an ID of a business hours record used in your organization.

{% hint style="warning" %}
Note: a `business_hours_id_optional` parameter is optional.

If you pass an empty string or null, the organization's default business hours setting will be used to calculate the date or datetime.
{% endhint %}
{% endtab %}

{% tab title="Example" %}
Let current system datetime value is **Wednesday, 03/13, 2 PM** and business hours are set to **8 AM to 11 AM, Monday to Friday**.

`STARTOFDAY({$System.DateTime}, id_of_business_hours_record)` will return datetime value for **Wednesday, 3/13 8:00 AM**.
{% endtab %}
{% endtabs %}

#### ENDOFDAY

Moves a date/datetime to the end of the working day according to business hours.

{% hint style="info" %}
To access business hours records, navigate to Setup > Company Profile > Business Hours.
{% endhint %}

{% tabs %}
{% tab title="Usage" %}
`ENDOFDAY(date/datetime, business_hours_id_optional)`

Replace:

`date/datetime` parameter with the value you need to calculate;

`business_hours_id_optional` with an ID of a business hours record used in your organization.

{% hint style="warning" %}
Note: a `business_hours_id_optional` parameter is optional.\
If you pass an empty string or null, the organization's default business hours setting will be used to calculate the date or datetime.
{% endhint %}
{% endtab %}

{% tab title="Example" %}
Let current system datetime value is **Wednesday, 03/13, 2 PM** and business hours are set to **8 AM to 11 AM, Monday to Friday**.

`ENDOFDAY({$System.DateTime}, id_of_business_hours_record)` will return datetime value for **Wednesday, 3/13, 11AM**.
{% endtab %}
{% endtabs %}

#### FORMAT

Returns a value containing a date expression formatted according to the given parameter.

{% tabs %}
{% tab title="Usage" %}
`FORMAT(date/datetime, format_string_optional)`

Replace:

`date/datetime` with the original date or datetime value

`format_string_optional` with an optional valid format expression.

{% hint style="info" %}
Check supported formats here: <http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html>.
{% endhint %}
{% endtab %}

{% tab title="Example 1" %}
`FORMAT(DATE(2019, 02, 12))` will return **2/12/2019**.
{% endtab %}

{% tab title="Example 2" %}
`FORMAT(DATETIME(2019, 02, 12, 11, 23, 0))` will return **2/12/2019 11:23 AM**
{% endtab %}

{% tab title="Example 3" %}
`FORMAT(DATETIME(2019, 02, 12, 11, 23, 0), 'h:mm a')` **will return 11:23 AM**.
{% endtab %}
{% endtabs %}

#### FORMATGMT

Returns a value containing an expression formatted according to the given parameter in GMT time value.

{% tabs %}
{% tab title="Usage" %}
`FORMATGMT(date/datetime, format_string_optional)`

Replace:

`date/datetime` with the original date or datetime value

`format_string_optional` with an optional valid format expression.

{% hint style="info" %}
Check supported formats here: <http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html>.
{% endhint %}
{% endtab %}

{% tab title="Example" %}
Let organization time zone is **Eastern Standard Time**.

`FORMATGMT(DATETIME(2019, 03, 12, 11, 23, 0))` will return **3/12/2019 3:23 PM**.
{% endtab %}
{% endtabs %}

#### STARTOFWEEK

Returns a date or a datetime representing the first day of the week for the date passed to the function in the local time zone of the current user.

{% tabs %}
{% tab title="Usage" %}
`STARTOFWEEK(date/dateTime)`

Replace `date/dateTime` parameter with the date or datetime value, whose start date of the week is to be determined.
{% endtab %}

{% tab title="Example" %}
`STARTOFWEEK({Opportunity.createdDate})` will return start of the week date for the value of createdDate in GMT timezone.

Let current system datetime value is **Wednesday, 03/13, 2 PM**.

`STARTOFWEEK({$System.DateTime})` will return datetime value for **Monday, 3/11, 2 PM**.
{% endtab %}
{% endtabs %}

#### DAYSBETWEEN

Returns the number of days between two date or datetime values.

{% tabs %}
{% tab title="Usage" %}
`DAYSBETWEEN(date/datetime, date/datetime, ignoreWeekends)`

Replace:

first two parameters with the two date/datetime values;

boolean `ignoreWeekends` with *true* if calculation should exclude Saturdays and Sundays, or *false* if it should not.
{% endtab %}

{% tab title="Example" %}
Let `createdDate` equals **2001-07-04** and `closedDate` equals **2001-07-10**

`DAYSBETWEEN({Opportunity.createdDate}, {Opportunity.closedDate}, false)` will return **5**.
{% endtab %}
{% endtabs %}

#### DATEDIFF

Returns the number of minutes, hours, and days between date/datetime values.

{% hint style="info" %}
To access business hours records, navigate to Setup > Company Profile > Business Hours.
{% endhint %}

{% tabs %}
{% tab title="Usage" %}
`DATEDIFF(date/datetime, date/datetime, business_hours_id_optional, result_type_optional, hours_in_day_optional)`

Replace:

first two parameters with two dates;

`business_hours_id_optional` optional parameter with an ID of a business hours record used in your organization;

`result_type_optional` optional parameter with one of the following values: *'Duration', 'Hours', 'Days'* (this parameter is case-insensitive);

{% hint style="info" %}
Duration format looks like this: **1d 7h 45m**.
{% endhint %}

`hours_in_day_optional` is an optional parameter that can be used when the user passes '*Duration*' or '*Days*' as a `result_type_optional` - It defines the number of hours in a day for function calculation.

{% hint style="warning" %}
Notes:

* A `business_hours_id_optional` The parameter is optional.\
  If you omit this parameter or pass null, the function will calculate the result using a 24-hour day, a 7-day week, and a 365-day year.
* Function with '*Hours*' result type will return the full number of hours rounded down. This can be useful to avoid lates in calculated schedules, etc.
* If `hours_in_day_optional` is used, `business_hours_id_optional` should be set to *null* (optional parameters can be omitted only if they are last, otherwise they should be set to *null*).
  {% endhint %}
  {% endtab %}

{% tab title="Example 1" %}
Let `createdDate` = **2001-07-0**4 and `closedDate` = **2001-07-05**

`DATEDIFF({Opportunity.createdDate}, {Opportunity.closedDate})` returns **1440** (minutes).
{% endtab %}

{% tab title="Example 2" %}
Let `createdDate` = **2020-06-29** and `closedDate` = **2020-07-01T03:30:05Z**, and business hours are set as **8 hours per day from Monday to Friday**.

`DATEDIFF({Opportunity.createdDate}, {Opportunity.closedDate}, business_hours_id)` returns **960** (minutes).
{% endtab %}

{% tab title="Example 3" %}
Let `createdDate` = **2020-06-29** and `closedDate` = **2020-07-01T03:30:05Z**.

`DATEDIFF({Opportunity.createdDate}, {Opportunity.closedDate}, null, DURATION)` will return **44h 30m**.
{% endtab %}

{% tab title="Example 4" %}
Let `createdDate` = **2020-06-29** and `closedDate` = **2020-07-01T03:30:05Z**.

`DATEDIFF({Opportunity.createdDate}, {Opportunity.closedDate}, null, Hours)` will return **44** (integer number of hours).
{% endtab %}

{% tab title="Example 5" %}
Let `createdDate` = **2001-07-04** and `closedDate` = **2001-07-05**.

`DATEDIFF({Opportunity.createdDate}, {Opportunity.closedDate}, false, 'Duration', 8)` returns **3d**.
{% endtab %}
{% endtabs %}

#### YEAR

Returns the year component of a date or datetime value.

{% tabs %}
{% tab title="Usage" %}
`YEAR(parameter)`

Replace `parameter` with the date/datetime value.
{% endtab %}

{% tab title="Example" %}
Let `RFQ_Date__c` = **2020-07-01T03:30:05Z**.

`YEAR({Opportunity.RFQ_Date__c})` returns **2020** (integer representing year component of `RFQ_Date__c` field value).
{% endtab %}
{% endtabs %}

#### MONTH

Returns the month component of a date or datetime value.

{% tabs %}
{% tab title="Usage" %}
`MONTH(parameter)`

Replace `parameter` with the date/datetime value.
{% endtab %}

{% tab title="Example" %}
Let `RFQ_Date__c` = **2020-07-01T03:30:05Z**.

`MONTH({Opportunity.RFQ_Date__c})` returns **7** (integer representing month component of `RFQ_Date__c` field value).
{% endtab %}
{% endtabs %}

### <mark style="color:$primary;">JSON Functions</mark>

***

#### JPARSE

Deserializes the specified JSON string into an object or an array.

{% tabs %}
{% tab title="Usage" %}
`JPARSE(json_string, separator_optional)`

Replace:

`json_string` parameter with a JSON format string you need to parse;

`separator_optional` An optional parameter to make the function convert a string to an array by splitting it.
{% endtab %}

{% tab title="Example 1" %}
`JPARSE('{"name" : "John Smith", "kids": [{"name": "Jim"}, {"name":"Nicky"}]}')` will return a proper object.
{% endtab %}

{% tab title="Example 2" %}
Let *String\_\_c* = **'\["Feb", "Jan", "Dec"]'**.

`JPARSE({Stub__c.String__c})` returns an array consisting of following elements: **'Feb', 'Jan', 'Dec'**.
{% endtab %}

{% tab title="Example 3" %}
Let *Area\_\_c* = **'Plane; Train; Car'**.

`JPARSE({Stub__c.Area__c}, ';')` returns an array consisting of following elements: **Plane, Train, Car.**
{% endtab %}
{% endtabs %}

#### JPARSEXML

Deserializes the specified XML string into an object.

{% tabs %}
{% tab title="Usage" %}
`JPARSEXML(xml_string)`

Replace `xml_string` parameter with a string (in XML format) you need to parse.
{% endtab %}

{% tab title="Example" %}
`JPARSEXML('<person><address>10 South Riverside Plaza</address><age>9</age><names><first>Jon</first><last>Smith</last></names></person>')` returns an object **person={address=10 South Riverside Plaza, age=9, names={first=Jon, last=Smith}}**.
{% endtab %}
{% endtabs %}

#### JOBJECT

Builds an object from provided key names and values.

{% tabs %}
{% tab title="Usage" %}
`JOBJECT(key1, value1, key2, value2, ...)`

Replace:

`key1, key2` parameters with strings that are key names

`value1, value2` parameters with strings that are key values

If you omit a value, it will be stored as null.
{% endtab %}

{% tab title="Example 1" %}
`JOBJECT()` will return an empty object **{}**.
{% endtab %}

{% tab title="Example 2" %}
`JOBJECT(key1, value1, key2, value2)` will return an object **{“key1“:”value1”, “key2“:”value2”}**
{% endtab %}

{% tab title="Example 3" %}
`JOBJECT(key1, value1, key2)` will return an object **{“key1“:”value1”, “key2“:null}**
{% endtab %}

{% tab title="Example 4" %}

```
JOBJECT(
  truevalue, true,
  falsevalue, false,
  evaluatedtruevalue, 1 = 1,
  evaluatedfalsevalue, 1 = 0,
  emptyvalue, ,
  emptystringvalue,'',
  nullvalue2, null,
  numbervalue, 17,
  negativevalue, -456.45,
  jsonvalue, JOBJECT(my2key1, my2value1, my2key2, my2value2)
)
```

will return an object

```
{
  "jsonvalue":{
    "my2key2":"my2value2",
    "my2key1":"my2value1"
  },
  "negativevalue":-456.45,
  "numbervalue":17,
  "nullvalue2":null,
  "emptystringvalue":"",
  "emptyvalue":null,
  "evaluatedfalsevalue":false,
  "evaluatedtruevalue":true,
  "falsevalue":false,
  "truevalue":true
}
```

{% endtab %}
{% endtabs %}

{% hint style="info" %}
Object values depend on data passed to the function:

* Blank space and `null` will give a **null** value
* Logical expression, and `true` will give a **true** value. **False** is got in the same way.
* If the value is numeric (or has a numeric result of a calculation), it will be given as a number, without quotes.
* To make an **empty string** value, pass `''`.
* To make a nested **object**, call `JOBJECT` function in the proper place.
  {% endhint %}

{% hint style="warning" %}
Empty keys will be ignored
{% endhint %}

#### JSTRING

Serializes objects into a JSON string.

{% tabs %}
{% tab title="Usage" %}
`JSTRING(json_object)`

Replace `json_object` parameter with an object you need to convert into a string.
{% endtab %}

{% tab title="Example" %}
`JSTRING(JEACH(SPLIT('12,14,15', ','), {$JEach}), true))` returns the following string: **'\["12","14","15"]'**.
{% endtab %}
{% endtabs %}

#### JGET

Gets a value from JSON content using a path provided.

{% tabs %}
{% tab title="Usage" %}
`JGET(json_object, path)`

Replace:

`json_object` parameter with an object you want to use;

`path` parameter with the path to find a field in the object (or pass an index for arrays).
{% endtab %}

{% tab title="Example 1" %}
`JGET(JPARSE('{"name" : "Jon Smith", "address": "10 South Riverside Plaza"}'), 'address')` returns Jon's address value **10 South Riverside Plaza**.
{% endtab %}

{% tab title="Example 2" %}
Let String field value is '**June, July, August**'.

`JGET(JPARSE({Stub__c.String__c}, ','), 2)` returns '**August**'.
{% endtab %}

{% tab title="Example 3" %}
`JGET(JPARSE('{"name" : "Jon Smith", "address": "10 South Riverside Plaza", "kids": ["Andrew","Jane", "Peter"]}'), JPARSE('["kids", 2]'))` will return '**Peter**' as "*kids*" list has element with index *2*.
{% endtab %}
{% endtabs %}

#### 6. JPUT

Puts a value into the JSON content at the provided path.

{% tabs %}
{% tab title="Usage" %}
`JPUT(json_object, path, value)`

Replace:

`json_object` parameter with an object you want to use;

`path` parameter with the path to find a field in the object to update;

`value` parameter with the value that a field will be updated with.

{% hint style="info" %}
You can also pass an index as a `path` if you operate with an array.

If a value needs to be added as the last element of an array, pass *-1* as a `path`.
{% endhint %}
{% endtab %}

{% tab title="Example 1" %}
`JPUT(JPARSE('{"name" : "Jon Smith"}'), 'address', '10 South Riverside Plaza')` will add Jon's address property and value to JSON object.
{% endtab %}

{% tab title="Example 2" %}
`JPUT(JPARSE('["Z", "Y", "X"]'), 0, 'A')` returns an array with following elements: **A, Y, X**.
{% endtab %}
{% endtabs %}

#### JREMOVE

Removes a field from JSON content using the provided path.

{% tabs %}
{% tab title="Usage" %}
`JREMOVE(json_object, key_or_jsonobject)`

Replace:

`json_object` parameter with an object you want to use;

`key_or_jsonobject` parameter with the key to find a field you need to remove, or with an array element to be removed from the JSON array.

{% hint style="info" %}
If you need to remove multiple values from an object, you can pass an array of object keys as a `key_or_jsonobject`

The same is true for JSON arrays: if you need to remove multiple elements, pass an array of elements to remove.
{% endhint %}
{% endtab %}

{% tab title="Example 1" %}
`JREMOVE(JPARSE('{"name" : "Jon Smith", "address": "10 South Riverside Plaza", "age" : 9}'), 'address')` removes an *address* field from the JSON content.
{% endtab %}

{% tab title="Example 2" %}
`JREMOVE(JPARSE('["Z", "Y", "X"]'), 'X')` returns an array with following elements: **Z, Y**.
{% endtab %}

{% tab title="Example 3" %}
`JREMOVE(JPARSE('{"name" : "Jon Smith", "address": "10 South Riverside Plaza", "age" : 9}'), SPLIT('name,age', ','))` returna a JSON object with address property only.
{% endtab %}

{% tab title="Example 4" %}
`JREMOVE(JPARSE('["1a", "2b", "3c", "4d"]'), SPLIT('1a,2b', ','))` returns an array with following elements: **3c, 4d**.
{% endtab %}
{% endtabs %}

#### JCLEAR

Clears a whole JSON object or a nested object using the provided path.

{% tabs %}
{% tab title="Usage" %}
`JCLEAR(json_object, key_or_jsonobject_optional)`

Replace:

`json_object` parameter with an object you want to use;

`key_or_jsonobject_optional` optional parameter with the key to find a field you need to remove, or with a path to a nested object as an array of elements.
{% endtab %}

{% tab title="Example 1" %}
Both

`JCLEAR(JPARSE('{"personnames":{"first":"Jon","last":{"a":"Smith","b":"Ryan"}}, "address": "10 South Riverside Plaza", "age" : 9}'))`

and

`JCLEAR(JPARSE('{"personnames":{"first":"Jon","last":{"a":"Smith","b":"eee"}}, "address": "10 South Riverside Plaza", "age" : 9}'),null)`

will return an empty object **{}**.
{% endtab %}

{% tab title="Example 2" %}
`JCLEAR(JPARSE('{"personnames":{"first":"Jon","last":{"a":"Smith","b":"Ryan"}}, "address": "10 South Riverside Plaza", "age" : 9}'),'personnames')` returns an initial object where nested object '*personnames*' will be empty: **{address=10 South Riverside Plaza, age=9, personnames={}}**
{% endtab %}

{% tab title="Example 3" %}
`JCLEAR(JPARSE('{"personnames":{"first":"Jon","last":{"a":"Smith","b":"Ryan"}}, "address": "10 South Riverside Plaza", "age" : 9}'),JPARSE('["personnames","last"]'))` returns an initial object where 2nd-level nested object '*last*' will be empty: **{address=10 South Riverside Plaza, age=9, personnames={first=Jon, last={}}}**
{% endtab %}
{% endtabs %}

#### JSIZE

Returns a number of elements in JSON content.

{% tabs %}
{% tab title="Usage" %}
`JSIZE(json_object)`

Replace `json_object` parameter with an object or array you want to get the size of.
{% endtab %}

{% tab title="Example 1" %}
`JSIZE(JPARSE('{"name" : "Jon Smith", "address": "10 South Riverside Plaza"}'))` returns **2** as a number of key-value pairs in JSON content provided.
{% endtab %}

{% tab title="Example 2" %}
`JSIZE(SPLIT('Name, Address, Age, Hair color'), ',')` returns **4**.
{% endtab %}
{% endtabs %}

#### JEXIST

Checks if an element exists in JSON.

{% tabs %}
{% tab title="Usage" %}
`JEXIST(json_object, path)`

Replace:

`json_object` parameter with an object you want to use ;

`path` parameter with the path to find a field you need to check (or index if you work with JSON array).
{% endtab %}

{% tab title="Example 1" %}
`JEXIST(JPARSE('{"name" : "Jon Smith", "address": "10 South Riverside Plaza"}'), 'Jon Smith')` returns **true** as *Jon Smith* node is present in JSON content.
{% endtab %}

{% tab title="Example 2" %}
`JEXIST(JPARSE('["one" , "two", "three", "four"]'), 2)` returns **true** as element with index *2* is present in list.
{% endtab %}

{% tab title="Example 3" %}
`JEXIST(JPARSE('{"name" : "Jon Smith", "address": "10 South Riverside Plaza", "appearance": {"eyes":"blue", "hair":"dark"}}'), JPARSE('["appearance", "hair"]'))` returns **true** as "*appearance*" object has key "*hair*".
{% endtab %}

{% tab title="Example 4" %}
`JEXIST(JPARSE('{"name" : "Jon Smith", "address": "10 South Riverside Plaza", "kids": ["Andrew","Jane", "Peter"]}'), JPARSE('["kids", 2]'))` returns **true** as "*kids*" list has element with index *2*.
{% endtab %}
{% endtabs %}

#### JMERGE

Merges 2 JSON objects into one.

{% tabs %}
{% tab title="Usage" %}
`JMERGE(json_object, json_object)`

Replace both `json_object` parameters with objects you want to merge.
{% endtab %}

{% tab title="Example" %}
`JMERGE(JPARSE('{"name" : "Jon Smith", "address": "10 South Riverside Plaza"}'), JPARSE('{"appearance": {"eyes":"blue", "hair":"dark"}}'))` will return object **{address=10 South Riverside Plaza, appearance={eyes=blue, hair=dark}, name=Jon Smith}**.
{% endtab %}
{% endtabs %}

#### JEACH

Executes the specified function for each element of the list.

{% tabs %}
{% tab title="Usage" %}
`JEACH(json_array, function, exclude_nulls_boolean)`

Replace:

`json_array` parameter with an array you want to use;

`function` parameter with expression to be executed for each array element;

optional `exclude_nulls_boolean` parameter with true to have null values excluded from the resulting array, or with false, if to include null values.

{% hint style="info" %}
You can get the current element by using the merge field "{$JEach}" and the current element index by using the merge field "{$JEachIndex}".
{% endhint %}
{% endtab %}

{% tab title="Example 1" %}
Let *String\_\_c* = **'Andrew,Alex,Helen,Ann,Robert'**.

`JOIN(JEACH(JPARSE({Stub__c.String__c}, ','), IF(STARTS({$JEach}, 'An'), {$JEach}, null), true), ',')` returns the following string '**Andrew, Ann**'.
{% endtab %}

{% tab title="Example 2" %}
Let *String\_\_c* = **'Andrew,Alex,Helen,Ann,Robert'**.

`'[' + JOIN(JEACH(JPARSE({Stub__c.String__c}, ','), {DBL_QUOTE} + TEXT({$JEachIndex}) + : + ' ' + {$JEach} + {DBL_QUOTE}), ',') + ']'` returns the following string '**\["0: 'Andrew","1: Alex","2: Helen","3: Ann","4: Robert"]**'.
{% endtab %}
{% endtabs %}

#### JEACHMAP

Executes specified functions for each key and/or value of the JSON object.

{% tabs %}
{% tab title="Usage" %}
`JEACHMAP(json_object, key_function, value_function)`

Replace:

`json_obect` parameter with an object you want to use;

`key_function` parameter with expression to be executed for each key of the object;

`value_function` with expression to be executed for each value of the object.

{% hint style="info" %}
If you don't need to trigger function execution for object keys, pass **{$JEachKey}** as a `key_function` parameter; if no function needs to be run for object values, pass **{$JEach}** as `value_function` a parameter.

You can get current element by using the merge field "**{$JEach}**" and you can get current element index by using the merge field "**{$JEachIndex}**".
{% endhint %}
{% endtab %}

{% tab title="Example 1" %}
`JEACHMAP(JPARSE({$Environment}), {$JEachKey}, IF(INSTANCEOF({$JEach}, Decimal), MULT({$JEach}, 2), {$JEach}))` doubles all numeric values of the *$Environment* context object.
{% endtab %}

{% tab title="Example 2" %}
`JEACHMAP(JPARSE({$Environment}), IF({$JEach} = true, UPPER({$JEachKey}), {$JEachKey}), {$JEach})` converts to uppercase only those object keys which values are equal to **true**.
{% endtab %}
{% endtabs %}

#### JVAR

Defines formula local variables.

{% tabs %}
{% tab title="Usage" %}
`JVAR(var1_name, va1_value_function, var2_name, va2_value_function, ..., value_function)`

Replace:

`var1_name`, `var2_name` , etc., parameters with variable names;

`va1_value_function`, `va2_value_function`, etc., parameters with an expression to be executed as the value for the variables;

`value_function` with an expression to be executed as the result of the function.

{% hint style="info" %}
To get access to the variable, use the merge field "**{$JVar.var1\_name}**", where "*var1\_name*" is the variable name.
{% endhint %}
{% endtab %}

{% tab title="Example" %}
`JVAR('var1', 1, 'var2', 2, {$JVar.var1} + {$JVar.var2})`- the result of this expression will be "**3**".
{% endtab %}
{% endtabs %}

#### JFOR

Executes the specified function for each element of the list.

{% tabs %}
{% tab title="Usage" %}
`JFOR(from_decimal, to_decimal, expression, exclude_nulls_boolean_optional)`

Replace:

`from_decimal` parameter with a number to start the loop from;

`to_decimal` with a number to stop the loop at;

`expression` with expression to be executed for each iteration;

optional `exclude_nulls_boolean_optional` parameter with true to have null values excluded from the result, or with false to include null values.
{% endtab %}

{% tab title="Example" %}
`JFOR(1, 10, TEXT(ADDDAYS({$System.Date}, {$JeachIndex} ,false)), false)` will return list of dates from current date + 1 day to current date + 10 days.
{% endtab %}
{% endtabs %}

### <mark style="color:$primary;">System Functions</mark>

***

#### COLUMNTOTAL

Calculates total quantities of records or evaluates total amounts / minimal values / maximal values/averages of values in the specified or current column, considering column conditions and filters applied.

{% tabs %}
{% tab title="Usage" %}
`COLUMNTOTAL(column_id_optional, filter_optional, aggregation_type_optional, aggregation_field_optional)`.

{% hint style="info" %}
All function parameters are optional and can be omitted (if last) or replaced with null (if not needed), but some arguments should be placed after them.
{% endhint %}

Parameters:

* `column_id_optional` - if left empty or set to `null` , it will calculate the desired values for the current column. If you specify column ID, it will calculate the result for that column (this argument is necessary if the column is unknown in the current context, e.g., when the function is executed on some cell in the Kanban header).
* `filter_optional` - If specified, will add a custom filter string to the query and calculate the result matching this filter.
* `aggregation_type_optional` - if left empty or set to `null`, will calculate total quantity of records matching the query, otherwise will calculate values with the way specified by proper aggregate function (see table below for a list and a [SOQL syntax](https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_agg_functions.htm) web reference for more help).
* `aggregation_field_optional` - The API name of the field should be specified when using aggregation types because different aggregation functions support only fields of specific types.

List of aggregation types:

<table><thead><tr><th width="124">Aggregation function</th><th>Returned result</th><th width="170">Ignores null values?</th><th>Supported field types</th></tr></thead><tbody><tr><td><strong>COUNT</strong></td><td>Number of records matching the query criteria <em>(actually the same result as for empty or null proper argument)</em>.</td><td>No (if aggregation field is ID or is not specified) Yes (if other aggregation field is specified)</td><td>Any</td></tr><tr><td><strong>COUNT_DISTINCT</strong></td><td>Number of distinct non-null field values matching the query criteria.</td><td>Yes</td><td>Any</td></tr><tr><td><strong>AVG</strong></td><td>Average value of a numeric field.</td><td>Yes</td><td>Numeric</td></tr><tr><td><strong>SUM</strong></td><td>Total sum of a numeric field.</td><td>Yes</td><td>Numeric</td></tr><tr><td><strong>MIN</strong></td><td>Minimum value of a field.</td><td>Yes</td><td>Any. If use on a picklist field, the function uses the sort order of the picklist values instead of alphabetical order.</td></tr><tr><td><strong>MAX</strong></td><td>Maximum value of a field.</td><td>Yes</td><td>Any. If use on a picklist field, the function uses the sort order of the picklist values instead of alphabetical order.</td></tr></tbody></table>
{% endtab %}

{% tab title="Example 1" %}
`COLUMNTOTAL()` returns a total number of records in the current column.
{% endtab %}

{% tab title="Example 2" %}
`COLUMNTOTAL({$Variables.myColumnId}, 'Type = {QUOTE}Electrical{QUOTE}')` will return total number of records that match custom filter **Type='Electrical'** and belong to column with ID stored in variable "*myColumnId*".
{% endtab %}

{% tab title="Example 3" %}
Let Priority = picklist with these values: **'High', 'Medium', 'Low', 'No priority'**.

`COLUMNTOTAL(null, null, 'MAX', 'Priority')` returnы '**No priority**' because it is the latest value in picklist field.
{% endtab %}

{% tab title="Example 4" %}
`COLUMNTOTAL(null, null, 'MIN', 'Units')` returns the minimal number value if the field is numeric, and first alphabetically sorted value if the field is text.
{% endtab %}

{% tab title="Example 5" %}
`COLUMNTOTAL({$Variables.myColumnId}, 'Stage = {QUOTE}Closed Won{QUOTE}', 'AVG', 'ExpectedRevenue__с')` Returns the average Expected Revenue amount for opportunities in the Closed Won stage.
{% endtab %}
{% endtabs %}

#### BUILDFORMLINK

Builds a web URL to the specified Form.

{% tabs %}
{% tab title="Usage" %}
`BUILDFORMLINK(form_id, record_id, mode, context_fields, parameters, entire_height, as_html_tag_optional, text_optional)`

Parameters:

* `form_id` - ID of form to be linked to
* `record_id` - ID of record to be used as data source
* `mode` - form mode (edit, view, or new)
* `context_fields` - JSON with the list of virtual fields and their values (for pre-population)
* `parameters` - JSON with parameters that will be passed to the form
* `entire_height` - **true** or **false**. If set to **true**, the form will take all available height of the container, even if no such space is required to show form elements
* `as_html_tag_optional` - optional argument. If set to **true**, not only will the form URL be generated, but the HTML tag `<a>` with `href` attribute equal to this URL
* `text_optional` - optional argument. If set to any text except null, this text will be placed as the inner content of `<a>` tag (has sense only if `as_html_tag_optional` is set to **true**)

{% hint style="info" %}
Note: if omitted `record_id` argument, form will be shown without object fields populated. If the form mode is "edit", the result will be the same as in "new" mode.
{% endhint %}
{% endtab %}

{% tab title="Example 1" %}
Let your application is located at **your-app-url.com** address.

`BUILDFORMLINK('a0C9m00000T4LvWDFC', '5001A00000SdeRqQFF', 'edit')` returns the following URL:

```
https://your-app-url.com/lightning/cmp/c__FormPanel?c__entireHeight=false&c__formId=a0C9m00000T4LvWDFC&c__mode=edit&c__recordId=5001A00000SdeRqQFF
```

{% endtab %}

{% tab title="Example 2" %}
Let your application is located at **your-app-url.com** address.

`BUILDFORMLINK('a0C9m00000T4LvWDFC','5001A00000SdeRqQFF','edit', null,'{"aaa":"bbb"}',false,true)` returns the following HTML code:

```
<a href="https://your-app-url.com/lightning/cmp/c__FormPanel?c__entireHeight=false&c__formId=a0C9m00000T4LvWDFC&c__mode=edit&c__parameters=%7B%22aaa%22%3A%22bbb%22%7D&c__recordId=5001A00000SdeRqQFF">https://your-app-url.com/lightning/cmp/c__FormPanel?c__entireHeight=false&c__formId=a0C9m00000T4LvWDFC&c__mode=edit&c__parameters=%7B%22aaa%22%3A%22bbb%22%7D&c__recordId=5001A00000SdeRqQFF</a>
```

{% endtab %}

{% tab title="Example 3" %}
Let your application is located at **your-app-url.com** address.

`BUILDFORMLINK('a0C9m00000T4LvWDFC','5001A00000SdeRqQFF','edit', '{"inputTextarea":"This is some description"}', null, false, true, 'My Link Text')` returns the following HTML code:

```
<a href="https://your-app-url.com/lightning/cmp/c__FormPanel?c__contextFields=%7B%22inputTextarea%22%3A%22This+is+some+description%22%7Dc__entireHeight=false&c__formId=a0C9m00000T4LvWDFC&c__mode=edit&c__recordId=5001A00000SdeRqQFF">My Link Text</a>
```

{% endtab %}
{% endtabs %}

#### BUILDDASHBOARDLINK

Builds a web URL to the specified Kanban.

{% tabs %}
{% tab title="Usage" %}
`BUILDDASHBOARDLINK(dashboard_id, variables, styles, as_html_tag_optional, text_optional)`

Parameters:

* `dashboard_id` - ID of dashboard (kanban) to be linked to
* `variables` - JSON with the list of variables to be passed to the kanban dashboard. This argument is mandatory, so replace it with `null` if no variables are needed.
* `styles` - string with CSS properties to be passed to the kanban dashboard. This argument is mandatory, so replace it with `null` if no styles are needed.

{% hint style="warning" %}
Note: `styles` The argument is actually passed as a URL parameter, but the Kanban page does not support it (it is reserved for future use).
{% endhint %}

* `as_html_tag_optional` - optional argument. If set to **true**, not only will the kanban dashboard URL be generated, but the HTML tag `<a>` with `href` attribute equal to this URL.
* `text_optional` - optional argument. If set to any text except null, this text will be placed as the inner content of `<a>` tag (has sense only if `as_html_tag_optional` is set to **true**)
  {% endtab %}

{% tab title="Example 1" %}
Let your application is located at **your-app-url.com** address.

`BUILDDASHBOARDLINK('a003m00002CvxffAAB','{"myVariable_1":"Some text"}',null)` returns the following URL:

```
https://your-app-url.com/lightning/cmp/c__KanBanComponent?c__kanbanId=a003m00002CvxffAAB&c__variables=%7B%22myVariable_1%22%3A%22Some+text%22%7D
```

{% endtab %}

{% tab title="Example 2" %}
`BUILDDASHBOARDLINK('a003m00002CvxffAAB',null,null,true)` returns the following HTML code:

```
<a href="https://your-app-url.com/lightning/cmp/c__KanBanComponent?c__kanbanId=a003m00002CvxffAAB">https://your-app-url.com/lightning/cmp/c__KanBanComponent?c__kanbanId=a003m00002CvxffAAB</a>
```

{% endtab %}

{% tab title="Example 3" %}
`BUILDDASHBOARDLINK('a003m00002CvxffAAB','{"myVariable_1":"Some text"}',null,true,'My Dashboard')` returns the following HTML code:

```
<a href="https://your-app-url.com/lightning/cmp/c__KanBanComponent?c__kanbanId=a003m00002CvxffAAB&c__variables=%7B%22myVariable_1%22%3A%22Some+text%22%7D">My Dashboard</a>
```

{% endtab %}
{% endtabs %}

#### CHECKPERMISSION

Returns **true** if the current user has listed permissions (any or all), and **false** otherwise.

{% tabs %}
{% tab title="Usage" %}
`CHECKPERMISSION(name_or_names_json_array, atleastone_boolean_optional)`

Parameters:

* `name_or_names_json_array` - name of the permission to be checked or a JSON array of permission names.
* `atleastone_boolean_optional` - if set to `true`The function will return true if the current user has at least one permission from the array in the first argument; otherwise (or if omitted), the function will return true only if the current user has all listed permissions.
  {% endtab %}

{% tab title="Example 1" %}
Let your user has these permissions: **User\_Test\_Permission, Process\_admin**.

`CHECKPERMISSION('User_Test_Permission')` returnы **true** if current user has this permission.
{% endtab %}

{% tab title="Example 2" %}
`CHECKPERMISSION(SPLIT('User_Test_Permission,Process_admin', ','))` returns **true** if current user has both these permissions.
{% endtab %}

{% tab title="Example 3" %}
`CHECKPERMISSION(SPLIT('User_Test_Permission,Process_admin', ','),false)` returns **true** if current user has both these permissions.
{% endtab %}

{% tab title="Example 4" %}
`CHECKPERMISSION(SPLIT('User_Test_Permission,Process_admin', ','),true)` returns **true** if current user has at least one of these permissions.
{% endtab %}
{% endtabs %}

#### USERIN

Returns true if the given user belongs to at least one of the listed users, roles, profiles, queues, or groups.

{% tabs %}
{% tab title="Usage" %}
`USERIN(user_id, users_roles_profiles_groups_ids)`.

Parameters:

* `user_id` - ID of the user who is being checked.
* `users_roles_profiles_groups_ids` - one ID or a list of IDs of users/roles/profiles/groups/queues.
  {% endtab %}

{% tab title="Example" %}
`USERIN({$User.id}, 0083n000008qatjCVV)` returns **true** if current user ID is equal to second parameter.
{% endtab %}

{% tab title="Example 2" %}
`USERIN({$User.id}, ('0083n000008qatjCVV,00G3m000003pllF'))` returns **true** if current user ID is in the listed IDs.
{% endtab %}
{% endtabs %}

### <mark style="color:$primary;">Charts Functions</mark>

***

#### PIECHART

Returns URL of dynamically formed .svg image that represents the given data as a pie chart. The chart legend (if turned on) will show proper data with their values, percentage, and total.

{% tabs %}
{% tab title="Usage" %}
`PIECHART(values_json_array, size, has_legend, font_size)`

Parameters:

* `values_json_array` - user data in JSON array. Each array item has the following properties:
  * mandatory decimal `value` that represents the value of the chart item
  * optional string `title` that will be written in the chart legend next to the proper item, after its value and before the percentage.
  * optional string `color` that will be applied to the chart sector representing the proper value. If you omit this property, the system will assign a random color.
* `size` - chart diameter size in pixels. If legend is on, the width will be 2 times bigger. This argument is optional (set it to **null** if you do not want to specify size); the default size is 100.
* `has_legend` - set to **true** if legend should be displayed, set to **null** or **false** if not.
* `font_size` - font size of legend (in pixels). If set to **null**, the font size will be calculated to fit the chart height, but not exceed 19px.

{% hint style="warning" %}
Note: Pie chart supports up to 20 values. Others will be combined together, and their value will be shown as a summary in the 21st sector.
{% endhint %}
{% endtab %}

{% tab title="Example" %}
{% code overflow="wrap" %}

```

PIECHART('{OPEN_SQR_BKT}{"value":"1","color":"red"},{"value":"2","title":"2 TEST"},{"value":"3"},{"value":"4"},{"value":"5"},{"value":"6"},{"value":"7"},{"value":"8"},{"value":"9"},{"value":"10"},{"value":"11"},{"value":"12"},{"value":"13"},{"value":"14"},{"value":"15"},{"value":"16"},{"value":"17"},{"value":"18"},{"value":"19"},{"value":"20"},{"value":"21"},{"value":"22"},{"value":"23"}{CLOSE_SQR_BKT}', 300, true, 14)
```

{% endcode %}

returns an **URL** for such picture:

<figure><img src="https://3097383375-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FK2dgObBO5ydOH2ZXDJLa%2Fuploads%2FQ8OAB4SLxETC99zuVrm7%2Fimage.png?alt=media&#x26;token=a4f6d42e-8a52-4724-9c26-f126d54cc021" alt=""><figcaption></figcaption></figure>
{% endtab %}
{% endtabs %}

#### DONUTCHART

Returns URL of dynamically formed .svg image that represents the given data as a Donut Chart. The chart legend (if enabled) will show the correct data with its values and percentages. The total (if allowed) will be shown in the chart's center hole.

{% tabs %}
{% tab title="Usage" %}
`DONUTCHART(values_json_array, size, hole_size, has_legend, font_size, has_total)`

Parameters:

* `values_json_array` - User data in JSON array. Each array item has the following properties:
  * Mandatory decimal `value` that represents the value of the chart item
  * Optional string `title` That will be written in the chart legend next to the proper item, after its value and before the percentage.
  * Optional string `color` That will be applied to the chart sector representing the proper value. If you omit this property, the system will assign a random color.
* `size` - Chart diameter size in pixels. If legend is on, the width will be 2 times bigger. This argument is optional (set it to **null** if you do not want to specify size); the default size is 100.
* `hole_size` - Chart hole radius size in pixels (or **null**). The default value will be calculated as 60% of the chart radius.
* `has_legend` - Set to **true** if legend should be displayed, set to **null** or **false** if not.
* `font_size` - Font size of legend (in pixels). If set to **null**, font size will be calculated to fit the chart height, but not more than 19px.
* `has_total` - Set to **true** if the total sum should be displayed, set to **null** or **false** if not. The total will be displayed in the center of the donut hole. The font size of the total depends on the hole size and fits it automatically.

{% hint style="warning" %}
Note: Donut chart supports up to 20 values. Others will be combined together, and their value will be shown as a summary in the 21st sector.
{% endhint %}
{% endtab %}

{% tab title="Example" %}
{% code overflow="wrap" %}

```
{"value":"3"},{"value":"4"},{"value":"5"},{"value":"6"},{"value":"7"},{"value":"8"},{"value":"9"},{"value":"10"},{"value":"11"},{"value":"12"},{"value":"13"},{"value":"14"},{"value":"15"},{"value":"16"},{"value":"17"},{"value":"18"},{"value":"19"},{"value":"20"},{"value":"21"},{"value":"22"},{"value":"23"}{CLOSE_SQR_BKT}', 300, 35, true, 14, true)
```

{% endcode %}

returns an **URL** for such picture:

<figure><img src="https://3097383375-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FK2dgObBO5ydOH2ZXDJLa%2Fuploads%2FPEne8ugZWYtEW2yBpM6n%2Fimage.png?alt=media&#x26;token=bdd04a88-5c66-4acc-b7e4-0087c980547d" alt=""><figcaption></figcaption></figure>
{% endtab %}
{% endtabs %}

#### PROGRESSCHART

Returns URL of dynamically formed .svg image that represents given data as a Progress Bar Chart. The chart legend (if enabled) will show each value with its percentage.

{% tabs %}
{% tab title="Usage" %}
`PROGRESSCHART(value, total, width, height, has_legend, font_size, color, bg_color, border_color, font_color)`

Parameters:

* `value` - Actual decimal value (mandatory).
* `total` - The maximum value that can be reached (mandatory).
* `width` - Chart width in pixels (optional). Default = 100. If a legend is shown, it will take some of this space.
* `height` - Chart height in pixels (optional). Default = 100.
* `has_legend` - Set to **1** if legend should be displayed, omit or set to **0** if not.
* `font_size` - Font size of legend (in pixels, optional). If omitted, font size will be calculated to fit the chart height, but not more than 19px.
* `color` - color of filled part of progress chart (optional, default is gray).
* `bg_color` - color of progress chart background (optional, default is light gray).
* `border_color` - color of progress chart border (optional, by default it is inherited from the color of the filled part of the progress chart).
* `font_color` - color of legend text (optional, default is black).

{% hint style="info" %}
Note: parameters noted as optional should be present in the function expression. Do not omit them, instead set them to *null* if you do not need override default values.
{% endhint %}
{% endtab %}

{% tab title="Example 1" %}
`PROGRESSCHART(55, 400,null,null,null,null,null,null,null,null)` returns an **URL** for such picture:

<figure><img src="https://3097383375-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FK2dgObBO5ydOH2ZXDJLa%2Fuploads%2FlimGOXZh0nmPgW9vps2G%2Fimage.png?alt=media&#x26;token=d19cc93b-33b2-4fcb-9e4c-0b76e333b7fa" alt=""><figcaption></figcaption></figure>
{% endtab %}

{% tab title="Example 2" %}
`PROGRESSCHART(55, 400,300,30,true,16,coral,pink,red,magenta)` returns an **URL** for such picture:

<figure><img src="https://3097383375-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FK2dgObBO5ydOH2ZXDJLa%2Fuploads%2F6YwBDhgaGBezSJvkcw66%2Fimage.png?alt=media&#x26;token=ac0bd8f1-71fa-4b2e-b8ed-9bfed122a00f" alt=""><figcaption></figcaption></figure>
{% endtab %}
{% endtabs %}