Functions: Usage and Library

Introduction and Usage

The Work-Relay functions is very powerful and flexible tool that may help to build your complex logic and get rid of custom code in your application. You can use functions in the Work-Relay Processes, Forms and Actions. 

The Work-Relay application provides its own library of functions, with the ability to extend the library with client custom functions.  

To use the function(s) or expression in your component configuration you have to wrap the whole expression with FORMULA[] construction, except the places where the type of content is "Formula" (for example Action conditions).

EXAMPLE 1:

1 + 2 = FORMULA[1 + 2]

EXAMPLE 2:

The current year is "FORMULA[FORMAT({$system.Date}, 'yyyy')]"

User can define custom functions and register them in the system. Read the proper article to know more.

Functions Library

Sobject Functions:

1. SOBJECTTYPE

Returns api name of an object.

USAGE: SOBJECTTYPE(id) and replace id with the id of an object.

EXAMPLE: SOBJECT({Stub.Id}) will return 'Stub__c'.

 

2. SOBJECTDESCRIBE

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

USAGE: SOBJECTDESCRIBE(object_api_name) and replace object_api_name with the api name of an object.

EXAMPLE: JGET(SOBJECTDESCRIBE('Account'), 'LabelPlural')  will return 'Accounts'.

 

3. SOBJECTFIELDDESCRIBE

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

USAGESOBJECTFIELDDESCRIBE(object_api_name, field_api_name) and 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.

EXAMPLEJGET(SOBJECTFIELDDESCRIBE('Stub__c', 'Check__c'), 'Type')  will return 'BOOLEAN' which is type of a Check__c field.

 

4. RECORDTYPEID

Available starting Work Relay 4.1 Release

Retrieves record type ID by its name for the specified object type. Returns ID.

USAGERECORDTYPEID(object_api_name, record_type_name) and replace object_api_name with the api name of an object and record_type_name with the name of record type.

EXAMPLERECORDTYPEID('Account', 'My_Record_Type')  will ID of record type of Account object.

 

Text Functions:

1. SPECIALSYMBOLS

Allows using of special symbols within the formula.

USAGE: use any of the below expressions whenever you need 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

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.

 

2. CONTAINS

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

USAGECONTAINS(source_string, search_string) and replace source_string with the text that should be searched for a value of search_string.

EXAMPLEIF(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'.

 

3. STARTS

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

USAGESTARTS(source_string, search_string) and replace source_string with the text that should be checked for starting from the value of search_string.

EXAMPLEIF(STARTS({Request__c.Name}, 'RFQ'), 'Request for Quote', 'Service') - this formula checks the content of a Name text field and returns 'Request for Quote' for any request name starting with 'RFQ'. Otherwise, it returns 'Service'.  

Note: values are case sensitive.

4. POS

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

USAGEPOS(source_string, search_string) and replace source_string with the field or expression you want to search and search_string with the string you want to find.

EXAMPLEPOS({Contact.Email}, '@') returns the location of the @ sign in a person's email address.

 

5. ENDS

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

USAGE: ENDS(source_string, search_string) and replace source_string with the text that should be checked for ending with the value of search_string.

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'.

Note: values are case sensitive.

6. SUBSTR

Returns a new string that begins with the character at the specified zero-based start_index position and extends until the character at end_index position.

USAGE: SUBSTR(source_string, start_index, end_index) and replace source_string with the string, field or expression where you want to get a substring from.

EXAMPLE: SUBSTR({Contact.Name}, 4, 8) in case when Contact.Name value = 'Jefferson' function will return 'erso'.

Note: function return a substring starting FROM start_index position UNTIL end_index (starting with a symbol at start_index position, and ending with end_index - 1 position).

7. REPLACE

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

USAGEREPLACE(source_string, from_string, to_string, regex_boolean_optional) and replace source_string with string to be changed, from_string with characters to be replaced in source_string, and to_string with the replacement set of characters.

EXAMPLE 1:  REPLACE({Opportunity.Unique_ID__c}, '3a', '2b') - in this example the Unique_ID__c will now contain new value with the '3a' replaced with '2b'.

User can pass regular expression as a from_string argument as well. In this case user should add 'true' as an optional regex_boolean_optional argument.

EXAMPLE 2:  REPLACE('a3f45qq456', '[^0-9]', '', true) will return 345456.

 

8. REMOVE

Removes one or more substrings from a source string.

USAGE: REMOVE(source_string, string_parameter1, string_parameter2, ...) and replace source_string 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.

EXAMPLE: REMOVE('(555) 555-6789 ', ' ', '(', ')', '-') will return '5555556789'.

 

9. LEN

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

USAGE: LEN(string) and replace string with the field or expression which length you want get.

EXAMPLE:  LEN({Product__с.Code__c}) returns the length of a Product.Code field.

 

10. SPLIT

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

USAGE: SPLIT(source_string, separator_string) and replace source_string with the text field or expression to be split, and separator_string  with a substring to split on.

EXAMPLE: SPLIT('AL, AK, AZ, AR', ', ') will return an array that of elements of string type. 

 

11. JOIN

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

USAGE: JOIN(array, separator_string, left_string_optional, right_string_optional) and 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_optional 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.

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"]'.

 

12. LOWER

Converts all of the characters in the string to lowercase.

USAGE: LOWER(string) and replace string parameter with a string you need to convert to lowercase.

EXAMPLE: LOWER({Account.Name}) will return account name value in lowercase.

 

13. UPPER

Converts all of the characters in the string to uppercase.

USAGE: UPPER(string) and replace string parameter with a string you need to convert to uppercase.

EXAMPLE: UPPER({Account.Name}) will return account name value in uppercase.

 

14. ESCAPE

Returns a string whose characters are escaped using specified rule.

USAGE: ESCAPE(string, JAVA|JSON|HTML|CSV|XML|UNICODE|QUOTE) and pass string to be escaped and one of the allowed escape rules as arguments.  

EXAMPLE: 

Let Stub__c.String__c value is 'Please, fill in "Name"'.
ESCAPE({Stub__c.String__c}, 'HTML') will return 'Please, fill in "Name"'
ESCAPE({Stub__c.String__c}, 'JAVA') will return 'Please, fill in \"Name\"'
ESCAPE({Stub__c.String__c}, 'CSV') will return '"Please, fill in ""Name"""'
ESCAPE({Stub__c.String__c}, 'XML') will return 'Please, fill in "Name"'


Let Stub__c.String__c value is 'De onde você é?'
ESCAPE({Stub__c.String__c}, 'UNICODE') will return 'De onde voc\u00EA \u00E9?'

Let Stub__c.String__c value is 'De onde você é?'
ESCAPE({Stub__c.String__c}, 'UNICODE') will return 'De onde voc\u00EA \u00E9?'

 

15. UNESCAPE

Available from version 7.0

Returns a string whose characters are unescaped using specified rule.

USAGE: UNESCAPE(string, JAVA|JSON|HTML|CSV|XML|UNICODE|QUOTE) and pass string to be unescaped and one of the allowed unescape rules as arguments.  

EXAMPLES: 

UNESCAPE('Please, fill in "Name"', 'HTML') will return 'Please, fill in "Name"'

UNESCAPE('Please, fill in \"Name\"', 'JAVA') will return 'Please, fill in "Name"'

UNESCAPE('"Please, fill in ""Name"""', 'CSV') will return 'Please, fill in "Name"'

UNESCAPE('Please, fill in "Name"', 'XML') will return 'Please, fill in "Name"'

UNESCAPE('De onde voc\u00EA \u00E9?', 'UNICODE') will return 'De onde você é?'

 

16. BR

Adds <br> (line break) HTML tag when used.

USAGE: add BR() to other functions or output text values wherever you need a line break.

EXAMPLE:
Let Account.Account_Multy__c value is [A, B, C] (these options had been checked).

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 (HTML string value):

A
B
C

17. TEXT

Converts any data type into text.

USAGE: TEXT(parameter) and replace parameter with the field or expression you want to convert to text format. 

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'.

 

18. BLOB

Casts the specified string to a BLOB.

USAGE: BLOB(string) and replace string argument with a string you need to cast to BLOB.

EXAMPLE: BLOB({Account.LongTextField__c}) will return field value as the BLOB content.

 

19. TOBASE64

Converts a BLOB to an unencoded String representing its normal form.

USAGE: TOBASE64(blob_parameter) and replace blob_parameter with a BLOB content you need to convert to base64.

EXAMPLE: TOBASE64(BLOB({$Account.Name})) will return 'TWFyeSBBbm4=' when given Account.Name value is 'Mary Ann'.

 

20. FROMBASE64

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

USAGE: FROMBASE64(string_parameter) and replace string_parater with Base64-encoded string.

EXAMPLE: FROMBASE64('TWFyeSBBbm4=') will return BLOB content of a 'Mary Ann' string.

 

21. TOHEX

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

USAGE: TOHEX(string_parameter) and replace string_parameter with a string you need to convert to hex.

EXAMPLE: TOHEX({Account.Name}) will return '4d61727920416e6e' when given Account.Name value is 'Mary Ann'.

 

22. FROMHEX

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

USAGE:  FROMHEX(string_parameter) and replace string_parameter with a hex string you need to convert to text.

EXAMPLE: FROMHEX('4d61727920416e6e') will return 'Mary Ann' string.

 

23. URLENCODE

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

USAGE: URLENCODE(string_to_encode, format_optional, boolean_encode_optional) and replace string_to_encode argument with string to be encoded (or decoded).
Optionally: provide 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 string.

EXAMPLE:  
URLENCODE({Stub__c.String__c}) will return 'Test+%2F+me' when given String value is 'Test/me'.
URLENCODE({Stub__c.String__c}, 'UTF-8', false) will return 'Test/me' when given String value is 'Test+%2F+me'.

24. TOTEXTDURATION

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

USAGE: TOTEXTDURATION(integer_minutes_parameter, hours_in_day_optional) and replace integer_minutes_parameter with integer representing number of minutes to get duration in hours and minutes. Add optional hours_in_day_optional argument to get duration in working days, hours and minutes.

EXAMPLE 1: TOTEXTDURATION(6067) will return '101h 7m'.
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).
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).

 

