Formula Functions

This page documents the list of functions that are supported in Formula Engine. Each of the functions provides examples of how that function can be used. Those examples can be inline edited to try out variations. You also try out any of these functions in Formula Playground.

Go through the Video Guide below for a short introduction on how to read this page. If you think any other formula is helpful, please let us know at support@datasert.com

info

All string functions are case-insensitive
While most of the functions are null tolerant, if there is a ? after parameter name, then it means that function is designed to handle the absence of that parameter
If there is a ... before a parameter name, it means you can specify multiple parameters separated by a comma

abbr

abbr(input, length)

Abbreviates the input to length chars.

If input is null, then returns as is
If input is string, then abbreviates the input. If string length is same or shorter than length then returns the input as is.
If input is longer than length, then truncates to length with ... appended.

Expression	Result
`abbr('Boing Inc', 5)`✏️	`Bo...`
`abbr('Boing Inc', 10)`✏️	`Boing Inc`

abs

abs(input)

Returns the absolute value of the input

If input is null, then returns the input as is
If input is a -ve number, then returns +ve of that number
If input is a +ve number, then returns the number as is
If input is not number, then return the input as is

Expression	Result
`abs(-10)`✏️	`10`
`abs(-10.45)`✏️	`10.45`
`abs(10)`✏️	`10`
`abs(10.45)`✏️	`10.45`
`abs(null)`✏️	`<null>`
`abs('John')`✏️	`John`

add_date

add_date(input: string, amount: number, unit: string)

Adds amount of date unit to given input

Valid unit values are years, quarters, months, weeks, days, hours, minutes, seconds, and milliseconds as well as its singular variants
amount can be -ve to subtract from the given input date
Input date must be in iso datetime or date format
Returns iso formatted datetime. If you want date formatted string, use format_date function

Expression	Result
`add_date('2020-01-01', 1, 'year')`✏️	`2021-01-01T00:00:00Z`
`add_date('2020-01-01', 4, 'month')`✏️	`2020-05-01T00:00:00Z`
`add_date('2020-01-01', 4, 'day')`✏️	`2020-01-05T00:00:00Z`
`add_date('2020-01-05T09:00:00', 4, 'hour')`✏️	`2020-01-05T13:00:00Z`
`add_date('2020-01-05T09:50:00', 10, 'minute')`✏️	`2020-01-05T10:00:00Z`
`add_date('2020-01-05T09:59:55', 10, 'second')`✏️	`2020-01-05T10:00:05Z`
`add_date('2020-01-05T09:09:55', 120, 'second')`✏️	`2020-01-05T09:11:55Z`

avg

avg(...inputs)

Calculates the average of given inputs.

If any of the inputs are null or not-number, then it is treated as 0.

Expression	Result
`avg(10)`✏️	`10`
`avg(1, 2, 3)`✏️	`2`
`avg(10, 'John')`✏️	`5`
`avg(null)`✏️	`0`
`avg('John')`✏️	`0`

blank_if_null

If value is blank, returns null else returns value as is.

Expression	Result
`blank_if_null(null)`✏️	`<blank>`
`blank_if_null('')`✏️	`<blank>`
`blank_if_null('abc')`✏️	`abc`

blank_if_zero

If input is blank, then returns 0

Expression	Result
`blank_if_zero(0)`✏️	`<blank>`
`blank_if_zero('John')`✏️	`John`
`blank_if_zero(['','8','7'])`✏️	`["","8","7"]`

capital_case

capital_case(input)

Converts the first char of input to capital case and rest of the chars to lower case.

If input is null, then returns it as is.
If input is not string, then returns it as is.

Expression	Result
`capital_case(null)`✏️	`<null>`
`capital_case('John')`✏️	`John`
`capital_case('John MALANA')`✏️	`John malana`
`capital_case('john manalan')`✏️	`John manalan`
`capital_case(10)`✏️	`10`
`capital_case([1, 2])`✏️	`[1,2]`
`capital_case({})`✏️	`{}`

compare

compares two values and returns -1, 0 or 1 depending on if first value is less than, equal or greater than second number

Expression	Result
`compare(0,5)`✏️	`-1`
`compare(4,5)`✏️	`-1`
`compare(10,1)`✏️	`1`
`compare(10,9)`✏️	`1`
`compare('abc','abcd')`✏️	`-1`

concat_array

Takes one or more inputs and returns single array consisting of all inputs. If any of the input is array, then items of that array are included, not array itself.

contains

Checks if second value contains within the first value

Expression	Result
`contains('James Smith', 'Smit')`✏️	`true`
`contains('James Smith', 'Smith')`✏️	`true`
`contains('James Smith', 'Smitha')`✏️	`false`

dedupe

Dedupes the given array. If second parameter is specified, it can be string representing the field of included items or function which takes array item and returns the string to dedupe by.

diff_date

diff_date(inputDate: string, compareDate: string | null, unit: DateUnit): int

Compares the inputDate to compareDate and returns the difference between two given dates in whole integer values.

If inputDate is null then returns null
If compareDate is null, then uses the current datetime
If inputDate earlier than compareDate, then returns -ve integer value
If inputDate later than compareDate, then returns +ve integer value
If unit is null, then defaults to days
Valid unit values are
- years
- months
- days
- hours
- minutes
- seconds
- milliseconds

ends_with

ends_with(input, suffixes): boolean

Checks if input ends with any of the suffixes (case-insensitive) and returns true, otherwise returns false

suffixes could be single suffix or multiple suffixes. Multiple suffixes must be specified as an array of string.
If input is blank or suffixes is blank then returns false
Both input and suffixes are converted to string before checking

Expression	Result
`ends_with('James Smith', 'Smith')`✏️	`true`
`ends_with('James Smith', 'Smitha')`✏️	`false`
`ends_with('James Smith', ['t','h'])`✏️	`true`
`ends_with('James Smith', ['t','a'])`✏️	`false`
`ends_with('James Smith', 'h')`✏️	`true`
`ends_with(445, 5)`✏️	`true`
`ends_with(445, 3)`✏️	`false`
`ends_with(1234, 4)`✏️	`true`

ensure_prefix

ensure_prefix(value, prefix)

