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. In a view formula it changes only the text the view shows for an existing field; the stored value stays.

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. Operation formulas 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. Operation formulas 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. Operation formulas 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. An item that is an object is carried whole and read afterwards with .prop. A row-set statement like groupBy: not allowed inside if() or do().

splitRows(list, name)

Statement position only. Operation formulas 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
  • splitRows(prop("rows"), "item"): One row per object of the rows list, the object under item; prop("item").prop("isin") then reads one of its fields

appendRows

Adds every row of a named table after the current rows, each with the data styles that table carries. The row type afterwards is the merge of the current rows and the table's columns, so a column only one side has reads empty on the other. A row-set statement like groupBy: not allowed inside if(), do() or each().

appendRows(table)

Statement position only. Operation formulas only.

  • appendRows(inputs().prop("corrections")): The current rows, then every row of the corrections table

each

Runs a statement once per item of a list, on the same row, with current bound to the item and index to its zero-based position; an empty list or an empty value runs it zero times and a value that is not a list fails the run. The body may set and unset row fields, raise an exception, write context, branch with if() and do(), and nest another each(); raiseException() needs both a field and a key there, and the key must identify the row as well as the item, because it is the whole identity of the ticket across the node. current is read-only and the list is not written back, so dropRow(), filter(), keepProps() and the row-set statements cannot run inside it.

each(list, statement)

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

  • each(prop("legs"), if(empty(current.prop("ISIN")), raiseException("legs", concat(prop("tradeId"), ":", format(index))).setProp("message", "Leg has no ISIN"))): One ticket on the legs column per leg with no ISIN, keyed by the trade and the leg's position; every row stays in the output
  • prop("legs").each(context().setProp("lastISIN", current.prop("ISIN"))): Rows unchanged; the context value lastISIN is the ISIN of the last row's last leg

dropRow

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

dropRow()

Statement position only. Operation formulas 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. Operation formulas 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. Operation formulas 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)

Operation formulas only.

  • 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. Operation formulas 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 named tables

context

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

context()

Operation formulas only.

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

inputs

Returns the node's named tables, one per wire into it; .prop(handle) is the table whose input port handle is handle.

inputs()

Operation formulas only.

  • inputs().prop("ibor_valuations"): The ibor_valuations table

rowIndex

Returns the current row's zero-based position in the row array entering the statement. Inside a list callback or an each() body index is still the item's position and rowIndex() is still the row's.

rowIndex()

Operation formulas only.

  • setProp("n", rowIndex() + 1): Every row numbered from 1 in arrival order
  • setProp("b", inputs().prop("b").rows().at(rowIndex()).prop("value")): Each row merged with the table row in the same position; empty past the end of the table

input

Returns the node's own incoming rows as a table; .rows() is every row in table order. A node condition reads its incoming dataset through it.

input()

Operation formulas only.

  • input().rows().some(current.prop("SIDE").test("BUY")): true when any incoming row has SIDE BUY
  • input().rows().count() > 0: true when the node has incoming rows

prop

Returns a field of the current row, of a table row, a context value, or a named table by its handle.

prop(name)
row.prop(name)
context().prop(name)
inputs().prop(handle)

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

  • prop("price") * 2: Twice the row's price
  • inputs().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 table 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 named 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. Operation formulas only.

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

findAll

On a named 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. Operation formulas only.

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

rows

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

table.rows()

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

  • inputs().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)

Operation formulas only.

  • 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), "type", or any other Exception Management field (status, assignee, custom properties) as text.

raiseException().setProp(name, value)

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

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

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

Styles and viewer

setStyle

Sets one style key on one field of the current row; a later write of the same key on the same field wins. Chains with .setStyle() and .setRowStyle().

setStyle(field, key, value)
style.setStyle(field, key, value)

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

  • if(test(prop("Fund ID"), "14$"), setStyle("Fund ID", "background", "lime")): Fund ID cells that end in 14 get a lime background
  • setStyle("Reporting GP", "icon", "thumbsup").setStyle("Reporting GP", "text_color", "indigo"): Every Reporting GP cell shows the thumbsup icon in indigo text

setRowStyle

Sets one style key on the current row itself, with the keys setStyle takes; the row's text styles reach every field that does not set that key itself.

setRowStyle(key, value)
style.setRowStyle(key, value)

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

  • if(prop("Done"), setRowStyle("strikethrough", true).setRowStyle("text_color", "gray")): Done rows are struck through in gray

clearStyle

Removes one style key from one field of the current row, or every style key on it when no key is given. Chains like setStyle.

clearStyle(field)
clearStyle(field, key)
style.clearStyle(field, key)

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

  • if(prop("Settled"), clearStyle("Settlement Date")): A settled row keeps no style on its Settlement Date cell
  • clearStyle("Notional", "background"): The Notional cell loses its background and keeps its other styles

clearRowStyle

Removes one style key from the current row itself, or every style key on the row when no key is given. Chains like setRowStyle.

clearRowStyle()
clearRowStyle(key)
style.clearRowStyle(key)

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

  • if(prop("Status") == "Confirmed", clearRowStyle()): A confirmed trade's row carries no row style

me

Returns the signed-in viewer's name, or empty where the view has no signed-in viewer.

me() → text

View formulas only.

  • if(prop("Assignee").includes(me()), setRowStyle("background", "sky")): Rows assigned to the viewer get a sky background

optionColor

Returns the colour token of the option the current row holds in a select, status or multi-select field (the first selected option that has a colour), or empty when it has none. The field is a text literal.

optionColor(field: text) → text

View formulas only.

  • setStyle("Status", "dot", optionColor("Status")): Each Status cell shows a dot in its option's colour; an option without a colour shows none