Calculating a Product cost based on options

I’m creating a table to help develop products. I would like to have certain parts determine pricing. So if I’m creating bikes for example - each type of seat or wheel will affect the price.

Seat A = $50
Seat B = $10

Wheel A = $100
Wheel B = $200

Seat A + Wheel B = $250
Seat B + Wheel A = $110

The real one would have several more options, but that’s the main idea.
Ideally there would be a “Wheel” field where you could choose A or B. Same with a Seat field. The Pricing would then calculate the total of whatever configuration you’ve chosen.
Thanks for any help!

Hi @Sam_Smiley - at least two ways you can do this. A simple way is to have a components table and a products table.

In the Products table, the Seat And Wheel fields are links to the components table, the Seat Price and Wheel Price are look-ups of the price in the components table and the Bike Price is a formula:

{Seat Price} + (2 * {Wheel Price})

This method is OK and would work for simple product configurations but could also hit some problems pretty quickly. For example, what happens if you start to sell tricyles or unicycles? The formula would need adjusting or you have to add a quantity field for each component. If the list of components is long, this type of “horizontal” layout isn’t great. And if you add a new component to a bike you need more columns and formula adjustments to match.

A better solution, particularly for more complex products, is a variant of this base:

(See also this post - Subtract more than one item from the same product)

The Product Compositions table is the key here. You can configure the components in a bike in a “vertical” way, which is much easier to handle when the list of components is long and doesn’t change the way your base is structured when you add new components to your build. It also doesn’t assume that every bike has the same set of components. Generally, this is a better design approach. Have a dig around the example base above - it uses roll-ups and formulas to manage inventory, but the addition of a price field to the components table would allow you to roll-up the price (times quantity) into the Products table, give you the overall bike price.

Hope this helps


1 Like

I’ve added component cost and the matching look-ups/roll-ups to the base to illustrate.

Thank you so much Jonathan! I haven’t had a chance to really go over it yet, but it looks like the second scenario is exactly what I need.