Formula, rollups or automations to deduct materials from product sets for inventory

[alexplp]

Hi! I'm trying to set up an inventory system that takes into account materials used for each of our products. On my products table, I have a column for materials and one for finished goods. I've set it up so that when we assemble a product, the materials qty decreases and the finished goods qty increases. This seemed to work well until I ran into this issue:

We make notebooks, so the raw materials are the covers. So when we make say, notebook A, notebook A covers would get deducted, when we make notebook B, B covers are deducted and so on. The problem is we also make 3-packs, with one of each notebook A, B and C. These sets are a different product, BUT they use the same raw material (covers) as the single notebooks. 

I can't seem to figure out how to make it so when we assemble a 3-pack, it automatically deducts one of each cover for A, B, and C. We have several 3-pack designs too, which further complicates it. 

Any help is very welcome!

[sambehrens]

A good work around for this could be to have multiple linked columns to your raw materials list and select each type of cover or other raw materials you are using and how many of that specific raw material you are using. 

If you were to always use 1 of each kind of cover to make the 3-pack then you could do 1 column where you allow for multiple selections from your raw materials, then do a count for that column and divide the total used by that count to say how much you used per cover type.

Hope this helps some!

[Dan_Montoya]

When you build a 3 pack, you have actually built 3 notebooks like you would if you built 3 individual books.  Those have now become raw material into another product , the three pack.