# Formulas

> Fields computed from a record's other fields: typed when written, empty where an input is, evaluated without re-reading.

A computed field holds a value derived from the record's other fields — a
line's total from its price and quantity, a due date from a release date, a
label from a name and a status — written once as a formula and kept up to date
whenever what it reads changes. People should never have to work out by hand
what the record already knows.

## Best practices

**Compute what follows from the record; store what was decided.** A total, an
age, a count of items, a date so many days on: these are formulas. A price, a
name, a choice someone made: these are entered. A computed field is shown like
any other, but it is not edited — what it says is changed by changing what it
reads.

**Name fields, not places.** A formula refers to fields by name —
`price * quantity` — so it means the same in every record and survives
reordering, sorting and filtering. A name in any script is written as it is:
`単価 * 数量`. A name with spaces or punctuation is written in backticks:
`` `unit price` * quantity ``. A path through a list of records names that
field of every item: `sum(lines.amount)`.

**Check a formula when it is written, not when it runs.** Every name, every
function and the kind of every value is known before the formula is used: a
field that does not exist, `name * 2`, a missing parenthesis or a function given
the wrong number of values is refused at once. The message says what is wrong in
plain words, and the part of the formula it is about is marked, so it can be put
right where it was typed. A formula that is accepted always gives a value of the
kind it promised, or none.

**Keep types apart, and say how they meet.** Numbers are added and multiplied;
a date and a number of days make a date, and two dates make a number of days;
texts are joined with `&`. A number becomes text only when asked —
`"Total: " & text(total)` — and then without the residue of binary arithmetic:
`text(0.1 + 0.2)` is `0.3`.

**Let empty mean empty.** A field that has not been filled in is empty, and so
is what is computed from it: a total with no price has no value, rather than a
value of zero that looks like an answer. A step that has no answer — a division
by zero, a date that does not exist — is empty in the same way, never an error
code or a number that is not one. Where a formula should carry on without a
value, it says so: `coalesce(discount, 0)`, `isempty(price)`.

**Where an empty value could not change the answer, give the answer.**
`false and anything` is false; `true or anything` is true. An `if` whose
question is empty has no answer, but the branch it does not take may be empty.

**Count what is there.** Functions over many values — `sum`, `average`, `min`,
`max`, `count` — skip what is empty: the average of 2, 4 and an unfilled value
is 3. A sum of nothing is 0; the average, smallest or largest of nothing is
empty.

**Text is empty when it says nothing.** An empty text joins as nothing and
compares as `""`, and a computed text that comes to nothing is shown as empty.

**Round as people write.** `round(1.005, 2)` is 1.01, halves away from zero, as
the number is written — not as binary floating point holds it.

**Show that a value is computed, and from what.** A computed field reads as
computed — distinct from a field to type into — and its formula, with the
fields it reads, is one step away. A field whose formula refers back to itself,
directly or through others, is a cycle: it is shown as one, naming the fields
in it, rather than computed.

**Recompute what changed, and only that.** When one field changes, only the
formulas that read it — and those that read them — are computed again, in an
order where each comes after what it reads. A formula that reads today's date
changes with the day as well.

## Conventions

| Written                                                              | Means                                                                          |
| -------------------------------------------------------------------- | ------------------------------------------------------------------------------ |
| `+ - * / %`                                                          | arithmetic on numbers; `%` is the remainder with the divisor's sign            |
| `released + 30`, `released - due`                                    | a date 30 days on; the days between two dates                                  |
| `&`                                                                  | texts joined                                                                   |
| `= <> < <= > >=`                                                     | comparison of two values of one kind                                           |
| `and`, `or`, `not`                                                   | logic on true-or-false values; `not` binds tighter than `and`, `and` than `or` |
| `if(question, yes, no)`                                              | one of two answers of one kind                                                 |
| `round(x, digits)`, `floor`, `ceil`, `abs`                           | numbers                                                                        |
| `sum`, `average`, `min`, `max`, `count`                              | over values and lists alike, skipping what is empty                            |
| `len`, `upper`, `lower`, `trim`, `text`                              | texts; `len` counts characters as people see them                              |
| `today()`, `days(from, to)`, `date(y, m, d)`, `year`, `month`, `day` | dates                                                                          |
| `coalesce(a, b, …)`, `isempty(x)`                                    | the first value that is not empty; whether a value is empty                    |

