Formula fields: let the record do the arithmetic

Most business records carry numbers derived from other numbers: a margin, a total, a balance, the days remaining.

Typed in by hand, each one is correct on the day somebody typed it and gradually stops being correct afterwards.

A formula field reads other fields

The field calculates from whatever the record currently holds, so a change to a cost updates the margin without anybody revisiting it.

That removes an entire category of error: the record that was right, changed, and kept showing the old answer.

The common ones, and what they answer

• A total, adding lines or multiplying a rate by a quantity.
• A margin or a percentage, comparing an item's cost against its sale price.
• A difference between dates, such as days since an invoice or weeks until a deadline.
• A balance, subtracting the payments received from the amount owing.
• A label, producing a word from a number, so a report reads as a category.

Between them those cover most of what people otherwise recalculate manually, one record at a time.

Dates are where formulas earn the most

A field holding days until a due date changes every morning, and no human process can keep that current across hundreds of records.

Once it exists, a list can sort by urgency and a dashboard can count what is overdue, neither of which works from a typed number.

Name the field for the question it answers

A field called Calculation tells nobody anything, and a field called Days Overdue explains itself in a list, a report and an export.

The name travels much further than the formula does, so it deserves more thought than it usually gets.

A formula is only as good as its inputs

A margin calculated from a cost field nobody fills in shows zero, confidently, on every record.

Making the inputs required, where the calculation matters, is what stops a formula producing tidy nonsense.

Empty is not zero

A blank field and a field holding zero mean different things, and a formula that treats them identically hides missing data.

Deciding which behaviour the business wants, before building the field, prevents a report that looks complete and is not.

Keep the formula readable

A long expression nobody can follow becomes something nobody will change, and eventually somebody rebuilds it beside the original.

Two simple fields usually beat one clever one, and the intermediate value is often useful in its own right.

A formula field is not for history

A formula shows today's answer, so a margin recalculates if somebody edits a past cost, and last year's report changes quietly.

Where a figure has to stay as it was at a moment, the record needs a stored value rather than a calculated one.

Widgets and dashboards read them like any field

A calculated field works in a filter, a total, a chart and a board column exactly as a typed field does.

Our guide to what to measure on a dashboard covers choosing which of those numbers deserves the space.

Start with the number somebody recalculates most

The best first formula field is whichever figure a person currently works out with a calculator every week.

That one saves time immediately and demonstrates the idea better than any explanation of the feature does.

What to change first

• The number somebody recalculates weekly becomes a formula field
• Each field's name states the question it answers
• Fields a formula depends on are required where the answer matters
• The treatment of empty values gets decided deliberately
• Long expressions break into two readable fields
• Figures that must not change later store as values, not formulas
• Calculated fields feed the filters, totals and charts directly