Formula module
Handle specific formulae that can be used inside computed fields.
Methods
# static ADJUST_DATE(date, yearsopt, monthsopt, daysopt, hoursopt, minutesopt, secondsopt, formatopt) → {string}
Adjust a date to a new date, passing the number of years, months, days, hours, minutes and seconds to add or subtract. If the hours, minutes and seconds are not specified, they are set to 0.
Parameters:
Name | Type | Attributes | Description |
---|---|---|---|
date |
date
|
string
|
Date or ISO date string like "2023-12-01" |
|
years |
number
|
<optional> |
|
months |
number
|
<optional> |
|
days |
number
|
<optional> |
|
hours |
number
|
<optional> |
|
minutes |
number
|
<optional> |
|
seconds |
number
|
<optional> |
|
format |
string
|
<optional> |
"string" (default) or "date" to return a date object |
The adjusted date, as an ISO string like "2023-01-01"
string
Example
ADJUST_DATE("2023-01-01", 0, 1, 0) // "2023-02-01"
ADJUST_DATE("2023-01-01", 0, 0, 0, 48, 0, 0) // "2023-01-03"
# static ADJUST_DATE_AND_TIME(date, yearsopt, monthsopt, daysopt, hoursopt, minutesopt, secondsopt, formatopt) → {string|date}
Adjust a date to a new date and time, passing the number of years, months, days, hours, minutes and seconds to add or subtract. If the hours, minutes and seconds are not specified, they are set to 0.
Parameters:
Name | Type | Attributes | Description |
---|---|---|---|
date |
date
|
string
|
Date or ISO date string like "2023-12-01" |
|
years |
number
|
<optional> |
|
months |
number
|
<optional> |
|
days |
number
|
<optional> |
|
hours |
number
|
<optional> |
|
minutes |
number
|
<optional> |
|
seconds |
number
|
<optional> |
|
format |
string
|
<optional> |
"string" (default) or "date" to return a date object |
The adjusted date and time, as an ISO string like "2023-01-01 12:34:56" or a date object
string
|
date
Example
ADJUST_DATE_AND_TIME("2023-01-01", 0, 1, 0) // "2023-02-01 00:00:00"
ADJUST_DATE_AND_TIME("2023-01-01", 0, 1, 0, 3, 0, 0) // "2023-02-01 03:00:00"
ADJUST_DATE_AND_TIME("2023-01-01 03:45:00", 0, 1, 0, 3, 0, 0) // "2023-02-01 06:45:00"
ADJUST_DATE_AND_TIME(new Date(), 0, 1, 0, 3, 0, 0) // "2023-01-01 06:45:00"
# static ARRAY(values) → {Array.<string>|Array.<number>|Array.<date>}
Converts a list of values into an array, to feed MULTI-VALUE fields.
Parameters:
Name | Type | Description |
---|---|---|
values |
*
|
Example
ARRAY( "a", "b", "c" ) // [ "a", "b", "c" ]
# static AVERAGE(…numbers) → {number}
AVERAGE
Parameters:
Name | Type | Attributes | Description |
---|---|---|---|
numbers |
any
|
<repeatable> |
number
Example
AVERAGE(10, 20, 30) // 20
# static CONCATENATE(…strings) → {string}
Concatenate any number of strings
Parameters:
Name | Type | Attributes | Description |
---|---|---|---|
strings |
any
|
<repeatable> |
string
Example
CONCATENATE("Bob", " ", "Wilson") // "Bob Wilson"
# static CONTAINS(string, value) → {boolean}
Check if a string contains a value
Parameters:
Name | Type | Description |
---|---|---|
string |
string
|
|
value |
string
|
boolean
Example
CONTAINS("San Francisco", "San") // true
CONTAINS("Paris", "San") // false
# static COS(number) → {number}
Returns the COSINUS of a number
Parameters:
Name | Type | Description |
---|---|---|
number |
number
|
number
Example
COS(2 * PI()) // 1
# static COUNT(…items) → {number}
COUNT the number of items passed as parameters
Parameters:
Name | Type | Attributes | Description |
---|---|---|---|
items |
any
|
<repeatable> |
number
Example
COUNT(1, "B", "C", 7) // 4
# static COUNT_EMPTY(…items) → {number}
COUNT the number of empty items passed as parameters
Parameters:
Name | Type | Attributes | Description |
---|---|---|---|
items |
any
|
<repeatable> |
number
Example
COUNT_EMPTY(1, "B", "C", 7, "", []) // 2
# static COUNT_NON_EMPTY(…items) → {number}
COUNT the number of non-empty items passed as parameters
Parameters:
Name | Type | Attributes | Description |
---|---|---|---|
items |
any
|
<repeatable> |
number
Example
COUNT_NON_EMPTY(1, "B", "C", 7, "", []) // 4
# static DAY(strDateISO) → {string}
Get the DAY of an ISO date
Parameters:
Name | Type | Description |
---|---|---|
strDateISO |
string
|
string
Example
DAY("2022-12-24") // "24"
# static DAYS_DIFFERENCE(fromISODate, toISODate) → {integer}
Compute the number of days between 2 dates
Parameters:
Name | Type | Description |
---|---|---|
fromISODate |
string
|
As an ISO date string like "2023-02-14T15:44:05.886Z" or "2023-02-14" |
toISODate |
string
|
As an ISO date string like "2023-02-14T15:44:05.886Z" or "2023-02-14" |
Number of days
integer
Example
DAYS_DIFFERENCE("2023-01-01T15:44:05.886Z", "2023-01-15T18:44:26.316Z") // 14
DAYS_DIFFERENCE("2023-01-01", "2023-01-10") // 9
# static FORMAT_DATE(date, format) → {string}
Convert a date to a formatted string
Parameters:
Name | Type | Description |
---|---|---|
date |
date
|
string
|
Date or ISO date string like "2023-12-01" |
format |
string
|
Ex: "yyyy-mm-dd", "yyyy-mm-dd hh:MM:ss", "yyyy-mm-dd hh:MM", "yy-mm-dd hh:MM" |
string
Example
FORMAT_DATE(new Date(), "yyyy-mm-dd") // "2023-01-01"
FORMAT_DATE(new Date(), "yyyy-mm-dd hh:MM:ss") // "2023-01-01 00:00:00"
FORMAT_DATE("2023-12-31", "yy-mm-dd") // "23-12-31"
# static HOURS_DIFFERENCE(fromISODate, toISODate) → {integer}
Compute the number of hours between 2 dates
Parameters:
Name | Type | Description |
---|---|---|
fromISODate |
string
|
|
toISODate |
string
|
Number of hours
integer
Example
HOURS_DIFFERENCE("2023-01-01T15:00:00.000Z", "2023-01-02T16:00:00.000Z") // 25
# static IF(…params) → {any}
Test a set of conditions, and returns the value of the first expression that matches the test. If no condition matches, returns the value of the last (default) expression. Always has an odd number of parameters:
IF(<condition 1>, <expression 1>, ..., ..., <condition N>, <expression N>, <expression Else>)
Parameters:
Name | Type | Attributes | Description |
---|---|---|---|
params |
any
|
<repeatable> |
Value of the first expression that matches the condition
any
Example
// Returns "Good" if the field "amount" = 65, or "Poor" if the field "amout" = 20
IF({{amount}} > 80, "Great", {{amount}} > 60, "Good", {{amount}} > 40, "Not bad", "Poor")
# static IS_EMPTY(value) → {boolean}
Check if a field value is empty
Parameters:
Name | Type | Description |
---|---|---|
value |
*
|
boolean
Example
IS_EMPTY([0, 1, 2, 3]) // false
IS_EMPTY([]) // true
IS_EMPTY("abc") // false
IS_EMPTY("") // true
IS_EMPTY(123) // false
IS_EMPTY(0) // false
# static IS_NOT_EMPTY(value) → {boolean}
Check if a field value is not empty
Parameters:
Name | Type | Description |
---|---|---|
value |
*
|
boolean
Example
IS_NOT_EMPTY([0, 1, 2, 3]) // true
IS_NOT_EMPTY([]) // false
IS_NOT_EMPTY("abc") // true
IS_NOT_EMPTY("") // false
IS_NOT_EMPTY(123) // true
IS_NOT_EMPTY(0) // true
# static IS_WORKFLOW_STARTED(stepName) → {boolean}
Check if a workflow has started
IMPORTANT: only valid on the client, for "hideWhen" formulae
Parameters:
Name | Type | Description |
---|---|---|
stepName |
string
|
true if the given step name is the current step
boolean
# static IS_WORKFLOW_STEP(stepName) → {boolean}
Check the current workflow step
IMPORTANT: only valid on the client, for "hideWhen" formulae
Parameters:
Name | Type | Description |
---|---|---|
stepName |
string
|
true if the given step name is the current step
boolean
# static JOIN(array, separator) → {string}
Join an array of strings into a single string, given a separator
Parameters:
Name | Type | Description |
---|---|---|
array |
Array.<string>
|
|
separator |
string
|
The resulting string
string
Example
JOIN(["Paris", "San Diego", "Ajaccio"], " & ") // "Paris & San Diego & Ajaccio"
# static LEFT(text, n) → {text}
Returns the left part of a string
Parameters:
Name | Type | Description |
---|---|---|
text |
string
|
|
n |
number
|
text
Example
LEFT("San Francisco", 3) // San
# static LENGTH(array) → {number}
Returns the LENGTH of an array or a string
Parameters:
Name | Type | Description |
---|---|---|
array |
array
|
string
|
number
Example
LENGTH([0, 1, 2, 3]) // 4
LENGTH("Satori") // 6
# static LIST(…strings) → {string}
Concatenate any number of strings as a comma separated text
Parameters:
Name | Type | Attributes | Description |
---|---|---|---|
strings |
any
|
<repeatable> |
string
Example
LIST("A", "B", "C") // "A, B, C"
# static LIST_NAMES(…names) → {Array.<string>}
Merge a list of names into an array of names
Parameters:
Name | Type | Attributes | Description |
---|---|---|---|
names |
string
|
<repeatable> |
Array.<string>
Example
LIST_NAMES("John", "Bob", "Steve") // ["John, Bob, Steve"]
# static LOWERCASE(text) → {text}
Convert a string to lowercase
Parameters:
Name | Type | Description |
---|---|---|
text |
string
|
text
Example
LOWERCASE("San Francisco") // san francisco
# static MAX(…numbers) → {number}
MAX
Parameters:
Name | Type | Attributes | Description |
---|---|---|---|
numbers |
any
|
<repeatable> |
number
Example
MAX(42, 666, 1515, 7) // 1515
# static MIDDLE(text, n) → {text}
Returns the middle part of a string
Parameters:
Name | Type | Description |
---|---|---|
text |
string
|
|
n |
number
|
text
Example
MIDDLE("San Francisco", 4, 8) // Fran
# static MIN(…numbers) → {number}
MIN
Parameters:
Name | Type | Attributes | Description |
---|---|---|---|
numbers |
any
|
<repeatable> |
number
Example
MIN(42, 666, 1515, 7) // 7
# static MONTH(strDateISO) → {string}
Get the MONTH of an ISO date
Parameters:
Name | Type | Description |
---|---|---|
strDateISO |
string
|
string
Example
MONTH("2022-12-24") // "12"
# static NOW() → {string}
Get the current date and time as a simple readable string (without time shift)
string
Example
NOW() // "2022-12-24 14:53:28"
# static NTH(array, index) → {*}
Returns the NTH element of an array or a string
Parameters:
Name | Type | Description |
---|---|---|
array |
array
|
string
|
|
index |
number
|
*
Example
NTH(["low", "medium", "high"], 1) // "medium"
# static POW(number, power) → {number}
POW
Parameters:
Name | Type | Description |
---|---|---|
number |
number
|
|
power |
number
|
number
Example
POW(4, 2) // 16
# static REPLACE(text, oldText, newText) → {text}
Replace a string inside another string
Parameters:
Name | Type | Description |
---|---|---|
text |
string
|
|
oldText |
string
|
|
newText |
string
|
text
Example
REPLACE("New York is great", "New York", "Paris") // Paris is great
# static RIGHT(text, n) → {text}
Returns the right part of a string
Parameters:
Name | Type | Description |
---|---|---|
text |
string
|
|
n |
number
|
text
Example
RIGHT("San Francisco", 9) // Francisco
# static ROUND(number, precision) → {number}
ROUND
Parameters:
Name | Type | Description |
---|---|---|
number |
number
|
|
precision |
number
|
number
Example
ROUND(12.367891, 3) // 12.378
# static SIN(number) → {number}
Returns the SINUS of a number
Parameters:
Name | Type | Description |
---|---|---|
number |
number
|
number
Example
SIN(PI() / 2) // 1
# static SLUG(title) → {string}
Generates a slug from a plain title
Parameters:
Name | Type | Description |
---|---|---|
title |
string
|
The generated slug
string
Example
SLUG("My article about dogs") // Returns "my-article-about-dogs"
# static SPLIT(text, separator) → {Array.<string>}
Split a string into an array of strings, given a separator
Parameters:
Name | Type | Description |
---|---|---|
text |
string
|
|
separator |
string
|
The resulting array of strings
Array.<string>
Example
SPLIT("Paris,San Diego,Ajaccio", ",") // ["Paris", "San Diego", "Ajaccio"]
# static SQRT(number) → {number}
Returns the square root of a number
Parameters:
Name | Type | Description |
---|---|---|
number |
number
|
number
Example
SQRT(16) // 4
# static STRLEFT(text, separator) → {text}
Returns the left part of a string, given a separator
Parameters:
Name | Type | Description |
---|---|---|
text |
string
|
|
separator |
string
|
text
Example
STRLEFT("San Francisco", " ") // San
# static STRRIGHT(text, separator) → {text}
Returns the right part of a string, given a separator
Parameters:
Name | Type | Description |
---|---|---|
text |
string
|
|
separator |
string
|
text
Example
STRRIGHT("San Francisco", " ") // Francisco
# static SUM(…numbers) → {number}
SUM
Parameters:
Name | Type | Attributes | Description |
---|---|---|---|
numbers |
any
|
<repeatable> |
number
Example
SUM(1, 2, 3) // 6
# static TAN(number) → {number}
Returns the TANGENT of a number
Parameters:
Name | Type | Description |
---|---|---|
number |
number
|
number
Example
TAN(PI() / 4) // 1
# static TIME() → {string}
Get the current time as an ISO string (without time shift)
string
Example
TIME() // "14:53:28"
# static TIME_DIFFERENCE(fromISODate, toISODate, unit) → {integer}
Compute the time difference between 2 dates
Parameters:
Name | Type | Description |
---|---|---|
fromISODate |
string
|
As an ISO date string like "2023-02-14T15:44:05.886Z" or "2023-02-14" |
toISODate |
string
|
As an ISO date string like "2023-02-14T15:44:05.886Z" or "2023-02-14" |
unit |
string
|
"d" for days, "h" for hours... "mn", "s", "ms" |
Time diffence in the required unit of time
integer
Example
TIME_DIFFERENCE("2023-02-14T15:44:05.886Z", "2023-02-14T18:44:26.316Z", "h") // 3
TIME_DIFFERENCE("2023-02-10", "2023-02-20", "d") // 10
# static TITLECASE(text) → {text}
Convert a string to titlecase
Parameters:
Name | Type | Description |
---|---|---|
text |
string
|
text
Example
TITLECASE("paris") // Paris
# static TODAY(strDateISO) → {string}
Get the current date as an ISO date string
Parameters:
Name | Type | Description |
---|---|---|
strDateISO |
string
|
string
Example
TODAY() // "2022-12-24"
# static UID() → {string}
Generates a unique id
string
Example
UID() // "01844399-988f-7974-a68f-92d35fc702cc"
# static UPPERCASE(text) → {text}
Convert a string to uppercase
Parameters:
Name | Type | Description |
---|---|---|
text |
string
|
text
Example
UPPERCASE("San Francisco") // SAN FRANCISCO
# static YEAR(strDateISO) → {string}
Get the YEAR of an ISO date
Parameters:
Name | Type | Description |
---|---|---|
strDateISO |
string
|
string
Example
YEAR("2022-12-24") // "2022"
# static YEAR_MONTH(strDateISO) → {string}
Get the YEAR and MONTH of an ISO date
Parameters:
Name | Type | Description |
---|---|---|
strDateISO |
string
|
The year
string
Example
YEAR_MONTH("2022-12-24") // "2022-12"
# static execute(formula, record, fields) → {*}
Execute a formula that is an arithmetic expression mixed or not with functions calls and return the result
Parameters:
Name | Type | Description |
---|---|---|
formula |
string
|
The formula to parse and execute |
record |
Object
|
A record object. Each object property will be available in the formula with the following syntax: "{{property_name}}" if the corresponding entry is listed in fields. |
fields |
Array.<{label: string, id: string}>
|
A list of record properties sorted in a way such as {{index}} will be resolved as a record property. |
- The formula result
*
Example
kiss.formula.execute("SUM(4, 6)") // returns 10
kiss.formula.execute("'Test: ' + (2 * SUM(3, 4))") // returns "Test: 14"
kiss.formula.execute("true && !false") // returns true
kiss.formula.execute("{{amount}} + {{vat}}") // returns 12 with record `{ amount: 10, vat: 2 }`