All Functions

SOBJECTTYPE(id)

Replace id with the id of an object.

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

SOBJECTDESCRIBE(object_api_name)

Replace object_api_name with the API name of an object.

JGET(SOBJECTDESCRIBE('Account'), 'LabelPlural') will return 'Accounts'

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.

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

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

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.

CONTAINS(source_string, search_string)

Replace source_string with the text that should be searched for a value of search_string.

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

STARTS(source_string, search_string)

Replace source_string with the text that should be checked for starting from the value of search_string.

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

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.

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

ENDS(source_string, search_string)

Replace source_string with the text that should be checked for ending with the value of search_string.

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

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.

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

REMOVE(source_string, string_parameter1, string_parameter2, ...)

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.

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

LEN(string)

Replace string with the field or expression whose length you want to get.

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

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.

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

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

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

LOWER(string)

Replace string parameter with a string you need to convert to lowercase.

LOWER({Account.Name}) will return Account Name value in lowercase.

UPPER(string)

Replace string parameter with a string you need to convert to uppercase.

UPPER({Account.Name}) will return Account Name value in uppercase.

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.

Let Error__c value is 'Please, fill in "Name"'. ESCAPE({Stub__c.Error__c}, 'HTML') will return 'Please, fill in "Name"' 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 "Name"'

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

ID(string)

Replace string argument with a value that should be treated as ID.

ID('a006m00007sMGHjAAO') will return a006m00007sMGHjAAO (some object ID).

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

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:

TEXT(parameter)

Replace parameter with the field or expression you want to convert to text format.

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

BLOB(string)

Replace string argument with a string you need to cast to BLOB

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

TOBASE64(blob_parameter)

Replace blob_parameter with a BLOB content, you need to convert it to base64.

Let Account.Name value is 'Mary Ann'.

TOBASE64(BLOB({$Account.Name})) will return 'TWFyeSBBbm4='

FROMBASE64(string_parameter)

Replace string_parater with Base64-encoded string.

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

TOHEX(string_parameter)

Replace string_parameter with a string you need to convert to hex.

Let Account.Name value is 'Mary Ann'.

TOHEX({Account.Name}) will return '4d61727920416e6e'.

FROMHEX(string_parameter)

Replace string_parameter with a hex string, you need to convert it to text.

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

MATCH(regex_string, data_string)

Replace regex_string with a regular expression, and data_string with the string to compare to regex.

Let Duration__c value is '8d'.

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

NOT(parameter)

Replace parameter with a logical expression that you want to evaluate.

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

IF(condition, result_if_true, result_if_false)

Replace: condition with 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.

IF({Opportunity.Items_Number__c} > 0, 'In Progress', 'Pending') will return 'In Progress' if number of items is more than 0, and 'Pending' in other case.

AND(boolean_parameter1, boolean_parameter2, ...)

Replace boolean_parameter1, boolean_parameter2, etc with values or expressions that you want evaluated.

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.

OR(boolean_parameter1, boolean_parameter2, ...)

Replace boolean_parameter1, boolean_parameter2, etc., with values or expressions you want evaluated.

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.

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 to any case.

Let String__c = 'Plane'.

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

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 second argument.

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

NUMBER(parameter)

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

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

INTNUMBER(parameter)

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

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

SQRT(parameter)

Replace parameter with the field or expression you want computed into a square root.

SQRT(26.5) returns 5.1478150704935.

ABS(parameter)

Replace parameter with a field or numeric value that has the sign you want removed.

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

LOG(parameter)

Replace parameter with the field or expression from which you want the base 10 logarithm calculated.

Let {$Variables.Concentration} = 10^-6.

LOG({$Variables.Concentration}) will return -6.

MOD(parameter1, parameter2)

Replace:

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

parameter2 with the number to use as the divisor.

MOD(123, 100) will return 23.

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

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.

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

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.

RANDOM()

This function doesn't require parameters.

RANDOM() will return a random 19-digit number like 2139744657709176245 or -6535028942888403203.

FORMATNUMBER(decimal_value)

Replace decimal_value with the decimal value you want to be formatted.

FORMATNUMBER(-01233534343453.566) will return -1,233,534,343,453.566.

MINUTES(parameter)

Replace parameter with the number value to convert it to minutes.

{Opportunity.RFQ_Date__c} + MINUTES(30) will add 30 minutes to RFQ_Date__c value.

HOURS(parameter)

Replace parameter with the number value to convert it to hours.

{Opportunity.RFQ_Date__c} + HOURS(12) will add 12 hours to RFQ_Date__c value.

DATE(year, month, day)

Replace:

year with a four-digit year;

month with a two-digit month;

day with a two-digit day.

DATE(2016, 10, 10) returns October 10, 2016 as a date value.

DATETIME(year, month, day, hours, minutes, seconds)

Replace parameters with integer values of year, month, day, hours, minutes, and seconds.

DATETIME(2005, 1, 2, 20, 30, 0) returns January 2, 2005, 8:30 PM as a datetime value.

DATETIMEGMT(year, month, day, hours, minutes, seconds)

Replace parameters with integer values of year, month, day, hours, minutes, and seconds.

DATETIMEGMT(2005, 1, 2, 20, 30, 0) returns January 2, 2005, 8:30 PM GMT as a datetime value.

DATEVALUE(datetime_or_string)

Replace datetime_or_string with a date, datetime, or text value.

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

DATETIMEVALUE(date_or_string)

Replace date_or_string with a date, datetime, or text value.

DATETIMEVALUE('2005-11-15 17:00:00') returns November 15, 2005 5:00 PM as a datetime value.

DATETIMEVALUEGMT(datetime_or_string)

Replace datetime_or_string with a date/datetime or text value.

DATETIMEVALUEGMT('2015-11-15 17:00:00') returns November 15, 2005 5:00 PM as a datetime value in GMT time zone.

DATETIMETOGMT(datetime_object)

Replace datetime_object with a datetime value in the context of the user's local time zone.

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.

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.

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

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.

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

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.

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

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.

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.

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.

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

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.

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.

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.

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.

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.

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.

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.

Let organization time zone is Eastern Standard Time.

FORMATGMT(DATETIME(2019, 03, 12, 11, 23, 0)) will return 3/12/2019 3:23 PM.

STARTOFWEEK(date/dateTime)

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

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.

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.

Let createdDate equals 2001-07-04 and closedDate equals 2001-07-10

DAYSBETWEEN({Opportunity.createdDate}, {Opportunity.closedDate}, false) will return 5.

YEAR(parameter)

Replace parameter with the date/datetime value.

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

MONTH(parameter)

Replace parameter with the date/datetime value.

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

JPARSEXML(xml_string)

Replace xml_string parameter with a string (in XML format) you need to parse.

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

JSTRING(json_object)

Replace json_object parameter with an object you need to convert into a string.

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

JMERGE(json_object, json_object)

Replace both json_object parameters with objects you want to merge.

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

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.

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

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.

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.

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.

returns an URL for such picture:

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.

returns an URL for such picture: