Skip to content
Back to

Formula functions

This page lists every function you can call in a Formula operation. A function that is not listed here does not exist in the language. Each example shows an expression and the value it gives.

Every function of the language, by family. Signatures list the receiver first; a name documented in several families (filter, find, if, setProp, prop) is one function whose argument count and receiver pick the form. A form whose description says Fontana extension works here but is not Notion 2.0 (concat(text, …), the third argument of formatNumber); write the Notion form when the formula should also hold in Notion.

Logic

if

Returns the first value if the condition is true; otherwise, returns the second value.

if(condition: boolean, then: any, else: any) → any
  • if(true, 1, 2) gives 1
  • if(false, 1, 2) gives 2
  • true ? "Complete" : "Incomplete" gives "Complete"

ifs

Returns the value of the first true condition, or the last value when none is true, in place of nested if() calls.

ifs(condition: boolean, value: any, condition2: boolean, value2: any, …, else: any) → any
  • ifs(true, 1, true, 2, 3) gives 1
  • ifs(false, 1, true, 2, 3) gives 2
  • ifs(false, 1, false, 2, 3) gives 3

empty

Returns true if the value is empty; 0, "", and [] are empty.

empty(value: any) → boolean

Also callable as receiver.empty(…), with the receiver as the first argument.

  • empty(0) gives true
  • empty("") gives true
  • empty([]) gives true
  • "Notion".empty() gives false

equal

Returns true if both values are equal and false otherwise, the same as ==.

equal(a: any, b: any) → boolean

Also callable as receiver.equal(…), with the receiver as the first argument.

  • equal(1, 1) gives true
  • "a" == "b" gives false
  • "1" == 1 gives false
  • [1, 2] == [2, 1] gives false

unequal

Returns false if both values are equal and true otherwise, the same as !=.

unequal(a: any, b: any) → boolean

Also callable as receiver.unequal(…), with the receiver as the first argument.

  • unequal(1, 2) gives true
  • "a" != "a" gives false

and

Returns the logical AND of its two arguments.

and(a: boolean, b: boolean) → boolean

Also callable as receiver.and(…), with the receiver as the first argument.

  • and(true, false) gives false
  • true && true gives true
  • true and false gives false

or

Returns the logical OR of its two arguments.

or(a: boolean, b: boolean) → boolean

Also callable as receiver.or(…), with the receiver as the first argument.

  • or(true, false) gives true
  • false || false gives false
  • false or true gives true

not

Returns the logical NOT of its argument.

not(value: boolean) → boolean

Also callable as receiver.not(…), with the receiver as the first argument.

  • not(true) gives false
  • !false gives true
  • not true gives false

let

Binds a name to a value for the body expression, so a value computed once can be used several times.

let(name, value: any, body: any) → any
  • let(x, 10, x * 2) gives 20
  • let(word, "go", word + word) gives "gogo"

lets

Binds several names in order for the body expression; each value can use the names bound before it.

lets(name, value: any, name2, value2: any, …, body: any) → any
  • lets(a, 1, b, a + 1, a + b) gives 3

Text

length

Returns the length of the text or list value.

length(text: text) → number
length(list: list<any>) → number

Also callable as receiver.length(…), with the receiver as the first argument.

  • length("hello") gives 5
  • "Notion".length() gives 6
  • length([1, 2, 3]) gives 3

substring

Returns the part of the text from the start index (inclusive) to the optional end index (exclusive).

substring(text: text, start: number, end?: number) → text

Also callable as receiver.substring(…), with the receiver as the first argument.

  • substring("Notion", 0, 3) gives "Not"
  • substring("Notion", 3) gives "ion"

contains

Returns true if the search text is present in the value.

contains(text: text, search: text) → boolean

Also callable as receiver.contains(…), with the receiver as the first argument.

  • contains("Notion", "ot") gives true
  • "Notion".contains("x") gives false

test

Returns true if the value matches the regular expression and false otherwise.

test(text: text, pattern: text) → boolean

Also callable as receiver.test(…), with the receiver as the first argument.

  • test("Notion", "Not") gives true
  • test("Notion", "\\d") gives false

match

Returns every match of the regular expression as a list.

match(text: text, pattern: text) → list<text>

