Pricing Table design

[Dan_Payne]

Hi!
I’m building out a product catalogue within AirTable and am stuck on the best way to manage updating prices for lots of similar products at once. Our company manufactures similar products but with variations of shapes and colours (23 colours for about 90 different shapes).

There are just two Retail prices for the colours: “Standard” & “Premium”. And each shape is priced differently. The premium colours are 20% more expensive than the standard colours; so, only one price ever needs to be input for each shape and formulas can calculate the premium price (add 20%) and then other formulas work out all the different pricing tiers: retail is 2x wholesale + tax, trade is retail -10%, etc.

In a spreadsheet this is pretty straightforward using lookups and IF statements that reference the base price and then a table that checks if the colour for X shape is considered “premium” or “standard”. But I cannot figure out how I’d achieve similar in AirTable without a convoluted repetition of fields across multiple tables which starts to really slow things down.

Ultimately, I need to be able to push an update to my table of our 2,000-ish products by updating the 90 ‘standard’ prices of the base shapes.

Any suggestions would be great. Thanks!

[JonathanBowen]

Hi @Dan_Payne - the relational aspect of Airtable is your friend here. This is how I would approach it:

3 tables - shapes, colours and SKUs (or products or something):

Shapes has a name and a price:

Colours has a name and a standard/premium selector:

SKUs combines shapes with colours to give you the 2000 products you sell:

You can bring the shape price and the standard/premium flag over the skus table from their origin tables (both lookup fields). Then add a SKU Price field with formula:

IF({Standard/Premium} = 'Standard', {Shape Price}, {Shape Price} * 1.2)

Now you can update the 90 shape prices on the shapes table and this will ripple through the SKUs table and update the retail prices. In a similar way, if the differential between standard and premium changes in the future you can simply update the formula above