Functions

Appsuite formula engine has built many functions that implement various logic that customers can make use of. This page supports list of functions that Appsuite formula engine supports. If you think any other formula is helpful, please let us know at support@datasert.com

tip

• 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(null)	null
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 returns the input as is

Expression	Result
abs(null)	null
abs('John')	John
abs(-10)	10
abs(-10.45)	10.45
abs(10)	10
abs(10.45)	10.45

add_date

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

Adds amount of date unit to given input

• Valid unit values are year, month, day, hour, minute, second, and ms.
• 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

Formula	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(null)	0
avg('John')	0
avg(10)	10
avg(1, 2, 3)	1.5
avg(10, 'John')	5

blank_if_null

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

Expression	Result
blank_if_null(null)	``
blank_if_null('')	``
blank_if_null("abc")	abc

blank_if_zero

If input is blank, then returns 0

Expression	Result
blank_if_zero(0)	``
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 malana'
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.

• 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 day
• Valid unit values are
  • year
  • month
  • day
  • hour
  • minute
  • second
  • ms

ends_with

ends_with(input, suffix): boolean

Checks if input ends with suffix (case-insensitive) and returns true, otherwise returns false

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

Expression	Result
ends_with("James Smith", "Smith")	true
ends_with("James Smith", "Smitha")	false
ends_with(1234, 4)	true

ends_with_any

ends_with_any(input, ...suffixes): boolean

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

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

Expression	Result
ends_with_any("James Smith", "t","h")	true
ends_with_any("James Smith", "t","a")	false
ends_with_any("James Smith", "h")	true
ends_with_any(445,5)	true
ends_with_any(445,3)	false

ensure_prefix

ensure_prefix(value: any | any[], prefix: string): any | any[]

Ensures that given text has 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 In-sensitive

Formula	Result
ensure_prefix('123', 'ACCT-')	ACCT-123
ensure_prefix('ACCT-123', 'ACCT-')	ACCT-123
ensure_prefix('acct-123', 'ACCT-')	ACCT-123 Note that existing prefix is converted to correct 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

Formula	Result
ensure_suffix('Boing', 'Inc')	Boing Inc
ensure_suffix('Boing Inc', 'Inc')	Boing Inc
ensure_suffix('Boing inc', 'Inc')	Boing Inc Note that existing suffix is converted to correct case

error

error(message)

Throws error with 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.

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

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

Formula	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

Formula	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

Formula	Result
first_not_zero('','','','Raj','')	Raj
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

Formula	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

Name	Example
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, 9:07 AM EDT
datetime_huge	Friday, October 14, 1983, 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, 9:07:04 AM EDT
datetime_huge_with_seconds	Friday, October 14, 1983, 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

Formula	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" ] }]

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

json_parse

Parses the given input as string into a json object

Expression	Result
json_parse('{ "name":"John", "age":30, "city":"New York"}')	{ "name": "John", "age": 30, "city": "New York"}

json_string

Converts input into json string

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

Formula	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

Formula	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

Formula	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'])	ny
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

Formula	Result
mpicklist_cleanup('abc;abc')	abc
mpicklist_cleanup('xyz;abc ;xyz; abc;abc')	xyz;abc
mpicklist_cleanup('abc;abc;;hello;;')	abc;hello

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

Formula	Result
mpicklist_sort('CA;NY;MD; OR;')	; OR;CA;MD;NY

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

Formula	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

Formula	Result
mpicklist_remove_value('CA;ny', 'NY')	CA
mpicklist_remove_value('CA;ny;som;Wa', ['NY', 'WA'])	CA;som
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.

2021-04-25T17:19:11.279+00:00

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)	<blank>
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(null)	0
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.

Formula	Result
random_integer(1,3)	1
random_integer(2,6)	3

random_null

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

Formula	Result
random_null(8)	null
random_null(3)	3

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

Formula	Result
random_value(3,34,5,88,99,00)	88
random_value()	null

remove_prefix

Removes the value if it is starts with given string

Formula	Result
remove_prefix('S-Kumar','S-')	Kumar
remove_prefix('abc','abc')
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 :::

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

Formula	Result
repeat(3,4,'-')	3-3-3-3
repeat(3,0,'-')
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 true or yes 1 then treated as true and everything else as 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.

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. If value is null, then returns ''

Formula	Result
to_string(33)	33
to_string(33)

today

today()

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

trim

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

Removes any white spaces from beginning and end of text.

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

Formula	Result
trim_end('Boing ')	Boing
trim_end(' Datasert')	<space>Datasert
trim_end(' Salesforce ')	<space>Salesforce

trim_start

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

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

Formula	Result
trim_start(' Boing')	Boing
trim_start('Datasert ')	Datasert<space>
trim_start(' Salesforce ')	Salesforce<space>

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

unix_time_ms

unix_time_ms()

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

unwrap

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

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

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

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

Formula	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

Formula	Result
wrap('name','start-','-end')	start-name-end
wrap('name','start')	startnamestart
wrap('','start','end')

wrap_bracket

Calls wrap with [ and ] as start and end guards

Formula	Result
wrap_bracket('abc')	[abc]
wrap_bracket('')
wrap_bracket(null)	null

zero_if_blank

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

Expression	Result
zero_if_blank("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("")	null
zero_if_null("abc")	abc
zero_if_null(0)	null

parse_number

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

Prases given string and tries to extract number according to given locale. If locale 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 locale is assumed as 'us'

Expression	Result
parse_number("")	null
parse_number('1.0')	1.0
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

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.0
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()	``
format_number()	``
format_number()	``
format_number()	``
format_number()	``
format_number()	``
format_number()	``
format_number()	``
format_number()	``
format_number()	``
format_number()	``
format_number()	``
format_number()	``
format_number()	``
format_number()	``
format_number()	``
format_number()	``
format_number()	``
format_number()	``
format_number()	``
format_number()	``
format_number()	``
format_number()	``
format_number()	``
format_number()	``
format_number()	``
format_number()	``