Loading

Creating a Calculated Field

A calculated field derives one column from others on the same row — a line net, a weight from dimensions, a margin from cost and sell.

Before you start

The target column must already exist and be numeric. Mark it read-only on your forms too, so nobody is invited to type into a box that will be overwritten on save.

Adding the definition

Open Calculated Fields and add a row.

  • Datastore — the datastore holding the row being calculated. For a line total this is the lines datastore, not the header.
  • Kind — Formula.
  • Target Column — the column the result is written into.
  • Expression — the arithmetic, written against other column names on that datastore.
  • Order — position in the sequence when a datastore has several definitions. Lower runs first.

The Aggregate and Child fields belong to rollups and are ignored here.

A worked example

Order lines with Quantity, Unit Price and Discount Percent, filling in Line Net:

ROUND(quantity * unit_price * (1 - discount_pct / 100), 2)

Note the rounding. Round money explicitly to the places you store, or fractions of a penny accumulate across a long document and the total will not match the sum of the printed lines.

Operators and functions

The four arithmetic operators and brackets, with the usual precedence. The function set is fixed: ROUND, ABS, CEIL, FLOOR, IF, COALESCE, and the aggregates SUM, COUNT, AVG, MIN and MAX. Anything else is rejected.

Empty values and division by zero

An empty column is not zero, and arithmetic involving one will not give you what you want — wrap optional columns in COALESCE so a missing discount behaves as no discount. Dividing by zero does not error and does not produce zero; the calculation is skipped and the column keeps its previous value, matching how the database itself behaves.

Why the language is limited

Expressions are not executed as program code and are never passed to the database as a query. They are checked against the fixed operator and function list and then evaluated. That is why the function set cannot be extended from the Architect Panel, and it is also why a mistake in an expression cannot damage anything beyond the column it was meant to fill.

Switching it on

The definition alone does nothing. The datastore also needs a table rule — see Keeping Totals Correct.