Ensures that given text has a specified prefix. If value does not start with given prefix, then prefix will be added else value is returned as is.

If input is an Array, then it operates on each element of Array.
Prefix check is case-insensitive

Expression	Result	Description
`ensure_prefix('123', 'ACCT-')`✏️	`ACCT-123`
`ensure_prefix('ACCT-123', 'ACCT-')`✏️	`ACCT-123`
`ensure_prefix('acct-123', 'ACCT-')`✏️	`ACCT-123`	If value starts with same prefix but different case, then prefix will be changed to specified case

ensure_suffix

ensure_suffix(value: any | any[], suffix: string): any | any[]

Ensures that given text has specified suffix. If value does not end with given suffix, then suffix will be added else value is returned as is.

If input is an Array, then it operates on each element of Array.
Suffix check is case In-sensitive

Expression	Result
`ensure_suffix('Boing', 'Inc')`✏️	`BoingInc`
`ensure_suffix('Boing Inc', 'Inc')`✏️	`Boing Inc`
`ensure_suffix('Boing inc', 'Inc')`✏️	`Boing Inc`

error

error(message)

Throws error with the given message. This function can be used in field or row mapping to mark record as an error. If Job is configured to treat Record errors as Error, then Job run will get Errored.

Expression	Result
`error('Simulating an error')`✏️	`Error: Simulating an error`

fill_array

fill_array(input: any[], fillValueOrFunction: (indexValue, index, array) => any)

Fills input array using value or function return value of fillValueOrFunction. If full function is not-function, then that value is used as is. Other wise fill function is called as many times as array length with three arguments viz

value at index, 2) index 3) array itself

If input or fillValueOrFunction is null then input is returned as is

Expression	Result
`fill_array(null)`✏️	`[]`

first

first(input, length)

If input is null, then returns the input as is.
If input is string, returns the first length chars of that string. If input is shorter than length, then input is returned as is.
If input is array, returns the first length elements of that array as new array. If input array length is shorter than length, then returns the input as is.

Expression	Result
`first('Mary John',4)`✏️	`Mary`
`first('Mary',3)`✏️	`Mar`
`first('Mary',5)`✏️	`Mary`

first_not_blank

From series of values, returns the first not blank value. If none of them are non-blank, then returns null

Expression	Result
`first_not_blank('Mary John','','John','Raj','','Mary')`✏️	`Mary John`
`first_not_blank('','John','Raj','','Mary')`✏️	`John`
`first_not_blank('','','')`✏️	`<null>`

first_not_null

From series of values, returns the first not null value. If none of them are non-null, then returns null

first_not_zero

From series of inputs, returns the first not zero value. If none of them are non-zero, then returns null

Expression	Result
`first_not_zero('','','','Raj','')`✏️	`<blank>`
`first_not_zero('0','0','0','0')`✏️	`0`
`first_not_zero('0','4','0','0')`✏️	`4`
`first_not_zero('3','4','0','0')`✏️	`3`
`first_not_zero('3','4','2','1')`✏️	`3`

flatten

flatten(input: T | T[], depth: number | undefined): T[]

Returns a new array with all input array elements concatenated into it single array recursively up to the specified depth, or infinite if depth not specified.

If input is null then returns empty array []
If there are null elements in the array, returns them in the array as is. If you want to remove those null elements, use filter_nulls method

Expression	Result
`flatten([['abc'], ['xyz']])`✏️	`["abc","xyz"]`
`flatten([['abc', ['abc2']], ['xyz', ['xyz2', ['xyz3']]]])`✏️	`["abc","abc2","xyz","xyz2","xyz3"]`
`flatten([['abc', ['abc2']], ['xyz', ['xyz2', ['xyz3']]]], 2)`✏️	`["abc","abc2","xyz","xyz2",["xyz3"]]`
`flatten([['abc'], ['xyz', null]])`✏️	`["abc","xyz",null]`
`flatten(null)`✏️	`[]`
`flatten([])`✏️	`[]`
`flatten('abc')`✏️	`["abc"]`

format_date

format_date(input, pattern, timeZone?)

Formats input date according to pattern in timeZone. This function uses Luxon formatting tokens.

If input is blank, then returns input without formatting
pattern can be either pre-defined string or series of formatting tokens. See below for list of supported pre-defiend formats as well as formatting tokens.
If pattern is blank, then it returns iso datetime string
If timeZone is blank, then defaults to UTC timeZone

Predefined Formats Examples below given for utc datetime 1983-10-14T13:07:04.054Z formatted with specified string in America/New_York timezone. For ex., format_date('1983-10-14T13:07:04.054Z', 'datetime', 'America/New_York')

Expression	Result
`datetime`✏️	`1983-10-14T09:07:04.054-04:00`
`date`✏️	`1983-10-14`
`ms`✏️	`434984824054`
`secs`✏️	`434984824`
`date_short`✏️	`10/14/1983`
`date_med`✏️	`Oct 14, 1983`
`date_full`✏️	`October 14, 1983`
`date_huge`✏️	`Friday, October 14, 1983`
`time_simple`✏️	`9:07 AM`
`time_with_seconds`✏️	`9:07:04 AM`
`time_with_short_offset`✏️	`9:07:04 AM EDT`
`time_with_long_offset`✏️	`9:07:04 AM Eastern Daylight Time`
`time_24_simple`✏️	`09:07`
`time_24_with_seconds`✏️	`09:07:04`
`time_24_with_short_offset`✏️	`09:07:04 EDT`
`time_24_with_long_offset`✏️	`09:07:04 Eastern Daylight Time`
`datetime_short`✏️	`10/14/1983, 9:07 AM`
`datetime_med`✏️	`Oct 14, 1983, 9:07 AM`
`datetime_full`✏️	`October 14, 1983 at 9:07 AM EDT`
`datetime_huge`✏️	`Friday, October 14, 1983 at 9:07 AM Eastern Daylight Time`
`datetime_short_with_seconds`✏️	`10/14/1983, 9:07:04 AM`
`datetime_med_with_seconds`✏️	`Oct 14, 1983, 9:07:04 AM`
`datetime_full_with_seconds`✏️	`October 14, 1983 at 9:07:04 AM EDT`
`datetime_huge_with_seconds`✏️	`Friday, October 14, 1983 at 9:07:04 AM Eastern Daylight Time`

