Formulas

A formula skill calculates a value from other columns. Refer to a column by its name, or to a parent table's column as table.column.

quantity * unit_price
concat('ACC-', pad(customer_id, 8))
'VIP' if lifetime_value > 5000 else 'Standard'
add_days(invoice_date, 14)
round(charge_amount * rand(0.45, 0.8), 2)

Conditions use if … else …, comparisons (==, !=, <, >= …), and, or, not and in [ … ]. Use null for an empty value.

Functions

Group	Functions
Numbers	`round(x, decimals=0)` `floor(x)` `ceil(x)` `abs(x)` `min(a, b, …)` `max(a, b, …)` `clamp(x, low, high)` `int(x)` `float(x)`
Random	`rand(low=0, high=1)` `randint(low, high)` `choice(a, b, c)` `weighted('A', 70, 'B', 30)`
Text	`upper` `lower` `title` `trim` `len` `left(text, n)` `right(text, n)` `substr(text, start, length)` `replace(text, old, new)` `pad(value, width, '0')` `rpad(value, width, ' ')` `concat(a, b, …)` `str` `digits` `split_part(text, sep, index)` `contains` `startswith` `endswith` `fmt(number, ',.2f')`
Dates	`today()` `date('2024-01-31')` `add_days` `add_months` `add_years` `days_between(start, end)` `year` `month` `day` `weekday` (Monday = 1) `age(date_of_birth)` `fmt_date(date, '%d/%m/%Y')` `add_minutes(datetime, n)` `at_time(date, 'HH:MM')`
Logic	`coalesce(a, b, …)` `is_null(value)`
Check digits	`luhn(digits)` `with_luhn(digits)`
Identifiers	`au_licence(state)`: licence number in that state's format · `nmi(network or state)` · `mirn(network or state)`
Airlines	`ticket_number(carrier)` `flight_number(carrier, haul)` `bag_tag(carrier)` `registration(carrier)` `passport(nationality)` `booking_class(haul)` `cabin_of(booking_class)` `fare_basis(booking_class, trip_type, point_of_sale)` `seat(cabin, haul)` `aircraft_for(carrier, haul)` `haul_between(origin, destination)` `eu261(distance_km, 'EU261' or 'UK261')` `ssr_for(passenger_type)` `ssr_text(code)` `avios_for(distance_km, cabin)` `avios_amount(type)` `points_amount(type)`
Position	`nth_in_parent`: the row's number among its siblings (1, 2, 3…), e.g. passenger 1, 2 in a booking
Value lists	`lookup('list_id', 'match_column', value, 'return_column')`
Columns	`skill('skill_id')`: this row's value from the column using that skill · `col('Column Name')`: a column whose name has spaces

The editor has samples to start from: Reference number, Code from a name, Tier by value, Due date, Line total, Weighted pick, Check digit, From a value list.

Formulas

Functions​

Functions