Function names and the words `and`, `or`, `not`, `true` and `false` may be
written in any case; field names are exact.

## Behaviour

Each statement is verified by a test in Chromium, Chromium (touch), Firefox, WebKit, WebKit (touch), unless it names the environments it applies to or is marked open.

### Values and operators

- Numbers are ASCII digits with an optional point, and texts are in quotation marks.
- A number of any length is the nearest value there is to it, as JavaScript reads it.
- A point needs a digit beside it, and a number has one point at most.
- Multiplication, division and remainder bind tighter than addition and subtraction, and each applies left to right.
- Any number of signs may precede a number, each minus turning it about.
- `%` is the remainder with the sign of the divisor, as spreadsheets have it.
- A date plus or minus a number is a date so many days on; a date less a date is the days between.
- `&` joins texts.
- A comparison gives true or false, and compares values of one kind.
- `and`, `or` and `not` combine true-or-false values; `not` binds tighter than `and`, and `and` than `or`.
- Words of the language and function names are read in any case; field names are exact.
- Spaces, tabs, line breaks and the ideographic space separate tokens.

### Names

- A field is named by its name, in any script.
- A name that is not an identifier is written in backticks, with a backtick or a backslash in it escaped.
- A dotted path names a field of each item of a list of records, as a list.
- A field called as a word of the language is written in backticks.
- A name followed by parentheses calls a function; alone, it names a field.

### Functions

- `round` rounds halves away from zero, to whole numbers or to the digits asked for, as the number is written.
- `round` to digits that are not a whole number from −15 to 15 is empty.
- `floor`, `ceil` and `abs` take a number.
- `min` and `max` take numbers or dates, any number of them, and lists of them.
- `sum`, `average` and `count` take any number of values and lists.
- `len` counts the characters of a text as people perceive them.
- `upper`, `lower` and `trim` change a text.
- `today` is the local date, unless fixed.
- Fixing today at what is not an ISO date is an error, and a fixed today is put back whatever the function does.
- `days` counts the days from one date to another.
- `year`, `month` and `day` take a date apart, and `date` puts one together.
- `date` of a day that does not exist is empty, rather than rolled over.
- `if` gives one of two answers of one kind.
- `coalesce` is its first value that is not empty, and `isempty` asks whether a value is.
- `text` writes a number to fifteen significant digits, a date as ISO, and true or false as words.

### Empty

- Arithmetic, comparison and functions of an empty number, date or true-or-false value are empty.
- A step that has no finite answer leaves the whole empty, whatever the steps after it would make of it.
- A date before year 1 or after 9999, or a fractional number of days on, is empty.
- `and` and `or` are not empty where the other side decides them.
- `if` with an empty question is empty; the answer not given may be empty.
- Text has no empty but the empty text: an empty text field joins and compares as "", and a result of "" is empty.
- `sum`, `average`, `min`, `max` and `count` skip what is empty.
- `sum` of nothing is 0; `average`, `min` and `max` of nothing are empty; `count` of nothing is 0.
- A value of the wrong kind from the record is empty.
- A result is never NaN and never negative zero.

### Errors

- A formula that cannot be read is refused, with a message and the part of it to mark.
- A field that refers to itself compiles: a cycle is for the caller to find.
- Parentheses and calls nest sixty-four deep; a formula nested deeper is refused at the opening too many.
- Ninety-nine thousand open parentheses, signs or `not`s are refused or read, never an exhausted stack.
- A formula is at most 100,000 characters; the characters past that are marked.

### Compiled once, evaluated often

- A compiled formula states its type and every path it reads, once each, in the order first mentioned.
- A formula that reads today says so, as its value changes with the day.
- Each mention of a field is asked for once per evaluation, as the formula reaches it.
- Evaluating never reads the formula's text again.
- A long formula is compiled in one pass: each character is looked at a fixed number of times.
- Folding a list reads each of its items once.
- A formula may be evaluated from within the `get` of another evaluation, of itself or another.

### Agreement with a model

- Agrees with a plain statement of its meaning on any arithmetic over fields that may be empty.
- `and`, `or` and `not` agree with three-valued logic, with precedence as written.
- A date so many days on agrees with the platform's calendar, across the years an ISO date can write.

## Reference implementation

React: `npx shadcn@latest add https://ui.publictheta.com/r/formula.json`