Formatting Tokens

Examples below given for 2014-08-06T13:07:04.054 considered as a local time in America/New_York

Token	Description	Example
`S`	millisecond, no padding	`54`
`SSS`	millisecond, padded to 3	`054`
`u`	fractional seconds, functionally identical to SSS	`054`
`s`	second, no padding	`4`
`ss`	second, padded to 2 padding	`04`
`m`	minute, no padding	`7`
`mm`	minute, padded to 2	`07`
`h`	hour in 12-hour time, no padding	`1`
`hh`	hour in 12-hour time, padded to 2	`01`
`H`	hour in 24-hour time, no padding	`9`
`HH`	hour in 24-hour time, padded to 2	`13`
`Z`	narrow offset	`+5`
`ZZ`	short offset	`+05:00`
`ZZZ`	techie offset	`+0500`
`ZZZZ`	abbreviated named offset	`EST`
`ZZZZZ`	unabbreviated named offset	`Eastern Standard Time`
`z`	IANA zone	`America/New_York`
`a`	meridiem	`AM`
`d`	day of the month, no padding	`6`
`dd`	day of the month, padded to 2	`06`
`E`	day of the week, as number from 1-7 (Monday is 1, Sunday is 7)	`3`
`EEE`	day of the week, as an abbreviate localized string	`Wed`
`EEEE`	day of the week, as an unabbreviated localized string	`Wednesday`
`EEEEE`	day of the week, as a single localized letter	`W`
`M`	month as an unpadded number	`8`
`MM`	month as an padded number	`08`
`MMM`	month as an abbreviated localized string	`Aug`
`MMMM`	month as an unabbreviated localized string	`August`
`MMMMM`	month as a single localized letter	`A`
`y`	year, unpadded	`2014`
`yy`	two-digit year	`14`
`yyyy`	four- to six- digit year, pads to 4	`2014`
`G`	abbreviated localized era	`AD`
`GG`	unabbreviated localized era	`Anno Domini`
`GGGGG`	one-letter localized era	`A`
`kk`	ISO week year, unpadded	`14`
`kkkk`	ISO week year, padded to 4	`2014`
`W`	ISO week number, unpadded	`32`
`WW`	ISO week number, padded to 2	`32`
`o`	ordinal (day of year), unpadded	`218`
`ooo`	ordinal (day of year), padded to 3	`218`
`q`	quarter, no padding	`3`
`qq`	quarter, padded to 2	`03`
`D`	localized numeric date	`9/4/2017`
`DD`	localized date with abbreviated month	`Aug 6, 2014`
`DDD`	localized date with full month	`August 6, 2014`
`DDDD`	localized date with full month and weekday	`Wednesday, August 6, 2014`
`t`	localized time	`9:07 AM`
`tt`	localized time with seconds	`1:07:04 PM`
`ttt`	localized time with seconds and abbreviated offset	`1:07:04 PM EDT`
`tttt`	localized time with seconds and full offset	`1:07:04 PM Eastern Daylight Time`
`T`	localized 24-hour time	`13:07`
`TT`	localized 24-hour time with seconds	`13:07:04`
`TTT`	localized 24-hour time with seconds and abbreviated offset	`13:07:04 EDT`
`TTTT`	localized 24-hour time with seconds and full offset	`13:07:04 Eastern Daylight Time`
`f`	short localized date and time	`8/6/2014, 1:07 PM`
`ff`	less short localized date and time	`Aug 6, 2014, 1:07 PM`
`fff`	verbose localized date and time	`August 6, 2014, 1:07 PM EDT`
`ffff`	extra verbose localized date and time	`Wednesday, August 6, 2014, 1:07 PM Eastern Daylight Time`
`F`	short localized date and time with seconds	`8/6/2014, 1:07:04 PM`
`FF`	less short localized date and time with seconds	`Aug 6, 2014, 1:07:04 PM`
`FFF`	verbose localized date and time with seconds	`August 6, 2014, 1:07:04 PM EDT`
`FFFF`	extra verbose localized date and time with seconds	`Wednesday, August 6, 2014, 1:07:04 PM Eastern Daylight Time`
`X`	unix timestamp in seconds	`1407287224`
`x`	unix timestamp in milliseconds	`1407287224054`

Examples

Expression	Result
`format_date('2020-01-01', 'yy-MMM')`✏️	`20-Jan`
`format_date('2020-01-01T00:00:00Z', 'yyyy-MM-dd')`✏️	`2020-01-01`
`format_date('2020-01-01T00:00:00Z', 'yyyy-MM-dd hh:mm:ss a', 'America/Los_Angeles')`✏️	`2019-12-31 04:00:00 PM`
`format_date('2020-01-02T03:04:05Z', 'HH:mm:ss a', 'America/Los_Angeles')`✏️	`19:04:05 PM`

geo_distance

Calculates the crow-fly distance between two lat/lon. Note that is based on the road conditions or real-world infrastructure.

get_value

Returns the value from current context. Depending on where/when formula is being executed, the list of available fields varies. Consult product docs for more info on available fields.

If a field is not available, then returns null

group

group(input: T[], keyProvider: string | (it: T) => string)

Groups given input array by specified keyProvider and returns array containing two values key and items.

If keyProvider is string, then that field of given object will be used as Key
If keyProvider is function, then that function will be called for each input element and string returned from function will be used as Key
Keys are grouped case-insensitively. the returned key will be first key found if there are multiple keys with different cases.

Expression	Result
`group([{state: 'KA', city: 'Bangalore'}, {state: 'KA', city: 'Davangere'}, {state: 'AP', city: 'Vizaq'}], (it) => it.state)`✏️	`[{"key":"AP","items":[{"state":"AP","city":"Vizaq"}]},{"key":"KA","items":[{"state":"KA","city":"Bangalore"},{"state":"KA","city":"Davangere"}]}]`
`group(['KA', 'KA', 'AP'], (it) => it)`✏️	`[{"key":"AP","items":["AP"]},{"key":"KA","items":["KA","KA"]}]`

