Formula API reference

One page with everything a formula can use: the globals, the helpers with their signatures, the Rows API and the token syntax. The second half lists the templating tokens of Card tiles, grid columns and chart labels.

For a gentler introduction, see Writing dynamic column formulas and How aggregated fields work. For a one-page summary, see the Cheat sheet.

Field tokens

{[InternalName]} inserts a field. Insert tokens by clicking a field in the Fields list or by typing @, because dynamic and aggregated fields have generated internal names.

Where	{[Field]} stands for
Dynamic column	this row's value
Dynamic column, token inside a string or template literal ('...{[Title]}...', `${...}`)	the value as text
Aggregated field	the list of the field's values across all rows
Either, when the field is an aggregated field	its single value
Either, {[Child_MergedData_]}	all rows of a Reference only child
Either, {[Child_Field]}	the list of that field's values across the child's rows

Values are passed as copies, so a formula can't change the source row. Reference data is shared and read-only: copy it before sorting ([...list].sort(...)).

Globals

This is the complete set. Nothing else is added: no lodash, no moment.

Global	Dynamic column	Aggregated field	What it is
DateTime	Yes	Yes	Luxon 2 DateTime, for dates
Interval	Yes	Yes	Luxon Interval, for durations between two dates
Rows	Yes	Yes	Indexed lookups over lists of rows, see Rows
The 12 helpers	Yes	Yes	Sum, Average, Min, Max, Median, Count, CountDistinct, First, Last, SumIf, CountIf, AverageIf, see Helpers
data	No	Yes	All rows of the data source, as objects keyed by internal name, see data
window	null	null	Deliberately empty

Text, number and list functions

There are no Sintel-specific string or math functions. Standard JavaScript works as usual: Math, JSON, Number, String, Array, Object, Date, RegExp, Intl and their methods (.toUpperCase(), .includes(), .map(), .filter(), .reduce(), .toFixed() and so on).

Don't use the page (document), the network (fetch) or other browser APIs. A formula is a single synchronous expression evaluated in the browser, so it can't wait for a request, and it shouldn't touch the page. window being null is not a security sandbox, see how the formula is evaluated.

Result and errors

	Dynamic column	Aggregated field
Runs	once per row	once per data source
Result converted to	the column type: Number (Number(value), not numeric becomes empty), Single Line of Text (toString()), Date and Time (a Date, ISO string, number or Luxon DateTime; anything else is an error), Yes/No (Boolean(value))	the field type, the same way
On an error	that row gets an empty value	the field is empty. The editor's preview shows the error

Helpers

Every helper accepts a list or a single value (a single value is treated as a list of one). Empty means null, undefined or ''.

Helper	Returns	Skips	With nothing to work on
Sum(values)	number: total of the numbers	empty and non-numeric values	0
Average(values)	number or null: mean of the numbers	empty and non-numeric values	null
Median(values)	number or null: middle value of the numbers	empty and non-numeric values	null
Min(values) / Max(values)	smallest / largest number. If there are no numbers: the earliest / latest date, as a Luxon DateTime	values that are neither numbers nor valid dates	null
Count(values)	number of non-empty values	empty values	0
CountDistinct(values)	number of unique non-empty values	empty values	0
First(values) / Last(values)	the first / last non-empty value	empty values	null
SumIf(values, criteriaValues, criterion)	number: Sum of values where the matching item of criteriaValues meets the criterion	as Sum	0
AverageIf(values, criteriaValues, criterion)	number or null: Average of the matching values	as Average	null
CountIf(criteriaValues, criterion)	number of items that meet the criterion	nothing	0

• criterion is a value, compared with === (so 1 doesn't match '1'), or a function such as (value) => value > 100.
• values and criteriaValues are matched by position, so pass two fields of the same rows, for example SumIf({[Amount]}, {[Status]}, 'Open').
• The If helpers take one condition. For two, use data.filter(...) or Rows.filter twice.
• Dates given to Min / Max can be Date objects, Luxon DateTimes or ISO strings.

More examples: Helpers.

Rows

Rows looks up rows by "field equals value", using an index that is built once for reference data.

Function	Returns	Empty or missing input
Rows.find(rows, field, value)	the first row whose field equals value	null
Rows.filter(rows, field, value)	all matching rows (read-only for reference data)	empty list
Rows.count(rows, field, value)	the number of matching rows	0
Rows.sum(rows, field)	total of the numbers in field	0
Rows.avg(rows, field) / Rows.min / Rows.max	average / smallest / largest number in field	null

• field is an internal name in quotes, or a path such as 'Author.id'.
• A null or missing value never matches.
• sum, avg, min and max count only numbers. Dates and numbers stored as text are skipped.

Full details, recipes and gotchas: The Rows helper.

Templating tokens

Some components take your own HTML or label text and replace tokens with values. These are plain text replacements, not formulas: you can't write expressions inside them. To show a calculated value, add a dynamic column and use its token.

Card tile Custom HTML

In a Card with Tile Custom HTML, each tile is one row of the data source.

Token	Replaced with
{[FieldName]}	the row's value of that field, by internal name
{webUrl}	the address of the current site

Grid Custom HTML columns

In a Grid column shown as Custom HTML, the HTML is filled in for each cell.

Token	Replaced with
{value}	this column's value, or nothing if it's empty
{[FieldName]}	the row's value of another field, by internal name
{webUrl}	the address of the current site
{encode(text)}	text URL-encoded (encodeURIComponent). Other tokens inside it are replaced first, so {encode({[Title]})} encodes the title

Example: <a href="{webUrl}/SitePages/Project.aspx?name={encode({value})}">{value}</a>

Good to know

• Values are inserted as they are stored, without the column's formatting. Dates are ISO text, for example. Format them in a dynamic column first.
• Values are inserted as HTML, not escaped.
• An empty {[FieldName]} is inserted as the text null. Use a dynamic column with a fallback ({[Field]} ?? '') if that matters.
• Scripts in the HTML don't run.

Chart labels

Chart data labels and the centre label of pie and donut charts have their own tags, such as {category}, {value}, {percentageValue} and {totalValue}. See Labels and Pie, donut and pyramid charts.