25. FROMTEXTDURATION

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

USAGE: FROMTEXTDURATION(string_parameter, hours_in_day_optional) and 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.

EXAMPLE 1: FROMTEXTDURATION('62h 34m') will return 3754 (62*60 + 34).
EXAMPLE 2: FROMTEXTDURATION('3d 2h 34m') will return 4474 (3*24*60 +2*60 + 34).
EXAMPLE 3: FROMTEXTDURATION('3d 2h 34m',8) will return 1594 (3*8*60 +2*60 + 34).

 

26. MATCH

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

USAGE: MATCH(regex_string, data_string), replace regex_string with a regular expression, and data_string with the string to compare to regular expression.

EXAMPLE:

Let Duration__c value is '8d'.
MATCH('[^abc]', {Stub__c.Duration__c}) will return following array: (8, d).

 

Logical Functions:

1. ISBLANK

Analyzes the given expression and returns:

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

USAGE: 

ISBLANK(parameter) and replace parameter with the expression you want evaluate.
ISBLANK(parameter1, if_true_parameter2) and replace parameters with the expressions you want evaluate.
ISBLANK(parameter1, if_true_parameter2, if_false_parameter3) and replace parameters with the expressions you want evaluate.

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. 

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 variable value in other case.

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.

 

2. NOT

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

USAGE:  NOT(parameter) and replace parameter with the expression that you want evaluated.

EXAMPLE: NOT(CONTAINS({Opportunity.Product_Type__c}, 'part')) will return true if product type value contains 'part' string and false if it doesn't.

 

3. IF

Determines if expressions are true or false. Returns a given value if true and another value if false.

USAGE: IF(condition, result_if_true, result_if_false) and replace condition with with the expression you want evaluated, result_if_true with the value you want returned if the expression is true and result_if_false with the value you want returned if the expression is false

EXAMPLE: IF({Opportunity.Items_Number__c} >0, 'In Progress', 'Pending') will return 'In Progress' if number of items is more than zero, and 'Pending' if it is zero.

 

4. AND

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

USAGE: AND(boolean_parameter1, boolean_parameter2, ...) and replace boolean_parameter1, boolean_parameter2, etc with values or expressions that you want evaluated.