parse_number

parse_number(input: string, format?: 'us' | 'eu'): number

Prases given string and tries to extract number according to given format. If format is not given then tries to understand the format automatically depending on number of comma/decimal chars in the input. If there is single comma, then format is assumed as us

Expression	Result
`parse_number('')`✏️	`<null>`
`parse_number('1.0')`✏️	`1`
`parse_number('1,05')`✏️	`105`
`parse_number('1,495')`✏️	`1495`
`parse_number('USD1,495')`✏️	`1495`
`parse_number('-USD1,495')`✏️	`-1495`
`parse_number('(USD1,495')`✏️	`1495`
`parse_number('usd -1,495')`✏️	`-1495`
`parse_number('(usd 1,495')`✏️	`1495`
`parse_number('$1,495')`✏️	`1495`
`parse_number('-$1,495')`✏️	`-1495`
`parse_number('($1,495')`✏️	`1495`
`parse_number('1,05.01')`✏️	`105.01`
`parse_number('-1,05.01')`✏️	`-105.01`
`parse_number('(1,05.01')`✏️	`105.01`
`parse_number('1,001,05.01')`✏️	`100105.01`
`parse_number('-1,001,05.01')`✏️	`-100105.01`
`parse_number('(1,001,05.01')`✏️	`100105.01`
`parse_number('1.05,01')`✏️	`105.01`
`parse_number('1.001.05,01')`✏️	`100105.01`
`parse_number('-1.001.05,01')`✏️	`-100105.01`
`parse_number('(1.001.05,01')`✏️	`100105.01`
`parse_number('1.05,01')`✏️	`105.01`
`parse_number('1,05,01')`✏️	`10501`
`parse_number('1.05.01')`✏️	`10501`
`parse_number('-1.05.01')`✏️	`-10501`
`parse_number('(1.05.01')`✏️	`10501`

format_number

format_number(input: number, format?: 'us' | 'eu' | string): string

Formats input number as per format string

If input is blank, then returns it as is
If format is blank, then assumes it is us

Expression	Result
`format_number()`✏️	`Error: numeral_es6 is not a function`

is_array

is_array(input)

Checks if given input is array and returns true, otherwise returns false.

Expression	Result
`is_array(['abc','Anu'])`✏️	`true`
`is_array(['',''])`✏️	`true`
`is_array('John')`✏️	`false`
`is_array([3,4])`✏️	`true`

is_blank

Checks if value is blank (null or string with spaces)

Expression	Result
`is_blank(['abc','Anu'])`✏️	`false`
`is_blank(['',''])`✏️	`false`
`is_blank('John')`✏️	`false`
`is_blank()`✏️	`true`
`is_blank('')`✏️	`true`

is_equal

Checks if two values have same content

Expression	Result
`is_equal(['abc','Anu'])`✏️	`false`
`is_equal('','')`✏️	`true`
`is_equal('abc','abc')`✏️	`true`
`is_equal(23,23)`✏️	`true`
`is_equal('')`✏️	`false`

is_equal_any

is_equal_any(input, values): boolean

Checks if input is equal to any one of the value in the values array (case-insensitive) and returns true, otherwise returns false.

If input is null, return false
If values is null or blank, then returns false

Expression	Result
`is_equal_any('abc',['abc','zya'])`✏️	`true`
`is_equal_any('abc',['xyz','lmk'])`✏️	`false`

is_nan

Checks if given input is NaN

Expression	Result
`is_nan(123)`✏️	`false`
`is_NaN(-1.23)`✏️	`false`
`is_NaN(5-2)`✏️	`false`

is_not_blank

Negation of is_blank

Expression	Result
`is_not_blank(7)`✏️	`true`
`is_not_blank()`✏️	`false`
`is_not_blank('')`✏️	`false`
`is_not_blank('John')`✏️	`true`

is_not_null

Negation of is_null

Expression	Result
`is_not_null('7')`✏️	`true`
`is_not_null('abc')`✏️	`true`
`is_not_null()`✏️	`false`
`is_not_null('')`✏️	`true`

is_number

is_number(input)

Checks if given input is number and returns true, otherwise returns false. Note that string which contains valid number still returns false. If you want to check if string can be converted to a number, then use is_number_like.

Expression	Result
`is_number(1234)`✏️	`true`
`is_number('1234')`✏️	`false`
`is_number(0)`✏️	`true`
`is_number('abc')`✏️	`false`

is_number_like

is_number_like(input)

Checks if given input is number or string which can be converted to a valid number and returns true, otherwise returns false. Note that string which contains valid number still returns false.

Expression	Result
`is_number_like('abc')`✏️	`false`
`is_number_like('123')`✏️	`true`

is_zero

is_zero(input)

Checks if given input equal to 0 and returns true, otherwise returns false.

Expression	Result
`is_zero(' 0')`✏️	`true`
`is_zero('10')`✏️	`false`

parse_json

Parses the given input as string into a json object

Expression	Result
`parse_json('{ "name":"John", "age":30, "city":"New York"}')`✏️	`{"name":"John","age":30,"city":"New York"}`

to_json

Converts input into json string

to_json_pretty

Converts input into pretty formatted json string

length

length(input)

Returns the length of input.

If input is null, then returns 0.
If input is array, then returns array length.
If input is string, then returns string length.

lower_case

lower_case(value: any | any[]): any | any[]

Converts given text to lower-cased text.

If input is an Array, then it operates on each element of Array.

Expression	Result
`lower_case('JOHN DOE')`✏️	`john doe`

map_value

map_value(input, mappings, [defaultValue])

Checks the input against values in mappings array and returns first match. If there are no matches found, then returns defaultValue or null if default value is not specified. This is similar to switch.

Mappings should be an array with even number of values with series of key=value pairs
If input is blank, returns defaultValue
If mappings is blank, returns defaultValue
input is matching is done case-insensitively
mappings value could be coming from params. For ex., $params.stateMappings

Expression	Result
`map_value('CA', ['ca', 'California', 'ny', 'New York'])`✏️	`California`
`map_value('Ny', ['ca', 'California', 'ny', 'New York'])`✏️	`New York`
`map_value('NV', ['ca', 'California', 'ny', 'New York'])`✏️	`<null>`
`map_value('NV', ['ca', 'California', 'ny', 'New York'], 'No State')`✏️	`No State`

