How to build 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, a kind of record your team defines (What is an Object? explains those), and an admin builds it on the Object's Fields tab in Settings › Objects.

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: each stays correct on its own once it is set up. This page covers the four places a calculation can read from and how to choose between them.

Before you start

You need to be an admin, because Settings 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 to calculate from before Use a formula appears: a Number, Money, Percent or Date field, a Linked record field, or a link to another of your Objects made with Link something. If it has none yet, a line in the dialog says 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. How to add a field and pick its type covers adding those.

How do you start a calculated field?

1. Open Settings › Objects, click the Object, and open its Fields tab.
2. Press Add field and type a Label.
3. Tick Use a formula, under the Type menu.
4. Pick the result in Type: Money, Number, Percent, Count, Text or Date.
5. In Where the numbers come from, pick one of the four sources described below, and fill in what it asks for.
6. Press Save field.

Save field stays greyed out until the calculation is complete, and a line under the builder says in one sentence what is still missing, such as "Choose the date to count from." Read that line rather than hunting for the problem.

Get the calculation right before you save it, because like a field's label and type it is fixed once the field exists. How to plan your fields before you add them explains what changing your mind costs.

Which source gives which result?

Each source can produce some results and not others, and the dialog steers you:

• Other fields on this record gives Money, Number or Percent.
• Linked records gives Count, and also Money, Number or Percent totals.
• Days from a date always gives Number, and switches Type to Number for you.
• A value from a linked record gives whatever type it reads, including Text and Date.

A source that cannot work yet shows greyed out with the reason in its name, for example "Days from a date" followed by "no date field yet". A Count result works only from Linked records, and Text or Date only from a value on a linked record; the line under the builder says which source to pick if the two do not match.

Work something out from other fields on this record

Choose Other fields on this record when everything you need is on the record in front of you. Pick an Operation: Add up, Subtract, Multiply, Divide, Average, Smallest or Largest. Then pick the fields under it.

Add up, Average, Smallest and Largest take up to eight fields, and you add or remove 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. Pick each field once.

Fairgreen's Landed cost per unit adds up three Money fields: Unit cost, Freight per unit and Duty per unit. Total landed cost then multiplies Landed cost per unit by Units ordered, because a calculation can read another calculation on the same record.

Build downwards like that: the fields people type in, then the field that combines them, then the field that combines that. Pick Money as the result for an amount and Percent for a share. How to read Tideswell's numbers and dates explains how each is displayed.

Count or total the records linked to this one

Choose Linked records when the answer lives on records of another of your Objects that are linked to this one with Link something, in the Related section of a record. Pick the Object in Which linked records, then an Operation: Count, Add up, Average, Smallest or Largest.

Make the first link from a record of the Object that will hold the calculation. At Fairgreen, someone opens a production run, presses Link something and picks a quality check; from then on, Production runs offers Quality checks here. How to link a record to work, and see what points at it covers making those links.

On a Fairgreen production run:

• Checks logged uses Count with a Count result, so it shows how many checks are linked.
• Units rejected uses Add up on the checks' Field on the linked record, Units rejected, so the run shows one total rather than a number somebody re-added by hand.
• Worst check uses Largest on the same field.

Only count some of them (optional) narrows which records are included. Leave it at Every one of them, or pick an Options list field on the linked Object, then is or is not, then a value. A second Count field with the rule Result is Failed gives each run a Failed checks figure beside Checks logged.

Count the days since or until a date

Choose Days from a date, pick the Date to count from, and under Count choose Days since that date or Days until that date. The result is a plain Number, and the dialog sets Type to Number for you.

Fairgreen uses two of these. Days until ex factory sits on every production run, so the sourcing lead can see how close each one is. The quality team has the mirror image on its own Object: Days since inspected, counted from each check's Inspected date.

The number moves on with the calendar, so nobody has to update it.

Pull a value across a link

Choose A value from a linked record to show a value from the record a Linked record field points at. Pick the field to read through in Which link, then the field you want in Which field on the linked record. Your field takes that field's type automatically.

Fairgreen's quality checks each point at a production run through their Production run field. Pulled across, the run's Ex factory date sits on every check, so an inspector sees the deadline without opening the run.

While you set one up:

• The value follows the link. Point a check at a different run and the date changes with it, because it was never a copy anyone typed.
• Pick a field that people type in. The menu offers the far record's Money, Number, Percent, Text and Date fields, and leaves out Count and calculated fields.
• Everyone who can see a record here sees the value. The dialog says so under the menus.

What if the other Object is more restricted?

Tideswell refuses to save the field and says how to fix it. This applies to Linked records and A value from a linked record, because both show what is on another Object to everyone who can see this one.

The message names who can see each Object and suggests two ways forward. Either raise who can see the Object you are building on in Settings › Permissions until the two match, or read a different field. How to set who can see and edit an Object covers the View row for each Object.

How do you tell a calculated field apart?

It carries a Calculated pill in the Fields tab list, so you can see which of your fields are answers and which are ingredients. On the record itself it shows its answer, with no box to type in.

How to add and edit a record shows what people see on the record, and How to rename, reorder or retire an Object covers tidying the field list once your costing sheet has grown.