EXAMPLE: AND({Contact.Migrated__c} = 'Yes', {Contact.Trusted__c} = 'Yes') will return true if both 'Migrated' and 'Trusted' values are equal to 'Yes'. Otherwise it will return false.

 

5. OR

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

USAGE: OR(boolean_parameter1, boolean_parameter2, ...) and replace and replace boolean_parameter1, boolean_parameter2, etc with values or expressions you want evaluated.

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.

 

6. CASE

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

USAGE: CASE(value, case1, result1, case2, result2, ..., else_result) and replace value with the field or value you want compared to each specified case. Replace each case with the data/expression/field for comparison. Replace each result with data/expression/field that must be returned for the proper case. Replace else_result with the data/expression/field that must be returned when the expression does not equal any case.

EXAMPLE:

Let String__c = 'Plane'.
CASE({Stub__c.String__c}, 'Car' , 'drive', 'Plane', 'fly', 'Boat', 'sail', 'none') will return 'fly'.

 

7. IN

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

USAGE: IN(value, list_of_values OR [value1, value2, ...]) and replace value with value you need to check in list. Replace list_of_values with the list to compare value parameter to; or pass list values as separate parameters  value1, value2, etc. You can also pass combination of lists and separate values as parameters.

EXAMPLE 1: IN({Opportunity.Stage}, 'Closed Lost', 'Closed Won', 'Negotiation/Review', 'Needs Analysis') returns true if Opportunity.Stage field value is present in the given set of strings and false if it is not.

EXAMPLE 2: 

Let Stub__c.String__c = '["YESTERDAY", "NOW", "TOMORROW"]' and Stub__c.Area__c = 'NEXT YEAR'.
IN('NOW', JPARSE({Stub__c.String__c})) will return true.
IN('NEXT YEAR', JPARSE({Stub__c.String__c}), {Stub__c.Area__c}, 'PREVIOUS YEAR') will return true.

 

8. NOTIN

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

USAGE: NOTIN(value, list_of_values OR [value1, value2, ...]) and replace value with value you need to check in list. Replace list_of_values with the list to compare value parameter to; or pass list values as separate parameters  value1, value2, etc. You can also pass combination of lists and separate values as parameters.

EXAMPLE 1: IN({Opportunity.Stage}, 'Closed Lost', 'Closed Won', 'Negotiation/Review', 'Needs Analysis') returns true if Opportunity.Stage field value is not present in the given set of strings and false if it is.

EXAMPLE 2: 

Let Stub__c.String__c = '["YESTERDAY", "NOW", "TOMORROW"]' and Stub__c.Area__c = 'NEXT YEAR'.
NOTIN('NOW', JPARSE({Stub__c.String__c})) will return false.
NOTIN('NEXT MONTH', JPARSE({Stub__c.String__c}), {Stub__c.Area__c}, 'PREVIOUS YEAR') will return true.

 

9. INSTANCEOF

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

USAGE: INSTANCEOF(value, Decimal|Boolean|String|Date|DateTime) and replace value argument with a value you want to validate and pass one of the allowed types (Decimal, Boolean, String, Date or DateTime) as second argument.  

EXAMPLE: INSTANCEOF({$Variables.Result}, Decimal) will return true if variable "Result" is a number, and false if it is not.

 

Numeric Functions:

1. NUMBER

Converts a string representing a number into a decimal number.

USAGE: NUMBER(parameter) and replace parameter with a string you need to convert to decimal.

EXAMPLE: NUMBER({Opportunity.Unique_ID__c}) returns decimal value of Unique_ID value, e.g if Unique_ID = '1234' function will return 1234.0.

Note: if Unique_ID value contains non-digit symbols, the function will throw an exception.

2. INTNUMBER

Converts a string representing a number into an integer.

USAGE: INTNUMBER(parameter) and replace parameter with a string you need to convert to integer

EXAMPLE: INTNUMBER({Opportunity.Unique_Value__c}) returns integer value of Unique_Value value, e.g for Unique_Value__с = '1234.32' it will return 1234.

Note: if Unique_Value__c value contains non-digit symbols, the function would throw an exception.

3. SUM

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

USAGE: SUM(list_of_values OR value1, value2, ...) and replace list_of_values argument with the list of numeric values or pass numeric values as value1, value2', etc. arguments. You can also pass combination of arrays and separate numeric arguments.

EXAMPLE: 

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})  formula will return 30.5.

Let String field stores following value: '8, 8, 10, 12'.
SUM(SPLIT({Stub__c.String__c}, ',')) will return 48.

Considering conditions above, the following formula 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)

 

4. SUB

Returns integer or decimal value representing result of sequential subtraction.

USAGE: SUB(list_of_values OR value1, value2, ...) and replace list_of_values argument with the list of numeric values or pass numeric values as value1, value2, etc. arguments. You can also pass combination of arrays and separate numeric arguments - they will be subtracted first and then results will be subtracted.

EXAMPLE: 

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})  formula will return -20 (5 - 10 - 15).

Let String field stores following value: '8, 8, 10, 12'.
SUB(SPLIT({Stub__c.String__c}, ',')) will return -22 (8 - 8 - 10 - 12).

Considering conditions above, the following formula 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)).

 

5. MULT

Returns integer or decimal value representing result of multiplication.

USAGE: MULT(list_of_values OR value1, value2, ...) and replace list_of_values argument with the list of numeric values or pass numeric values as value1, value2, etc. arguments. You can also pass combination of arrays and separate numeric arguments.

EXAMPLE: 

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})  formula will return 750 (5 * 10 * 15).

Let String field stores following value: '8, 8, 10, 12'.
MULT(SPLIT({Stub__c.String__c}, ',')) will return 7680 (8 * 8 * 10 * 12).

Considering conditions above, the following formula 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).

 

6. DIV

Returns integer or decimal value representing result of sequential dividing.

USAGE: DIV(list_of_values OR value1, value2, ...) and replace list_of_values argument with the list of numeric values or pass numeric values as value1, value2, etc. arguments. You can also pass combination of arrays and separate numeric arguments - they will be divided first and then results will be divided.

EXAMPLE: 

Let x = 450, y = 10 and z = 15.
DIV({Stub__c.x}, {Stub__c.y}, {Stub__c.z})  formula will return 3 (450 / 10 / 15).

