Creating a Lookup Table

What is a Lookup Table?

Setting a price based on a lookup table involves referencing a pre-defined table or dataset to determine the price of a product. It will help to achieve a complex pricing structure for your products.

The above shows an example grid displaying prices for an item based on two parameters( here height and width) When a user inputs a width of 2000 and a height of 300 , the system will retrieve the corresponding price of 12 from this grid.

Table Structure

• First Space (Disabled):

The top-left cell of the table is typically left empty or disabled. It serves as a placeholder and is not meant to contain any data.

• First Column (Row Values):

The first column of the table is dedicated to the different row values or options that you want to map. For example, if you are creating a pricing lookup table for different products, each row might represent a specific product.

• First Row (Column Parameters)

The first row of the table contains the various parameters or conditions based on which you want to define prices. These could be attributes such as size, color, quantity, or any other relevant factor that influences pricing.

• Corresponding Prices

The intersection of a row and column represents the specific price for the corresponding combination of options. For instance, if you have a “Medium” size product with a “Blue” color, the cell where the “Medium” row intersects with the “Blue” column contains the price for that specific product variant. (500 in the above example)

• Navigate to the Lookup tables from the dashboard.
• Click on Add New Table as highlighted in the below image

• Enter the required values for both row and column to get the exact result. For example, entering 10 in both fields, give a calculated price of 50.

• Then, Publish and Save Changes.

Assigning the Lookup Table for price:

• Selecting the field to set the price

First, select a field in which you wish to set the price according to the lookup table. Then enable the field’s “Pricing” option.

Then, you can set the condition for the row and column to display the price.

• To show the price when entering the both value.

Lookup Table

Backend:

Frontend

To show the price when entering the same value.

Lookup Table:

Backend :

Frontend :

Value Round Off

During the configuration of the Lookup Table, you have the option to set rounding off to get precise values. Please refer to the options below :

Take only the Exact value: The Lookup Table will only consider the exact value defined by the admin.

Round to the Previous highest number:  rounding up to the Previous highest number

Round to the next highest number: rounding up to the next highest number.

Round to the nearest number: round to the nearest number.

Custom Formula

Formula template: lookup(Lookup_Table_ID,{Value_of_row},{Value_of_column}) 

This function refers to the Lookup Table you created for pricing. lookup() is the function used to fetch a value from the lookup table.

Lookup_Table_ID is the table ID (you can copy this from the Lookup Table page).

lookup(wcpa-table-1752816016256,{Value_of_row},{Value_of_column}) ,

In this formula, the Value of row and column can be either a constant value or called from other fields using a custom formula

Example :

 1. Constant value:

lookup(wcpa-table-1752816016256,10,20) – where 10 and 20 are constant values

2.  Custom formula

lookup(wcpa-table-1752816016256,{field.number_5788381173.value},{field.number-4797511322.value})

Custom Formula Round Off

1. Take only the Exact value:

Use the ‘exact’ function to consider the exact value

lookup(wcpa-table-1752816016256,{field.number_5788381173.value},{field.number-4797511322.value},exact)

2. Round to the Previous highest number: 

Use the ‘floor’ function to round to the previous highest number

lookup(wcpa-table-1752816016256,{field.number_5788381173.value},{field.number-4797511322.value},floor)

3. Round to the next highest number:
Use the ‘ceil’ function to round to the next highest number.

lookup(wcpa-table-1752816016256,{field.number_5788381173.value},{field.number-4797511322.value},ceil)

4. Round to the nearest number
Use the ‘round’ function to round to the nearest number.

lookup(wcpa-table-1752816016256,{field.number_5788381173.value},{field.number-4797511322.value},round)