Calculated fields that work themselves out in Base by Zaweba
A calculated field in Base by Zaweba works out its value from other fields, such as quantity times price, and stays read-only and up to date on every form.
Some values should never be typed at all. A line total is the quantity times the unit price. A display name is a name and a town put together. A renewal date is a start date plus a month. Type those by hand and sooner or later one is wrong, because someone changed the quantity and forgot the total. A calculated field in Base by Zaweba avoids that: the database works the value out from the other fields in the record, every time.
You add one in the table designer like any other field, but instead of a type you give it an expression. Examples that Base supports include [Quantity] * [UnitPrice], UCase([Name]) & " (" & [Town] & ")" and DateAdd("m", 1, [Start]).
What happens behind the scenes
Base doesn't calculate these values in a form and hope they stay right. It creates them as generated columns in the SQLite database inside your .obase file, so the database engine itself keeps them up to date whenever the fields they depend on change. When you export the database as a website, they become generated columns in MySQL as well, so the browser version works them out in exactly the same way.
Because the value is generated, it is read-only. In the datasheet and on forms it shows like any other field, but nobody can type over it. Change the quantity and the total follows.
Good uses for a calculated field
Money is the obvious one. A line total on an order line, or a price including a fixed markup, is something you want stored with the record and always right. Joined text is another: a full name from first and last names, or a label that combines a name and a town for a dropdown, as in the UCase example. Dates work well too, such as a review date a month after a start date with DateAdd.
Calculated fields are especially handy where several things use the same figure. A form, a report and a query can all show the line total without each one repeating the sum.
Calculated field or calculated query column
Base can also put a calculation in a query as a calculated column. The choice comes down to where the value belongs. If it is part of what a record is, like a line total, and you want it on every form and in every export, put it in the table. If it is a one-off view for a particular report, such as a total with VAT for this quarter's figures, keep it in a query and leave the table simple.
A field can only work from other fields in the same record. Totals across many records, such as the sum of an invoice's lines, belong in a query, a sub-form total or a report.
Mistakes to avoid
Keep expressions short and readable. A long chain of functions is hard to check and hard to change later. If a calculation has several steps, it is often clearer to split it across two calculated fields.
Watch out for empty fields. If the quantity can be blank, think about what the total should show then, and test a record with the field empty before you build reports on it.
Finally, don't make a calculated field from something that ought to be a fixed record of the past. A price charged on an old invoice should stay as it was, even if today's price changes. Store that as an ordinary field, and calculate only the things that really should always follow their inputs.
