Function Reference (Managed Package)

&&

AND

True if all of the joined expressions are true. For example, A AND B returns true if both A and B are true.

||

OR

True if any of the joined expressions are true. For example, A AND B returns true if either A or B is true.

>

True if the first expression has a greater value than the second. For example, 3 > 2 returns true.

>=

True if the first expression has a greater value than the second or if the values are equal. For example, both 3 >= 2 and 2 >= 2 return true.

<

True if the second expression has a greater value than the first. For example, 2 < 3 returns true.

<=

True if the second expression has a greater value than the first or if the values are equal. For example, both 2 <= 3 and 2 <= 2 return true.

!=

<>

True if the values of the first and second expressions aren’t equal. For example, 3 != 2 returns true.

==

True if the values of the first and second expressions are equal. For example, 2 == 2 returns true.

=

Assigns the value of the second expression to the first expression. For example, A = 2 sets the value of A to 2.

LIKE

True if the left-hand side contains the right-hand side. For example, "ABC" LIKE "A" returns true.

NOTLIKE

True if the left-hand side doesn’t contain the right-hand side. For example, "ABC" NOTLIKE "A" returns false.

~=

Performs a case insensitive comparison. For example, "ABC" ~= "abc" returns true. Introduced in Summer '20.

+

Adds two numbers.

-

Subtracts the second number from the first.

*

Multiplies two numbers.

/

Divides the first number by the second.

%

Returns the percentage of the first number specified by the second number. For example, 50 % 20 returns 10.

^

Returns the power of the first number specified by the second number. For example, 2 ^ 3 returns 8.

ABS(number)

Absolute value of the specified number

Data type: Number

ABS(-1) returns 1

ADDDAY(date, days)

Adds the specified number of days to the specified date and returns the resulting date.

Data type: Datetime

ADDDAY("1999-01-01",100) returns "1999-04-11 00:00:00"

ADDMONTH(date, months)

Adds the specified number of months to the specified date and returns the resulting date.

Data type: Datetime

ADDMONTH("1999-01-01",100) returns "2007-05-01 00:00:00"

ADDYEAR(date, years)

Adds the specified number of years to the specified date and returns the resulting date.

Data type: Datetime

ADDYEAR("1999-01-01",100) returns "2099-01-01 00:00:00"

AGE(birthdate)

Returns the age for the specified birthdate.

Data type: Number

AGE(Account:Contact:Birthdate)

AGEON(date)

For a specified birthdate, returns the age on the specified date

Data type: Number

AGEON(Account:Contact:Birthdate,TODAY()) (same as AGE).

AVG(list)

Average of list of values.

Data type: Number

AVG(1,2,3,4,5,6,7,8,9,10) returns 5.5

You can use AVG on a JSON array. For example, AVG(List:Item) returns 4 for the following sample data:

{
  "List": [ 
    {
      "Item": 3
    },
    {
      "Item": 4
    },
    {
      "Item": 5
    }
  ]
}	

BASE64ENCODE(String)

Returns the input string with base 64 encoding applied.

Data type: String

BASE64ENCODE(Encode this string) returns RW5jb2RlIHRoaXMgdGV4dA==

BASEURL()

Returns the base URL of the Org. A parameter isn’t required.

Data type: String

BASEURL()

CEILING

Use with ROUND to specify that the rounding result is rounded up (away from zero) to the nearest integer.

Data type: Number

ROUND(2.572, 0, CEILING) returns 3

CONCAT(String1, String2, ...)

Returns the concatenation of the specified strings.

Data type: String

CONCAT(Contact:FirstName, " ", Contact:LastName)

COUNTQUERY(query)

Returns the number of rows that meet the WHERE clause criteria.

Data type: Number

COUNTQUERY ( "SELECT COUNT() FROM Case WHERE AccountId = '{0}'" , Id )

DATEDIFF(date, date)

Returns the number of days between two specified dates. If the first date is greater than the second date, the value is returned as a negative number.

Data type: Number

DATEDIFF("1900-01-01","2000-01-01") returns 36524

DATEDIFF("2000-01-01","1999-01-01") returns -365

DATEDIFF(Account:Cases:CreatedDate,TODAY()) returns the number of days a case has been open

DATETIMETOUNIX(datetime)

Converts a DateTime value to epoch (the number of milliseconds elapsed since 1/1/1970).

