> Operators, functions, aggregates and field paths available in formula fields

# 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.

```text filename="formula"
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](/concepts/schema-changes#fields).

## 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:

```text filename="formula"
quantity * unit_price
```

Only `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](#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.

```text filename="formula"
quantity * unit_price * (1 - customer.discount)
```

A relation that points to many records, like an order's `line_items`, only works inside an
[aggregate](#aggregates).

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.

```text filename="formula"
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
  `employee` with a one-to-one relation `mentor` / `mentee` to another `employee` cannot use
  `mentor.salary`. A many-to-one relation to itself, like `manager` / `reports`, works.
- A plain field cannot become a formula field or back. Create a new field instead.