Also callable as receiver.match(…), with the receiver as the first argument.

  • match("Notion Notion", "Not") gives ["Not", "Not"]
  • match("Notion 123 Notion 456", "\\d+") gives ["123", "456"]

replace

Replaces the first match of the regular expression with the replacement text.

replace(text: text, pattern: text, replacement: text) → text

Also callable as receiver.replace(…), with the receiver as the first argument.

  • replace("Notion Notion", "N", "M") gives "Motion Notion"

replaceAll

Replaces every match of the regular expression with the replacement text.

replaceAll(text: text, pattern: text, replacement: text) → text

Also callable as receiver.replaceAll(…), with the receiver as the first argument.

  • replaceAll("Notion Notion", "N", "M") gives "Motion Motion"
  • "Notion 123".replaceAll("\\d", "") gives "Notion "

lower

Converts the text to lowercase.

lower(text: text) → text

Also callable as receiver.lower(…), with the receiver as the first argument.

  • lower("NOTION") gives "notion"
  • "NOTION".lower() gives "notion"

upper

Converts the text to uppercase.

upper(text: text) → text

Also callable as receiver.upper(…), with the receiver as the first argument.

  • upper("notion") gives "NOTION"
  • "notion".upper() gives "NOTION"

repeat

Repeats the text the given number of times.

repeat(text: text, times: number) → text

Also callable as receiver.repeat(…), with the receiver as the first argument.

  • repeat("0", 4) gives "0000"
  • "~=".repeat(10) gives "~=~=~=~=~=~=~=~=~=~="

trim

Removes whitespace from the beginning and end of the text.

trim(text: text) → text

Also callable as receiver.trim(…), with the receiver as the first argument.

  • trim(" Notion ") gives "Notion"
  • " Notion ".trim() gives "Notion"

format

Converts any value to text; the text conversion for a function or operator that needs text. Numbers print without grouping, dates as ISO-8601 UTC.

format(value: any) → text

Also callable as receiver.format(…), with the receiver as the first argument.

  • format(prop("qty")) gives "3"
  • format(1234) gives "1234"
  • format(1234567.5) gives "1234567.5"
  • format(true) gives "true"

padStart

Pads the beginning of the value, formatted as text, with the pad text until it reaches the given length.

padStart(value: text, length: number, pad: text) → text
padStart(value: text, length: number, pad: number) → text
padStart(value: number, length: number, pad: text) → text
padStart(value: number, length: number, pad: number) → text

Also callable as receiver.padStart(…), with the receiver as the first argument.

  • "A".padStart(3, ".") gives "..A"
  • 123.padStart(5, 0) gives "00123"
  • "hi".padStart(5, "!") gives "!!!hi"
  • "hi".padStart(5, "!".length()) gives "111hi"

padEnd

Pads the end of the value, formatted as text, with the pad text until it reaches the given length.

padEnd(value: text, length: number, pad: text) → text
padEnd(value: text, length: number, pad: number) → text
padEnd(value: number, length: number, pad: text) → text
padEnd(value: number, length: number, pad: number) → text

Also callable as receiver.padEnd(…), with the receiver as the first argument.

  • "A".padEnd(3, ".") gives "A.."
  • 123.padEnd(5, 0) gives "12300"

Numbers

add

Returns the sum of two numbers.

add(a: number, b: number) → number

Also callable as receiver.add(…), with the receiver as the first argument.

  • add(5, 10) gives 15
  • 5 + 10 gives 15

subtract

Returns the difference of two numbers.

subtract(a: number, b: number) → number

Also callable as receiver.subtract(…), with the receiver as the first argument.

  • subtract(5, 10) gives -5
  • 5 - 10 gives -5

multiply

Returns the product of two numbers.

multiply(a: number, b: number) → number

Also callable as receiver.multiply(…), with the receiver as the first argument.

  • multiply(5, 10) gives 50
  • 5 * 10 gives 50

divide

Returns the quotient of two numbers.

divide(a: number, b: number) → number

Also callable as receiver.divide(…), with the receiver as the first argument.

  • divide(5, 10) gives 0.5
  • 5 / 10 gives 0.5

mod

Returns the first number modulo the second number; the result has the sign of the first.

