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.

Where to find it

Architect Panel → ERP - Setup:

  • Calculated Fields — add a row with Kind set to Formula

Before you start

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

The fields

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

A worked example

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

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

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. This is the single most common cause of an invoice that is a penny out.

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, 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 behaves.

Why the language is limited

Expressions are never executed as program code and 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 set cannot be extended from the panel — and also why a mistake in an expression cannot damage anything beyond the column it was meant to fill.

A second worked example — margin

A sales line holds Cost and Sell. Margin percent is ROUND(IF(sell = 0, 0, (sell - cost) / sell * 100), 1). The IF guards the divide, so a zero-value line reports a zero margin rather than being skipped and left stale.

Recommendations

  • Round every money column explicitly.
  • Wrap optional inputs in COALESCE.
  • Guard divisions with IF where a zero denominator is possible.
  • Number Order in tens so you can insert later.