Calculated Fields & Rollups

Formulas and Rollups

A datastore can carry values the platform works out rather than somebody typing them. There are two kinds.

Where to find it

Architect Panel → ERP - Setup:

• Calculated Fields — formulas and rollups, as a flat typed list

Architect Panel → Automation:

• Tasks — the recalculation task, which ships disabled

Formula and rollup

• Formula — arithmetic over other columns of the same row. Quantity multiplied by unit price.
• Rollup — an aggregate over a child table. The sum of line values across an invoice’s lines.

A definition names the table and target column, its kind, and then either an expression or the aggregate, source table, link, field and filter.

Why server-side matters

Arithmetic used to happen only in the browser, on line-item grids. A value computed in the browser is a value the user can change, nothing recomputed it on save, and nothing could aggregate it.

That is adequate for a display total and not adequate for anything you bill from.

Five aggregates

Sum, count, average, minimum and maximum. Count is the one that works without naming a source field, since it is counting rows rather than adding values.

The expression language is small on purpose

Arithmetic over columns, with a whitelisted set of functions: rounding, absolute value, floor, ceiling, a three-argument conditional, and a coalesce taking any number of arguments.

That is deliberately less than a general language, and the smallness is the feature.

There is no path to executing code

Expressions are tokenised, compiled against a fixed operator table and the whitelisted functions, and evaluated by a loop. A stored expression cannot reach PHP or SQL. Rollups build their query from validated identifiers with every value bound.

Worth knowing, because two older field types in the platform do evaluate authored code, and this deliberately repeats neither.

A failure leaves the field alone

The behaviour to understand before relying on this. An unknown column, a malformed expression or an unknown function writes an error to the log and leaves the target field untouched. It is not set to zero, and the save is not blocked.

The reasoning is that a wrong number silently written to a money column is the worst outcome, and blocking every save on a configuration typo is the second worst. The field keeps its previous value and the error is in the log.

Which means: check the log. A field that quietly stopped updating looks exactly like one whose inputs have not changed.

Division by zero produces nothing

The field is skipped rather than set to zero or an error — matching how a database behaves rather than how PHP does.

Round money explicitly

Evaluation is in floating point, and the module does not guess a scale. Wrap money expressions in a rounding function to two places and make the target column a fixed-decimal type.

Skipping this produces totals that are a penny out, occasionally, in a way that is very tedious to trace.

Recalculation is a task, and it ships disabled

A scheduled task exists to recompute stored rows in batch. It is installed switched off, so turning it on is a decision — and one worth making, because otherwise a definition changed today does not reach records saved yesterday.

Worked example

An invoice line carries a formula rounding quantity times unit price to two places, into a fixed-decimal column. The invoice header carries a rollup summing those line values, filtered to non-cancelled lines. A definition typo was found in the error log rather than in an incorrect invoice, because the field had kept its previous value.

Recommendations

• Round money explicitly and use fixed-decimal columns.
• Watch the error log — a failed calculation is silent by design.
• Enable the recalculation task deliberately if you change definitions.
• Filter rollups so cancelled or draft children do not count.

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.

Creating a Rollup

A rollup totals a child datastore onto its parent — invoice lines into an invoice, timesheet entries into a week, components into an assembly.

Where to find it

Architect Panel → ERP - Setup:

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

What it needs

A rollup is defined on the parent and points down at the child.

• Datastore — the parent holding the total.
• Target Column — the column receiving it.
• Aggregate — SUM, COUNT, AVG, MIN or MAX.
• Child Datastore — where the rows being totalled live.
• Child Link Column — the column on the child pointing back at the parent. This decides which lines belong to which invoice.
• Child Value Column — the column being totalled. Not needed for COUNT.
• Child Filter — optional.

A worked example

Invoices have Net Total; invoice lines have Line Net from a calculated field. The rollup sits on invoices: Net Total as target, SUM as aggregate, invoice lines as child, the invoice reference as the link, Line Net as the value.