mod(a: number, b: number) → number

Also callable as receiver.mod(…), with the receiver as the first argument.

  • mod(5, 10) gives 5
  • 5 % 10 gives 5

pow

Returns the result of a base number raised to an exponent power.

pow(a: number, b: number) → number

Also callable as receiver.pow(…), with the receiver as the first argument.

  • pow(5, 10) gives 9765625
  • 5 ^ 10 gives 9765625

min

Returns the smallest number of the arguments.

min(a: number, b: number, …more: number) → number
min(list: list<number>, …more: number) → number

Also callable as receiver.min(…), with the receiver as the first argument.

  • min(1, 2, 3) gives 1
  • min([1, 2, 3]) gives 1

max

Returns the largest number of the arguments.

max(a: number, b: number, …more: number) → number
max(list: list<number>, …more: number) → number

Also callable as receiver.max(…), with the receiver as the first argument.

  • max(1, 2, 3) gives 3
  • max([1, 2, 3]) gives 3

sum

Returns the sum of its arguments.

sum(a: number, b: number, …more: number) → number
sum(list: list<number>, …more: number) → number

Also callable as receiver.sum(…), with the receiver as the first argument.

  • sum(1, 2, 3) gives 6
  • sum([1, 2, 3], 4, 5) gives 15

median

Returns the middle value of its arguments.

median(a: number, b: number, …more: number) → number
median(list: list<number>, …more: number) → number

Also callable as receiver.median(…), with the receiver as the first argument.

  • median(1, 2, 4) gives 2
  • median([1, 2, 3], 4) gives 2.5

mean

Returns the arithmetic average of its arguments.

mean(a: number, b: number, …more: number) → number
mean(list: list<number>, …more: number) → number

Also callable as receiver.mean(…), with the receiver as the first argument.

  • mean(1, 2, 3) gives 2
  • mean([1, 2, 3], 4, 5) gives 3

abs

Returns the absolute value of the number.

abs(number: number) → number

Also callable as receiver.abs(…), with the receiver as the first argument.

  • abs(10) gives 10
  • abs(-10) gives 10

round

Returns the number rounded to the nearest integer, or to the given decimal places; halves round away from zero.

round(number: number, places?: number) → number

Also callable as receiver.round(…), with the receiver as the first argument.

  • round(0.4) gives 0
  • round(-0.6) gives -1
  • round(1.234, 0) gives 1
  • round(1.234, 2) gives 1.23
  • round(1234, -2) gives 1200

ceil

Returns the smallest integer greater than or equal to the number.

ceil(number: number) → number

Also callable as receiver.ceil(…), with the receiver as the first argument.

  • ceil(0.4) gives 1
  • ceil(-0.6) gives 0

floor

Returns the largest integer less than or equal to the number.

floor(number: number) → number

Also callable as receiver.floor(…), with the receiver as the first argument.

  • floor(0.4) gives 0
  • floor(-0.6) gives -1

sqrt

Returns the positive square root of the number.

sqrt(number: number) → number

Also callable as receiver.sqrt(…), with the receiver as the first argument.

  • sqrt(4) gives 2
  • sqrt(7) gives 2.6457513110645905905

cbrt

Returns the cube root of the number.

cbrt(number: number) → number

Also callable as receiver.cbrt(…), with the receiver as the first argument.

  • cbrt(9) gives 2.0800838230519041145
  • cbrt(64) gives 4

exp

Returns e^x, where x is the argument, and e is Euler's number (2.718…), the base of the natural logarithm.

exp(number: number) → number

Also callable as receiver.exp(…), with the receiver as the first argument.

  • exp(1) gives 2.7182818284590452354
  • exp(-1) gives 0.3678794411714423216

ln

Returns the natural logarithm of the number.

ln(number: number) → number

Also callable as receiver.ln(…), with the receiver as the first argument.

  • ln(2.718281828459045) gives 0.99999999999999991342
  • ln(10) gives 2.302585092994045684

log10

Returns the base 10 logarithm of the number.

log10(number: number) → number

Also callable as receiver.log10(…), with the receiver as the first argument.

  • log10(10) gives 1
  • log10(100000) gives 5

log2

Returns the base 2 logarithm of the number.

log2(number: number) → number

Also callable as receiver.log2(…), with the receiver as the first argument.

  • log2(4) gives 2
  • log2(1024) gives 10

sign

Returns 1 if the number is positive, -1 if it is negative, and 0 if it is zero.

sign(number: number) → number

Also callable as receiver.sign(…), with the receiver as the first argument.

  • sign(-10) gives -1
  • sign(10) gives 1

pi

Returns the ratio of a circle's circumference to its diameter.

pi() → number
  • pi() gives 3.1415926535897932385

e

Returns the base of the natural logarithm.

e() → number
  • e() gives 2.7182818284590452354

toNumber

Converts a value to a number; the number conversion for a function or operator that needs a number. Text is parsed, a boolean is 1 or 0 and a date is its Unix timestamp in milliseconds.

toNumber(value: text | number | boolean | date) → number

Also callable as receiver.toNumber(…), with the receiver as the first argument.

  • toNumber("2") gives 2
  • toNumber(now()) gives 1693418100000
  • toNumber(true) gives 1

formatNumber

Formats the number as text: "commas" (the default) groups thousands, "usd" writes dollars, "humanize" abbreviates to K, M, B or T. The optional third argument, which fixes the decimal places, is a Fontana extension: Notion 2.0 is formatNumber(number, format).

formatNumber(number: number, format?: text, places?: number) → text

Also callable as receiver.formatNumber(…), with the receiver as the first argument.

  • formatNumber(1234567.891) gives "1,234,567.891"
  • formatNumber(1234567.891, "commas", 2) gives "1,234,567.89"
  • formatNumber(1234567.891, "humanize") gives "1.2M"
  • formatNumber(-1234.5, "usd") gives "-$1,234.50"
  • "Revenue: " + formatNumber(1234.5, "usd", 0) gives "Revenue: $1,235"

Dates

now

Returns the current date and time.

now() → date
  • now() gives @August 30, 2023 5:55 PM

today

Returns the current date without the time.

today() → date
  • today() gives @August 30, 2023

minute

Returns the minute of the date (0-59).

minute(date: date) → number

Also callable as receiver.minute(…), with the receiver as the first argument.

  • minute(parseDate("2023-07-10T17:35Z")) gives 35

hour

Returns the hour of the date (0-23).

hour(date: date) → number

Also callable as receiver.hour(…), with the receiver as the first argument.

  • hour(parseDate("2023-07-10T17:35Z")) gives 17

day

Returns the day of the week of the date, between 1 (Monday) and 7 (Sunday).

day(date: date) → number

Also callable as receiver.day(…), with the receiver as the first argument.

  • day(parseDate("2023-07-10T17:35Z")) gives 1

date

Returns the day of the month from the date (1-31).

date(date: date) → number

Also callable as receiver.date(…), with the receiver as the first argument.

  • date(parseDate("2023-07-10T17:35Z")) gives 10

week

Returns the ISO week of the year of the date (1-53).

week(date: date) → number

Also callable as receiver.week(…), with the receiver as the first argument.

  • week(parseDate("2023-01-02")) gives 1

month

Returns the month of the date (1-12).

month(date: date) → number

Also callable as receiver.month(…), with the receiver as the first argument.

  • month(parseDate("2023-07-10T17:35Z")) gives 7

year

Returns the year of the date.

year(date: date) → number

Also callable as receiver.year(…), with the receiver as the first argument.

  • year(now()) gives 2023

dateAdd

Adds time to the date; the unit is "years", "quarters", "months", "weeks", "days", "hours" or "minutes".

dateAdd(date: date, amount: number, unit: text) → date

Also callable as receiver.dateAdd(…), with the receiver as the first argument.

  • dateAdd(now(), 1, "days") gives @August 31, 2023 5:55 PM
  • dateAdd(now(), 2, "months") gives @October 30, 2023 5:55 PM
  • dateAdd(now(), 3, "years") gives @August 30, 2026 5:55 PM

dateSubtract

Subtracts time from the date; the unit is "years", "quarters", "months", "weeks", "days", "hours" or "minutes".

dateSubtract(date: date, amount: number, unit: text) → date

Also callable as receiver.dateSubtract(…), with the receiver as the first argument.

  • dateSubtract(now(), 1, "days") gives @August 29, 2023 5:55 PM
  • dateSubtract(now(), 2, "months") gives @June 30, 2023 5:55 PM
  • dateSubtract(now(), 3, "years") gives @August 30, 2020 5:55 PM

dateBetween

Returns the difference between two dates as whole units, first date minus second, truncated toward zero.

dateBetween(date: date, other: date, unit: text) → number

Also callable as receiver.dateBetween(…), with the receiver as the first argument.

  • dateBetween(now(), parseDate("2022-09-07"), "days") gives 357
  • dateBetween(parseDate("2030-01-01"), now(), "years") gives 6

dateRange

Returns the date range from a start date to an end date, as the two-item list [start, end]; dateStart() and dateEnd() read its ends.

dateRange(start: date, end: date) → list<date>

Also callable as receiver.dateRange(…), with the receiver as the first argument.

  • dateRange(parseDate("2023-07-10"), parseDate("2023-07-20")) gives [@July 10, 2023, @July 20, 2023]

dateStart

Returns the start of a date range; a plain date is its own start.

dateStart(range: list<date>) → date
dateStart(date: date) → date

Also callable as receiver.dateStart(…), with the receiver as the first argument.

  • dateStart(dateRange(parseDate("2023-07-10"), parseDate("2023-07-20"))) gives @July 10, 2023
  • dateStart(now()) gives @August 30, 2023 5:55 PM

dateEnd

Returns the end of a date range; a plain date is its own end.

dateEnd(range: list<date>) → date
dateEnd(date: date) → date

Also callable as receiver.dateEnd(…), with the receiver as the first argument.

  • dateEnd(dateRange(parseDate("2023-07-10"), parseDate("2023-07-20"))) gives @July 20, 2023

timestamp

Returns the Unix timestamp of the date: the milliseconds elapsed since January 1, 1970 UTC.

timestamp(date: date) → number

Also callable as receiver.timestamp(…), with the receiver as the first argument.

  • timestamp(now()) gives 1693418100000

fromTimestamp

Returns the date from the given Unix timestamp in milliseconds.

fromTimestamp(timestamp: number) → date

Also callable as receiver.fromTimestamp(…), with the receiver as the first argument.

  • fromTimestamp(1689024900000) gives @July 10, 2023 9:35 PM

parseDate

Returns the date parsed according to the ISO 8601 standard, in UTC; with a format, parsed with the Moment tokens YYYY, YY (00-68 is 2000-2068), MMMM, MMM, MM, M, DD, D, Do, dddd, ddd, HH, H, hh, h (with A or a), mm, m, ss, s, SSS, A, a and [literal] text. The format needs the year; a missing month or day is the first, a missing time is midnight. Text that does not match the format fails the run.

parseDate(text: text) → date
parseDate(text: text, format: text) → date

Also callable as receiver.parseDate(…), with the receiver as the first argument.

  • parseDate("2022-01-01") gives @January 1, 2022
  • parseDate("2022-01-01T00:00Z") gives @January 1, 2022
  • parseDate("30/09/2026", "DD/MM/YYYY") gives @September 30, 2026
  • parseDate("30 Sep 2026 5:55 PM", "D MMM YYYY h:mm A") gives @September 30, 2026 5:55 PM

formatDate

Formats the date with Moment tokens: YYYY, YY, Y (year), Q (quarter), MMMM, MMM, MM, M (month), DD, D, Do (day of month), DDDD, DDD (day of year), dddd, ddd, dd, d (weekday; d is 0 for Sunday), E (ISO weekday), WW, W (ISO week), HH, H (hour 0-23), hh, h (hour 1-12), mm, m (minute), ss, s (second), SSS (millisecond), A, a (AM/PM, am/pm), Z, ZZ (offset), X, x (Unix seconds, milliseconds). Text in [brackets] is written as is; any other letter is an error.

formatDate(date: date, format: text) → text

Also callable as receiver.formatDate(…), with the receiver as the first argument.

  • formatDate(now(), "MMMM D, Y") gives "August 30, 2023"
  • formatDate(now(), "MM/DD/YYYY") gives "08/30/2023"
  • formatDate(now(), "h:mm A") gives "5:55 PM"

