WooCommerce calculation look up tables

Often, you might want to calculate your product price based on user measurements. And, in some cases, you need to round up the user measurements to specific values in order to return your price. In this article, we’ll look at creating a WooCommerce calculation field using a look up table or pricing grid.

WooCommerce calculation look up tables

We can use calculation fields in the Pro version of the WooCommerce Product Add-Ons Ultimate plugin for complex calculations. And there’s a complete guide to WooCommerce measurement price calculators here.

WooCommerce Product Add-Ons Ultimate

Enhance the shopping experience on your store by allowing customers to personalise your products

Find Out More

But, in this specific case, we want to use a look up table to return our prices.

Here’s an example.

Selling made to measure blinds and curtains through WooCommerce

In our example, we’re selling made to measure blinds. The customer enters the width and the height (or drop) required. Then our product calculates the price.

WooCommerce window blinds price calculation

However, we don’t want to use the exact measurements provided by the customer to calculate the price. Instead, we want to round each value up to one of our predefined values then get our price from a pricing table.

The screenshot below of a spreadsheet should help to explain. Across the horizontal x axis on the top row you can see values in centimetres, i.e. 80, 100, 120 etc. These are our values for the width of the blind.

And down the first column, the vertical y axis, you can see values in centimetres again. These are our values for the blind’s height.

Look up price from CSV file

We can use this grid to return a price based on user measurements. So if the user enters 139cm width by 178cm height, we round up both measurements to the nearest value in the table, which will be 140cm for the width and 180cm for the height.

Then we look across to the 140 column and down to the 180 row to find the price. In this case, the price will be $414.

You can take a look at this in action in the demo product.

Creating the look up table

To upload the look up table, you’ll need the Advanced Calculations extension for Add-Ons Ultimate.

Advanced Calculations for Add-Ons Ultimate

Make your calculations and look up tables easier to manage

Find Out More

This extension requires the Pro version of Add-Ons Ultimate.

Uploading look up tables as CSV files

The Advanced Calculations extension allows you to upload your look up tables as CSV files. You’ll need to format the files in a specific way:

  • Give each table a unique name in the top left cell. Table names should be alphanumeric – they can’t just contain numbers. Note that you can upload multiple tables in just one file using this method.
  • Enter your X-axis values along the first row
  • Enter your Y-axis values in the first column of each subsequent row
  • If you have multiple tables, just add the next table as a subsequent row – the plugin will work out that this is a new table. Don’t leave any empty rows

Here’s an example of what a CSV will look like:

You can see that this file contains two tables – ‘TABLE 1’ and ‘TABLE 2’. These are different look up tables that can be used by different calculation fields. You can have as many look up tables as you like in the CSV file.

To upload your CSV file, just go to Product Add-Ons > Calculations.

Creating add-on fields for a pricing grid calculation field

With your CSV file uploaded, you can now assign tables to different calculation fields.

We need to create two number fields for our width and height measurements. You can take a look at the support document or in the main calculation field article for how to do this.

Now we need to create a calculation field that will use the measurements. To add the calculation field:

  • Click ‘Add Field’
  • Choose ‘Calculation’ in the ‘Field Type’
  • In the ‘Formula’ field enter {look_up_table} – make sure you include the curly brackets
  • In the ‘Action’ field, choose ‘Display As Cost’. This will ensure that the result of the calculation will be formatted with the correct currency and add to the product price

Then:

  • In ‘Field ID for X’ enter the field ID for the values along your x axis
  • In ‘Field ID for Y’ enter the field ID for the values along your y axis
  • In ‘Look Up Table’, select the name of your table from your CSV file

You can now publish your product.

WooCommerce calculation look up tables – recap

The Pro version of the Product Add-Ons Ultimate plugin and the Advanced Calculations extension make it easy to use look up tables to determine prices based on user inputs and measurements.

WooCommerce Product Add-Ons Ultimate

