Calculating a Product cost based on options

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

JB

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.

Hi Jonathan!!! thank you for the information, is there a possibility that you could reshare the Product Inventory table?? Your help was great! But if I could see the table it would be of much greater help. We’d appreciate it dearly :winking_face: