Standard Library
Functions
Collection
AGGREGATEGROUPS
Applies the given aggregator to each group in the given grouping, returning the results together as a map with the respective group keys. Accepts 2 arguments: the grouping to aggregate and the expression to evaluate. Provides the following special variables in the inner expression:
$current(the current item being iterated over).$key(the current group key).$isFirst(true if the current item is the first in the group).$acc(the current accumulator value).
AGGREGATEGROUPS({ "a": [1, 2], "b": [3, 4] }, IF($isFirst, $current, SUM([$acc, $current])) // { "a": 3, "b": 7 }
ANY
Returns true if any element in the list matches the given expression. Provides 1 special variable in the inner expression: $current (the current item being iterated over). Accepts 2 arguments: the list to evaluate and the expression to evaluate.
ANY([1, 2, 3], $current = 2) // true
APPEND
Appends an item to a list. Accepts 2 arguments: the list and the item to append.
APPEND(LIST(1, 2, 3), 4) // [1, 2, 3, 4]
ASSOCIATEBY
Creates a map by associating each element of the input list with a key generated by the given expression. Accepts 2 arguments: the list to evaluate and the expression to evaluate. Provides the following special variables in the inner expression:
$current(the current item being iterated over).
ASSOCIATEBY([1, 2, 3], $current + 1) // { 2: 1, 3: 2, 4: 3 }
AT
Returns the element at the specified index. If the list is empty or the index is out of bounds, this function will return null. Accepts 2 arguments: the list to evaluate and the index to return.
AT([1, 2, 3], 1) // 2
AVERAGE
Returns the average given a list of numbers. Accepts 1 argument: the list of numbers to evaluate.
AVERAGE(LIST(1, 2, 3))
DISTINCT
Returns a list of unique values.
DISTINCT(LIST(1, 2, 3, 1, 2, 3)) // [1, 2, 3]
EVERY
Returns true if every element in the list matches the given expression. Provides 1 special variable in the inner expression: $current (the current item being iterated over). Accepts 2 arguments: the list to evaluate and the expression to evaluate.
EVERY([1, 2, 3], $current > 0) // true
EXPAND
Expands each element of a list into zero or more elements, resulting from the evaluation of the given expression. Provides 1 special variable in the inner expression: $current (the current item being iterated over). Accepts 2 arguments: the list to evaluate and the expression to evaluate. The expression must return a list.
EXPAND([1, 2, 3], LIST($current, $current + 1)) // [1, 2, 2, 3, 3, 4]
FIRST
Returns the first element in a list. Accepts 1 argument: the list to evaluate.
FIRST(LIST(1, 2, 3)) // 1
FIRSTWHERE
Returns the first element of a list that matches the given expression or null if the list is empty or no element matches the expression. Provides 1 special variable in the inner expression: $current (the current item being iterated over). Accepts 2 arguments: the list to evaluate and the expression to evaluate.
FIRSTWHERE([1, 2, 3], $current > 2) // 3
FLATTEN
Flattens a list of lists into a single list. Accepts 1 argument: the list of lists to flatten.
FLATTEN([[1, 2], [3, 4]]) // [1, 2, 3, 4]
FOLLOWEDBY
Appends a list to another list. Accepts 2 arguments: the list to append to and the list to append.
FOLLOWEDBY([1, 2, 3], [4, 5, 6]) // [1, 2, 3, 4, 5, 6]
GET
Returns the value of a key in a map or the field in an SObject. Accepts 2 arguments: the map or SObject to evaluate and the key to get.
GET(MAP('a' => 1, 'b' => 2, 'c' => 3), 'a') // 1
GROUPBY
Groups the elements of a list by the result of the given expression. Accepts 2 arguments: the list to evaluate and the expression to evaluate. Provides the following special variables in the inner expression:
$current(the current item being iterated over).
GROUPBY([
{ "FirstName": "John", "LastName": "Doe" },
{ "FirstName": "Jane", "LastName": "Doe" },
], $current.LastName)
// { "Doe": [{ "FirstName": "John", "LastName": "Doe" }, { "FirstName": "Jane", "LastName": "Doe" }] }
ISEMPTY
Returns true if the list or map is empty. Accepts 1 argument: the list or map to evaluate.
ISEMPTY(LIST(1, 2, 3)) // false
JOIN
Joins a list of values into a string using the specified delimiter. Accepts 2 arguments: the list to join and the delimiter.
JOIN([1, 2, 3], ", ")
KEYS
Returns a list of keys in a map.
KEYS(MAP('a' => 1, 'b' => 2, 'c' => 3)) // ['a', 'b', 'c']
LAST
Returns the last element in a list. Accepts 1 argument: the list to evaluate.
LAST(LIST(1, 2, 3)) // 3
LASTWHERE
Returns the last element of a list that matches the given expression or null if the list is empty or no element matches the expression. Provides 1 special variable in the inner expression: $current (the current item being iterated over). Accepts 2 arguments: the list to evaluate and the expression to evaluate.
LASTWHERE([1, 2, 3], $current > 2) // 3
LIST
Creates a list using the received arguments as the values.
LIST(1, 2, 3, ...LIST(4, 5, 6))
MAP
Maps to a list using the first argument as the context and the second argument as the expression to evaluate. Accepts 2 arguments: List of objects and an expression to evaluate.
MAP(["a", "b", "c"], UPPER($current))
PUT
Adds a key/value pair to a map. Accepts 3 arguments: the map to add to, the key to add, and the value to add.
PUT({ "a": 1, "b": 2, "c": 3 }, "d", 4) // { "a": 1, "b": 2, "c": 3, "d": 4 }
RANGE
Returns a list of numbers from the start to the end, inclusive. Accepts 2 arguments: the start and end numbers.
RANGE(1, 3) // [1, 2, 3]
REDUCE
Reduces a list to a single value using the first argument as the context, the second argument as the expression to evaluate, and the third argument as the initial value. Accepts 3 arguments: List of objects, an expression to evaluate, and the initial value.
REDUCE([1, 2, 3], $accumulator + $current, 0) // 6
SIZE
Returns the number of elements in a list or map. Accepts 1 argument: the list or map to evaluate.
SIZE(LIST(1, 2, 3)) // 3
SKIP
Skips the first N elements of a list. Accepts 2 arguments: the list to skip and the number of elements to skip.
SKIP([1, 2, 3], 2) // [3]
SKIPWHILE
Skips elements of a list while the given expression evaluates to true. Provides 1 special variable in the inner expression: $current (the current item being iterated over). Accepts 2 arguments: the list to evaluate and the expression to evaluate.
SKIPWHILE([1, 2, 3], $current < 3) // [3]
SORT
Sorts a list. Accepts at least one argument: the list to sort. When sorting a list of Maps or a list of SObjects, three additional arguments can be provided: the field to sort by, the sort direction, and the position of nulls (nulls first or nulls last).
The field to sort can either be a field name as a merge field (field name without quotes), or an expression that evaluates to a string representing the field name. Merge fields are only supported when sorting SObjects and are useful to get the framework to automatically query the field for you.
Note: The merge field must be a field on the SObject being sorted itself, not a relationship field.
The sort direction can either be the literal string (requires quotes) ASC or DESC. The position of nulls can either be the literal string (requires quotes) NULLS_FIRST or NULLS_LAST.
SORT([{ "a": 3 }, { "a": 2 }, { "a": 1 }], "a", "DESC") // [{ "a": 3 }, { "a": 2 }, { "a": 1 }]
SORT([SObject1, SObject2, SObject3], "Name", "ASC", "NULLS_LAST") // [SObject1, SObject2, SObject3]
SUM
Returns the sum of a list of numbers. Accepts 1 argument: the list of numbers to evaluate.
SUM([1, 2, 3]) // 6
TAKE
Returns the first N elements of a list. Accepts 2 arguments: the list to take from and the number of elements to take.
TAKE([1, 2, 3], 2) // [1, 2]
TAKEWHILE
Returns elements of a list while the given expression evaluates to true. Provides 1 special variable in the inner expression: $current (the current item being iterated over). Accepts 2 arguments: the list to evaluate and the expression to evaluate.
TAKEWHILE([1, 2, 3], $current < 3)
VALUES
Returns a list of values in a map.
VALUES(MAP('a' => 1, 'b' => 2, 'c' => 3)) // [1, 2, 3]
WHERE
Filters a list using the first argument as the context and the second argument as the expression to evaluate. Accepts 2 arguments: List of objects and an expression to evaluate.
WHERE([1, 2, 3], $current > 1)
Data
LET
Allows you to define custom variables that can be used in the expression. Accepts 2 arguments: a map of variables to define and the expression to evaluate. The map keys should be the variable names prefixed with $.
LET({ "$a": 1, "$b": 2 }, $a + $b) // 3
PARSEJSON
Parses a JSON string into a usable map/object structure. Accepts 1 argument: the JSON string to parse.
PARSEJSON(Contact.Custom_JSON_Field__c) // Parses JSON from a field
Allows you to print a value to the playground console. Accepts 1 argument: the value to print.
PRINT("Hello World")
RAWQUERY
Allows you to run a raw query against the database. Accepts 1 argument: the query to run.
RAWQUERY("SELECT Id, Name FROM Account LIMIT 10")
TRANSFORM
Transforms any input using the provided expression. Provides a special variable $source in the inner expression that contains the original input.
Accepts 2 arguments: the input to transform and the expression to evaluate.
TRANSFORM("Hello World", UPPER($source)) // "HELLO WORLD"
Date and Time
ADDMONTHS
Returns a date that is a specified number of months before or after a given date.
Accepts 2 arguments: the date and the number of months to add.
ADDMONTHS(DATE(2020, 1, 1), 1) // 2020-02-01
DATE
Returns a date value from the provided year, month, and day values.
Accepts 3 arguments: the year, month, and day.
DATE(2020, 1, 1) // 2020-01-01
DATETIME
Returns a datetime value from the provided year, month, day, hour, minute, and second values.
Accepts 6 arguments: the year, month, day, hour, minute, and second.
DATETIME(2020, 1, 1, 12, 0, 0) // 2020-01-01 12:00:00
DATETIMEFORMAT
Formats a DateTime into a string using the provided format.
Accepts 2 arguments: the DateTime to format and the format string.
DATETIMEFORMAT(DATETIMEVALUE("2020-01-01 12:00:00"), "yyyy-MM-dd") // 2020-01-01
DATETIMEVALUE
Returns a datetime value from a string representation of a date time.
Accepts 1 argument: the date time as a string.
DATETIMEVALUE("2020-01-01 00:00:00") // 2020-01-01 00:00:00
DATETODATETIME
Converts a Date to a Datetime.
Accepts 1 argument: the Date to convert.
DATETODATETIME(DATE(2020, 1, 1)) // 2020-01-01 00:00:00
DATEVALUE
Returns a date value from a string representation of a date or a datetime.
Accepts 1 argument: the date as a string or datetime.
DATEVALUE("2020-01-01") // 2020-01-01
DAY
Returns the day of the month, a number from 1 to 31.
Accepts 1 argument: the date to evaluate.
DAY(DATE(2020, 1, 1)) // 1
DAYOFYEAR
Returns the day of the year, a number from 1 to 366.
Accepts 1 argument: the date to evaluate.
DAYOFYEAR(DATE(2020, 1, 1)) // 1
DAYSBETWEEN
Returns the number of days between two dates.
Accepts 2 arguments: the first date and the second date.
DAYSBETWEEN(DATE(2020, 1, 1), DATE(2020, 1, 2)) // 1
FORMATDURATION
Calculates the difference between 2 Times or 2 DateTimes and formats it as "HH:MM:SS".
Accepts 2 arguments: either 2 Times or 2 DateTimes.
Note that the order of the argument is not important, the function will always return a positive duration.
FORMATDURATION(TIMEVALUE("12:00:00"), TIMEVALUE("12:00:01")) // 00:00:01
FROMUNIXTIME
Returns the GMT Datetime from a Unix timestamp.
Accepts 1 argument: the Unix timestamp to evaluate.
FROMUNIXTIME(1577836800) // 2020-01-01 00:00:00
HOUR
Returns the hour value of a provided time.
Accepts 1 argument: the time to evaluate.
HOUR(TIMEVALUE("12:00:00")) // 12
ISOWEEK
Returns the ISO week number of the year for a given date.
Accepts 1 argument: the date to evaluate.
ISOWEEK(DATE(2020, 1, 1)) // 1
ISOYEAR
Returns the ISO year number for a given date.
Accepts 1 argument: the date to evaluate.
ISOYEAR(DATE(2020, 1, 1)) // 2020
MILLISECOND
Returns the millisecond value of a provided time.
Accepts 1 argument: the time to evaluate.
MILLISECOND(TIMEVALUE("12:00:00.123")) // 123
MINUTE
Returns the minute value of a provided time.
Accepts 1 argument: the time to evaluate.
MINUTE(TIMEVALUE("12:10:00")) // 10
MONTH
Returns the month, a number between 1 and 12 (December) in number format of a given date.
Accepts 1 argument: the date to evaluate.
MONTH(DATE(2020, 1, 1)) // 1
NOW
Returns the current Datetime in the GMT time zone.
Accepts no arguments.
NOW() // 2020-01-01 00:00:00
SECOND
Returns the second value of a provided time.
Accepts 1 argument: the time to evaluate.
SECOND(TIMEVALUE("12:00:45")) // 45
TIMENOW
Returns the current time.
Accepts no arguments.
TIMENOW() // 12:00:00
TIMEVALUE
Returns a time value from a datetime or from a string representation of a datetime.
Accepts 1 argument: the datetime or string in datetime format to evaluate.
TIMEVALUE(DATETIMEVALUE("2020-01-01 12:00:00")) // 12:00:00
TODAY
Returns the current date.
Accepts no arguments.
TODAY() // 2020-01-01
UNIXTIMESTAMP
Returns the number of seconds since 1 Jan 1970 for the given date or datetime, or number of seconds in the day for a time.
Values are returned in the GMT time zone.
Accepts 1 argument: the date, datetime, or time to evaluate.
UNIXTIMESTAMP(DATE(2020, 1, 1)) // 1577836800
WEEKDAY
Returns the day of the week for the given date, using 1 for Sunday, 2 for Monday, through 7 for Saturday.
Accepts 1 argument: the date to evaluate.
WEEKDAY(DATE(2020, 1, 1)) // 2
YEAR
Returns the year value of a provided date.
Accepts 1 argument: the date to evaluate.
YEAR(DATE(2020, 1, 1)) // 2020
Location
DISTANCE
Returns the distance between two locations in the specified unit.
Accepts 3 arguments: the first location, the second location, and the unit (either "mi" or "km").
DISTANCE(LOCATION(37.7749, 122.4194), LOCATION(40.7128, 74.0060), "mi") // 2565.6985207767134
LOCATION
Returns a location object from the provided latitude and longitude.
Accepts 2 arguments: the latitude and longitude.
LOCATION(37.7749, 122.4194) // { "latitude": 37.7749, "longitude": 122.4194 }
Logical
AND
Returns a TRUE response if all values are true; returns a FALSE response if one or more values are false.
Accepts multiple arguments, but must have at least 2.
AND(true, false, true) // false
BLANKVALUE
Returns a specified value if the expression is blank (null value or empty string); otherwise, returns the result of the expression.
Accepts 2 arguments: the expression and the value to return if the expression is blank.
BLANKVALUE(null, "Hello") // "Hello"
CASE
Compares a given expression to a set of values. If the expression matches a value, the corresponding value is returned, otherwise the default value is returned.
Accepts any number of arguments where the first is the expression to evaluate, the last is the "else" case and in between each pair of arguments is a value to compare against and the value to return if the expression matches. Format: CASE(expression,value1, result1, value2, result2,..., else_result)
CASE(Rating, "Hot", "🔥", "Cold", "🧊", "🤷") // "🔥", "🧊", or "🤷"
IF
Returns one value if a condition is true and another value if it's false.
Accepts 3 arguments: the condition, the value if true, and the value if false.
IF(true, "Hello", "World") // "Hello" IF(false, "Hello", "World") // "World"
ISBLANK
Returns TRUE if the expression is blank (null value or empty string); otherwise, returns FALSE.
Accepts 1 argument: the expression to check.
ISBLANK(null) // true
ISBLANK("") // true
ISBLANK("Hello") // false
ISNUMBER
Returns TRUE if the expression is a number; otherwise, returns FALSE.
Accepts 1 argument: the expression to check.
ISNUMBER(1) // true
ISNUMBER("Hello") // false
NOT
Reverses the logical value of its argument.
Accepts 1 argument.
NOT(true) // false
OR
Returns a TRUE response if any value is true; returns a FALSE response if all values are false.
Accepts any number of arguments.
OR(true, false, true) // true OR(false, false, false) // false
Math
ABS
Returns the absolute value of a number.
Accepts 1 argument: the number to evaluate.
ABS(-1) // 1
CEILING
Returns the smallest integer greater than or equal to the specified number.
Accepts 1 argument: the number to evaluate.
CEILING(1.5) // 2
FLOOR
Returns the largest integer less than or equal to the specified number.
Accepts 1 argument: the number to evaluate.
FLOOR(1.5) // 1
FORMATNUMBER
Formats a number with comma as thousand separator.
Accepts 1 or 2 arguments: the number to format and optionally the number of decimal places.
FORMATNUMBER(20000.53) // "20,000.53" FORMATNUMBER(20000.53, 1) // "20,000.5"
MAX
Returns the largest value in a list of numbers.
Accepts either a list of numbers as a single argument, or multiple numerical arguments.
MAX(1, 2, 3) // 3 MAX([1, 2, 3]) // 3
MIN
Returns the smallest value in a list of numbers.
Accepts either a list of numbers as a single argument, or multiple numerical arguments.
MIN(1, 2, 3) // 1 MIN([1, 2, 3]) // 1
MOD
Returns the remainder of one number divided by another.
Accepts 2 arguments: the dividend and the divisor.
MOD(5, 2) // 1
ROUND
Returns a rounded number. Optionally specify the number of decimal places to round to.
Accepts 1 or 2 arguments: the number to round and optionally the number of decimal places to round to.
ROUND(1.234) // 1 ROUND(1.234, 2) // 1.23
TRUNC
Returns a truncated number. Optionally specify the number of decimal places to truncate to.
Accepts 1 or 2 arguments: the number to truncate and optionally the number of decimal places to truncate to.
TRUNC(1.234) // 1 TRUNC(1.234, 2) // 1.23
String
BEGINS
Returns TRUE if the first character(s) in a text field match a given string.
Accepts 2 arguments: the text field and the string to match.
BEGINS("Hello World", "Hello") // TRUE
BR
Inserts a line break in a string of text.
When no arguments are provided, it inserts a line break. When a number is provided, it inserts that number of line
⚠️ Note that the inserted line break depends on the call context based on the Request Quiddity. When called from an Aura/LWC or Visualforce context it will insert a <br> tag, otherwise it will insert a newline character.
BR() // "<br/>" BR(2) // "<br/><br/>"
CONTAINS
Returns TRUE if a text field contains a given string.
Accepts 2 arguments: the text field and the string to match.
CONTAINS("Hello World", "World") // TRUE
FIND
Returns the starting position of one text string within another text string. If the text string is not found, FIND returns a value -1.
Accepts either 2 or 3 arguments: the text to find, the text to search, and optionally the starting position.
FIND("World", "Hello World") // 7
FIND("World", "Hello World", 7) // -1
HYPERLINK
Returns a text string of an HTML anchor tag that displays a hyperlink.
Accepts 2 or 3 arguments: the URL and the text to display. Optionally, the third argument is the target of the link.
The target should be one of _blank, _parent, _self, or _top.
HYPERLINK("https://www.google.com", "Google") // "<a href="https://www.google.com">Google</a>"
INITCAP
Converts the first letter of each word in a text string to uppercase and converts all other letters to lowercase.
Accepts 1 argument: the text to convert.
INITCAP("hello world") // "Hello World"
LEFT
Returns the specified number of characters from the beginning of a text string.
Accepts 2 arguments: the text to evaluate and the number of characters to return.
LEFT("Hello World", 5) // "Hello"
LEN
Returns the number of characters in a text string.
Accepts 1 argument: the text to evaluate.
LEN("Hello World") // 11
LIKE
Returns TRUE if a text field matches a given pattern. The pattern can include regular characters and wildcard characters. The supported wildcard characters are the percent sign (%), which matches zero or more characters, and the underscore (_), which matches exactly one character.
Accepts 2 arguments: the text field and the pattern to match.
LIKE("Hello World", "Hello%") // TRUE
LIKE("Hello World", "Hello_") // FALSE
LOWER
Converts all letters in the specified text to lowercase.
Accepts 1 argument: the text to convert.
LOWER("Hello World") // "hello world"
LPAD
Returns a text value padded to the specified length with the specified set of characters.
Accepts 2 or 3 arguments: the text to pad, the length to pad to, and optionally the padding character. If the padding character is not specified, it defaults to a space.
LPAD("Hello", 10) // " Hello"
LPAD("Hello", 10, "*") // "*****Hello"
MID
Returns a specified number of characters from a text string starting at the position you specify up to the number of characters you specify.
Note that the position is 1-based, not 0-based.
Accepts 3 arguments: the text to evaluate, the starting position, and the number of characters to return.
MID("Hello World", 7, 5) // "World"
REVERSE
Returns a text value with the order of the characters reversed.
Accepts 1 argument: the text to reverse.
REVERSE("Hello World") // "dlroW olleH"
RIGHT
Returns the specified number of characters from the end of a text string.
Accepts 2 arguments: the text to evaluate and the number of characters to return.
If the second argument is a negative number, it gets treated as a 0
RIGHT("Hello World", 5) // "World"
RIGHT("Hello World", -5) // ""
RPAD
Returns a text value padded to the specified length with the specified set of characters.
Accepts 2 or 3 arguments: the text to pad, the length to pad to, and optionally the padding character. If the padding character is not specified, it defaults to a space.
RPAD("Hello", 10) // "Hello "
RPAD("Hello", 10, "*") // "Hello*****"
SPLIT
Returns a list that contains each substring of the String that is terminated by the provided delimiter.
Accepts 2 arguments: the text to split and the delimiter.
SPLIT("Hello World", " ") // ["Hello", "World"]
SUBSTITUTE
Substitutes new text for old text in a text string.
Accepts 3 arguments: the text to evaluate, the text to replace, and the text to replace it with.
SUBSTITUTE("Hello World", "World", "Universe") // "Hello Universe"
SUBSTRING
Returns a specified number of characters from a text string starting at the position you specify. Optionally, you can specify the number of characters to return.
Note that the position is 1-based, not 0-based.
Accepts 2 or 3 arguments: the text to evaluate and the starting position. Optionally, the number of characters to.
SUBSTRING("Hello World", 7) // "World"
SUBSTRING("Hello World", 7, 5) // "World"
TEXT
Converts a value to text.
Accepts 1 argument: the value to convert.
TEXT(123) // "123"
TRIM
Removes the spaces and tabs from the beginning and end of a text string.
Accepts 1 argument: the text to trim.
TRIM(" Hello World ") // "Hello World"
UPPER
Converts all letters in the specified text to uppercase.
Accepts 1 argument: the text to convert.
UPPER("Hello World") // "HELLO WORLD"
URLENCODE
Encodes text and merge field values for use in URLs by replacing characters that are illegal in URLs, such as blank spaces.
Accepts 1 argument: the text to encode.
URLENCODE("Hello World") // "Hello+World"
VALUE
Converts a text string that represents a number to a number.
Accepts 1 argument: the text to convert.
VALUE("123") // 123