Nested BOM for Inventory Tracking

Hi guys,
Just trying to achieve the following. No luck so far.
It’s a way to reduce inventory numbers of products based on the available qty of the parts that make up that product. I’d like for this to be nested. So if we have a wheel that is made of [tyre,tube,rim] and a bike that is made of [2 x wheel,1 frame etc], if we increase the qty of tyres, we will increase the qty of the wheels which will increase the quantity of the bikes buildable.eg.

Table 1. (Products)

Tyre
Tube
Rim
Wheel
Frame
Seat
Handlebars
Bike

Table 2 (Bill of Materials)
Wheel - 1 x tire, 1 x tube, 1 x rim.
Bike - 2 x wheels, 1 x frame etc.

I can get it to work with one level of BOM. So if I had it as
Bike = 2 x tires, 2 x tubes, 2 x rims, 1 x frame etc it works well.

I’ve tried countless things to try have it work with nesting but I haven’t solved it yet.
Any tips of the method to use would be great.

Obviously, it has to have the formula of MIN(values) as we can only build a bike if ALL the components are available.

Any help much appreciated.
Thanks
Jason

Hi @Jason_Ure,

Welcome to Airtable Community! :smiley:

I think you need a 3rd table called Buildable, in this table you use the Rollup function with the argument Min() to check what is the minimum number you can build.

Alternatively, you can use the Group By option and in the summary field choose the Min value.

If this helps you, please mark the answer as Solution so others can benefit from it :slight_smile:

BR,
Mo

This topic was automatically closed 15 days after the last reply. New replies are no longer allowed.