Class: ExpressionGlobalContext
Hierarchy
ExpressionGlobalContext↳
ExpressionDecodeResultContext↳
ExpressionDataSourceRowContext↳
ExpressionMesPageEventHandlerContext↳
ExpressionMesWorkflowContext↳
ExpressionReportDataSourceContext
Constructors
constructor
• new ExpressionGlobalContext(timeZone)
Parameters
| Name | Type |
|---|---|
timeZone | undefined | null | string |
Properties
timeZone
• Readonly timeZone: undefined | null | string
Aggregate Functions Methods
ALL
▸ ALL(criteria, ...values): boolean
Returns true if all values match the criteria
Example
ALL('a', 'a', 'b', 'c') = false
Example
ALL('a', 'a', 'a') = true
Example
ALL('>5', 6, 7, 8) = true
Parameters
| Name | Type | Description |
|---|---|---|
criteria | any | The criteria to match. Use a string in quotes starting with >, <, =, >=, <=, or != for comparison, or a value for equality. |
...values | any[] | The values to check against the criteria |
Returns
boolean
True if all values match the criteria, false otherwise
ANY
▸ ANY(criteria, ...values): boolean
Returns true if any of the values match the criteria
Example
ANY('a', 'a', 'b', 'c') = true
Example
ANY('x', 'a', 'b', 'c') = false
Example
ANY('>5', 1, 2, 3, 6, 7) = true
Parameters
| Name | Type | Description |
|---|---|---|
criteria | any | The criteria to match. Use a string in quotes starting with >, <, =, >=, <=, or != for comparison, or a value for equality. |
...values | any[] | The values to check against the criteria |
Returns
boolean
True if any value matches the criteria, false otherwise
AVERAGE
▸ AVERAGE(...values): number
Returns the average of the passed numbers
Example
AVERAGE(1, 2, 3) = 2
Parameters
| Name | Type | Description |
|---|---|---|
...values | any[] | The numbers to average, can be numbers or null |
Returns
number
The average of the numbers, or null if all values are null
COALESCE
▸ COALESCE(...values): boolean
Returns the first non null value from the supplied values
Example
COALESCE(null, 'value', null) = 'value'
Example
COALESCE(null, null, null) = null
Example
COALESCE('value1', 'value2') = 'value1'
Parameters
| Name | Type | Description |
|---|---|---|
...values | any[] | The values to check |
Returns
boolean
The first non null value, or null if all values are null
COUNT
▸ COUNT(...values): number
Returns the count of non-null values from the passed values
Example
COUNT(1, 2, 3) = 3
Example
COUNT(1, null, 3) = 2
Parameters
| Name | Type | Description |
|---|---|---|
...values | any[] | The values to count, can be numbers, strings, or null |
Returns
number
The count of non-null values, or 0 if all values are null
COUNTIF
▸ COUNTIF(criteria, ...values): number
Example
COUNTIF('>5', 1, 2, 3, 6, 7) = 2
Example
COUNTIF('=a', 'a', 'b', 'c') = 1
Parameters
| Name | Type | Description |
|---|---|---|
criteria | any | The criteria to match. Use a string in quotes starting with >, <, =, >=, <=, or != for comparison, or a value for equality. |
...values | any[] | The values to check against the criteria |
Returns
number
The count of values that match the criteria
DEFAULT
▸ DEFAULT(value, defaultValue): boolean
Returns the defaultValue if the value is null
Example
DEFAULT(null, 'default') = 'default'
Parameters
| Name | Type | Description |
|---|---|---|
value | any | The value to check |
defaultValue | any | The value to return if the value is null |
Returns
boolean
The value if it is not null, otherwise the defaultValue
DIVIDE
▸ DIVIDE(value, ...values): null | number
Returns the first number divided by the following numbers
Example
DIVIDE(10, 2, 5) = 1
Example
DIVIDE(10, null, 5) = 2
Parameters
| Name | Type | Description |
|---|---|---|
value | any | The first number to divide, can be a number or null |
...values | any[] | The following numbers to divide by, can be numbers or null |
Returns
null | number
The result of the division, or null if all values are null
MAX
▸ MAX(...values): number
Returns the maximum value from the passed numbers
Example
MAX(1, 2, 3) = 3
Parameters
| Name | Type | Description |
|---|---|---|
...values | any[] | The numbers to find the maximum of, can be numbers or null |
Returns
number
The maximum value from the numbers, or null if all values are null
MIN
▸ MIN(...values): number
Returns the minimum value from the passed numbers
Example
MIN(1, 2, 3) = 1
Parameters
| Name | Type | Description |
|---|---|---|
...values | any[] | The numbers to find the minimum of, can be numbers or null |
Returns
number
The minimum value from the numbers, or null if all values are null
MULTIPLY
▸ MULTIPLY(value, ...values): null | number
Returns the multiplication of the passed numbers
Example
MULTIPLY(2, 3, 4) = 24
Example
MULTIPLY(2, null, 4) = 8
Parameters
| Name | Type | Description |
|---|---|---|
value | any | The first number to multiply, can be a number or null |
...values | any[] | The following numbers to multiply, can be numbers or null |
Returns
null | number
The product of the numbers, or null if all values are null
POW
▸ POW(value, exponent): null | number
Returns the first number raised to the power of the exponent
Example
POW(2, 3) = 8
Example
POW(5, 2) = 25
Parameters
| Name | Type | Description |
|---|---|---|
value | any | The base number, can be a number or null |
exponent | any | The exponent to raise the base number to, can be a number or null |
Returns
null | number
The result of the base number raised to the power of the exponent, or null if either value is null
SQRT
▸ SQRT(value): null | number
Returns the square root of a number
Example
SQRT(16) = 4
Example
SQRT(2) = 1.414213562373095
Parameters
| Name | Type | Description |
|---|---|---|
value | any | The number to get the square root of, can be a number or null |
Returns
null | number
The square root of the number, or null if the input is null
SUBTRACT
▸ SUBTRACT(...values): number
Returns the first number minus by the following numbers
Example
SUBTRACT(10, 2, 3) = 5
Example
SUBTRACT(10, null, 3) = 7
Parameters
| Name | Type | Description |
|---|---|---|
...values | any[] | The numbers to subtract, can be numbers or null |
Returns
number
The result of the subtraction, or null if all values are null
SUM
▸ SUM(...values): number
Returns the sum of the passed numbers
Example
SUM(1, 2, 3) = 6
Example
SUM(1, null, 3) = 4
Parameters
| Name | Type | Description |
|---|---|---|
...values | any[] | The numbers to sum, can be numbers or null |
Returns
number
The sum of the numbers, or null if all values are null
Aggregate Functions
Returns the count of values that match the criteria Methods
COUNTIF
▸ COUNTIF(criteria, ...values): number
Example
COUNTIF('>5', 1, 2, 3, 6, 7) = 2
Example
COUNTIF('=a', 'a', 'b', 'c') = 1
Parameters
| Name | Type | Description |
|---|---|---|
criteria | any | The criteria to match. Use a string in quotes starting with >, <, =, >=, <=, or != for comparison, or a value for equality. |
...values | any[] | The values to check against the criteria |
Returns
number
The count of values that match the criteria
Array Functions Methods
ARRAYAPPEND
▸ ARRAYAPPEND(array, value): any
Appends a value to the end of an array
Parameters
| Name | Type | Description |
|---|---|---|
array | any[] | The source array |
value | any | The value to append |
Returns
any
A new array with the value appended
ARRAYCONCAT
▸ ARRAYCONCAT(...values): any
Concatenates all provided arrays and values into a single array
Parameters
| Name | Type | Description |
|---|---|---|
...values | any[] | Arrays or values to concatenate |
Returns
any
A new concatenated array
ARRAYDISTINCT
▸ ARRAYDISTINCT(array): any
Returns an array with only unique values from the source array
Parameters
| Name | Type | Description |
|---|---|---|
array | any[] | The source array |
Returns
any
A new array with only unique values
ARRAYEXCEPT
▸ ARRAYEXCEPT(array, index): any
Returns an array without the item at the specified index
Parameters
| Name | Type | Description |
|---|---|---|
array | any[] | The source array |
index | number | The zero-based index to remove |
Returns
any
A new array with the specified index removed
ARRAYFILTER
▸ ARRAYFILTER(array, property, value): any
Filters an array of objects by property equality
Parameters
| Name | Type | Description |
|---|---|---|
array | any[] | The source array |
property | string | The object property to match |
value | any | The value the property must equal |
Returns
any
A filtered array of matching items
ARRAYPREPEND
▸ ARRAYPREPEND(array, value): any
Prepends a value to the start of an array
Parameters
| Name | Type | Description |
|---|---|---|
array | any[] | The source array |
value | any | The value to prepend |
Returns
any
A new array with the value prepended
ARRAYREVERSE
▸ ARRAYREVERSE(array): any
Reverses the order of items in an array
Parameters
| Name | Type | Description |
|---|---|---|
array | any[] | The source array |
Returns
any
A new reversed array
ARRAYSORT
▸ ARRAYSORT(array, property?, descending?): any
Sorts an array optionally by object property and direction
Parameters
| Name | Type | Description |
|---|---|---|
array | any[] | The source array |
property? | string | Optional property to sort by |
descending? | boolean | When true, sorts descending |
Returns
any
A sorted array
AT
▸ AT(index, ...values): number
Returns the item at the specified index in the array (zero based)
Example
AT(0, 1, 2, 3) = 1
Example
AT(2, 1, 2, 3) = 3
Parameters
| Name | Type | Description |
|---|---|---|
index | number | The index of the item to return, zero based |
...values | any[] | The values to get the item from, can be numbers, strings, or null |
Returns
number
The item at the specified index, or null if the index is out of bounds or all values are null
CELLLOOKUP
▸ CELLLOOKUP(rows, lookupCol, lookupValue, outputCol): any
Returns the value of the outputCol for the first row where the lookupCol has the lookupValue
Parameters
| Name | Type |
|---|---|
rows | any[] |
lookupCol | string |
lookupValue | string |
outputCol | string |
Returns
any
FIRST
▸ FIRST(...values): number
Returns the first item in an array of values
Example
FIRST(1, 2, 3) = 1
Example
FIRST(null, 2, 3) = 2
Parameters
| Name | Type | Description |
|---|---|---|
...values | any[] | The values to get the first item from, can be numbers, strings, or null |
Returns
number
The first item from the values, or null if all values are null
LAST
▸ LAST(...values): number
Returns the last item in an array of values
Example
LAST(1, 2, 3) = 3
Example
LAST(1, 2, null) = 2
Parameters
| Name | Type | Description |
|---|---|---|
...values | any[] | The values to get the last item from, can be numbers, strings, or null |
Returns
number
The last item from the values, or null if all values are null
LOOKUP
▸ LOOKUP(key, criteria, ...values): any
Returns the first object which matches the lookup from an array objects
Example
LOOKUP('id', '=1', [{id: 1, name: 'John'}, {id: 2, name: 'Jane'}]) = {id: 1, name: 'John'}
Parameters
| Name | Type | Description |
|---|---|---|
key | string | The key to lookup |
criteria | string | The criteria to match. Use a string in quotes starting with >, <, =, >=, <=, or != for comparison, or a value for equality. |
...values | any[] | The array of objects to search |
Returns
any
The first object which matches the lookup, or null if no match is found
NATURALORDER
▸ NATURALORDER(...values): any[]
Returns the values sorted in natural order. Can be used with NATURALORDERBY to sort by a specific key
Parameters
| Name | Type |
|---|---|
...values | any[] |
Returns
any[]
The values sorted in natural order
NATURALORDERBY
▸ NATURALORDERBY(key, ...values): any[]
Returns the values sorted in natural order by the specified key
Parameters
| Name | Type | Description |
|---|---|---|
key | string | The key to sort by |
...values | any[] | The values to sort, can be objects or primitive values. If objects, the key will be used to sort |
Returns
any[]
The values sorted in natural order by the specified key
ORDER
▸ ORDER(...values): any[]
Returns the values sorted in ascending order. Can be used with ORDERBY to sort by a specific key
Parameters
| Name | Type |
|---|---|
...values | any[] |
Returns
any[]
The values sorted in ascending order
ORDERBY
▸ ORDERBY(key, ...values): any[]
Returns the values sorted in ascending order by the specified key
Parameters
| Name | Type | Description |
|---|---|---|
key | string | The key to sort by |
...values | any[] | The values to sort, can be objects or primitive values. If objects, the key will be used to sort |
Returns
any[]
The values sorted in ascending order by the specified key
REVERSE
▸ REVERSE(...values): any[]
Returns the values in reverse order
Parameters
| Name | Type | Description |
|---|---|---|
...values | any[] | The values to reverse, can be objects or primitive values |
Returns
any[]
The values in reverse order
SELECT
▸ SELECT(data, ...fields): null | any[]
Returns the specified fields from the data array as a new array of objects
Parameters
| Name | Type |
|---|---|
data | any[] |
...fields | (string | [string, string])[] |
Returns
null | any[]
SELECTPROPERTY
▸ SELECTPROPERTY(data, field): null | any[]
Returns an array of the specified field from the data array
Example
SELECTPROPERTY([{name: 'John'}, {name: 'Jane'}], 'name') = ['John', 'Jane']
Parameters
| Name | Type | Description |
|---|---|---|
data | any[] | The array of objects to select from |
field | string | The field to select from each object in the array |
Returns
null | any[]
An array of the specified field from each object in the data array
UNIQUE
▸ UNIQUE(...values): any[]
Returns the unique values
Parameters
| Name | Type | Description |
|---|---|---|
...values | any[] | The values to filter for uniqueness |
Returns
any[]
The unique values
Barcode Functions Methods
GS1ENCODE
▸ GS1ENCODE(...data): string
Returns a GS1 barcode string from the provided data
Parameters
| Name | Type | Description |
|---|---|---|
...data | [string, string][] | An array of tuples where each tuple contains a GS1 Application Identifier and its corresponding value |
Returns
string
A string representing the encoded GS1 barcode
Comparison Functions Methods
BETWEEN
▸ BETWEEN(value, minValue, maxValue): boolean
Returns true if value is between minValue and maxValue (inclusive)
Parameters
| Name | Type | Description |
|---|---|---|
value | any | The value to check |
minValue | any | The minimum value |
maxValue | any | The maximum value |
Returns
boolean
Boolean indicating if value is between minValue and maxValue
EQUALS
▸ EQUALS(value1, value2): boolean
Compares two values and returns true if they are equal or equivelent This function is used to compare values in expressions and is not the same as the JavaScript === operator For example, EQUALS(1, '1') will return true
Parameters
| Name | Type | Description |
|---|---|---|
value1 | any | First value to compare |
value2 | any | Second value to compare |
Returns
boolean
Boolean indicating if the two values are equal
GT
▸ GT(value1, value2): boolean
Returns true if value1 is greater than value2
Parameters
| Name | Type | Description |
|---|---|---|
value1 | any | First value to compare |
value2 | any | Second value to compare |
Returns
boolean
Boolean indicating if value1 is greater than value2
GTE
▸ GTE(value1, value2): boolean
Returns true if value1 is greater than or equal to value2
Parameters
| Name | Type | Description |
|---|---|---|
value1 | any | First value to compare |
value2 | any | Second value to compare |
Returns
boolean
Boolean indicating if value1 is greater than or equal to value2
ISEMPTY
▸ ISEMPTY(value): boolean
Returns true is the value is empty/blank
Parameters
| Name | Type |
|---|---|
value | string |
Returns
boolean
ISFALSE
▸ ISFALSE(value): boolean
Returns true is the value is false
Parameters
| Name | Type |
|---|---|
value | BooleanLike |
Returns
boolean
ISNEGATIVE
▸ ISNEGATIVE(value): boolean
Returns true is the value is less than 0
Parameters
| Name | Type |
|---|---|
value | string |
Returns
boolean
ISPOSITIVE
▸ ISPOSITIVE(value): boolean
Returns true is the value is greater than 0
Parameters
| Name | Type |
|---|---|
value | string |
Returns
boolean
ISTRUE
▸ ISTRUE(value): boolean
Returns true is the value is true
Parameters
| Name | Type |
|---|---|
value | BooleanLike |
Returns
boolean
ISZERO
▸ ISZERO(value): boolean
Returns true is the value is 0
Parameters
| Name | Type |
|---|---|
value | string |
Returns
boolean
LT
▸ LT(value1, value2): boolean
Returns true if value1 is less than value2
Parameters
| Name | Type | Description |
|---|---|---|
value1 | any | First value to compare |
value2 | any | Second value to compare |
Returns
boolean
Boolean indicating if value1 is less than value2
LTE
▸ LTE(value1, value2): boolean
Returns true if value1 is less than or equal to value2
Parameters
| Name | Type | Description |
|---|---|---|
value1 | any | First value to compare |
value2 | any | Second value to compare |
Returns
boolean
Boolean indicating if value1 is less than or equal to value2
NOTEMPTY
▸ NOTEMPTY(value): boolean
Returns true is the value is not empty/blank
Parameters
| Name | Type |
|---|---|
value | string |
Returns
boolean
NOTEQUALS
▸ NOTEQUALS(value1, value2): boolean
Compares two values and returns true if they are not equal or equivelent This function is used to compare values in expressions and is not the same as the JavaScript !== operator For example, NOTEQUALS(1, '1') will return false
Parameters
| Name | Type | Description |
|---|---|---|
value1 | any | First value to compare |
value2 | any | Second value to compare |
Returns
boolean
Boolean indicating if the two values are not equal
Date Functions Methods
ADDDAYS
▸ ADDDAYS(date, amount): null | Date
Adds the specified number of days to the given date.
Parameters
| Name | Type |
|---|---|
date | any |
amount | any |
Returns
null | Date
ADDHOURS
▸ ADDHOURS(date, amount): null | Date
Adds the specified number of hours to the given date.
Parameters
| Name | Type |
|---|---|
date | any |
amount | any |
Returns
null | Date
ADDMINUTES
▸ ADDMINUTES(date, amount): null | Date
Adds the specified number of minutes to the given date.
Parameters
| Name | Type |
|---|---|
date | any |
amount | any |
Returns
null | Date
ADDMONTHS
▸ ADDMONTHS(date, amount): null | Date
Adds the specified number of months to the given date.
Example
ADDMONTHS('2023-01-01', 2) = new Date('2023-03-01')
Parameters
| Name | Type | Description |
|---|---|---|
date | any | The date to add months to, can be a Date object or a string in ISO format |
amount | any | The number of months to add, can be a positive or negative integer |
Returns
null | Date
A new Date object with the specified number of months added, or null if the input date is null
ADDSECONDS
▸ ADDSECONDS(date, amount): null | Date
Adds the specified number of seconds to the given date.
Parameters
| Name | Type |
|---|---|
date | any |
amount | any |
Returns
null | Date
ADDWEEKS
▸ ADDWEEKS(date, amount): null | Date
Adds the specified number of weeks to the given date.
Parameters
| Name | Type |
|---|---|
date | any |
amount | any |
Returns
null | Date
ADDYEARS
▸ ADDYEARS(date, amount): null | Date
Adds the specified number of years to the given date.
Example
ADDYEARS('2023-01-01', 2) = new Date('2025-01-01')
Parameters
| Name | Type | Description |
|---|---|---|
date | any | The date to add years to, can be a Date object or a string in ISO format |
amount | any | The number of years to add, can be a positive or negative integer |
Returns
null | Date
A new Date object with the specified number of years added, or null if the input date is null
DAY
▸ DAY(date): null | number
Returns the day component of a date
Parameters
| Name | Type | Description |
|---|---|---|
date | any | The date to get the day from, can be a Date object or a string in ISO format |
Returns
null | number
The day component of the date, or null if the date is null
DIFFDAYS
▸ DIFFDAYS(endDate, startDate): null | number
Subtracts the second date from the first date and returns the number of days difference e.g. DIFFDAYS('2023-01-05', '2023-01-01') = 4
Example
DIFFDAYS('2023-01-05', '2023-01-01') = 4
Parameters
| Name | Type | Description |
|---|---|---|
endDate | any | The end date to subtract from, can be a Date object or a string in ISO format |
startDate | any | The start date to subtract, can be a Date object or a string in ISO format |
Returns
null | number
The number of days difference between the two dates, or null if either date is null
DIFFHOURS
▸ DIFFHOURS(endDate, startDate): null | number
Subtracts the second date from the first date and returns the number of hours difference e.g. DIFFHOURS('2023-01-01 05:01:01', '2023-01-01 00:00:00') = 5
Example
DIFFHOURS('2023-01-01 05:01:01', '2023-01-01 00:00:00') = 5
Parameters
| Name | Type | Description |
|---|---|---|
endDate | any | The end date to subtract from, can be a Date object or a string in ISO format |
startDate | any | The start date to subtract, can be a Date object or a string in ISO format |
Returns
null | number
The number of hours difference between the two dates, or null if either date is null
DIFFMINUTES
▸ DIFFMINUTES(endDate, startDate): null | number
Subtracts the second date from the first date and returns the number of minutes difference e.g. DIFFMINUTES('2023-01-01 00:01:02', '2023-01-01 00:00:00') = 1
Example
DIFFMINUTES('2023-01-01 00:01:02', '2023-01-01 00:00:00') = 1
Parameters
| Name | Type | Description |
|---|---|---|
endDate | any | The end date to subtract from, can be a Date object or a string in ISO format |
startDate | any | The start date to subtract, can be a Date object or a string in ISO format |
Returns
null | number
The number of minutes difference between the two dates, or null if either date is null
DIFFMONTHS
▸ DIFFMONTHS(endDate, startDate): null | number
Subtracts the second date from the first date and returns the number of months difference e.g. DIFFMONTHS('2023-03-05', '2023-01-01') = 2
Example
DIFFMONTHS('2023-03-05', '2023-01-01') = 2
Parameters
| Name | Type | Description |
|---|---|---|
endDate | any | The end date to subtract from, can be a Date object or a string in ISO format |
startDate | any | The start date to subtract, can be a Date object or a string in ISO format |
Returns
null | number
The number of months difference between the two dates, or null if either date is null
DIFFSECONDS
▸ DIFFSECONDS(endDate, startDate): null | number
Subtracts the second date from the first date and returns the number of seconds difference e.g. DIFFSECONDS('2023-01-01 00:01:02', '2023-01-01 00:00:00') = 62
Example
DIFFSECONDS('2023-01-01 00:01:02', '2023-01-01 00:00:00') = 62
Parameters
| Name | Type | Description |
|---|---|---|
endDate | any | The end date to subtract from, can be a Date object or a string in ISO format |
startDate | any | The start date to subtract, can be a Date object or a string in ISO format |
Returns
null | number
The number of seconds difference between the two dates, or null if either date is null
DIFFTIME
▸ DIFFTIME(endDate, startDate): null | string
Subtracts the second date from the first date and returns the time diff in the format [days].HH.MM:SS
Example
DIFFTIME('2023-01-01 00:01:02', '2023-01-01 00:00:00') = '0.00.01:02'
Parameters
| Name | Type | Description |
|---|---|---|
endDate | any | The end date to subtract from, can be a Date object or a string in ISO format |
startDate | any | The start date to subtract, can be a Date object or a string in ISO format |
Returns
null | string
The time difference between the two dates in the format [days].HH.MM:SS, or null if either date is null
DIFFWEEKDAYS
▸ DIFFWEEKDAYS(endDate, startDate): null | number
Subtracts the second date from the first date and returns the number of weekdays difference e.g. DIFFWEEKDAYS('2023-01-05', '2023-01-01') = 3
Example
DIFFWEEKDAYS('2023-01-05', '2023-01-01') = 3
Parameters
| Name | Type | Description |
|---|---|---|
endDate | any | The end date to subtract from, can be a Date object or a string in ISO format |
startDate | any | The start date to subtract, can be a Date object or a string in ISO format |
Returns
null | number
The number of weekdays difference between the two dates, or null if either date is null
DIFFWEEKS
▸ DIFFWEEKS(endDate, startDate): null | number
Subtracts the second date from the first date and returns the number of weeks difference e.g. DIFFWEEKS('2023-01-01', '2023-01-17') = 2
Example
DIFFWEEKS('2023-01-01', '2023-01-17') = 2
Parameters
| Name | Type | Description |
|---|---|---|
endDate | any | The end date to subtract from, can be a Date object or a string in ISO format |
startDate | any | The start date to subtract, can be a Date object or a string in ISO format |
Returns
null | number
The number of weeks difference between the two dates, or null if either date is null
DIFFYEARS
▸ DIFFYEARS(endDate, startDate): null | number
Subtracts the second date from the first date and returns the number of years difference e.g. DIFFYEARS('2025-01-05', '2023-01-01') = 2
Example
DIFFYEARS('2025-01-05', '2023-01-01') = 2
Parameters
| Name | Type | Description |
|---|---|---|
endDate | any | The end date to subtract from, can be a Date object or a string in ISO format |
startDate | any | The start date to subtract, can be a Date object or a string in ISO format |
Returns
null | number
The number of years difference between the two dates, or null if either date is null
ENDOFDAY
▸ ENDOFDAY(date): null | Date
Returns the end of the day for a given date and time
Parameters
| Name | Type | Description |
|---|---|---|
date | any | The date to get the end of day for, can be a Date object or a string in ISO format |
Returns
null | Date
The end of day date, or null if the input date is null
HOURS
▸ HOURS(date): null | number
Returns the hour component of a date
Parameters
| Name | Type | Description |
|---|---|---|
date | any | The date to get the hour from, can be a Date object or a string in ISO format |
Returns
null | number
The hour component of the date, or null if the date is null
ISAFTER
▸ ISAFTER(date, dateToCompare): boolean
Returns true if the first date is after the second one
Example
ISAFTER('2023-01-02', '2023-01-01') = true
Parameters
| Name | Type | Description |
|---|---|---|
date | DateLike | The date to compare |
dateToCompare | DateLike | The date to compare against |
Returns
boolean
True if the first date is after the second date, false otherwise
ISBEFORE
▸ ISBEFORE(date, dateToCompare): boolean
Returns true if the first date is before the second one
Example
ISBEFORE('2023-01-01', '2023-01-02') = true
Parameters
| Name | Type | Description |
|---|---|---|
date | DateLike | The date to compare |
dateToCompare | DateLike | The date to compare against |
Returns
boolean
True if the first date is before the second date, false otherwise
ISDATE
▸ ISDATE(value): boolean
Returns true if the value is a valid date, false otherwise
Parameters
| Name | Type | Description |
|---|---|---|
value | any | The value to check if it is a date |
Returns
boolean
True if the value is a valid date, false otherwise
ISFUTURE
▸ ISFUTURE(date): boolean
Returns true if the date is in the future
Parameters
| Name | Type | Description |
|---|---|---|
date | DateLike | The date to check |
Returns
boolean
True if the date is in the future, false otherwise
ISPAST
▸ ISPAST(date): boolean
Returns true if the date is in the past
Parameters
| Name | Type | Description |
|---|---|---|
date | DateLike | The date to check |
Returns
boolean
True if the date is in the past, false otherwise
JULIANDAY
▸ JULIANDAY(date): null | number
Returns the Julian day number for a given date
Parameters
| Name | Type | Description |
|---|---|---|
date | any | The date to get the Julian day number for, can be a Date object or a string in ISO format |
Returns
null | number
The Julian day number, or null if the date is null
LOCALTOUTC
▸ LOCALTOUTC(date): null | Date
Converts a local date to a UTC date based on the time zone set in the context
Parameters
| Name | Type | Description |
|---|---|---|
date | any | The local date to convert, can be a Date object or a string in ISO format |
Returns
null | Date
The UTC date, or null if the input date is null
MAXDATE
▸ MAXDATE(...values): Date
Returns the latest of the given dates.
Example
MAXDATE('2023-01-01', '2023-01-02') = new Date('2023-01-02')
Example
MAXDATE('2023-01-01', '2023-01-02', '2023-01-03') = new Date('2023-01-03')
Example
MAXDATE(null, '2023-01-02', null) = new Date('2023-01-02')
Parameters
| Name | Type | Description |
|---|---|---|
...values | any[] | The dates to compare, can be Date objects or strings in ISO format |
Returns
Date
The latest date from the provided values, or null if all values are null
MINDATE
▸ MINDATE(...values): Date
Returns the earliest of the given dates.
Example
MINDATE('2023-01-01', '2023-01-02') = new Date('2023-01-01')
Example
MINDATE('2023-01-01', '2023-01-02', '2023-01-03') = new Date('2023-01-01')
Example
MINDATE(null, '2023-01-02', null) = new Date('2023-01-02')
Parameters
| Name | Type | Description |
|---|---|---|
...values | any[] | The dates to compare, can be Date objects or strings in ISO format |
Returns
Date
The earliest date from the provided values, or null if all values are null
MINUTES
▸ MINUTES(date): null | number
Returns the minutes component of a date
Parameters
| Name | Type | Description |
|---|---|---|
date | any | The date to get the minutes from, can be a Date object or a string in ISO format |
Returns
null | number
The minutes component of the date, or null if the date is null
MONTH
▸ MONTH(date): null | number
Returns the month component of a date
Parameters
| Name | Type | Description |
|---|---|---|
date | any | The date to get the month from, can be a Date object or a string in ISO format |
Returns
null | number
The month component of the date, or null if the date is null
NOW
▸ NOW(): Date
Returns the current date and time
Returns
Date
PARSEDATE
▸ PARSEDATE(date): null | Date
Converts a value into a date object
Parameters
| Name | Type | Description |
|---|---|---|
date | any | The value to convert into a date, can be a string in ISO format or a Date object |
Returns
null | Date
A Date object representing the parsed date, or null if the input is null
PARSEDATEANDTIME
▸ PARSEDATEANDTIME(date, time): null | Date
Parses a date and time string into a Date object
Example
PARSEDATEANDTIME('2023-01-01', '12:00:00') = new Date('2023-01-01T12:00:00Z')
Parameters
| Name | Type | Description |
|---|---|---|
date | DateLike | The date to parse, can be a string or Date object |
time | string | The time to parse, can be a string in the format HH:MM:SS |
Returns
null | Date
A Date object representing the parsed date and time, or null if parsing fails
SECONDS
▸ SECONDS(date): null | number
Returns the seconds component of a date
Parameters
| Name | Type | Description |
|---|---|---|
date | any | The date to get the seconds from, can be a Date object or a string in ISO format |
Returns
null | number
The seconds component of the date, or null if the date is null
SECONDSTOTIME
▸ SECONDSTOTIME(seconds): null | string
Converts a number of seconds into a time string in the format HH:MM:SS
Example
SECONDSTOTIME(3661) = '01:01:01'
Example
SECONDSTOTIME(1800) = '00:30:00'
Parameters
| Name | Type | Description |
|---|---|---|
seconds | null | number | The number of seconds to convert, can be a number or null |
Returns
null | string
A time string in the format HH:MM:SS representing the input seconds, or null if the input is null
STARTOFDAY
▸ STARTOFDAY(date): null | Date
Returns the start of the day for a given date and time
Parameters
| Name | Type |
|---|---|
date | any |
Returns
null | Date
SUBTIMES
▸ SUBTIMES(time1, time2): null | string
Subtracts the second time string from the first time string in the format HH:MM:SS and returns the result as a time string in the same format
Example
SUBTIMES('02:00:00', '01:30:00') = '00:30:00'
Example
SUBTIMES('01:00:00', '00:45:00') = '00:15:00'
Parameters
| Name | Type | Description |
|---|---|---|
time1 | null | string | The first time string, can be in the format HH:MM:SS or null |
time2 | null | string | The second time string to subtract from the first, can be in the format HH:MM:SS or null |
Returns
null | string
A time string in the format HH:MM:SS representing the result of the subtraction, or null if either input is null
SUMTIMES
▸ SUMTIMES(...times): null | string
Sums multiple time strings in the format HH:MM:SS and returns the total as a time string in the same format
Example
SUMTIMES('01:00:00', '02:30:00', '00:45:00') = '04:15:00'
Example
SUMTIMES('00:30:00', '00:45:00') = '01:15:00'
Example
SUMTIMES(null, '01:00:00', null) = '01:00:00'
Parameters
| Name | Type | Description |
|---|---|---|
...times | (null | string)[] | The time strings to sum, can be in the format HH:MM:SS or null |
Returns
null | string
A time string in the format HH:MM:SS representing the total sum of the input times, or null if all inputs are null
TIMETOSECONDS
▸ TIMETOSECONDS(time): null | number
Converts a time string in the format HH:MM:SS to the total number of seconds
Example
TIMETOSECONDS('01:01:01') = 3661
Example
TIMETOSECONDS('00:30:00') = 1800
Parameters
| Name | Type | Description |
|---|---|---|
time | null | string | The time string to convert, can be in the format HH:MM:SS or null |
Returns
null | number
The total number of seconds represented by the time string, or null if the input is null
TODAY
▸ TODAY(): Date
Returns the start of the current day
Returns
Date
UTCTOLOCAL
▸ UTCTOLOCAL(date): null | Date
Converts a UTC date to a local date based on the time zone set in the context
Parameters
| Name | Type | Description |
|---|---|---|
date | any | The UTC date to convert, can be a Date object or a string in ISO format |
Returns
null | Date
The local date, or null if the input date is null
WEEK
▸ WEEK(date, weekStartsOn?): null | number
Returns the week of the year of a date
Parameters
| Name | Type | Description |
|---|---|---|
date | any | The date to get the year from, can be a Date object or a string in ISO format |
weekStartsOn? | any | - |
Returns
null | number
The year component of the date, or null if the date is null
YEAR
▸ YEAR(date): null | number
Returns the year component of a date
Parameters
| Name | Type | Description |
|---|---|---|
date | any | The date to get the year from, can be a Date object or a string in ISO format |
Returns
null | number
The year component of the date, or null if the date is null
Logical Functions Methods
IF
▸ IF(logicalTest, valueIfTrue, valueIfFalse): any
Parameters
| Name | Type |
|---|---|
logicalTest | any |
valueIfTrue | any |
valueIfFalse | any |
Returns
any
SWITCH
▸ SWITCH(valueToSwitch, valuesToMatchAndReturn, defaultValue?): any
Returns a value based on a value Example: SWITCH('g', [['r','Red'], ['g','Green'], ['b','Blue']], 'Other') = 'Green'
Argument
valueToSwitch The input value
Argument
valuesToMatchAndReturn An array of key value pairs to match with the valueToSwitch and return
Parameters
| Name | Type |
|---|---|
valueToSwitch | any |
valuesToMatchAndReturn | [any, any][] |
defaultValue? | any |
Returns
any
Math Functions Methods
ABS
▸ ABS(number): null | number
Returns the absolute value of a number. The absolute value of a number is the number without its sign.
Example
ABS(-5) = 5
Example
ABS(5) = 5
Parameters
| Name | Type | Description |
|---|---|---|
number | any | The number to get the absolute value of, can be a number or null |
Returns
null | number
The absolute value of the number, or null if the input is null
CEILING
▸ CEILING(number): null | number
Rounds a number up to a whole number
Example
CEILING(5.2) = 6
Example
CEILING(5.8) = 6
Parameters
| Name | Type | Description |
|---|---|---|
number | any | The number to round up, can be a number or null |
Returns
null | number
The rounded up number, or null if the input is null
FLOOR
▸ FLOOR(number): null | number
Rounds a number down to a whole number
Example
FLOOR(5.2) = 5
Example
FLOOR(5.8) = 5
Parameters
| Name | Type | Description |
|---|---|---|
number | any | The number to round down, can be a number or null |
Returns
null | number
The rounded down number, or null if the input is null
ISNUMERIC
▸ ISNUMERIC(number): boolean
Returns true if the value is a valid number
Example
ISNUMERIC(5) = true
Example
ISNUMERIC('5') = true
Example
ISNUMERIC('abc') = false
Parameters
| Name | Type | Description |
|---|---|---|
number | any | The value to check if it is numeric, can be a number, string, or null |
Returns
boolean
True if the value is numeric, false otherwise
ROUND
▸ ROUND(number, numberOfDigits?): null | number
Rounds a value to the nearest whole number or number of decimal places if specified
Example
ROUND(5.2) = 5
Example
ROUND(5.8) = 6
Example
ROUND(5.12345, 2) = 5.12
Example
ROUND(5.6789, 3) = 5.679
Parameters
| Name | Type | Description |
|---|---|---|
number | any | The number to round, can be a number or null |
numberOfDigits? | number | The number of decimal places to round to, defaults to 0 (whole number) |
Returns
null | number
The rounded number, or null if the input is null
Other Methods
DAYOFWEEK
▸ DAYOFWEEK(date, weekStartsOn?): null | number
Returns the day of the week for a given date
Parameters
| Name | Type | Description |
|---|---|---|
date | any | The date to get the day of the week for, can be a Date object or a string in ISO format |
weekStartsOn? | any | The day of the week to start counting from, can be a number (0-6) or a string representing the day (e.g. 'SUNDAY', 'MONDAY', etc.). Defaults to 0 (Sunday). |
Returns
null | number
The day of the week as a number (0-6), or null if the date is null. 0 represents Sunday, 1 represents Monday, and so on.
ENDOFMONTH
▸ ENDOFMONTH(date): null | Date
Returns the end of the month for a given date
Parameters
| Name | Type | Description |
|---|---|---|
date | any | The date to get the end of month for, can be a Date object or a string in ISO format |
Returns
null | Date
The end of month date, or null if the input date is null
ENDOFWEEK
▸ ENDOFWEEK(date, weekStartsOn?): null | Date
Returns the end of the week for a given date
Parameters
| Name | Type | Description |
|---|---|---|
date | any | The date to get the end of the week for, can be a Date object or a string in ISO format |
weekStartsOn? | any | The day of the week to start counting from, can be a number (0-6) or a string representing the day (e.g. 'SUNDAY', 'MONDAY', etc.). Defaults to 0 (Sunday). |
Returns
null | Date
The end of the week as a Date object, or null if the date is null. The returned date will be the last day of the week based on the specified weekStartsOn value.
STARTOFMONTH
▸ STARTOFMONTH(date): null | Date
Returns the start of the month for a given date
Parameters
| Name | Type | Description |
|---|---|---|
date | any | The date to get the start of month for, can be a Date object or a string in ISO format |
Returns
null | Date
The start of month date, or null if the input date is null
STARTOFWEEK
▸ STARTOFWEEK(date, weekStartsOn?): null | Date
Returns the start of the week for a given date
Parameters
| Name | Type | Description |
|---|---|---|
date | any | The date to get the start of the week for, can be a Date object or a string in ISO format |
weekStartsOn? | any | The day of the week to start counting from, can be a number (0-6) or a string representing the day (e.g. 'SUNDAY', 'MONDAY', etc.). Defaults to 0 (Sunday). |
Returns
null | Date
The start of the week as a Date object, or null if the date is null. The returned date will be the first day of the week based on the specified weekStartsOn value.
Text Functions Methods
CONCAT
▸ CONCAT(...values): string
Joins the values together without a seperator
Parameters
| Name | Type |
|---|---|
...values | any[] |
Returns
string
ESCAPE
▸ ESCAPE(value): string
Escapes a string for use in a JSON context
Parameters
| Name | Type | Description |
|---|---|---|
value | string | The string to escape |
Returns
string
The escaped string
FORCELINEBREAKS
▸ FORCELINEBREAKS(value): string
Replaces all line breaks in the string with double line breaks
Parameters
| Name | Type | Description |
|---|---|---|
value | string | The string to modify |
Returns
string
The string with double line breaks
FORMAT
▸ FORMAT(value, format): string
Formats a value as a string using the specified format The format can be a date format, number format or a boolean format
Parameters
| Name | Type | Description |
|---|---|---|
value | string | number | Date | The value to format, can be a string, number or date |
format | string | The format string, can be a date format, number format or a boolean format |
Returns
string
The formatted string
ISEMAIL
▸ ISEMAIL(value): boolean
Returns true is the value is a valid email address
Parameters
| Name | Type |
|---|---|
value | string |
Returns
boolean
JOIN
▸ JOIN(seperator, ...values): string
Joins the values together with the seperator
Parameters
| Name | Type |
|---|---|
seperator | string |
...values | any[] |
Returns
string
LEFT
▸ LEFT(text, numberOfchars): string
Returns specified number of charachters the left hand side of a text string
Example
LEFT('Hello World', 5) = 'Hello'
Parameters
| Name | Type | Description |
|---|---|---|
text | string | The text to extract from |
numberOfchars | number | The number of characters to extract |
Returns
string
The leftmost characters of the text string
LEN
▸ LEN(text): number
Retruns the length of a text string
Parameters
| Name | Type |
|---|---|
text | string |
Returns
number
LIKE
▸ LIKE(value, like, caseSensitive?): boolean
Compare the value string to the like string and returns true if it is a match Case insensitve by default Wildcards: ? = Single character, # = Single number character, * = Zero or more characters,
Parameters
| Name | Type | Default value |
|---|---|---|
value | string | undefined |
like | string | undefined |
caseSensitive | boolean | false |
Returns
boolean
LOWER
▸ LOWER(text): string
Converts a text string to lower case
Parameters
| Name | Type | Description |
|---|---|---|
text | string | The text to convert to lower case |
Returns
string
The lower case version of the text
MATCH
▸ MATCH(value, pattern): boolean
Returns true if the value matches the pattern The pattern must contain a regular expression
Parameters
| Name | Type | Description |
|---|---|---|
value | string | The string to match |
pattern | string | The regular expression pattern to match against |
Returns
boolean
True if the value matches the whole pattern, false otherwise
MID
▸ MID(text, startFrom, numberOfchars): string
Retruns a section of a text string starting from the specified position
Parameters
| Name | Type | Description |
|---|---|---|
text | string | The text to extract from |
startFrom | number | The position to start extracting from (1 based index) |
numberOfchars | number | The number of characters to extract |
Returns
string
The extracted section of the text
NEWID
▸ NEWID(): string
Retruns a globally unique identifier
Returns
string
A globally unique identifier
NUMBERVALUE
▸ NUMBERVALUE(text, defaultValue?): null | number
Converts a text string to a number
Parameters
| Name | Type | Description |
|---|---|---|
text | string | The text to convert to a number |
defaultValue? | null | number | - |
Returns
null | number
The number value of the text, or null if it cannot be converted *
PADEND
▸ PADEND(value, length, filler): string
Returns a string padded with the specified filler to the end of the specified length If the value is longer than the length, it will be returned as is
Parameters
| Name | Type | Description |
|---|---|---|
value | string | The string to pad |
length | number | The length to pad to |
filler | string | The string to pad with, defaults to a space |
Returns
string
The padded string
PADSTART
▸ PADSTART(value, length, filler): string
Returns a string padded with the specified filler to the start of the specified length If the value is longer than the length, it will be returned as is
Parameters
| Name | Type | Description |
|---|---|---|
value | string | The string to pad |
length | number | The length to pad to |
filler | string | The string to pad with, defaults to a space |
Returns
string
The padded string
REPLACE
▸ REPLACE(text, start, length, newText): string
Replaces a section of a text string with new text
Parameters
| Name | Type | Description |
|---|---|---|
text | string | The text to replace in |
start | any | The position to start replacing from (1 based index) |
length | any | The number of characters to replace |
newText | any | The text to replace with |
Returns
string
The text with the specified section replaced
RIGHT
▸ RIGHT(text, numberOfchars): string
Retruns specified number of charachters the right hand side of a text string
Example
RIGHT('Hello World', 5) = 'World'
Parameters
| Name | Type | Description |
|---|---|---|
text | string | The text to extract from |
numberOfchars | number | The number of characters to extract |
Returns
string
The rightmost characters of the text string
SPLIT
▸ SPLIT(seperator, value): string[]
Splits a string into an array of strings using a seperator
Parameters
| Name | Type | Description |
|---|---|---|
seperator | string | The string to split the value by |
value | string | The string to split |
Returns
string[]
SUBSTITUTE
▸ SUBSTITUTE(text, oldText, newText): string
Substitutes all instances of oldText in text with newText
Parameters
| Name | Type | Description |
|---|---|---|
text | string | The text to search in |
oldText | string | The text to replace |
newText | string | The text to replace with |
Returns
string
The text with the oldText replaced by newText
TRIM
▸ TRIM(value): string
Removes whitespace from both ends of a string
Parameters
| Name | Type | Description |
|---|---|---|
value | any | The string to trim |
Returns
string
The trimmed string
TRIMEND
▸ TRIMEND(value): string
Removes whitespace from the end of a string
Parameters
| Name | Type | Description |
|---|---|---|
value | any | The string to trim |
Returns
string
The trimmed string
TRIMSTART
▸ TRIMSTART(value): string
Removes whitespace from the start of a string
Parameters
| Name | Type | Description |
|---|---|---|
value | any | The string to trim |
Returns
string
The trimmed string
UPPER
▸ UPPER(text): string
Converts a text string to upper case
Parameters
| Name | Type | Description |
|---|---|---|
text | string | The text to convert to upper case |
Returns
string
The upper case version of the text