WooCommerce calculation look up tables

mitchell griest Bpi11FI B o unsplash

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 featured image

WooCommerce Product Add-Ons Ultimate

Personalize products with extra fields and custom options

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.

This is a simple example of how to use a lookup table to calculate price based on user measurements. You can see this article for a more detailed tutorial on how to create custom products for blinds and shutters in WooCommerce.

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 featured image

Advanced Calculations for Add-Ons Ultimate

Read prices and values from lookup tables

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:

example csv file

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 featured image

WooCommerce Product Add-Ons Ultimate

Personalize products with extra fields and custom options

Find Out More

Advanced Calculations for Add-Ons Ultimate featured image

Advanced Calculations for Add-Ons Ultimate

Read prices and values from lookup tables

Find Out More

53 comments

  1. User image

    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. User image

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

    Thanks,

  3. User image

    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. User image

    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. User image

    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. User image

    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. User image

    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. User image

    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. User image

    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. User image

    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.

    • User image

      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. User image

    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?

    • User image

      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. User image

    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. User image

    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

    • User image

      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.

  14. User image

    Hi there, I have 200+ products could I make this v lookup table as globally, so I don’t have to recreate table for each product and when price changes then I have to change only in table that all.

    Kind regards

    Dex

  15. User image

    Hey dear Developer,
    is the plugin still up to date and compatible with all actual versions (wordpress 6, woocommerce, etc)?
    Thx, regards,

  16. User image

    Is it possible to use additional calculations right in the formula field where you’d initiate the {look_up_table} price search?
    For example do: ({look_up_table} * 0.9) directly in the formula field to get a 10% discount, instead of having to create an extra calculation field with: ({field_xxx_field_price} * 0.9)

  17. User image

    Hei,
    Is it possible to look up a value on the X-axis based on a field input, but, for the Y-axis, instead of looking up a field, specify a fixed value?

  18. User image

    Hi,

    I am trying to do two table lookups in one product. Only the first table works. The second table doesn’t seem to load up. Any thoughts?

Leave a Reply

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