Lists

at

Returns the value at the specified index in a list.

at(list, index)

Also callable as receiver.at(…), with the receiver as the first argument.

  • at([1, 2, 3], 1) gives 2
  • [1, 2, 3].at(1) gives 2

first

Returns the first item in the list.

first(list)

Also callable as receiver.first(…), with the receiver as the first argument.

  • first([1, 2, 3]) gives 1
  • [1, 2, 3].first() gives 1

last

Returns the last item in the list.

last(list)

Also callable as receiver.last(…), with the receiver as the first argument.

  • last([1, 2, 3]) gives 3
  • [1, 2, 3].last() gives 3

slice

Returns the items of the list from the provided start index (inclusive) to the end index (exclusive).

slice(list, start, end)

Also callable as receiver.slice(…), with the receiver as the first argument.

  • slice([1, 2, 3], 1, 2) gives [2]
  • slice(["a", "b", "c"], 1) gives ["b", "c"]

concat

Returns the concatenation of multiple lists. The text form concat(text, …), which joins text, is a Fontana extension: Notion 2.0 joins text with + or join().

concat(list, …)
concat(text, …)

Also callable as receiver.concat(…), with the receiver as the first argument.

  • concat([1, 2], [3, 4]) gives [1, 2, 3, 4]
  • concat(["a", "b"], ["c", "d"]) gives ["a", "b", "c", "d"]
  • concat("dog", "go") gives "doggo"

sort

Returns the list in sorted order.

sort(list)

Also callable as receiver.sort(…), with the receiver as the first argument.

  • sort([3, 1, 2]) gives [1, 2, 3]
  • ["b", "c", "a"].sort() gives ["a", "b", "c"]

reverse

Returns the reversed list.

reverse(list)

Also callable as receiver.reverse(…), with the receiver as the first argument.

  • reverse(["green", "eggs", "ham"]) gives ["ham", "eggs", "green"]

join

Returns the values of the list with the joiner placed in between each of the values.

join(list, joiner)

Also callable as receiver.join(…), with the receiver as the first argument.

  • join(["a", "b", "c"], ", ") gives "a, b, c"
  • join(["dog", "go"], "") gives "doggo"

split

Returns the list of values created by separating a text input at a separator.

split(text, separator)

Also callable as receiver.split(…), with the receiver as the first argument.

  • split("apple,pear,orange", ",") gives ["apple", "pear", "orange"]

unique

Returns the list of unique values in the input list.

unique(list)

Also callable as receiver.unique(…), with the receiver as the first argument.

  • unique([1, 1, 2]) gives [1, 2]
  • ["a", "b", "a"].unique() gives ["a", "b"]

includes

Returns true if the list contains the specified value, and false otherwise.

includes(list, value)

Also callable as receiver.includes(…), with the receiver as the first argument.

  • includes(["a", "b", "c"], "b") gives true
  • includes([1, 2, 3], 4) gives false

find

Returns the first item in the list for which the condition evaluates to true.

find(list, condition)

Also callable as receiver.find(…), with the receiver as the first argument.

  • find(["a", "b", "c"], current == "b") gives "b"
  • [1, 2, 3].find(current > 1) gives 2

findIndex

Returns the index of the first item in the list for which the condition is true, or -1 when none is.

findIndex(list, condition)

Also callable as receiver.findIndex(…), with the receiver as the first argument.

  • findIndex(["a", "b", "c"], current == "b") gives 1
  • [1, 2, 3].findIndex(current > 5) gives -1

filter

Returns the values in the list for which the condition is true.

filter(list, condition)

Also callable as receiver.filter(…), with the receiver as the first argument.

  • filter([1, 2, 3], current > 1) gives [2, 3]
  • filter(["a", "b", "c"], current == "a") gives ["a"]

some

Returns true if any item in the list satisfies the given condition, and false otherwise.

some(list, condition)

Also callable as receiver.some(…), with the receiver as the first argument.

  • some([1, 2, 3], current == 2) gives true
  • some(["a", "b", "c"], current.length() > 2) gives false

every

Returns true if every item in the list satisfies the given condition, and false otherwise.

every(list, condition)