Filtering which rows count

The Child Filter uses the same criteria format as table rules, so the full operator vocabulary is available. Common uses:

• Billable lines only — cancelled or informational lines stop contributing without being deleted.
• Outstanding items — a COUNT filtered to lines not yet complete, giving a live count on the header.
• Subtotals — two rollups on the same parent reading the same child, filtered differently, producing a goods total and a carriage total side by side.

A caution about filters

A filtered total is only as trustworthy as the column it filters on. If the billable flag can be changed after approval, the total changes with it — including on documents already sent.

Where that matters, lock the column with field-level permissions, or copy the value onto the line at approval so later edits cannot rewrite history.

Rollups that consume other rollups

A rollup can total a column produced by another definition, provided those values are already calculated. Within one datastore that is the Order column; across datastores it is the timing of the table rules.

Recommendations

• Use COUNT rollups for live queue figures — outstanding lines, unmatched items — not just for totals.
• Filter on a column that cannot change after approval, or freeze it at approval.
• Check the link column is indexed on large child datastores.
• Build the line formula before the header rollup, and test in that order.

Keeping Totals Correct

Defining a calculation does not make it happen. Each datastore that should calculate needs a table rule, and the timing matters.

Where to find it

Architect Panel → ERP - Setup:

• Calculated Fields — the definitions themselves

Architect Panel → Automation:

• Tasks — where the recalculation sweep is enabled
• Task Log — what the sweep corrected

The two rules

On the datastore, add a table rule with the action Run PHP Function.

• Same-row formulas — function AMformula_rule, running before the save, because the values it produces are part of the record being written.
• Rollups — function AMformula_rollupRule, running after the save, because it totals rows that must already be stored.

Both names are typed by hand and are not validated when the rule is saved, so a typo means nothing calculates. Check spelling first when a new definition appears to do nothing.

Where to attach the rollup rule

The step most often got wrong. A rollup is defined on the parent, but the total goes stale when a child changes — so the child datastore also needs a rule, so that adding, editing or deleting a line updates the invoice it belongs to.

A calculation that works on creation but not on edit is almost always this.

Evaluation order

Several definitions on one datastore run in Order sequence, lowest first. A line with net, then tax from net, then gross adding the two, only works if each has a lower Order than the one that consumes it.

Number them 10, 20, 30 so you can insert one later without renumbering everything. Order applies within a single datastore; the parent-child relationship is handled by rule timing, not by Order.

The recalculation sweep

Table rules cannot catch changes arriving by other routes — imports, data extraction, direct database edits, a restored backup. Enable Calculated Field Recalculation under Automation → Tasks.

It reads the audit trail for changed child rows, maps them back to their parents, and recalculates those. After a large import, run it by hand rather than waiting for the schedule.

When a number looks wrong

Calculations fail quietly by design: a bad definition leaves its column untouched and logs the reason rather than writing a wrong number or blocking the save. So a broken calculation looks like nothing happening.

• Nothing calculates — the table rule, the function name, or the timing. Then check Enabled.
• Works on create, not on edit — the rule is on the header but not the lines.
• Keeps its old value — could not be evaluated. Check the error log for a renamed column, a misspelled function or unbalanced brackets.
• Empty result — a division by zero, or an input empty rather than zero.
• Close but not exact — rounding. Total the rounded column, not the unrounded one.
• Wrong after an import — run the recalculation task.

Worked example

An invoice total is right when raised and wrong after a line is edited. The header has the rollup rule; the lines have the formula rule but nothing telling the header to recalculate. Adding the rollup rule to the lines datastore fixes it, and running the sweep once corrects the invoices already affected.

Recommendations

• Attach the rollup rule to the child as well as the parent.
• Test create, edit and delete — all three, every time.
• Enable the sweep, but treat frequent corrections as a missing rule.
• Check the error log first when a column keeps a stale value.