A formula from other fields
->calculate('price * quantity') multiplies two fields of the form. A formula can use + - * /, brackets and numbers such as 0.0625. The calculated field is read-only.
Note: Try it
Type 12.50 as the price and 4 as the quantity: the amount becomes $50.00 straight away. Change the quantity to 10: $125.00.
Lines of the invoice
In a repeater row, the formula uses the fields of the same row: ->calculate('unit_price * quantity') on the Amount column.
Note: Try it
Change the quantity of the rope from 4 to 5: its amount goes from $258.00 to $322.50. Press “Add line” and type 10 and 3 in the new row: $30.00.
| Reorder | Item | Unit price | Quantity | Amount | Actions |
|---|---|---|---|---|---|
|
|
|
$
|
|
$
|
|
|
|
|
$
|
|
$
|
|
The total of a column
->calculate('sum(lines.amount)') adds up the Amount column of every row of the lines above. Empty rows are skipped.
Note: Try it
It starts at $411.00 ($258.00 + $153.00). Change a quantity in the lines above, or add or delete a line: the subtotal follows at once.
A discount in a formula
A DiscountInput is read as its amount, so ->calculate('subtotal - discount') works whether the discount was typed as 10% or as 41.10.
Note: Try it
Type 10% as the discount: after discount $369.90. Press $ on the right of the discount: it shows 41.10 and after discount stays $369.90. Type 50: $361.00.
Rounding in a formula
->calculate('round(net * 6.25 / 100, 2)') works out the sales tax and rounds it half up to 2 places. round(x) rounds to whole numbers.
Note: Try it
With the 10% discount above, the tax is $23.12 (369.90 × 6.25% = 23.11875).
A formula from calculated fields
A formula may use other calculated fields: ->calculate('net + tax'). When a quantity changes in the lines, the subtotal, tax and total all follow.
Note: Try it
With the 10% discount, the total is $393.02. Delete the discount: $436.69. Press “Show what would be saved”: the server works it out again and gets the same.
Smallest and largest
min(a, b) and max(a, b) choose the smaller or larger value. Here shipping is $25 minus 5% of the order, but never below 0: max(25 - order_value * 0.05, 0).
Note: Try it
Type 200 as the order: shipping $15.00. Type 600: $0.00, not −$5.00.
Empty fields and division by zero
Empty fields count as 0, but when every field of the formula is empty, the result stays empty (a new row doesn’t show $0.00). Dividing by zero gives an empty result instead of an error.
Note: Try it
The cost per unit starts empty. Type 120 as the batch cost and 0 as the units: still empty. Type 8: $15.00.
Calculated quantities
NumberInput takes ->calculate() too. The result is rounded to ->decimals(): here one place.
Note: Try it
Type 12.25 as the case weight and 3 as the number of cases: 36.8 lb (36.75 rounded).
Letting people type over the result
Calling ->readOnly(false) after ->calculate() lets people type over the value. The server still calculates it again when saving, so the typed value is not kept.
Note: Try it
Type 100 as the list price: the sale price becomes $90.00. Type 85 over it, then press “Show what would be saved”: the server saves 90.00.
Totals under a table
MoneySum adds up a money column under a table, one total per currency. TaxSummary groups the amounts by tax rate and works out the tax once per rate, as invoices need. Both run one query on the table’s rows, with its filters and search.
Note: Try it
Open the Quotes list and scroll to the bottom. Under “After discount”, TaxSummary shows each rate with its amount and tax (such as “6.25%: $…, tax $…”). Under “Total” (scroll the table to the right), MoneySum shows the sum. Type Bayside in the search: the totals change to that customer’s quotes.