Also callable as receiver.every(…), with the receiver as the first argument.

  • every([1, 2, 3], current > 0) gives true
  • every(["a", "b", "c"], current == "b") gives false

map

Returns the list populated with the results of calling the expression on every item in the input list.

map(list, expression)

Also callable as receiver.map(…), with the receiver as the first argument.

  • map([1, 2, 3], current + 1) gives [2, 3, 4]
  • map([1, 2, 3], current + index) gives [1, 3, 5]
  • [1, 2, 3].map(current * 2) gives [2, 4, 6]

flat

Flattens a list of lists into a single list.

flat(list)

Also callable as receiver.flat(…), with the receiver as the first argument.

  • flat([1, 2, 3]) gives [1, 2, 3]
  • flat([[1, 2], [3, 4]]) gives [1, 2, 3, 4]

count

Returns the number of items in the list.

count(list)

Also callable as receiver.count(…), with the receiver as the first argument.

  • count([1, 2, 3]) gives 3
  • [].count() gives 0

splice

Returns the list with the given number of items removed at the start index and the given values inserted there.

splice(list, start, deleteCount, value, …)

Also callable as receiver.splice(…), with the receiver as the first argument.

  • splice([1, 2, 3], 1, 1, "X") gives [1, "X", 3]
  • [1, 2, 3].splice(0, 2) gives [3]

Statements

setProp

Sets a field on every row to the value of an expression; a new name adds the field. On context() it sets a context value instead; chained on insertRow() it sets a field of the inserted row from an expression that reads the current row.

setProp(name, value)
context().setProp(name, value)
insertRow().setProp(name, value)

Also callable as receiver.setProp(…), with the receiver as the first argument. Statement position only.

  • setProp("Side", prop("Side").upper()): Every row with Side in upper case
  • setProp("price", prop("price").sum()): Every group row with price set to the sum of its members
  • context().setProp("highestPrice", 0): Rows unchanged; context highestPrice is 0
  • insertRow().setProp("leg", "B").setProp("qty", 0 - prop("qty")): Every row followed by a copy whose leg is B and whose qty is the row's qty negated

unsetProp

Removes a field from every row.

unsetProp(name)

Statement position only.

  • unsetProp("expired"): Every row without the expired field

keepProps

Keeps only the named fields on every row, in that order, and removes every other field.

keepProps(name, …)

Statement position only.

  • keepProps("owner", "ISIN"): Every row with only the owner and ISIN fields

insertRow

Adds a copy of the current row, as it stands at that point, directly after it; chained .setProp(name, value) calls set fields on the copy from expressions that read the current row, and every other cell is copied. Allowed inside if() and do(); the statement never runs over the rows it inserted.

insertRow()

Statement position only.

  • insertRow().setProp("leg", "B"): Every row followed by a copy of itself with leg set to B
  • if(prop("qty") > 100, insertRow().setProp("qty", prop("qty") - 100)): Rows over 100 are followed by a copy holding the excess; the other rows pass through alone
  • do(insertRow().setProp("leg", "A"), insertRow().setProp("leg", "B"), dropRow()): Every row is replaced by two copies, leg A then leg B

splitRows

Emits one copy of every row per item of the list, the item written under name and every other field copied; an empty list drops the row. A row-set statement like groupBy: not allowed inside if() or do().

splitRows(list, name)

Statement position only.

  • splitRows(prop("tags"), "tag"): One row per tag, with the tag under tag and the other fields copied; a row with no tags is dropped
  • splitRows(prop("ISINs").split(";"), "ISIN"): One row per semicolon-separated ISIN

dropRow

Leaves the current row out of the output. A bare dropRow() drops every row.

dropRow()

Statement position only.

  • if(prop("expired").equal(1), dropRow()): Rows where expired is 1 are dropped

filter

With one argument, keeps the rows where the condition is true. The two-argument form is the list function.

filter(condition)

Statement position only.

  • filter(prop("expired") != 1): Rows where expired is not 1

sortBy

Stable-sorts every row by the keys, first key primary; a key is ascending unless wrapped in desc(), and empties sort last in both directions.

sortBy(key, …)

Statement position only.

  • sortBy(prop("owner"), desc(prop("price"))): Rows by owner ascending, then price descending