map_value_reverse

map_value_reverse(input, mappings, [defaultValue])

Same as map_value but uses the given mapset in reverse order (that is matches input with value of key=value pairs and returns the key)

If there are multiple matches, picks the first match
Look for notes section of map_value for additional details

Expression	Result
`map_value_reverse('california', ['ca', 'California', 'ny', 'New York'])`✏️	`ca`
`map_value_reverse('california', ['CA', 'California', 'ny', 'New York'])`✏️	`CA`
`map_value_reverse('california', ['CA', 'California', 'cali', 'california', 'ny', 'New York'])`✏️	`CA`
`map_value('Ny', ['ca', 'California', 'ny', 'New York'])`✏️	`New York`
`map_value('NV', ['ca', 'California', 'ny', 'New York'], 'No State')`✏️	`No State`

max

Returns the max value among given series of values

min

Returns the min value among given series of values

month_names

Returns array consisting of month names.

['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December']

month_names_short

Returns array consisting of month first 3 chars of names

['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']

mpicklist_map_value

mpicklist_map_value(input: string, mapset: string[], defaultValue?: string)

Maps the individual multi-picklist value inside input using mapset array with defaultValue as default

mpicklist_cleanup

mpicklist_cleanup(input: string)

Cleanups the given multi-picklist input

Trims the individual Picklist values
Removes the duplicate values
Removes blank values

Expression	Result
`mpicklist_cleanup('abc;abc')`✏️	`Error: unterminated string`
`mpicklist_cleanup('xyz;abc ;xyz; abc;abc')`✏️	`Error: unterminated string`
`mpicklist_cleanup('abc;abc;;hello;;')`✏️	`Error: unterminated string`

mpicklist_sort

mpicklist_sort(input: string)

Sorts the given multi-picklist input

It doesn't alter the individual values. If you wan to remove blanks or trim values, use mpicklist_cleanup

Expression	Result
`mpicklist_sort('CA;NY;MD; OR;')`✏️	`Error: unterminated string`

mpicklist_add_value

mpicklist_add_value(input: string)

Appends given value to input multi picklist.

Value could be single value or an Array
Doesn't add the value if value already exists

Expression	Result
`mpicklist_add_value('CA', 'NY')`✏️	`CA;NY`
`mpicklist_add_value('CA', ['NY', 'WA'])`✏️	`CA;NY;WA`
`mpicklist_add_value('CA', ['CA'])`✏️	`CA`

mpicklist_remove_value

mpicklist_remove_value(input: string)

Removes given value from input multi picklist, if it exists

Value could be single value or an Array
Returns null if all values are removed from the input

Expression	Result
`mpicklist_remove_value('CA;ny', 'NY')`✏️	`Error: unterminated string`
`mpicklist_remove_value('CA;ny;som;Wa', ['NY', 'WA'])`✏️	`Error: unterminated string`
`mpicklist_remove_value('CA', ['CA'])`✏️	`<null>`

new_array

new_array(length: number, fillValueOrFunction: any | (indexValue, index, array) => any)

Creates new array of length and fills the array using fillValueOrFunction. See fill_array for more information on fill function.

If length is not a number or less than 0 then defaults to 0
Maximum length array you can create is 10,000. If length is bigger than that, then it will error out.
If fillValueOrFunction is not specified then it defaults to null

Expression	Result
`new_array(1)`✏️	`[null]`
`new_array(5, 10)`✏️	`[10,10,10,10,10]`
`new_array(2, 'abc')`✏️	`["abc","abc"]`
`new_array(5, (value, index) => index + 1)`✏️	`[1,2,3,4,5]`
`new_array()`✏️	`[]`
`new_array(-1)`✏️	`[]`

now

now()

Returns current time in ISO 8601 extended format. For ex., 2020-01-01T18:23:45Z.

now_iso_basic

now_iso_basic()

Returns current time in ISO 8601 basic format. For ex., 20200101T182345.

null_if_blank

null_if_blank(input)

Returns blank string if input is null, otherwise returns the input as is.

Expression	Result
`null_if_blank('')`✏️	`<null>`
`null_if_blank(' ')`✏️	`<null>`
`null_if_blank(' Jack ')`✏️	`Jack`
`null_if_blank('John')`✏️	`John`
`null_if_blank(10)`✏️	`10`
`null_if_blank([1, 2])`✏️	`[1,2]`

null_if_zero

If input is null, then returns 0

Expression	Result
`null_if_zero(0)`✏️	`<null>`
`null_if_zero(10)`✏️	`10`
`null_if_zero('abc')`✏️	`abc`

pad

Pads both right and left side of the value upto size using chars

pad_end

Pads the right side of value upto size using chars

pad_start

Pads the start side of value upto size using chars

parse_date

parse_date(input, pattern, zone?)

Parses input date string according to pattern in zone. This function uses Luxon library to parse string. You can refer to that documentation also to get additional context.

If input is blank, then defaults to
If pattern is blank, then it returns iso datetime string
If zone is blank, then defaults to UTC timeZone

Pre-defined Formats

Pattern	Description
`iso`	Parses string as iso date/datetime. It parses all datetime string listed here.
`ms`	Assumes the input as number of milliseconds since unix epoch
`secs`	Assumes the input as number of seconds since unix epoch

Parse Tokens

