What is lookup table pricing and how does it work?
If you’re selling products with additional options that require more complex pricing, chances are you can fix it with a formula. An example: you could be selling fabrics. Depending on the size the user buys, the price increases. Most commonly, you’ll have a unit price (the price per square centimetre, for example) and multiply it by the width & height the customer selected.
But what if you can’t distill your logic into a formula? For this scenario, we offer lookup table pricing.
What is lookup table pricing?
A lookup table is an Excel-like table holding values and their price. Here’s an example table for a custon price by “width” and “height”:
Across the horizontal x-axis on the top row you can see values in centimetres, i.e. 200, 220, etc. These are our values for the width.
Across the vertical y-axis, you’ll see the values for the height (100 cm, 120 cm, etc.).
The other values are the prices that belong to each width/height pair.
If a user enter 240cm as their width and 140cm as their height, you can use the table to see the addon price will be $76:
What if a user enters a width and height that is not seen in the table? Then the system will round up to the nearest pair, effectively allowing you to create pricing between ranges. Here’s an example:
If your user enters 210cm width by 150cm height, our plugin will find the nearest fitting pair from the table by rounding up (which is 220 by 160). The price will be set to $78.
Note that in our example, we only use 2 values (width & height), but you can also use this for just 1 value, or even more than 2 (unlimited, to be precise).
Adding a lookup table to your site
There are currently 2 ways to add lookuptables to your site:
- Via an add-on plugin which allows you to upload CSV (Excel) files. The advantage is that you or your customer can easily maintain several lookup tables through your favorite table editor (Excel, Airtable, Google Sheets, …) and then upload them to your website. The downside is that there is a cost involved as this add-on is not free (but relatively cheap). Read how to use the lookup table add-on.
- You can also define the table with a code snippet. It requires some more work but it’s a free alternative. If you’re unsure on how to add code snippets to your site, read this article first.
If you are adding tables via the code snippet, below is a code example:
Here’s what you should know:
- Each of your lookup tables should have a unique name. Notice
$tables['yourtablename']where yourtablename should be replaced with the name of your table. It should only contain letters and underscores.
- The table is build by a 2 dimensional array. The first level contains the numbers of the x-axis of our table, which contains the “width” numbers (200, 220, 240, etc..).
- The second level is a key value pair written as
key => value. The key contains the numbers of the y-axis (the “height” values in our example). The value contains the price.
Using the lookup table in a formula
Now that you have loaded one or more lookup tables into your site, you can use a formula to call upon the lookup table. Go back to your “height” field’s Adjust Pricing setting and enter the formula:
- lookuptable() is the function we’re using
- lookuptablename is the name of your table as defined in the code snippet above.
- 5e8ecdf46ea7d is the ID of your field for the x-axis. In our example, it’s the Width field’s ID.
- 1d3exl146en4w is the ID of your field for the y-axis. In our example, it’s the Height fields’s ID.
Was this article helpful?