Documentation

Namespace

formula

kiss.formula

Formula module

Handle specific formulae that can be used inside computed fields.

View Source common/formula.js, line 9

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

View Source common/formula.js, line 496

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

View Source common/formula.js, line 538

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 *

View Source common/formula.js, line 687

The resulting array

Array.<string> | Array.<number> | Array.<date>
Example
ARRAY( "a", "b", "c" ) // [ "a", "b", "c" ]

# static AVERAGE(…numbers) → {number}

AVERAGE

Parameters:
Name Type Attributes Description
numbers any <repeatable>

View Source common/formula.js, line 235

number
Example
AVERAGE(10, 20, 30) // 20

# static CONCATENATE(…strings) → {string}

Concatenate any number of strings

Parameters:
Name Type Attributes Description
strings any <repeatable>

View Source common/formula.js, line 172

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

View Source common/formula.js, line 188

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

View Source common/formula.js, line 306

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>

View Source common/formula.js, line 888

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>

View Source common/formula.js, line 901

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>

View Source common/formula.js, line 917

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

View Source common/formula.js, line 382

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"

View Source common/formula.js, line 454

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"

View Source common/formula.js, line 573

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

View Source common/formula.js, line 471

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>

View Source common/formula.js, line 760

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 *

View Source common/formula.js, line 712

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 *

View Source common/formula.js, line 737

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

View Source common/formula.js, line 808

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

View Source common/formula.js, line 789

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

View Source common/formula.js, line 673

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

View Source common/formula.js, line 26

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

View Source common/formula.js, line 632

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>

View Source common/formula.js, line 933

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>

View Source common/formula.js, line 944

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

View Source common/formula.js, line 114

text
Example
LOWERCASE("San Francisco") // san francisco

# static MAX(…numbers) → {number}

MAX

Parameters:
Name Type Attributes Description
numbers any <repeatable>

View Source common/formula.js, line 221

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

View Source common/formula.js, line 56

text
Example
MIDDLE("San Francisco", 4, 8) // Fran

# static MIN(…numbers) → {number}

MIN

Parameters:
Name Type Attributes Description
numbers any <repeatable>

View Source common/formula.js, line 207

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

View Source common/formula.js, line 368

string
Example
MONTH("2022-12-24") // "12"

# static NOW() → {string}

Get the current date and time as a simple readable string (without time shift)

View Source common/formula.js, line 989

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

View Source common/formula.js, line 654

*
Example
NTH(["low", "medium", "high"], 1) // "medium"

# static PI() → {number}

PI

View Source common/formula.js, line 292

number
Example
PI() // 3.1415927...

# static POW(number, power) → {number}

POW

Parameters:
Name Type Description
number number
power number

View Source common/formula.js, line 279

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

View Source common/formula.js, line 144

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

View Source common/formula.js, line 41

text
Example
RIGHT("San Francisco", 9) // Francisco

# static ROUND(number, precision) → {number}

ROUND

Parameters:
Name Type Description
number number
precision number

View Source common/formula.js, line 250

number
Example
ROUND(12.367891, 3) // 12.378

# static SHORT_ID() → {string}

Generates a short id

View Source common/formula.js, line 843

string
Example
SHORT_ID() // "A84F007X"

# static SIN(number) → {number}

Returns the SINUS of a number

Parameters:
Name Type Description
number number

View Source common/formula.js, line 320

number
Example
SIN(PI() / 2) // 1

# static SLUG(title) → {string}

Generates a slug from a plain title

Parameters:
Name Type Description
title string

View Source common/formula.js, line 158

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

View Source common/formula.js, line 958

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

View Source common/formula.js, line 264

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

View Source common/formula.js, line 71

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

View Source common/formula.js, line 86

text
Example
STRRIGHT("San Francisco", " ") // Francisco

# static SUM(…numbers) → {number}

SUM

Parameters:
Name Type Attributes Description
numbers any <repeatable>

View Source common/formula.js, line 617

number
Example
SUM(1, 2, 3) // 6

# static TAN(number) → {number}

Returns the TANGENT of a number

Parameters:
Name Type Description
number number

View Source common/formula.js, line 334

number
Example
TAN(PI() / 4) // 1

# static TIME() → {string}

Get the current time as an ISO string (without time shift)

View Source common/formula.js, line 979

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"

View Source common/formula.js, line 413

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

View Source common/formula.js, line 128

text
Example
TITLECASE("paris") // Paris

# static TODAY(strDateISO) → {string}

Get the current date as an ISO date string

Parameters:
Name Type Description
strDateISO string

View Source common/formula.js, line 969

string
Example
TODAY() // "2022-12-24"

# static UID() → {string}

Generates a unique id

View Source common/formula.js, line 830

string
Example
UID() // "01844399-988f-7974-a68f-92d35fc702cc"

# static UPPERCASE(text) → {text}

Convert a string to uppercase

Parameters:
Name Type Description
text string

View Source common/formula.js, line 100

text
Example
UPPERCASE("San Francisco") // SAN FRANCISCO

# static YEAR(strDateISO) → {string}

Get the YEAR of an ISO date

Parameters:
Name Type Description
strDateISO string

View Source common/formula.js, line 354

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

View Source common/formula.js, line 396

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.

View Source common/formula.js, line 1005

  • 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 }`