Token	Description	Example
`S`	millisecond, no padding	`54`
`SSS`	millisecond, padded to 3	`054`
`u`	fractional seconds, (5 is a half second, 54 is slightly more)	`54`
`uu`	fractional seconds, (one or two digits)	`05`
`uuu`	fractional seconds, (only one digit)	`5`
`s`	second, no padding	`4`
`ss`	second, padded to 2 padding	`04`
`m`	minute, no padding	`7`
`mm`	minute, padded to 2	`07`
`h`	hour in 12-hour time, no padding	`1`
`hh`	hour in 12-hour time, padded to 2	`01`
`H`	hour in 24-hour time, no padding	`13`
`HH`	hour in 24-hour time, padded to 2	`13`
`Z`	narrow offset	`+5`
`ZZ`	short offset	`+05:00`
`ZZZ`	techie offset	`+0500`
`z`	IANA zone	`America/New_York`
`a`	meridiem	`AM`
`d`	day of the month, no padding	`6`
`dd`	day of the month, padded to 2	`06`
`E`	day of the week, as number from 1-7 (Monday is 1, Sunday is 7)	`3`
`EEE`	day of the week, as an abbreviate localized string	`Wed`
`EEEE`	day of the week, as an unabbreviated localized string	`Wednesday`
`M`	month as an unpadded number	`8`
`MM`	month as an padded number	`08`
`MMM`	month as an abbreviated localized string	`Aug`
`MMMM`	month as an unabbreviated localized string	`August`
`y`	year, 1-6 digits, very literally	`2014`
`yy`	two-digit year, interpreted as > 1960 (also accepts 4)	`14`
`yyyy`	four-digit year	`2014`
`yyyyy`	four- to six-digit years	`10340`
`yyyyyy`	six-digit years	`010340`
`G`	abbreviated localized era	`AD`
`GG`	unabbreviated localized era	`Anno Domini`
`GGGGG`	one-letter localized era	`A`
`kk`	ISO week year, unpadded	`17`
`kkkk`	ISO week year, padded to 4	`2014`
`W`	ISO week number, unpadded	`32`
`WW`	ISO week number, padded to 2	`32`
`o`	ordinal (day of year), unpadded	`218`
`ooo`	ordinal (day of year), padded to 3	`218`
`q`	quarter, no padding	`3`
`D`	localized numeric date	`9/6/2014`
`DD`	localized date with abbreviated month	`Aug 6, 2014`
`DDD`	localized date with full month	`August 6, 2014`
`DDDD`	localized date with full month and weekday	`Wednesday, August 6, 2014`
`t`	localized time	`1:07 AM`
`tt`	localized time with seconds	`1:07:04 PM`
`T`	localized 24-hour time	`13:07`
`TT`	localized 24-hour time with seconds	`13:07:04`
`TTT`	localized 24-hour time with seconds and abbreviated offset	`13:07:04 EDT`
`f`	short localized date and time	`8/6/2014, 1:07 PM`
`ff`	less short localized date and time	`Aug 6, 2014, 1:07 PM`
`F`	short localized date and time with seconds	`8/6/2014, 1:07:04 PM`
`FF`	less short localized date and time with seconds	`Aug 6, 2014, 1:07:04 PM`

convert_date

convert_date(input, parsePattern, formatPattern, zone?)

Parses input date string according to parsePattern in zone and then formats that date into format given in formatPattern. This basically combines parse_date with format_date as this convention is used many times.

Old name for this function was parse_date_to_format

If input is blank, then returns input as is
If parsePattern is blank, then it returns input as is
If formatPattern is blank, then it formats it in iso datetime format
If zone is blank, then defaults to UTC timeZone

random_boolean

Returns random boolean (true or false)

random_integer

Returns random integer between two values.

Expression	Result
`random_integer(1,3)`✏️	`3`
`random_integer(2,100)`✏️	`75`

random_null

Randomly returns null and other times it returns one the given values

Expression	Result
`random_null(3)`✏️	`<null>`
`random_null(3)`✏️	`<null>`
`random_null(3)`✏️	`3`
`random_null(3)`✏️	`<null>`
`random_null(3)`✏️	`3`
`random_null(3)`✏️	`<null>`
`random_null(3)`✏️	`<null>`
`random_null(3)`✏️	`<null>`

random_string

Returns random string of length (default 100). for ex,. lsmztmc4ali1nnrk6972gdjwu64uk6xfkimwdpc1sgbk9gg6dkickh718c2kvovz13wnxlndgc3h97g1kuv7e9wbmwtpnalqx2ut

random_value

random_value(values)

Returns one of the given values at random

Expression	Result
`random_value(3,34,5,88,99,00)`✏️	`3`
`random_value()`✏️	`<null>`

remove_prefix

Removes the value if it is starts with given string

Expression	Result
`remove_prefix('S-Kumar','S-')`✏️	`Kumar`
`remove_prefix('abc','abc')`✏️	`<blank>`
`remove_prefix('S-Kumar','T-')`✏️	`S-Kumar`

remove_suffix

remove_suffix(input: any, suffix: string | string[]): string

Removes the suffix from string, if it is present. If not, returns the input as is. Suffix can be multiple values and in that case it removes any one of those suffixes from input.

::: warning Caveat If you specify multiple suffixes, once a suffix is matched, then input is returned. For ex., remove_suffix('Boing-K-J', ['-K', '-J']) => Boing-K :::

Expression	Result
`remove_suffix('AFAWGAGT25A-J', '-J')`✏️	`AFAWGAGT25A`
`remove_suffix('AFAWGAGT25A-K', '-J')`✏️	`AFAWGAGT25A-K`
`remove_suffix('AFAWGAGT25A-K', ['-J', '-K'])`✏️	`AFAWGAGT25A`

repeat

Repeats given input times using separator (if specified, else blank).

Expression	Result
`repeat(3,4,'-')`✏️	`3-3-3-3`
`repeat(3,0,'-')`✏️	`<blank>`
`repeat(3,1,'-')`✏️	`3`

replace_regex

replace_regex(input, regex, replaceWith?)

Replace all matches in input for regex with replaceWith string. If replaceWith is not provided, then uses blank.

replace_whitespace

replace_whitespace(input, replaceWith?)

Replace all whitespaces in input with replaceWith string. If replaceWith is not provided, then uses blank.

round

round(input)

Rounds the value. If value is .5 or more, then rounds up else rounds down

round_down

round_down(input)

Rounds down the given input number.

If input is not a number like, then returns it as is.

round_up

round_up(input)

Rounds up the given input to next integer.

If input is null or not a number like, then returns it as is.

Expression	Result
`round_up(null)`✏️	`<null>`
`round_up('John')`✏️	`John`
`round_up([1, 2])`✏️	`[1,2]`
`round_up({})`✏️	`{}`
`round_up('10.5')`✏️	`11`
`round_up(10)`✏️	`10`
`round_up(10.4)`✏️	`11`
`round_up(10.5)`✏️	`11`
`round_up(10.9)`✏️	`11`

