Computed columns

A computed field works its value out from other fields rather than asking anybody to type it. A line total, a variance, a percentage, an elapsed time.

Set the field's type to Computed in the form builder and give it a formula.

Writing a formula

Fields are named in {braces}.

Token	Refers to
{fieldKey}	A field in the same section, on the same row.
{sectionKey.fieldKey}	A field in another section. Always its first row.

The builder gives you buttons that insert the right token, which is faster and safer than typing the key by hand.

Functions

Function	What it does
SUM(section.field)	Adds that field down every row of the section.
AVG(section.field)	Averages it.
MIN(section.field) / MAX(section.field)	Smallest / largest.
COUNT(section.field)	How many rows have a value.
SUMIF(...)	Adds, but only the rows meeting a condition.
TIMEDIFF(...)	The elapsed time between two values.

Ordinary arithmetic — + - * / ( ) — works as you would expect.

Examples

{quantity} * {unitPrice}
{closingMeter} - {openingMeter}
SUM(lines.lineTotal)
({actual} - {planned}) / {planned} * 100

A computed field may use another computed field

Line total can feed Subtotal, and Subtotal can feed Total including tax. Trackapt resolves computed fields in repeated passes until the figures settle, so the order you define them in does not matter.

A circular reference — two fields each depending on the other — settles as blank rather than hanging.

They recalculate as you type

Every computed field on the form recalculates whenever any value changes, including values in other sections. You do not have to leave a field or save to see the result.

Publishing a computed value

A computed field can be linked to a monitored parameter like any other, in which case its result is published as a reading against the item on the row.

Be deliberate about this. A computed figure that is simply a restatement of two other published figures — closing minus opening, where both are already published — adds a third number to the reports that cannot disagree with the other two but does clutter them.

Computed parameters are a different thing

Two similar-sounding features, and it is worth knowing which you want.

	Computed field	Computed parameter
Lives on	A form	A tracker group
Works over	Other fields on the same entry	Other parameters over time
Recalculates	As you type	When its inputs change

A form field can be used as an operand in a computed parameter, which is how a figure captured on a document reaches the parameter engine. Only a range-based computed parameter takes form fields. A form field counts as one of its inputs when the figure is capped at the last date every input has figures, and its entries count for the period the form is filled for: a monthly form's for its whole month.

What a computed field cannot do

• It cannot reference a balance. Balances are a separate mechanism, so cross-mechanism figures — litres per hour run against a booked balance — cannot be expressed.
• Aggregation functions are not recalculated on the server after the fact. A SUM or AVG is worked out when the form is filled and stored as it stood. Plain arithmetic is re-evaluated.

If a computed field is not updating

1. Check the token spelling against the button the builder offers — a token naming a field that does not exist yields nothing rather than an error.
2. Check that a field it references has not been retired.
3. Remember that {sectionKey.fieldKey} always reads the first row of that section. If you meant to read down the section, you want SUM or AVG.