Enhance the shopping experience on your store by allowing customers to personalise your products

Find Out More

Advanced Calculations for Add-Ons Ultimate

Make your calculations and look up tables easier to manage

Find Out More

34 comments

  1. Hi,
    Regarding look up tables, is it possible to add or subtract a percentage from them should you want to adjust the price in the front end?

  2. Should I be building this as a variable product or a simple product if I’m including attributes such as color.

    Thanks,

  3. Hi,

    In one product i have couple look up tables with different price, depends of chosen material (three price groups of materials) and dimensions.
    How can i define which table it will use by clicking material?

    Example
    Client chose material group 1, insert dimensions and price should be importet form look up table 1
    Client chose material group 2, insert dimensions and price should be importet form look up table 2
    Etc

  4. Hi Gareth,
    Would it be possible to modify the plugin to use CSV files to upload the prices to save creating Arrays?
    I know Magento use this option.

    Nigel.

  5. Hi,

    I have determined min and max value for height and width, but after puting number over the range this is just took it.

    How can i make comunicate as in your demo here? to Show information ” value can’t be less than … ”

    Thanks

  6. Can this plugin support converting a .csv to a website table? We have a very large csv file with different rows and columns and we might find that doing it manually will within the function like you have shown above would take too long. If you can advise that would be most appreciated 🙂

  7. Hi Gareth,
    I have installed the pro version. I had to convert from cm to mm to get an accurate measurement from the customer size input, so all good. Can you give me an heads up for installing tooltiped dashicons as in your demo site. I can insert one next to width/drop but at the front end it has added text: “data-field-value=””> before the dashicon. I appreciate this is off topic, but would appreciate your thoughts and also the plugin for the tooltip you used, or even if it is at all possible without going into the php files. Great work!

  8. Hi, My company sells gifts online, and we have about 10.000 products without the color variations. What I would like to do is:
    Create a product with color variations, then add print costs that are gathered from tables with let´s say “2 dimensions”. Meaning. I got the t-shirt, the the user selects the number of color he wishes to print that looks up a table of colors vs quantities (or instance silkscreen). Your Product sounds perfect.

    The only question I have is: After I create all my tables manually (ex: pad printing, silkscreen…) can I use the all import plugin (https://www.wpallimport.com/documentation/woocommerce/variable-products/) to assign each table to a product, or, each product must have it’s own table, created manually?

    Since wp all import imports custom fields, this would be a game changer for everyone with a large store 🙂

    Great tutorial and work, anyway!

  9. Hi!

    how did You set up on this “Window Blinds demo page” Price form 118 if you have to set price 0 because this blinds calculation is set as a cost ? Did YOu Put some Extra Field Or What ?

    You could make better tutorial how to do that or make a YouTube movie.

    regards

  10. Is it possible to set a ratio, and the change 1 field, height or width, for certain things that require to maintain an aspect ratio?

    Thanks.

    • Hi

      I’m not 100% clear on the question. The look up table allows you to return a value based on one or two user inputs. What is it you are looking to do?

      Thanks.

  11. Hi Gareth! There is a way to reuse lookup table value? I want to add extra 30% to lookup table price in one option, and another option I want to add extra XX price (no percentages). How to do that?

    • Hi

      Yes, you can use a calculation field to get a value from the look up table; then you can use the value of that calculation field in another calculation field.

      Thanks.

  12. Hi there, I have a few questions including questions specific to an Excel document, please can I send this over to you.

    Kind regards

    Jamie

  13. Hi Gareth
    Can I use this plugin to do a size calculation without using a look-up table?
    So customer inputs width* height, plug-in calculates area and provides a price based on the area formula such as £100 per square metre?

    Thanks
    Kim

    • Hi Kim

      You can create an area calculation using the customer inputs for width and length. Then you can also calculate the product price based on the area and your own formula.

      Thanks.

Leave a Reply

Your email address will not be published. All fields are required.