sequence

Generates length (defaults to 0) array of sequence numbers with start (default 0) and sequence step of step (default 1). Step can be -ve and in that case, numbers will be decremented

sf_id15

Converts salesforce ids into 15 digit one. If input is null or not equal to 18 chars, then returns the input as is.

sf_id18

Converts salesforce ids into 18 digit one. If input is null or not equal to 15 chars, then returns the input as is.

soql_in_string

Creates soql in clause compatible string (comma separated string with ' escaped). Second arg can be used to default if first argument is null or array containing zero items.

soql_string

Converts given input to string with ' escaped and wrapped with 's. If input is null, then returns ''

split

split(input: string, separator: string, options?: SplitOptions)

Splits input string by separator and returns the string array.

If input is null then returns empty array
If separator is blank then returns array containing input as is
Separator is treated as case-insensitive
Depending on the separator in the input, blanks or values with spaces may be returned in the output. If you want to remove blanks, specify remove_blanks and or trim as options

Where SplitOptions is:

Option	Description
`remove_blanks`	Removes blanks from resulting array
`trim`	Trims the values of resulting array

Expression	Result
`split('This is my home', ' ')`✏️	`["This","is","my","home"]`
`split('This is my Home and this is my Car', 'IS MY')`✏️	`["This "," Home and this "," Car"]`
`split('This is my Home and this is my Car', 'IS MY', {trim: true})`✏️	`["This","Home and this","Car"]`
`split('This is my Home and this is my Caris my', 'IS MY', {trim: true})`✏️	`["This","Home and this","Car",""]`
`split('This is my Home and this is myis my Car', 'IS MY', {trim: true})`✏️	`["This","Home and this","","Car"]`
`split('This is my Home and this is myis my Car', 'IS MY', {trim: true, remove_blanks: true})`✏️	`["This","Home and this","Car"]`

starts_with

starts_with(input: string | number, prefix: string): boolean

Checks if input starts with prefix (case-insensitive) and returns true, otherwise returns false

If input or prefix is blank then returns false
If input is number then it is converted to string before checking prefix

starts_with_any

starts_with_any(input, ...prefixes): boolean

Checks if input starts with any one of the prefixes (case-insensitive) and returns true, otherwise returns false

If input is blank or prefix is blank then returns false
If input is number, then it is converted to string before checking prefix

to_array

Converts given value to array. if value is already an array, then returns it as is.

to_boolean

Converts value to boolean. If value is false, 0, or null then treated as false and everything else as true

Expression	Result
`to_boolean('false')`✏️	`false`
`to_boolean('FALSE')`✏️	`false`
`to_boolean('0')`✏️	`false`
`to_boolean('no')`✏️	`false`
`to_boolean('NO')`✏️	`false`
`to_boolean(false)`✏️	`false`
`to_boolean('true')`✏️	`true`
`to_boolean('TRUE')`✏️	`true`
`to_boolean('1')`✏️	`false`
`to_boolean('yes')`✏️	`false`
`to_boolean('anything')`✏️	`false`
`to_boolean(null)`✏️	`false`
`to_boolean(undefined)`✏️	`false`
`to_boolean(NaN)`✏️	`false`
`to_boolean(1)`✏️	`false`
`to_boolean(0)`✏️	`false`
`to_boolean(true)`✏️	`true`
`to_boolean({})`✏️	`false`
`to_boolean([])`✏️	`false`

to_integer

to_integer(input, default?): number

Converts input to integer. If it cannot be converted, then throws error unless a default is provided.

Expression	Result
`to_integer('10')`✏️	`<null>`
`to_integer('abc')`✏️	`<null>`

to_number

to_number(input, default?): number

Converts given input to number. If it cannot be converted, then throws error unless a default is provided.

to_string

Converts given value to string in human-readable format. If value is null, then returns ''

Expression	Result
`to_string(33)`✏️	`33`
`to_string('Jake')`✏️	`Jake`
`to_string(['Jake', 'John', 10, 100.5])`✏️	`[Jake,John,10,100.5]`
`to_string({first: 'Jake', last: 'John'})`✏️	`{first=Jake,last=John}`
`to_string([{first: 'Jake', last: 'John'}, {first: 'Jane', last: 'Doe'}])`✏️	`[{first=Jake,last=John},{first=Jane,last=Doe}]`
`to_string(null)`✏️	`<blank>`
`to_string(undefined)`✏️	`<blank>`
`to_string(NaN)`✏️	`<blank>`

today

today()

Returns today's date in yyyy-MM-dd format. For ex., 2020-05-23

Expression	Result
`today()`✏️	`2024-12-10`

trim

trim(input: string | string[]): string | string[]

Removes any white spaces from the beginning and end of input.

Expression	Result
`trim('Boing ')`✏️	`Boing`
`trim(' Datasert')`✏️	`Datasert`
`trim(' Salesforce ')`✏️	`Salesforce`

trim_end

trim_end(value: any | any[]): any | any[]

Removes any white spaces from end of text. It does not check or alter beginning of strings.

Expression	Result
`trim_end('Boing ')`✏️	`Boing`
`trim_end(' Datasert')`✏️	`⠀Datasert`
`trim_end(' Salesforce ')`✏️	`⠀⠀Salesforce`

trim_start

trim_start(value: any | any[]): any | any[]

Removes any whitespaces from the beginning of text. It does not check or alter end of strings.

Expression	Result
`trim_start(' Boing')`✏️	`Boing`
`trim_start('Datasert ')`✏️	`Datasert⠀`
`trim_start(' Salesforce ')`✏️	`Salesforce⠀`

truncate_end

truncate_end(input: string, length: number)

Same as truncate_start but truncates end of the value instead of beginning.

Expression	Result
`truncate_end('This is my Home', 10)`✏️	`This is my`
`truncate_end('This is my Home', null)`✏️	`This is my Home`
`truncate_end('David', 10)`✏️	`David`
`truncate_end(null, 10)`✏️	`<null>`
`truncate_end(null, 10)`✏️	`<null>`

truncate_start

truncate_start(input: string, length: number)

Truncates the beginning of the given input to length chars.

If input or length is null then input is returned as is
If input size is less or equal to length, then input is returned as is
If length is less than or equal to 0, then input is returned as is

Expression	Result
`truncate_start('This is my Home', 10)`✏️	`is my Home`
`truncate_start('This is my Home', null)`✏️	`This is my Home`
`truncate_start('David', 10)`✏️	`David`
`truncate_start(null, 10)`✏️	`<null>`
`truncate_start(null, 10)`✏️	`<null>`

unix_time

unix_time()

Returns number of seconds since Unix Epoch time (1970 Jan 1st). For ex., 1614829329

Expression	Result
`unix_time()`✏️	`1733852537`

unix_time_ms

unix_time_ms()

Returns number of milliseconds since Unix Epoch time (1970 Jan 1st). For ex., 1614829329000

Expression	Result
`unix_time_ms()`✏️	`1733852537557`

unwrap

Unwraps the value if it is wrapped with given string. If it is not wrapped with given string, then returns value as is.

Expression	Result
`unwrap('car-BMW-car','car')`✏️	`-BMW-`
`unwrap('car-BMW-car','bus')`✏️	`car-BMW-car`
`unwrap('car-BMW-car','')`✏️	`car-BMW-car`

upper_case

upper_case(value: any | any[]): any | any[]

Converts given text to upper-cased text.

If input is an Array, then it operates on each element of Array.

Expression	Result
`upper_case('John Doe')`✏️	`JOHN DOE`
`upper_case(['Prakash','Santhosh'])`✏️	`PRAKASH,SANTHOSH`

url_decode

Url decodes the given input. If input is null then returns input as is.

Expression	Result
`url_decode('my%20test.asp?name=st%C3%A5le&car=saab')`✏️	`my test.asp?name=ståle&car=saab`
`url_decode(null)`✏️	`<null>`

url_encode

Url encodes the given input. If input is null then returns input as is.

Expression	Result
`url_encode('my test.asp?name=ståle&car=saab')`✏️	`my%20test.asp?name=st%C3%A5le&car=saab`
`url_encode(null)`✏️	`<null>`

us_state_codes

Returns array consisting of US state codes

['AL', 'AK', 'AZ', 'AR', 'CA', 'CO', 'CT', 'DE', 'DC', 'FL', 'GA', 'HI', 'ID', 'IL', 'IN', 'IA', 'KS', 'KY', 'LA', 'ME', 'MD', 'MA', 'MI', 'MN', 'MS', 'MO', 'MT', 'NE', 'NV', 'NH', 'NJ', 'NM', 'NY', 'NC', 'ND', 'OH', 'OK', 'OR', 'PA', 'RI', 'SC', 'SD', 'TN', 'TX', 'UT', 'VT', 'VA', 'WA', 'WV', 'WI', 'WY']

us_state_names

Returns array consisting of US state names

['Alabama', 'Alaska', 'Arizona', 'Arkansas', 'California', 'Colorado', 'Connecticut', 'Delaware', 'District of Columbia', 'Florida', 'Georgia', 'Hawaii', 'Idaho', 'Illinois', 'Indiana', 'Iowa', 'Kansas', 'Kentucky', 'Louisiana', 'Maine', 'Maryland', 'Massachusetts', 'Michigan', 'Minnesota', 'Mississippi', 'Missouri', 'Montana', 'Nebraska', 'Nevada', 'New Hampshire', 'New Jersey', 'New Mexico', 'New York', 'North Carolina', 'North Dakota', 'Ohio', 'Oklahoma', 'Oregon', 'Pennsylvania', 'Rhode Island', 'South Carolina', 'South Dakota', 'Tennessee', 'Texas', 'Utah', 'Vermont', 'Virginia', 'Washington', 'West Virginia', 'Wisconsin', 'Wyoming']

weekday_names

Returns array consisting of weekday names.

['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']

weekday_names_short

Returns array consisting of weekday first 3 chars of names

['Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun']

wrap

Wraps the value with wrap string

Expression	Result
`wrap('name','start-','-end')`✏️	`start-name-end`
`wrap('name','start')`✏️	`startnamestart`
`wrap('','start','end')`✏️	`<blank>`

wrap_bracket

Calls wrap with [ and ] as start and end guards

Expression	Result
`wrap_bracket('abc')`✏️	`[abc]`
`wrap_bracket('')`✏️	`<blank>`
`wrap_bracket(null)`✏️	`<null>`

zero_if_blank

If value is zero, returns null else returns value as is.

Expression	Result
`zero_if_blank('0')`✏️	`0`
`zero_if_blank('abc')`✏️	`abc`

zero_if_null

If value is zero, returns null else returns value as is.

Expression	Result
`zero_if_null('')`✏️	`<blank>`
`zero_if_null('abc')`✏️	`abc`
`zero_if_null(0)`✏️	`0`

abbr​

abs​

add_date​

avg​

blank_if_null​

blank_if_zero​

capital_case​

compare​

concat_array​

contains​

dedupe​

diff_date​

ends_with​

ensure_prefix​

ensure_suffix​

error​

fill_array​

first​

first_not_blank​

first_not_null​

first_not_zero​

flatten​

format_date​

geo_distance​

get_value​

group​

parse_number​

format_number​

is_array​

is_blank​

is_equal​

is_equal_any​

is_nan​

is_not_blank​

is_not_null​

is_number​

is_number_like​

is_zero​

parse_json​

to_json​

to_json_pretty​

length​

lower_case​

map_value​

map_value_reverse​

max​

min​

month_names​

month_names_short​

mpicklist_map_value​

mpicklist_cleanup​

mpicklist_sort​

mpicklist_add_value​

mpicklist_remove_value​

new_array​

now​

now_iso_basic​

null_if_blank​

null_if_zero​

pad​

pad_end​

pad_start​

parse_date​

convert_date​

random_boolean​

random_integer​

random_null​

random_string​

random_value​

remove_prefix​

remove_suffix​

repeat​

replace_regex​

replace_whitespace​

round​

round_down​

round_up​

sequence​

sf_id15​

sf_id18​