A Computed field in a form can read from your data tables with two functions. Both take the table's name in formulas and column names written as text in quotes, and both return a blank result (never an error) when something does not match, so a half-filled form never breaks.
lookup - find the row with this value
lookup(table, key_column, key, value_column)
Finds the first row where key_column equals key and returns the value in value_column. Text is compared ignoring capital letters and surrounding spaces; numbers are compared as numbers.
Example: a zones table with a postcode_area column and a surcharge column.
lookup('zones', 'postcode_area', delivery_area, 'surcharge')
If the visitor picks North in the field named delivery_area, the result is the surcharge on the North row.
band - find the right price band
band(table, threshold_column, value, value_column)
Looks at the rows ordered by threshold_column (smallest first) and returns the value from the first row whose threshold is at or above value. If the value is bigger than every threshold, the result is blank. Use it for weight, distance, or quantity bands.
Example: a freight quote
A carrier wants visitors to see a price before they send the request. The pallet_rates table:
| max_kg | price |
|---|---|
| 100 | 40 |
| 500 | 90 |
| 1000 | 150 |
The form has two number fields, weight_kg (kilograms per pallet) and pallets, and a Computed field named freight_price with this formula:
band('pallet_rates', 'max_kg', weight_kg * pallets, 'price')
| Visitor enters | Total weight | Result |
|---|---|---|
| 60 kg, 1 pallet | 60 | 40 |
| 120 kg, 2 pallets | 240 | 90 |
| 800 kg, 1 pallet | 800 | 150 |
| 700 kg, 2 pallets | 1,400 | blank (above the last band) |
To show a fallback for very heavy loads, add a last row with a very large max_kg, or show a message such as "Heavy load - we will quote you by email" when the result is blank.
You can combine the two functions. This adds a zone surcharge to the band price:
band('pallet_rates', 'max_kg', weight_kg * pallets, 'price') + lookup('zones', 'postcode_area', delivery_area, 'surcharge')
Good to know
- The result is saved with the submission, so you keep the exact price the visitor saw even if you change the table later.
- Matching on column names ignores capital letters, so
'Max_KG'findsmax_kg. - A blank cell in the value column counts as no result.
- Formulas read the stored copy of the table. When you push or refresh new rows, the next visitor sees them.