This article covers how to get started with Advanced Calculations by connecting a calculation field to a lookup table so it returns a price (or any value) based on what the customer selects or enters. For how to create, edit or upload the lookup tables themselves, see this article on working with lookup tables.
How do lookup tables work?
A lookup table is a table of values with one or more axes (inputs). Instead of writing a mathematical formula, you give the plugin a table of pre-calculated values and the calculation field looks up the right one based on the customer’s input.
For example, a blinds store can price each blind from a table of widths and heights. A customer who enters a width of 140cm and a drop of 100cm gets the price from the 140 column and 100 row. A print shop can do the same with paper size and quantity, returning a set price for 250 A5 flyers or 500 A4 posters.
There are two kinds of table:
- Grid tables have exactly two axes, an X axis and a Y axis, laid out in rows and columns like a spreadsheet. The plugin matches one input against the column headers (X) and another against the row headers (Y), then reads the cell where they intersect
- Flat tables have three or more axes, listed as rows. Every column must match the customer’s input exactly for that row’s value to be returned
See this article for more detail on the types of lookup table.
Axis values can be numbers or text. For example, you can match a width and height, or match a dropdown choice like ‘Small’ or ‘Large’ along with a material.
Once a table exists, you connect it to a calculation field and choose which product fields supply each axis’s input value.
How do you add a lookup table to a WooCommerce product?
You add a lookup table to a WooCommerce product with a ‘calculation’ add-on field.
- Create a calculation field
Edit the product and click on the ‘Product Add-Ons’ on the ‘Product data’ tab. Add a new field, or edit an existing one, and set its ‘Field Type’ to ‘Calculation’.
- Update the formula field
Click on the ‘Calculations’ tab and click the ‘Look Up Table’ button in the formula builder panel.
This tells the field to get its value from a lookup table rather than a mathematical formula. An ‘Advanced Calculations Options’ section appears below with the settings covered next. - Specify the user input fields
If you’re using a standard grid table with an x and y axis, choose which of the product’s other fields supply the X and Y axis values under ‘Field for X’ and ‘Field for Y’.These dropdowns list every eligible field on the product. Number and calculation fields are listed directly. Fields with options, such as select, radio, checkbox group and image swatch fields, appear twice so you can use either the option’s value or its price. Choose the table this field should use under ‘Look Up Table’.
If you’re using a list-style table with more than two inputs, see below.
That’s how to add a lookup table to your WooCommerce product.
Other field settings
There are some additional options for your lookup table.
Exact match
Check ‘Exact match for X field?’ and/or ‘Exact match for Y field?’ if that axis should only return a value when the customer’s input matches a column or row heading exactly.
Leave these unchecked to have the plugin find the nearest matching value instead. This is useful for numeric ranges, such as finding the next size up. See the next section for how this works.
Display table
Check ‘Display table?’ to show the full table to the customer on the product page, alongside the field.
Save or update the product.
Now, whenever the customer changes the fields specified, the calculation field updates to show the matching value from the table.
Single column lookup tables
Most lookup tables have two or more inputs – products like blinds require a width and drop measurement, or stationery products might require quantity, paper type and size selections.
Occasionally, though, only a single parameter is required in your lookup tables, for example if you are offering price breaks based on quantity.
A single column lookup table is exactly what it sounds like – just a lookup table with input values and the corresponding return values. In the example below, the user selects a zip code and a price is returned for that zip code. (Here’s how to create lookup tables based on zip codes).
To use a table like this, follow the steps above but just leave the ‘Field for X’ setting blank. You only need to read the value from the Y-axis input field.
What happens if there’s no exact match?
For grid tables, the plugin always rounds up to the next header value when ‘Exact match?’ is unchecked and the input doesn’t land on a header. It doesn’t pick the numerically closest header.
For example, with X axis headers of 100, 200 and 300, an input of 150 returns the 200 column, not the 100 column.
What about edge cases?
- Input lower than every header value: returns the lowest header’s column or row
- Input higher than every header value: returns the highest header’s column or row
- Text headers: rounding up only works reliably with numeric header values.
- Exact match checked: if the customer’s input doesn’t match a header exactly, no rounding happens and the field returns no value
How do you use ‘max’ as a catch-all header?
You can an axis’s last header value to the text ‘max’ to catch any input larger than the highest numeric header. These inputs then match the max column or row directly, rather than falling back to the highest numeric header.
The max header only works when ‘Exact match?’ is unchecked for that axis. It must also be the last (highest) header on that axis.
How do you use a table with three or more axes?
If you’re using a list style ‘flat’ table with three or more inputs, follow exactly the same steps as above to add your lookup table. The only difference is when you come to select which input fields are used to lookup values in the table
Once you’ve selected the lookup table, you’ll see one dropdown per axis column set up on the table. Fill them in the same left-to-right order as the table’s columns, so the first dropdown corresponds to the first axis column, the second to the second and so on.
Click ‘+ Add Axis’ to add another dropdown if the table has more axis columns than are shown. Click ‘Remove’ next to a row to take one away.
A row in a flat table is only used when every axis matches the customer’s input exactly. Make sure the values your fields produce match the table precisely, including the case and spelling of text values like dropdown option text.
How do you use a lookup result in another calculation field?
A lookup table field is a calculation field whose formula is {look_up_table}. Other calculation fields can use its result just like any number or calculation field’s value.
Once you’ve created your lookup calculation field, create another calculation field. Find the lookup field button in the formula builder and click on it to add it to your new formula. Then add any operators, functions or other field values you like.