Let String field stores following value: '1200, 2, 10, 12'.
DIV(SPLIT({Stub__c.String__c}, ',')) will return 5 (1200 / 2 / 10 / 12).

Let String field stores following value: '["24", "2", "3"]' and Area field stores  '["6", "3"]'.
Considering conditions above, the formula  DIV(JPARSE({Stub__c.String__c}), JPARSE({Stub__c.Area__c})) will return 2 (((24 / 2) / 3) / (6 / 3)).

 

7. SQRT

Returns the positive square root of a given number.

USAGE: SQRT(parameter) and replace parameter with the field or expression you want computed into a square root.

EXAMPLE: SQRT(26.5) returns 5.1478150704935, which is a square root of 26.5.

 

8. ABS

Calculates the absolute value of a number. The absolute value of a number is the number without its positive or negative sign.

USAGE: ABS(parameter) and replace parameter with a field or numeric value that has the sign you want removed.

EXAMPLE: ABS({Department__c.Expected_Revenue__c}) calculates the positive value of the Expected Revenue amount regardless of whether it is positive or negative.

 

9. LOG

Returns the base 10 logarithm of a number.

USAGE: LOG(parameter) and replace parameter with the field or expression from which you want the base 10 logarithm calculated.

EXAMPLE: Let {$Variables.Concentration} = 10-6. The formula LOG({$Variables.Concentration}) will return -6.

 

10. POW

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

USAGE: POW(parameter, exponent) and replace parameter argument with integer or double value that needs to be raised to the power of exponent argument.

EXAMPLE 1:  POW (8, 3) will return 512.

EXAMPLE 2:  POW (1000, -1/3) will return 0.1.

 

11. MOD

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

USAGE: MOD(parameter1, parameter2) and replace parameter1 with the field or expression you want to be divided; replace parameter2 with the number to use as the divisor.

EXAMPLE:  MOD(123, 100) will return 23.

 

12. MIN

Returns the lowest number from several numeric values.

USAGE: MIN(list_of_values OR value1, value2, ...) and replace list_of_values argument with the list of numeric values or pass numeric values as value1, value2, etc. arguments. You can also pass combination of arrays and separate numeric arguments.

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.

 

13. MAX

Returns the highest number from several numeric values.

USAGE: MAX(list_of_values OR value1, value2, ...)  and replace list_of_values argument with the list of numeric values or pass numeric values as value1, value2, etc. arguments. You can also pass combination of arrays and separate numeric arguments.

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.

 

14. ROUND

Returns the rounded approximation of a decimal value.

USAGE: ROUND(decimal_value, CEILING|DOWN|FLOOR|UP|HALF_DOWN|HALF_UP) and 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).

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_UP') will return 123.
ROUND(-122.5, 'HALF_DOWN') will return -122.
ROUND(-122.5, 'HALF_UP') will return -123.

 

15. SCALE

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

USAGE: SCALE(decimal_value, decimal_places) and replace decimal_value with decimal value you want scaled, and decimal_places with number of decimal places to be left.

EXAMPLE: 

Let salary value is 122.3456669.
Following formula SCALE({User.Salary__c}, 2) will return 122.35.

Let number value  is 25.
SCALE({Stub__c.Number__c}, 3) will return 25.000.

 

16. RANDOM

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

USAGE: RANDOM() doesn't require parameters.

EXAMPLE:  RANDOM() can return 2139744657709176245 or -6535028942888403203 (a random 19-digit number).

 

17. FORMATNUMBER

Available starting with 4.1 release.

Formats the decimal value to make it have readable view (adds thousands separators and removes leading zeroes) using the locale of the context user.

USAGE: FORMATNUMBER(decimal_value) and replace decimal_value with decimal value you want to be formatted.

EXAMPLE:  FORMATNUMBER(-01233534343453.566) will return -1,233,534,343,453.566 for the user with US locale.

 

Date and Time Functions:

1. MINUTES

Converts number into minutes value.

USAGE: MINUTES(parameter)  and replace parameter with the number value to convert it to minutes.

EXAMPLE:  {Opportunity.RFQ_Date__c} + MINUTES(30) will add 30 minutes to RFQ_Date__c value. If do not convert number to minutes, error will occur.

 

2. HOURS

Converts number into hours value.

USAGE: HOURS(parameter) and replace parameter with the number value to convert it to hours.

EXAMPLE: {Opportunity.RFQ_Date__c} + Hours(12) will add 12 hours to RFQ_Date__c value. 

 

3. MONTH

Returns the month component of a date or datetime value.

USAGE: MONTH(parameter) and replace parameter with the date/datetime value.

EXAMPLE: 

Let RFQ_Date__c = 2020-07-01T03:30:05Z.
MONTH({Opportunity.RFQ_Date__c}) will return 7 (integer representing month component of RFQ_Date__c field value).

 

4. YEAR

Returns the year component of a date or datetime value.

USAGE: YEAR(parameter) and replace parameter with the date/datetime value.

EXAMPLE: 

Let RFQ_Date__c = 2020-07-01T03:30:05Z.
YEAR({Opportunity.RFQ_Date__c}) will return 2020 (integer representing year component of RFQ_Date__c field value).

 

5. DATE

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

USAGE: DATE(year, month, day) and replace year with a four-digit year, month with a two-digit month, and day with a two-digit day.

EXAMPLE:  DATE(2016, 10, 10) creates a date field of October 10, 2016.

 

6. DATETIME

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

USAGE: DATETIME(year, month, day, hours, minutes, seconds) and replace parameters with integer values of year, month, day, hours, minutes and seconds.

EXAMPLE:  DATETIME(2005, 1, 2, 20, 30, 0) creates a date field of January 2, 2005, 8:30 PM.

 

7. DATETIMEGMT

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

USAGE: DATETIMEGMT(year, month, day, hours, minutes, seconds) and replace parameters with integer values of year, month, day, hours, minutes and seconds.

EXAMPLE:  DATETIMEGMT(2005, 1, 2, 20, 30, 0) creates a date field of January 2, 2005, 8:30 PM GMT as a datetime value.

 

8. DATEVALUE

Returns a date value for a date, datetime or text expression.

USAGE: DATEVALUE(datetime_or_string) and replace datetime_or_string with a date, datetime or text value.

EXAMPLE:  DATEVALUE('11/15/2015') returns November 15, 2015 as a date value.

 

9. DATETIMEVALUE

Returns a date and time (month, year, day and time) in the local time zone of the context user.

USAGE: DATETIMEVALUE(date_or_string) and replace date_or_string with a date, datetime or text value.

EXAMPLE:  DATETIMEVALUE('2005-11-15 17:00:00') returns November 15, 2005 5:00 PM.

 

10. DATETIMEVALUEGMT

Returns a year, month, day and time as GMT time value.

USAGE: DATETIMEVALUEGMT(datetime_or_string) and replace datetime_or_string with a date/datetime or text value.

EXAMPLE:  DATETIMEVALUEGMT('2015-11-15 17:00:00') returns November 15, 2005 5:00 PM in GMT.

 

11. DATETIMETOGMT

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

USAGE: DATETIMETOGMT(datetime_object) and replace datetime_object with a datetime value in context user local time zone.

EXAMPLE: given 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.

 

12. ADDDAYS

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

USAGE: ADDDAYS(date/datetime, number_of_days, boolean_consider_weekends) and replace date/datetime with the date or datetime you want to add days to, number_of_days with integer number of days that needs to be added to original value, and boolean_consider_weekends with true if calculation should exclude Saturday and Sunday, or false if it should not.

EXAMPLE:  ADDDAYS(DATE(2019, 02, 12), 2, false) will return 2019-02-14 00:00:00.

 

13. ADDMONTHS

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

USAGE: ADDMONTHS(date/datetime, number_of_months) and replace date/datetime with the date or datetime you want to add months to and number_of_months with the integer number of months that needs to be added to original value.

EXAMPLE: ADDMONTHS(DATE(2019, 02, 12), 5) will return 2019-07-12 00:00:00.

 

14. ADDYEARS

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

USAGE: ADDYEARS(date/datetime, number_of_years)  and replace date/datetime with the date or datetime you want to add years to and number_of_years with the integer number of years that needs to be added to original value.

EXAMPLE: ADDYEARS(DATE(2019, 02, 12), 5) will return 2024-02-12 00:00:00.

 

15. ADDINTERVAL

Adds time interval to the datetime based on the business hours for the SalesForce organization. 

USAGE: ADDINTERVAL(datetime, interval_string, business_hours_id_optional) and replace datetime parameter with a datetime value, interval_sting with time interval in the following format '1d 1h 1m' and  business_hours_id_optional with an id of a business hours record used in your organization.

EXAMPLE:

Let current system datetime value is Tuesday, 04/01, 8 AM; business hours setup is 8 AM to 11 AM, Monday to Friday.
FORMULA[FORMAT(ADDINTERVAL({$System.DateTime}, '5h 30m', 'id_of_business_hours_record'))] will return Wednesday, 04/02, 10:30 AM value.

To access business hours records navigate to Setup > Company Profile > Business Hours.

Note: a business_hours_id_optional parameter is optional.
If pass an empty string or null, interval will be added using 24-hours day, 7-days week, 365-days year.

16. SUBTRACTINTERVAL

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

USAGE: SUBTRACTNTERVAL(datetime, interval_string, business_hours_id_optional) and replace datetime parameter with a datetime value, interval_sting with time interval in the following format '1d 1h 1m' and  business_hours_id_optional with an id of a business hours record used in your organization.

EXAMPLE: FORMAT(SUBSTRACTINTERVAL(DATETIME(2019, 03, 12, 11, 0, 0), '3h 30m')) will return 3/12/2019 7:30 AM.

To access business hours records navigate to Setup > Company Profile > Business Hours.

Note: a business_hours_id_optional parameter is optional.
If pass an empty string or null, interval will be added using 24-hours day, 7-days week, 365-days year.

17. NORMALIZEDATE

Moves a date or datetime to the closer working day according to business hours.

USAGE: NORMALIZEDATE(date/datetime, business_hours_id_optional) and replace date/datetime parameter with value you need to normalize, and business_hours_id_optional with an id of a business hours record used in your organization.

'business_hours_id_optional' parameter is optional, if one passes an empty string or null value as business hours id default business hours setting for the organization will be used to calculate date/datetime.

EXAMPLE:

Let current system datetime value is Wednesday, 03/13, 2 PM; selected business hours setup is 8 AM to 11 AM, Monday to Friday.
NORMALIZEDATE({$System.DateTime}, id_of_business_hours_record) will return Monday, 3/18 8:00 AM.

To access business hours records navigate to Setup > Company Profile > Business Hours.

Note: a business_hours_id_optional parameter is optional.
If pass an empty string or null, default business hours setting for the organization will be used to calculate date or datetime.

18. STARTOFDAY

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

USAGE: STARTOFDAY(date/datetime, business_hours_id_optional) and replace date/datetime parameter with value you need to calculate, and business_hours_id_optional with an id of a business hours record used in your organization.

EXAMPLE:

Let current system datetime value is Wednesday, 03/13, 2 PM; selected business hours setup is 8 AM to 11 AM, Monday to Friday.
STARTOFDAY({$System.DateTime}, id_of_business_hours_record) will return Monday, 3/13 8:00 AM.

To access business hours records navigate to Setup > Company Profile > Business Hours.

Note: a business_hours_id_optional parameter is optional.
If pass an empty string or null, default business hours setting for the organization will be used to calculate date or datetime.

19. ENDOFDAY

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

USAGE: ENDOFDAY(date/datetime, business_hours_id_optional) and replace date/datetime parameter with value you need to calculate, and business_hours_id_optional with the business_hours_id_optional with an id of a business hours record used in your organization.

EXAMPLE:

Let current system datetime value is Wednesday, 03/13, 2 PM; selected business hours setup is 8 AM to 11 AM, Monday to Friday.
ENDOFDAY({$System.DateTime}, id_of_business_hours_record) will return Monday, 3/13 11:00 AM.

To access business hours records navigate to Setup > Company Profile > Business Hours.

Note: a business_hours_id_optional parameter is optional.
If pass an empty string or null, default business hours setting for the organization will be used to calculate date or datetime.

20. FORMAT

Returns a value containing an expression formatted according to given parameter.

USAGE: FORMAT(date/datetime, format_string_optional) and replace date/datetime with the original date or datetime value, replace optional parameter format_string_optional with the valid format expression (check formats supported here).

EXAMPLE: 

FORMAT(DATE(2019, 02, 12)) will return 2/12/2019.
FORMAT(DATETIME(2019, 02, 12, 11, 23, 0)) will return 2/12/2019 11:23 AM.
FORMAT(DATETIME(2019, 02, 12, 11, 23, 0), 'h:mm a') will return 11:23 AM.

 

21. FORMATGMT

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

USAGE: FORMATGMT(date/datetime, format_string_optional) and replace date/datetime with the original date or datetime value, replace optional parameter format_string_optional with the valid format expression (check formats supported here).

EXAMPLE: Given organization time zone is Eastern Standard Time. FORMATGMT(DATETIME(2019, 03, 12, 11, 23, 0)) will return 3/12/2019 3:23 PM.

 

22. STARTOFWEEK

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

USAGE: STARTOFWEEK(date/dateTime) and replace date/dateTime parameter with the date or datetime value whose start date of the week is to be determined.

EXAMPLE: STARTOFWEEK({Opportunity.createdDate}) will return start of the week date for the value of createdDate in GMT timezone.

23. DAYSBETWEEN

Returns number of days between two date or datetime values. 

USAGE: DAYSBETWEEN(date/datetime, date/datetime, ignoreWeekends) and replace parameters with the two dates and boolean ignoreWeekends with true if calculation should exclude Saturday and Sunday, or false if it should not.  

EXAMPLE: DAYSBETWEEN({Opportunity.createdDate}, {Opportunity.closedDate}, false) if createdDate equals 2001-07-04 and closedDate equals 2001-07-10 function will return 5.

 

24. DATEDIFF

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

USAGE: DATEDIFF(date/datetime, date/datetime, business_hours_id_optional, result_type_optional, hours_in_day_optional) replace parameters with two dates (see below for other parameters).

EXAMPLE 1: DATEDIFF({Opportunity.createdDate}, {Opportunity.closedDate}) if createdDate equals 2001-07-04 and closedDate equals 2001-07-05 function will return 1440 (minutes).

Replace  business_hours_id_optional parameter with an id of a business hours record used in your organization.

EXAMPLE 2: DATEDIFF({Opportunity.createdDate}, {Opportunity.closedDate}, business_hours_id) if createdDate equals 2020-06-29 and closedDate equals 2020-07-01T03:30:05Z, and business hours are set as 8 hours per day from Monday to Friday, function will return 960 (minutes).

Function can also return difference in Duration format (1d 1h 1m) or number of hours or days. To change format of result use 'result_type_optional' parameter: replace it with one of the following values: 'Duration', 'Hours', 'Days' (this parameter is case-insensitive).

Let createdDate = 2020-06-29 and closedDate = 2020-07-01T03:30:05Z

EXAMPLE 3: DATEDIFF({Opportunity.createdDate}, {Opportunity.closedDate}, null, DURATION) will return 44h 30m.
EXAMPLE 4: DATEDIFF({Opportunity.createdDate}, {Opportunity.closedDate}, null, Hours) will return 44 (full number of hours).
EXAMPLE 5: DATEDIFF({Opportunity.createdDate}, {Opportunity.closedDate}, null, days) will return 1.8716 (days).

hours_in_day_optional is another optional parameter that can be used when one passes 'Duration' or 'Days' as a 'result_type_optional' - it defines number of hours in a day for DATEDIFF calculation.

EXAMPLE 6: DATEDIFF({Opportunity.createdDate}, {Opportunity.closedDate}, false, 'Duration', 8) if createdDate = 2001-07-04 and closedDate = 2001-07-05 function will return '3d'.

To access business hours records navigate to Setup > Company Profile > Business Hours.

Notes:

  • A business_hours_id_optional parameter is optional. If omit this parameter or pass null, function will calculate result using default organization Business Days.
  • Function with 'Hours' result type will return full number of hours rounded down. This can be useful to avoid lates in calculated schedules, etc.
  • Optional parameters can be omitted only if they are last, otherwise they should be set as null.
JSON Functions:

1. JPARSE

Deserializes the specified JSON string into object or array.

USAGE: JPARSE(json_string, separator_optional) and replace json_string parameter with a JSON format string you need to parse. If one passes separator_optional param then JPARSE will convert string to array by splitting it.

EXAMPLES:
JPARSE('{"name" : "John Smith", "kids": [{"name": "Jim"}, {"name":"Nicky"}]}') will return  object.

Let String value is following: '["Feb", "Jan", "Dec"]'.
JPARSE({Stub__c.String__c}) will return array consisting of following elements: 'Feb', 'Jan', 'Dec'.

Let Area__c = 'Plane; Train; Car'.
JPARSE({Stub__c.Area__c}, ';') will return array consisting of following elements: Plane, Train, Car.

 

2. JOBJECT

Builds an object from provided key names and values.

USAGE: JOBJECT(key1, value1, key2, value2, ...) and replace key1,  key2 parameters with strings that are key names, and  value1,  value2 parameters with strings that are key values. If omit some value, it will be stored as null.

EXAMPLES:

JOBJECT() will return an empty object {}.

JOBJECT(key1, value1, key2, value2) will return an object {“key1“:”value1”, “key2“:”value2”}.

JOBJECT(key1, value1, key2) will return an object {“key1“:”value1”, “key2“:null}

 

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
}

Object values depend on data passed to the function:

  • blank space and null will give null value
  • logical expression and true will give true value. False is got in the same way.
  • if value is numeric (or has numeric result of calculation), it will be given as a number, without quotes.
  • to make empty string value, pass ''.
  • to make a nested object, call JOBJECT function in the proper place.

Empty keys will be ignored.

3. JSTRING

Serializes objects into JSON string.  

USAGE: JSTRING(json_object) and replace json_object parameter with an object you need to convert into  string. 

EXAMPLE: JSTRING(JEACH(SPLIT('12,14,15', ','), {$JEach}), true)) will return following string: '["12","14","15"]'.

 

4. JGET

Gets  a value from JSON content using a path provided.

USAGE: JGET(json_object, path) and replace json_object parameter with an object you want to use and path parameter with the path to find a field in the object or pass index for arrays.

EXAMPLES: JGET(JPARSE('{"name" : "Jon Smith", "address": "10 South Riverside Plaza"}'), 'address') will return Jon's address value "10 South Riverside Plaza".

