A field that works itself out

A calculated field is a field nobody fills in. You describe what it should work out, using menus, and Tideswell keeps the answer up to date as the records around it change. Like every other field, it belongs to an Object, the kind of record your team defines (What is an Object? explains what those are), and an admin builds it in Settings › Objects on that Object's own page.

This page covers the four things a calculation can read from, and how to choose between them. A landed cost that adds up three amounts, a count of the quality checks against a production run, the number of days until a factory date: all of them stay correct on their own once you have set them up.

Before you start

You need to be an admin, because Settings › Objects only appears for admins. How to work out who can do what in your workspace explains how a person's role decides what they see.

The Object also needs something worth calculating from before the option appears: at least one Number, Money or Date field, or a Linked record field. If it has none of those yet, a line on the screen tells you what to add first.

Add the ingredients before the answer. For Fairgreen Golf Co., that means a Production runs Object already holding Unit cost, Freight per unit, Duty per unit, Units ordered and an Ex factory date. Add a field and pick the right type covers adding those, and Link one Object to another covers the links a calculation can reach across.

What can a calculated field work out?

Tick Use a formula in the Add field dialog and the field stops being something people type into. You then pick where the numbers come from, one of four sources: other fields on this record, its linked records, days from a date, or a value carried across a link. What you pick decides what the field can produce.

The result is one of six things: Money, Number, Percent, Count, Text or Date. The Type menu narrows itself to whatever your source can actually produce, so you cannot build something that does not add up.

The operations available are Add up, Subtract, Multiply, Divide, Average, Smallest, Largest and Count. Add up, Average, Smallest and Largest take up to eight inputs, and you add or remove input rows as you go. Subtract, Multiply and Divide take exactly two, labelled First number and Second number, so the order is explicit and a division never lands upside down.

If Save is greyed out, the dialog says in one sentence what is still missing, for example that a second number has not been chosen. Read that line rather than hunting for the problem.

Get the calculation right before you save it. Like a field's label and type, the calculation is fixed once the field exists. Plan your fields before you add them explains what changing your mind costs, and why this is worth ten minutes up front.

Work something out from other fields on this record

Choose the source "Other fields on this record" when everything you need is already on the record in front of you. Fairgreen's Landed cost per unit is Add up across three Money fields: Unit cost, Freight per unit and Duty per unit. Pick Money as the result and the field shows with a currency wherever it appears.

Then build on it. Total landed cost is Multiply, with Landed cost per unit as the first number and Units ordered as the second. A calculation can read another calculation on the same record, which is how a costing sheet gets built up one honest step at a time.

Two things to keep in mind while you pick the inputs:

• Pick each field once. If you find yourself wanting the same value twice, you are usually really after a second step, as above.
• The result type has to suit the inputs. Money times a Number gives Money; a share of one amount against another wants Percent. How to read Tideswell's numbers and dates explains how each of those is displayed.

Count or total the records linked to this one

Choose the source "Linked records" when the answer lives on the records pointing at this one. Pick the connection, then the field on the far record, then the operation.

Count tells you how many there are. Add up, Average, Smallest and Largest give you a number drawn from every one of them.

On a Fairgreen production run:

• Checks logged counts the linked quality checks. Count is the result type, and counting linked records is what produces it.
• Units rejected adds up the Units rejected field across those same checks, so the run shows one honest total rather than a number somebody re-added by hand.
• Worst check takes the Largest reject count of any single inspection.

You can narrow which records are included with a rule. Set "Result is Failed" and Checks logged becomes Failed checks, counting only the inspections that went badly.

Rules read as a sentence and can be negative too, such as "Type is not Warranty". If the rule points at a stage rather than a field, The stages a record moves through covers naming those stages.

Count the days since or until a date

Choose the source "Days from a date", pick the Date field, and say whether you want the days since it or the days until it. The result is a plain Number, and the Type control switches to Number for you, so a run can show "Days until ex factory" as a figure you can sort a list by.

Fairgreen uses two of these. Days until ex factory sits on every production run, so the sourcing lead can sort the list and see what is closest. The quality team has the mirror image on its own Object: Days since inspected, counted from each check's Inspected date, so an inspection that has gone stale is obvious at a glance.

The number moves on its own as the calendar moves, which is exactly what you want on a field people make decisions from.

Pull a value across a link

Choose the source "A value from a linked record" to copy a value in from the record you have linked to, so it can be read and sorted on from this side. Pick the link to read through, then the field on the far record. The menu shows each field with its own type icon, and your field takes that type automatically.

Fairgreen's production runs each link to a Style, and the style carries the Target cost. Pulled across, that target sits on the run right next to Landed cost per unit, so the two numbers people actually compare are finally on one screen.

Worth knowing while you set one up:

• The value follows the link. Change which style a run points at and the target cost changes with it, because it was never a copy anyone typed.
• The fields offered are the ones holding a number, an amount, a percentage, a count, a line of text or a date. This is also the source to reach for when you want a Text or Date result.
• Everyone who can see a record here will see the value you pull onto it. If the Object you are reading from is more tightly restricted than this one, Tideswell says so and names the fix. Either raise the View dial on this Object until the two match, or read a different field instead. See Who can see and edit an Object.

What do you do when a calculation depends on itself?

Tideswell refuses to save a calculation that would end up feeding itself, and it names the loop so you can see it. The message walks the chain back for you, for instance that Total landed cost depends on Landed cost per unit, which depends on Total landed cost. Nothing is saved, so nothing is broken.

To untangle it, work out which of the two fields is the real ingredient and which is the real answer. In the Fairgreen example, Landed cost per unit is the ingredient (it adds up three typed-in amounts) and Total landed cost is the answer (it multiplies that by the units ordered). The mistake is almost always an attempt to make the ingredient read the answer back, usually to divide a total by a quantity.

The habit that prevents it is to build downwards: start with the fields people type in, then the field that combines them, then the field that combines that. Each step reads only from the ones above it, and naming them in that order ("Landed cost per unit", then "Total landed cost") makes the direction obvious at a glance.

Calculated fields are marked with a Calculated pill in the field list, so you can see which of your fields are answers and which are ingredients. From there, How to add and edit a record shows what people see on the record itself, and Rename, reorder or retire an Object covers tidying the field list once your costing sheet has grown.