Formulas
A formula field computes a number from other fields of the same record or of related records. Pylo computes it on every read, so it is always current and never stored. You can sort and filter by it like any other field, but you cannot write to it.
1
round(sum(line_items.quantity * line_items.unit_price) * (1 - customer.discount), 2)A formula field has the data type FLOAT. To add one through the API, see
Schema Changes.
Referencing fields
The examples on this page are formula fields on an order entity. An order has the fields
quantity (INT), unit_price (FLOAT) and express (BOOLEAN), and belongs to one customer.
Write a field of the same record by its name:
1
quantity * unit_priceOnly INT, FLOAT and BOOLEAN fields work. A BOOLEAN field counts as 1 when it is true and 0
when it is false, so quantity * unit_price + express * 15 adds 15 for express orders. A field that
was never set is empty, not 0 (see Empty values). Text, date, enum and variant
fields cannot be used.
Write a field of a related record as a path: the relation name, a dot, then the field name. The path
can follow up to 3 relations, like customer.country.vat_rate, as long as each one points to a
single record.
1
quantity * unit_price * (1 - customer.discount)A relation that points to many records, like an order's line_items, only works inside an
aggregate.
A formula can use another formula field like a normal field. If net_total is a formula field on
order, net_total * 1.19 works. Formulas can build on each other up to 3 levels deep.
Pylo stores which fields and relations a formula uses, not their names. Renaming a field keeps every formula that uses it working.
Operators
| Operator | Meaning |
|---|---|
+ | Addition |
- | Subtraction |
* | Multiplication |
/ | Division |
-x | Negation |
( ) | Grouping |
* and / bind tighter than + and -. Division always returns decimals, so 7 / 2 is 3.5.
Functions
| Function | Returns |
|---|---|
round(value) | value rounded to a whole number |
round(value, decimals) | value rounded to decimals places |
ceil(value) | The next whole number up |
floor(value) | The next whole number down |
abs(value) | value without its sign |
coalesce(value, fallback, …) | The first argument that is not empty |
least(a, b, …) | The smallest argument |
greatest(a, b, …) | The largest argument |
The names and behavior match the PostgreSQL functions of the same name.
Aggregates
An aggregate combines one value across all records of a to-many relation. Every path inside it starts with that relation.
| Aggregate | Returns | No related records |
|---|---|---|
sum(relation.field) | The total | 0 |
avg(relation.field) | The average | Empty |
min(relation.field) | The smallest value | Empty |
max(relation.field) | The largest value | Empty |
count(relation) | The number of related records | 0 |
The argument can be an expression over the related record, for example
sum(line_items.quantity * line_items.unit_price). Aggregates cannot be nested, and a formula field
that contains an aggregate cannot be used inside another aggregate.
The viewer's permissions apply. An aggregate only counts the related records the viewer may read.
Empty values
An empty field makes the whole expression empty: quantity * unit_price is empty when either is
unset. Use coalesce to treat it as a number instead.
1
unit_price * coalesce(quantity, 1)Division by zero returns empty, not an error.
Limits
- A formula is at most 2000 characters long.
- A formula cannot reference itself, directly or through other formula fields.
- One-to-one and many-to-many relations from an entity to itself cannot be used. For example, an
employeewith a one-to-one relationmentor/menteeto anotheremployeecannot usementor.salary. A many-to-one relation to itself, likemanager/reports, works. - A plain field cannot become a formula field or back. Create a new field instead.