Calculated Fields & Rollups

Calculated Fields and Rollups Overview

Some numbers should never be typed in. A line total is quantity times price less discount; an invoice total is the sum of its lines. If a person can edit those figures, or they are only worked out in the browser, the system will eventually bill somebody the wrong amount.

Why the browser is not good enough

ActiveManage has long done arithmetic in a line-item builder as you type. That is useful for showing a running total, but it is display only. A value produced in the browser is a value the user can change before saving, nothing recalculates it if the row is edited later, and nothing can add it up across rows. An ERP cannot bill from a number like that.

Calculated fields close the gap. The arithmetic runs on the server every time the row is saved, using the values actually being stored.

The two kinds

• Formula — reads other columns on the same row. A line total from quantity, unit price and discount.
• Rollup — reads a child datastore and aggregates it. An invoice net total, being the sum of the net on each line.

They work together: a formula produces a value on each line, then a rollup totals it onto the header.

Where to find it

These live in a datastore called Calculated Fields, opened from All Datastores in the Architect Panel. One row per derived column, with an Enabled tick so a calculation can be suspended without being deleted.

How this relates to the rest of the ERP layer

Calculated fields are the lowest layer, and unlike the other modules they need no binding — they are configured directly against a datastore and its columns. Higher modules assume the numbers underneath them are already right: the ledger posts what a document says, and a document total is usually a rollup. Get this layer correct first.

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.

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.

What a rollup needs

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

• Datastore — the parent, holding the total.
• Target Column — the column on the parent 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, below.

A worked example

Invoices have Net Total; invoice lines each 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

Not every child row should be included. The Child Filter uses the same criteria format as table rules, so the full operator vocabulary is available — equals, not equals, greater than, is empty and the rest. 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.

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. 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 of 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.

Keeping Totals Correct

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

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 in by hand and are not validated when you save the rule, so a typo simply means nothing calculates. Check spelling first when a new definition appears to do nothing.

Where to attach the rollup rule

This is 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

Where a datastore has several definitions they run in the sequence set by Order, lowest first. A line with net, then tax calculated 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 rather than 1, 2, 3 so you can insert one later without renumbering.

Order applies within a single datastore. The relationship between a child and its parent is handled by the rule timing, not by Order.

The recalculation task

Table rules cannot catch changes that arrive by other routes — a bulk import, data extraction writing straight into a lines datastore, a correction applied directly to the database, a restored backup. The recalculation task is the safety net. It reads the audit trail for changed child rows, maps them back to their parents, and recalculates those parents.

It ships disabled. Enable it in Scheduled Tasks once your calculations are working, and run it overnight. If it is regularly correcting things, a table rule is missing somewhere — add the rule rather than running the sweep more often. After a large import, trigger it manually rather than waiting.

When a number looks wrong

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

• Nothing calculates at all — the table rule, its function name, or its timing. Then check the definition is Enabled.
• Works on creation, not on edit — the rule is attached to the header but not the lines.
• The column keeps its old value — the definition could not be evaluated. Look in the error log for a renamed column, a misspelled function or unbalanced brackets.
• The result is empty — a division by zero, or an input that is empty rather than zero.
• Close but not exact — rounding. Decide which figure is authoritative, usually the rounded line, and total that column.
• Wrong after an import — imports do not fire table rules. Run the recalculation task.