desc

Sorts a sortBy key descending. Valid only as a sortBy argument.

desc(key)
  • sortBy(desc(prop("price"))): Rows by price descending

groupBy

Emits one row per key tuple in first-seen order; every field becomes the list of member values, plus __groupKey (the key values) and __groupIndices (the member indices).

groupBy(key, …)

Statement position only.

  • groupBy(prop("owner"), prop("ISIN")): One row per owner and ISIN: { __groupKey: ["zoltan", "SOMEISIN"], __groupIndices: [0, 1], owner: ["zoltan", "zoltan"], ISIN: ["SOMEISIN", "SOMEISIN"], price: [3, 6] }

do

Runs statements left to right on the same row.

do(statement, …)

Statement position only.

  • if(prop("owner").empty(), do(raiseException().setProp("message", "Owner is missing"), dropRow())): Rows without an owner raise an exception and are dropped

if

With statement branches, runs the first branch on rows where the condition is true and the second, when present, on the others; rows without a branch pass through.

if(condition, statement, statement)

Statement position only.

  • if(prop("Created by").equal("buy"), setProp("Created by", "BUY"), setProp("Created by", "SELL")): Created by is "BUY" on buy rows and "SELL" on the others

Context and lookups

context

Returns the formula context, one object for the whole run that starts empty; read it with .prop and write it with .setProp.

context()
  • context().prop("highestPrice"): The highestPrice context value

lookups

Returns the node's lookup inputs; .prop(key) is the table whose edge Lookup Key is key.

lookups()
  • lookups().prop("ibor_valuations"): The ibor_valuations lookup table

prop

Returns a field of the current row, of a lookup row, a context value, or a lookup table by its Lookup Key.

prop(name)
row.prop(name)
context().prop(name)
lookups().prop(key)

Also callable as receiver.prop(…), with the receiver as the first argument.

  • prop("price") * 2: Twice the row's price
  • lookups().prop("ibor_valuations").find("ISIN", prop("ISIN")).prop("value"): value of the first ibor_valuations row whose ISIN matches; empty on a miss

getProp

Returns a field of the current row or of a lookup row; the same read as prop.

getProp(name)
row.getProp(name)

Also callable as receiver.getProp(…), with the receiver as the first argument.

  • getProp("price"): The row's price

find

On a lookup table, returns the first row in table order whose column equals the value under ==, or empty when none matches.

table.find(column, value)

Also callable as receiver.find(…), with the receiver as the first argument.

  • lookups().prop("ibor_valuations").find("ISIN", prop("ISIN")): The first ibor_valuations row whose ISIN matches; empty on a miss

findAll

On a lookup table, returns every row in table order whose column equals the value under ==.

table.findAll(column, value)

Also callable as receiver.findAll(…), with the receiver as the first argument.

  • lookups().prop("ibor_valuations").findAll("ISIN", prop("ISIN")).map(current.prop("value")).sum(): Sum of value over every matching ibor_valuations row

rows

On a lookup table, returns every row in table order.

table.rows()

Also callable as receiver.rows(…), with the receiver as the first argument.

  • lookups().prop("ibor_valuations").rows().count(): Number of rows in the ibor_valuations table

Exceptions

raiseException

Records an exception for the current row and keeps the row; field pins the ticket's column and key (text or number; an empty key is the blank text "") its identity across runs.

raiseException()
raiseException(field)
raiseException(field, key)
  • if(prop("owner").empty(), raiseException().setProp("message", "Owner is missing").setProp("severityLevel", 3)): Rows without an owner raise a level 3 exception and stay in the output
  • raiseException("price", concat(prop("owner"), prop("ISIN"))).setProp("type", "Pricing"): One Pricing ticket on the price column per owner and ISIN

setProp

On an exception, sets an exception field: "message", "severityLevel" (alias "severity", an integer 1 through 5) or "type".

raiseException().setProp(name, value)

Also callable as receiver.setProp(…), with the receiver as the first argument.

raiseException()
  .setProp("message", "Owner is missing")
  .setProp("severity", 3)
  .setProp("type", "Data quality")

Result: An exception with message "Owner is missing", severity level 3 and type "Data quality"