Formula field

A field that works itself out from others, like in a spreadsheet: a total that multiplies price by quantity, the days between two dates, a full name that joins first and last name. Nobody types it; Selfform works it out when the record is saved.

Setting it up

• Label and Help text, as in any field.
• Result type: whether the formula gives a number, a text or a date.
• Decimals and Currency: for a number, how it shows, as in a number field.
• Expression: the formula. Other fields are written by their name in square brackets, like [price] * [quantity].

You don't have to type the names: Insert field, Insert operator and Insert function put them in for you. When you leave the expression, Selfform checks it and tells you if something's off; for that the application has to be saved.

What you can use

• Arithmetic: + - * /. You can add or take days from a date ([start] + 7), and taking one date from another gives the days between them.
• Joining text: &, as in [first] & ' ' & [last].
• Comparing (inside IF): = <> > >= < <=.
• Functions: IF(), AND(), OR(), NOT(), ROUND(), MIN(), MAX(), ABS(), SUM(), AVG(), COUNT(), CONCAT(), LEN(), UPPER(), LOWER(), TRIM(), TODAY(), DATE() and DAYS().

A formula reads text, number, date and checkbox fields; an email, a phone, a picked option or a folio it reads as text. It can also use another formula, but not two formulas that use each other.

Reading another record

Through a relation, the formula reads a field of the chosen record: [relation].[field], as in [price] * [customer].[discount]. Insert field lists them as Relation → Field. While the relation is empty, the formula stays blank.

If the relation holds several records, the formula gets a list, which has to be summed up with SUM(), MIN(), MAX(), AVG(), COUNT() or CONCAT() (which joins them with commas). COUNT([relation]) says how many records it holds. With none, SUM() and COUNT() give 0 and CONCAT() gives empty text.

The rows of a dialog read the same way: SUM([lines].[quantity]) adds up the quantity of every row, and COUNT([lines]) counts them. If the rows have a relation, SUM([lines].[product].[price]) adds up each row's product price.

A formula can also go inside a dialog: it's worked out on each row, like [quantity] * [product].[price], and the form can add it up with SUM([lines].[amount]).

When a related record changes, Selfform works out again the formulas that read it.

Reading the records that point here

It works the other way too: a formula can sum up the records of another entity that point to this one. A product's stock, say, is the sum of its movements: SUM([Movements ← product].[quantity]), and COUNT([Movements ← product]) counts them. You can type <- instead of ←. Insert field offers them as "Movements ← Product → Quantity" once the application is saved.

Try it

Here the field looks empty, as on a real form before saving: the result shows up when the record is saved.

Good to know

• The value doesn't change while you type; it's worked out on saving.
• A formula reads its own record's fields, and one step through a relation: it can't go on from a related record to another.