Data type: Number

DATETIMETOUNIX('11/30/2016 07:15:34') returns 1480490134000

DESERIALIZE(jsonStr)

Converts a JSON string into a JSON object based on the specified key. If the data is in list format, use the DESERIALIZE function inside the LIST function.

Available in Winter '20 and later releases.

Data type: JSON object

DESERIALIZE("{"key":"value"}")

returns { "key": "value" }

LIST(DESERIALIZE("[{"key":"value"},{"key2":"value2"}]"))

returns [ { "key": "value" }, { "key2": "value2" } ]

DOWN

Use with ROUND to specify that the rounding result is rounded down (towards zero).

Data type: Number

ROUND(2.572, 2, DOWN) returns 2.57

EOM(date)

For a specified date, returns the date of the last day of the month.

Data type: Datetime

EOM(Account:Cases:CreatedDate)

FILTER(searchList, condition)

Filter a list of JSON objects and return the subset that matches the specified conditions.

The last two examples show how to filter based on a variable rather than a hard-coded value.

Data type: JSON list

FILTER(LIST(InputList), 'LastName == "Smith"')

FILTER(LIST(Names), 'Name == "' + FindName + '"')

FILTER(LIST(data(PromotionProducts), 'productCode == "' + ConnectionProdCode + '"')

FLOOR

Use with ROUND to specify that the rounding result is rounded down (towards zero) to the nearest integer.

Data type: Number

ROUND(2.572, 0, FLOOR) returns 2

FORMATDATETIME(datetime, format, timezone)

Accepts a date or datetime SObject field or a datetime string and returns a formatted datetime string. A string input must be in one of these formats:

• 2019-02-02T00:00:00

• 2018-12-30T00:00:00.000000Z

You can supply an optional output formatting parameter using SimpleDateFormat notation.

The time zone is GMT, so specifying GMT times is recommended. These times are converted to the optional specified time zone. If no time zone is specified, the default is the user's time zone.

For a list of time zone names, see the List of tz database time zones Wikipedia page.

The default time is 00:00:00 GMT. The default date is 01-01-1970, so specifying a complete date is recommended.

Data type: String

FORMATDATETIME("2019-02-02T00:00:00")

FORMATDATETIME("2018-12-30T00:00:00.000000Z","yyyy-MM-dd'T'HH:mm:ss.SSSZ","America/New_York")

FORMATDATETIMEGMT(datetime, timezone, format)

Formats datetime data as FORMATDATETIME does, but also converts from the specified time zone to GMT.

If no optional time zone name parameter is specified, the default is the current user’s time zone. For a list of time zone names, see the List of tz database time zones Wikipedia page.

If no optional output format parameter is specified, the default output format is yyyy-MM-dd'T'HH:mm:ss.SSSZ.

Data type: String

FORMATDATETIMEGMT("2018-12-30T00:00:00.000000Z","America/New_York","yyyy-MM-dd'T'HH:mm:ss.SSSZ")

FUNCTION('class', 'method', input)

Executes a custom function defined in the method of an Apex class.

If the input includes a LIST function, the LIST part of the input is saved to a VLOCITY-FORMULA-LIST key.

Available in Vlocity Health and Insurance Spring '21 and later releases.

Data type: depends on function

FUNCTION('Statistics', 'calcStdDev', DataPoints)

HALF_DOWN

Use with ROUND to specify that the rounding result is rounded towards the nearest neighbor (up or down) unless the neighbors are equidistant, in which case it rounds down.

Data type: Number

ROUND(2.575, 2, HALF_DOWN) returns 2.57

HALF_EVEN

Use with ROUND to specify that the rounding result is rounded towards the nearest neighbor (up or down) unless the neighbors are equidistant, in which case it rounds to the nearest even neighbor.

Behaves as for ROUND HALF_UP if the digit to the left of the discarded fraction is odd; behaves as for ROUND HALF_DOWN if it is even.

Data type: Number

ROUND(2.57, 1, HALF_EVEN) returns 2.6, which is rounded up to the nearest even one-decimal place.

HALF_UP

Use with ROUND to specify that the rounding result is rounded towards the nearest neighbor (up or down) unless the neighbors are equidistant, in which case it rounds up.

Data type: Number

ROUND(2.575, 2, HALF_UP) returns 2.58

GENERATEGLOBALKEY(prefix)

Generates a global key that can be used as an Id in a DataPack. If you include an optional parameter, it’s prefixed to the key.

Data type: String

GENERATEGLOBALKEY(SUB)

Returns a value such as SUB-4c6aaba3-88e4-13f3-fe07-afa756f31e92

IF(expression, trueResult, falseResult)

Evaluates the expression. If true, returns the first result value, if false, returns the second.

You can use IF to filter a list. The second example filters an input list of phones to an output list of only Apple phones.

The third example removes an initial dollar sign if it’s present, leaving a number that can be used in calculations.

The fourth example demonstrates the nesting of IF functions. Drivers under 25 qualify for a discount if they’re good students, and drivers over 25 qualify if they refer two or more friends.

The fifth example shows a workaround to avoid dividing by zero when you calculate a reciprocal.

Data type: depends on trueResult and falseResult

IF(InputDate < "2000-01-01", "20th Century", "21st Century")

IF(phones:brand == "Apple", phones:name, null)

IF(SUBSTRING(price,0,1) == "$", SUBSTRING(price,1), price)

IF(DriverAge >= 25,IF(FriendsReferred >= 2, true, false),IF(GPA >= 3.0, true, false))

IF(amount > 0, 1/IF(amount > 0, amount, 1), 0)

IF(%Color% = "Red","Black",%Color%)

InvokeIP ('IP_Name', INPUT('Key', Value), INPUT('Key', Value)..., 'OutputKey')

Calls an Integration Procedure for formulas with complex use cases.

Note that the InvokeIP formula function is only supported in the insurance package (vlocity_ins package).

Takes the 'IP_Name' in the format Type_SubType.

Takes one or more INPUT('Key',Value) pairs as inputs to the Integration Procedure.

The 'OutputKey' is the IP Response Action output JSON key to get the resulting value.

Data type: JSON key

InvokeIP ('Auto_Renewal', INPUT('assetId', 123456), 'AnOutPutKey')

ISBLANK(expression)

Returns true if the argument is blank, false if it isn’t.

Data type: Boolean

ISBLANK('Marty') returns false

ISNOTBLANK(expression)

Returns true if the argument isn’t blank, false if it is.

Data type: Boolean

ISNOTBLANK('Marty') returns true

LIST(expression)

Encapsulates the argument as a list, for calling functions that require input in list format.

Data type: JSON list

LISTMERGE("merge_key1"[,..."mergekeyN"], LIST(List1), ...LIST(ListN))

Merges any number of lists, combining entries when the values of the specified key match . Specify the keys as a comma-separated list of quoted key names, and specify lists using the LIST function. If lists contain identically-named nodes that contain different values, subsequent values overwrite earlier values in the result list. The output contains all the keys from all the lists.

Data type: JSON list

LISTMERGE("Id", LIST(CurrentAccounts), LIST(ArchivedAccounts), LIST(ProspectiveAccounts))

LISTMERGEPRIMARY("merge_key1"[,..."mergekeyN"], LIST(List1), ...LIST(ListN))

Identical to LISTMERGE, except that the output list contains only the keys from the first list. Useful, for example, when you have a list of qualified products that you want to augment with data from related lists, but you do not want any products from the related lists added to the output.

Data type: JSON list

LISTMERGEPRIMARY("FirstName,LastName", LIST(ListMerge1), LIST(ListMerge2), LIST(ListMerge3))

LISTSIZE(list)

Returns the number of items in the list.

To test for an empty list, use the ISBLANK function.

Data type: Number

LIST(6,7,8,9,10) returns 5

IF(ISBLANK(list),0,LISTSIZE(list)) tests for a blank list and then returns the size

LISTSIZE(QUERY()) returns 1 if QUERY() returns no results. To return 0 in this case, use two expressions in separate Set Values actions or Data Mapper functions, such as:

QUERY("SELECT Name FROM Contact WHERE LastName LIKE '{0}'",%InputName%)
IF(ISBLANK(QueryResult),0,LISTSIZE(QueryResult))

You can use LISTSIZE on a JSON array. For example, LISTSIZE(NameList) returns 3 for the following sample data:

{
  "NameList": [ 
    {
      "FirstName": "Aaron",
      "LastName": "Xavier"
    },
    {
      "FirstName": "Zellie",
      "LastName": "Xavier"
    },
    {
      "FirstName": "Mike",
      "LastName": "Smith"
    }
  ]
}						

MAX(list)

Maximum value in a list of values.

In Vlocity Health and Insurance Spring '21 or later, this function no longer works for Strings.

Data type: Number

MAX(1,2,3,4,5,6,7,8,9,10) returns 10

You can use MAX on a JSON array. For example, MAX(List:Item) returns 5 for the following sample data:

{
  "List": [ 
    {
      "Item": 3
    },
    {
      "Item": 4
    },
    {
      "Item": 5
    }
  ]
}

MAXSTRING(String1, String2, ...)

Returns the last string alphabetically.

Available in Vlocity Health and Insurance Spring '21 and later releases.

Data type: String

MAXSTRING("Amy","Ziggy") returns "Ziggy"

MIN(list)

Returns the lowest in a list of values

Data type: Number

MIN(1,2,3,4,5,6,7,8,9,10) returns 1

You can use MIN on a JSON array. For example, AVG(List:Item) returns 3 for the following sample data:

{
  "List": [ 
    {
      "Item": 3
    },
    {
      "Item": 4
    },
    {
      "Item": 5
    }
  ]
}

MONTH(date)

Returns the month of the specified date as an integer.

Data type: Number

MONTH("1999-01-11") returns 1

NOW(format)

Returns the current date and time. You can supply an optional formatting parameter using SimpleDateFormat notation.

You can use NOW to specify dates and times relative to the current date and time.

The $Vlocity.NOW environment variable applies the user or org time zone, while the NOW() function applies UTC time.

Data type: Datetime

NOW()

NOW("yyyy-MM-dd'T'HH:mm:ss.SSSZ")

ORDERITEMATTRIBUTES(Orderitemid)

Returns the attributes of the order item in key-value pairs. OrderItemId must be specified as a parameter.

Output Format:

List<Map<String,Object>>

ORDERITEMATTRIBUTES(80146000002I7dO)

QUERY(query)

Executes a SOQL query that returns a JSON list of values. The query can’t retrieve more than one column.

Data type: JSON list

QUERY ("SELECT Name FROM Account WHERE BillingState ='CA'")

You can also pass in values. For example:

QUERY( "SELECT AccountId FROM User WHERE Id = '{0}'",$Vlocity.UserId )

LISTSIZE(QUERY()) returns 1 if QUERY() returns no results. To return 0 in this case, use two expressions in separate Set Values actions or Data Mapper functions, such as:

QUERY("SELECT Name FROM Contact WHERE LastName LIKE '{0}'",%InputName%)
IF(ISBLANK(QueryResult),0,LISTSIZE(QueryResult))

RESERIALIZE(jsonStr)

Equivalent to SERIALIZE(DESERIALIZE()). Converts JSON string into a generic Map<String, Object> format based on the specified key. Useful in Remote Actions for converting Apex class output so that Integration Procedures and Data Mappers can accept it.

Available in Winter '20 and later releases.

Output Format:

Map<String, Object>

See Remote Action for Integration Procedures (Managed Package).

ROUND(expression, precision, direction)

Rounds the specified number or expression. By default, results are rounded to two decimal places, but you can specify an optional precision for the result.

You can also use the optional UP, DOWN, HALF_UP, HALF_DOWN, HALF_EVEN, CEILING, and FLOOR direction parameters to refine the rounding results.

Data type: Number

ROUND(3.1415 * 3) = 9.42

ROUND(3.1415 * 3, 0) = 9

SERIALIZE(jsonStr)

Converts a JSON string into a JSON object based on the specified key. If the data is in list format, use the LIST function inside the SERIALIZE function. Note how it differs from TOSTRING.

Available in Winter '20 and later releases.

Data type: String

SERIALIZE({ "key": "value" })

returns "{"key":"value"}"

SERIALIZE(LIST([ { "key": "value" }, { "key2": "value2" } ]))

returns "[{"key":"value"},{"key2":"value2"}]"

SORTBY(LIST(InputList), 'key1', 'key2', ... , '[:DSC]')

Sorts a list of JSON objects by specified nodes. By default, the sort is ascending. For a descending sort, specify the optional [:DSC] parameter. Supported only for Data Mapper Transforms.

Data type: JSON list

SORTBY(LIST(NameList), 'LastName', 'FirstName')

SORTBY(LIST(NameList), 'LastName', 'FirstName', '[:DSC]')

Sample data:

{
  "NameList": [ 
    {
      "FirstName": "Aaron",
      "LastName": "Xavier"
    },
    {
      "FirstName": "Zellie",
      "LastName": "Xavier"
    },
    {
      "FirstName": "Mike",
      "LastName": "Smith"
    }
  ]
}						

SQRT(number)

Calculates the square root of a value.

Data type: Number

SQRT(12 * 3) returns 6

STRINGINDEXOF(String, substring)

Returns the position index of a substring in the given String. The first position is zero. If the substring isn’t present, the value returned is -1.

Data type: Number

STRINGINDEXOF("This is the test String","test") returns 12

SUBSTRING(String, startIndex, endIndex)

Returns the portion of the String that begins at the startIndex and ends at the endIndex. The optional startIndex and endIndex can be Strings or integers. For integers, the first position is zero. If the startIndex isn’t found, the first position is used. If the endIndex isn’t found, the last position is used.

Data type: String

The following examples operate on this input:

{ "input": "The quick brown fox jumped over the lazy dog." }

SUBSTRING(input) returns the entire String

SUBSTRING(input,32) returns "the lazy dog."

SUBSTRING(input,"lazy") returns "lazy dog."

SUBSTRING(input,0,9) returns "The quick"

SUBSTRING(input,10,19) returns "brown fox"

SUBSTRING(input,"q","n") returns "quick brow"

SUM(list)

Returns the sum of a list of values.

Data type: Number

SUM(1,2,3,4,5,6,7,8,9,10) returns 55

You can use SUM on a JSON array. For example, AVG(List:Item) returns 12 for the following sample data:

{
  "List": [ 
    {
      "Item": 3
    },
    {
      "Item": 4
    },
    {
      "Item": 5
    }
  ]
}

TODAY(format)

Returns today's date. You can supply an optional formatting parameter using SimpleDateFormat notation.

You can use TODAY to specify dates relative to the current date.

Data type: Datetime

TODAY()

TODAY("yyyy-MM-dd'T'HH:mm:ss.SSSZ")

ADDDAY(EOM(ADDMONTH(TODAY(),-1)),1) returns the first day of the current month

ADDDAY(EOM(ADDMONTH(TODAY(),0)),1) returns the first day of next month

CONCAT(YEAR(ADDYEAR(TODAY(),1)),"-01-01") returns the first day of next year

TOSTRING(data)

Converts any input to a literal String. Note how it differs from SERIALIZE in the way it converts JSON data.

Available in Winter '20 and later releases.

Data type: String

The function shows up to 10 items per collection in the output, followed by an ellipsis (…).

TOSTRING(3.0) returns "3.0"

TOSTRING([4,5]) returns "4,5"

TOSTRING({ "key": "value" }) returns "{key=value}"

TOSTRING([ { "key": "value" }, { "key2": "value2" } ])

returns "{key=value}, {key2=value2}"

UNIXTODATETIME(epoch)

Given a Unix epoch value, returns the corresponding DateTime.

Data type: Datetime

UNIXTODATETIME('1480490134000')

returns "2016-11-30T07:15:34.000Z"

UP

Use with ROUND to specify that the rounding result is rounded up (away from zero).

Data type: Number

ROUND(2.572, 2, UP) returns 2.58

VALUELOOKUP(StartNode, NodeVar, NodeVar ,,,)

Returns the value of a JSON node referenced by another JSON node. This function lets you dynamically specify the node to retrieve from.

The StartNode parameter must be a hard-coded node. It can be a path such as Data:Name.

Each NodeVar parameter must be an input node with the name of another node as its value.

If more than one NodeVar is specified, the previous NodeVar defines the starting node for the next NodeVar. You can retrieve any number of levels.

Data type: String

For the following sample input data:

{
  "Data": {
    "Name": {
      "FirstName": "Thomas",
      "MiddleName": "Alva",
      "LastName": "Edison"
    }
  },
  "GetGroup": "Name",
  "GetField": "FirstName"
}

VALUELOOKUP(Data, GetGroup, GetField) returns "Thomas"

VALUELOOKUP(Data:Name, GetField) also returns "Thomas"

VALUELOOKUP(Data, GetGroup) returns the contents of Name

YEAR(date)

Returns the year of the specified date as an integer.

Data type: Number

YEAR("1999-01-11") returns 1999