Let String field value is 'June, July, August'.
JGET(JPARSE({Stub__c.String__c}, ','), 2) will return 'August'.

JEXIST(JPARSE('{"name" : "Jon Smith", "address": "10 South Riverside Plaza", "kids": ["Andrew","Jane", "Peter"]}'), JPARSE('["kids", 2]')) will return true as "kids" list has element with index 2.

 

5. JPUT

Puts  a value into JSON content using a path provided.

USAGE: JPUT(json_object, path, value) and replace json_object parameter with an object you want to use and path parameter with the path to find a field in the object to update it with a value parameter. You can also pass index as a path if you operate with an array (if value needs to be added as a last element of an array pass -1 as a path).

EXAMPLES:
JPUT(JPARSE('{"name" : "Jon Smith"}'), 'address', '10 South Riverside Plaza') will add Jon's address property and value to JSON object. 
JPUT(JPARSE('["Z", "Y", "X"]'), 0, 'A') will return array with following elements: A, Y, X

 

6. JREMOVE

Removes  a field from JSON content using a path provided.

USAGE: JREMOVE(json_object, key_or_jsonobject) and replace json_object parameter with an object you want to use and key_or_jsonobject parameter with the key to find a field you need to remove or with array element to be removed from JSON array.

EXAMPLE 1:
JREMOVE(JPARSE('{"name" : "Jon Smith", "address": "10 South Riverside Plaza", "age" : 9}'), 'address') will remove an address field from the JSON content.
JREMOVE(JPARSE('["Z", "Y", "X"]'), 'X') will return array with following elements: Z, Y.

If you need to remove multiple values from object you can pass array of object keys as a key_or_jsonobject, Same is true for JSON arrays: if you need to remove several elements from JSON array pass an array of elements to be removed.

EXAMPLE 2:
JREMOVE(JPARSE('{"name" : "Jon Smith", "address": "10 South Riverside Plaza", "age" : 9}'), SPLIT('name,age', ',')) will return JSON object with address property only.
JREMOVE(JPARSE('["1a", "2b", "3c", "4d"]'), SPLIT('1a,2b', ',')) will return an array with following elements: 3c, 4d.

 

7. JCLEAR

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

USAGE: JCLEAR(json_object, key_or_jsonobject_optional) and replace json_object parameter with an object you want to use and key_or_jsonobject_optional parameter with the key to find a field you need to remove or with path to nested object as array of elements.

EXAMPLE 1:
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 empty object {}.

EXAMPLE 2:
JCLEAR(JPARSE('{"personnames":{"first":"Jon","last":{"a":"Smith","b":"Ryan"}}, "address": "10 South Riverside Plaza", "age" : 9}'),'personnames') will return initial object where nested object 'personnames' will be empty: {address=10 South Riverside Plaza, age=9, personnames={}}

EXAMPLE 3:
JCLEAR(JPARSE('{"personnames":{"first":"Jon","last":{"a":"Smith","b":"Ryan"}}, "address": "10 South Riverside Plaza", "age" : 9}'),JPARSE('["personnames","last"]')) will return initial object where 2nd-level nested object 'last' will be empty: {address=10 South Riverside Plaza, age=9, personnames={first=Jon, last={}}}

 

8. JSIZE

Provides a number of elements in JSON content.

USAGE: JSIZE(json_object) and replace json_object parameter with an object or array you want to get size of.

EXAMPLES:
JSIZE(JPARSE('{"name" : "Jon Smith", "address": "10 South Riverside Plaza"}')) will return 2 as a number of key-value pairs in JSON content provided.
JSIZE(SPLIT('Name, Address, Age, Hair color', ',')) will return 4.

 

9. JEXIST

Checks if an element exists in JSON.

USAGE: JEXIST(json_object, path) and replace json_object parameter with an object you want to use and path parameter with the path to find a field you need to check, and index if you work with JSON array.

EXAMPLES:

JEXIST(JPARSE('{"name" : "Jon Smith", "address": "10 South Riverside Plaza"}'), 'Jon Smith') will return true as Jon Smith node is present in JSON content.

JEXIST(JPARSE('["one" , "two", "three", "four"]'), 2) will return true as element with index 2 is present in list.

JEXIST(JPARSE('{"name" : "Jon Smith", "address": "10 South Riverside Plaza", "appearance": {"eyes":"blue", "hair":"dark"}}'), JPARSE('["appearance", "hair"]')) will return true as "appearance" object has key "hair".

JEXIST(JPARSE('{"name" : "Jon Smith", "address": "10 South Riverside Plaza", "kids": ["Andrew","Jane", "Peter"]}'), JPARSE('["kids", 2]')) will return true as "kids" list has element with index 2.

 

10. JMERGE

Merges 2 JSON objects into one.

USAGE: JMERGE(json_object, json_object) and replace both json_object parameters with objects you want to merge.

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}.

 

11. JEACH

Executes specified function for each element of the list.  

USAGE: JEACH(json_array, function, exclude_nulls_boolean) and replace json_array parameter with an array you want to use, replace function parameter with expression to be executed for each array element. exclude_nulls_boolean is an optional parameter, if set it  to true nulls will be excluded from the resulting array. 

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

EXAMPLES:

Let String = 'Andrew,Alex,Helen,Ann,Robert'.
JOIN(JEACH(JPARSE({Stub__c.String__c}, ','), IF(STARTS({$JEach}, 'An'), {$JEach}, null), true), ',') will return following string 'Andrew, Ann'.

'[' + JOIN(JEACH(JPARSE({Stub__c.String__c}, ','), {DBL_QUOTE} + TEXT({$JEachIndex}) + : + ' ' + {$JEach} + {DBL_QUOTE}), ',') + ']' will return following string '["0: 'Andrew","1: Alex","2: Helen","3: Ann","4: Robert"]'.

 

12. JEACHMAP

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

USAGE: JEACHMAP(json_object, key_function, value_function) and replace json_obect parameter with an object you want to use, replace key_function parameter with expression to be executed for each key of the object, replace value_function with expression to be executed for each value of the object. 

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 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}".

EXAMPLES: 

JEACHMAP(JPARSE({$Environment}), {$JEachKey}, IF(INSTANCEOF({$JEach}, Decimal), MULT({$JEach}, 2), {$JEach})) will double all numeric values of the $Environment context object.

JEACHMAP(JPARSE({$Environment}), IF({$JEach} = true, UPPER({$JEachKey}), {$JEachKey}), {$JEach}) will convert to uppercase only those object keys whose values equal true.

 

13. JVAR

Defines formula local variables.

USAGE: JVAR(var1_name, va1_value_function, var2_name, va2_value_function, ..., value_function) and replace var1_name, var2_name , etc. parameters with an variables names, replace va1_value_function, va2_value_function, etc parameters with an expression to be executed as the value for the variables, replace value_function with an expression to be executed as the result of the function. 

To get access to the variable you should use the merge field "{$JVar.var1_name}", where "var1_name" is the variable name.

EXAMPLE: JVAR('var1', 1, 'var2', 2, {$JVar.var1} + {$JVar.var2}) - the result of this expression will be "3".

 

14. JFOR

Available starting Work Relay 4.1 Release.

Iterates through list/object using specified index range and executes specified function for each element of the list.  

USAGE: JFOR(from_decimal, to_decimal, expression, exclude_nulls_boolean_optional) and replace from_decimal parameter with a number to start loop from,  to_decimal with a number to stop loop at, expression parameter with expression to be executed for each iteration. exclude_nulls_boolean_optional is an optional parameter, if set it  to true nulls will be excluded from the result map. 

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.

 

System Functions:

Builds the task link or HTML <a>...</a> tag that leads to the record page layout. The task will be loaded automatically after the record page layout is loaded.

USAGE: BUILDRECORDTASKLINK(cursor_id, record_id, as_html_tag_optional, text_optional) and replace cursor_id with the id of flow instance cursor, record_id with the id of a task record, as_html_tag_optional with the boolean (true or false) value, text_optional parameter with the link text.  If the parameter as_html_tag_optional equals true, the function will build the HTML link tag.

EXAMPLES

BUILDRECORDTASKLINK({$Cursor.Id}, {$Cursor.FlowInstance.ObjectId}) will generate an URL text (like http://yoursite.domain/lighning/r/object/object_id/view#someParametersHere)

BUILDRECORDTASKLINK({$Cursor.Id}, {$Cursor.FlowInstance.ObjectId}, true, '[Link]') will generate a HTML code like this:

<a href="http://yoursite.domain/lighning/r/object/object_id/view#someParametersHere">[Link]</a>

Note: to make this link have sense, record page layout should have WR Workflow Line component (where proper step will be shown).

Available starting 4.1 release.

Builds the task link or HTML <a>...</a> tag that leads to the Work-Relay tasks dashboard. The task will be opened automatically after the dashboard is loaded.  

USAGE: BUILDDASHBOARDTASKLINK(cursor_id, as_html_tag_optional, text_optional) and replace cursor_id with the id of flow instance cursor, as_html_tag_optional with the boolean (true or false) value, text_optional parameter with the link text.  If the parameter as_html_tag_optional equals true, the function will build the HTML tag.

EXAMPLES:

BUILDDASHBOARDTASKLINK({$Cursor.Id}) will generate a simple URL as text.

BUILDDASHBOARDTASKLINK({$Cursor.Id}, true, '[Link]') will generate a HTML tag like <a href="http://your_dashboard_URL">[Link]</a>.

 

Available starting 4.1 release.

Builds the link or HTML <a>...</a> tag  that leads to the specified form.

USAGE: BUILDFORMLINK(form_id, record_id, mode, context_fields, parameters, entire_height, as_html_tag_optional, text_optional) and replace form_id with the ID of the target form, record_id with target form source record, mode with 'view' or 'edit', context_fields with JSON object (use context fields format), parameters with JSON object {"key":"value"}, entire_height with the boolean (true or false), as_html_tag_optional with the boolean (true or false), text_optional with the link text. If the parameter as_html_tag_optional equals "true", the function will build the HTML tag.

EXAMPLE: BUILDFORMLINK('formId', {$Account.Id}, 'edit', '{"InputText":"Name"}', null, true, true, 'FORM') will build HTML link tag like <a href="http://your_form_URL">FORM</a>. Link will redirect user to the form in edit mode with context field Name passed as {$FormParameters.InputText}.

Context Field format example:

The form source object is Contact (FirstName='Jim', LastName='Lee').
The context_fields in this case may look like: {"fnameParam":"FirstName", "lnameParam":"LastName"}.
On the target form merge field {$FormParameters.fnameParam} will be replaced with "Jim", and  {$FormParameters.lnameParam} will be replaced with 'Lee'.

Note: context_fields parameter will only work if record_Id is passed.

Builds a link to use email assistant feature, that allows users to proceed process steps via replying to emails with step notifications.

USAGE: BUILDEMAILASSISTANTLINK(text, instance_id, cursor_id, step_id, assignee, operation, comment, replyToSender) and replace text parameter with the link text, instance_id with the ID of a flow instance, cursor_id with the ID of flow instance cursor, step_id with the ID of the process step, assignee with the cursor assignee, operation with operation that needs to be performed (Proceed, Approve, Reject), comment with the comment that will be attached to a step.

Available starting 5.0 release: Function supports new boolean parameter replyToSender. Set it to true, if you want to receive notification about the step proceeding result to the email address of the person, who has proceeded the step by clicking this link, otherwise notification will be sent to the email address of the SF user, on whose behalf the step was proceeded.

For more information regarding Email Assistant feature setup please refer to the following article: Working with Email Assistant

EXAMPLE: BUILDEMAILASSISTANTLINK('Approve', {$Cursor.FlowInstance.Id}, {$Cursor.Id}, {$Step.Id}, {$Step.AssignTo}, 'Approve', '', true) will return a link to be included into email; clicking on this link will approve the step and send notification to the same address where email with this link has been sent to.

 

5. GETASSIGNEENAMES

Available starting with 4.1 release.

Returns names of Flow Instance Cursor assignees.  

USAGE: GETASSIGNEENAMES(ids_string) and replace ids_string with a single ID or group of IDs.

EXAMPLE: JOIN(GETASSIGNEENAMES({$Cursor.Assignee}), ',') will return 'User A, User B, User C' if cursor assignee type is User and 3 users are added as assignees.  

 

0 Comments

Add your comment

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.