How to make a field that calculates itself
Add a calculated field in Base by Zaweba with an expression like [Quantity] * [UnitPrice], and the database works the value out and keeps it right.
Use a calculated field. In Base by Zaweba you add a field to the table whose value is worked out from other fields by an expression, such as [Quantity] * [UnitPrice]. The database fills it in for every record, updates it when the other fields change, and shows it read-only in the datasheet and on forms, so nobody can type over it by mistake.
Writing the expression
Field names go in square brackets, and the expressions follow the style Access users know. A line total is [Quantity] * [UnitPrice]. Text can be joined with an ampersand, so UCase([Name]) & " (" & [Town] & ")" gives a name in capitals with the town in brackets after it. Dates can be moved on with DateAdd, so DateAdd("m", 1, [Start]) is one month after the start date, which is handy for renewals and review dates.
Start simple. Get the line total working, check it against a few records you can add up in your head, then build on it.
How it is stored
Behind the scenes Base makes the field a generated column, a feature of SQLite and of MySQL. The database itself owns the calculation, which has two good consequences. The value can never drift out of step with the fields it comes from, and anything that reads the table (a query, a report, an export) sees the right answer without needing its own copy of the formula.
It also explains why the field is read-only. If you need to override a value by hand now and then, a calculated field is the wrong tool; use an ordinary field and fill it with an update query instead.
Calculated field or calculated query column
Base offers a second way to calculate: a column in a query, such as Gross: [Quantity] * [UnitPrice] * 1.2, which can be sorted and totalled like any other column. Choosing between them is mostly about how widely you need the result.
Put it in the table when the value belongs to the record and you want it everywhere: on the form, in the datasheet, in every query and on the website. A line amount on an order line is a good example. Put it in a query when it is a one-off view or depends on something that changes, such as a VAT rate you are trying out, or a figure that only matters for one report.
Getting totals from it
Once the field exists, totalling it is easy. The datasheet shows totals for currency columns. In the visual query builder you can sum the field grouped by customer or by month. A grouped report can subtotal it under each heading with a grand total at the end, then print the result or save it as a PDF. Sub-forms showing order lines under their order also show an Amount column with a total.
Things to watch
Choose the types of the source fields well. Multiplying a quantity by a price needs both to be numbers, so make Quantity a whole number and UnitPrice currency rather than text. If one of the fields can be empty, think about what the result should be; giving the source field a default of 0 is often the simplest answer.
Renaming a field that an expression uses is handled for you in most of Base, since forms, queries and reports follow renamed fields. Even so, after any big redesign, open the datasheet and glance down the calculated column to make sure it still reads sensibly.
The calculation on the web
When Base by Zaweba exports the database as a PHP and MySQL site, the calculated field goes with it as a MySQL generated column. The value is worked out by the database on the server as well, so the pages people see in a browser show the same figures you see on the desktop.
