Rollup over three tables (inventory, recipes and production mix)

[Tuxie]

I've been banging my head on this one for a while so any outside ideas would be more than welcome!

We have three tables.

• Inventory: contains list of materials and their initial amounts. Each material is treated as a batch material so once it runs out, it's permanently out. 
• Recipes: As the name suggests, contains recipes that can contain multiple ingredients. For each ingredient amount (or rather ratio of ingredients...) and few other details are given. Lookup fields bring some info from inventory for each raw material.
• Production mix: A right recipe is chosen and how much total material is wanted to be produced. Formula fields then calculate how much of each ingredient is needed. lookup fields bring the names of raw materials from recipe tables.

My problem is that rollup does not seem to work over three tables . Basically I would need to take the information of how much of each ingredient the production mix uses and then get the info back to the inventory row for each ingredient. However because I have the recipe table in between, rollup seems to get confused.  Any ideas?

[TheTimeSavingCo]

Hmm, I think I'd add a fourth table called "Uses" or something, where each record is linked to one "Production Mix" record and one "Inventory record".  I'd have lookups in this new table to pull the quantity required from "Production Mix", and the quantity of each ingredient needed from the linked "Ingredient" record, then use a formula field to multiply it

In the "Ingredients" table, I'd then have a rollup field to get the total amount used for that ingredient

[Tuxie]

Thanks for the idea. I was thinking something like this but then would hit a snag with getting the Uses table going... Since production mix is connected to recipe which has N ingredients. So when I select a recipe for production mix, it should create N rows to uses table immediately. When I change the "production amount" field for the production mix, all those N rows would need to be recalculated.... Are there any good ways to do this kind of one to many linking?

[TheTimeSavingCo]

> When I change the "production amount" field for the production mix, all those N rows would need to be recalculated....

Changing the "Production Amount" field should be fine as we're pulling that value over to the "Uses" table via a lookup everything should recalculate automatically

----
> Are there any good ways to do this kind of one to many linking?

Yeap, check out repeating groups

---
Apologies, I also made an error in my previous post.  We would pull the quantity required for the "Production Mix" via a lookup, and the quantity required of each Ingredient would be populated via the automation that creates the records, and it would use the data from the "Recipes" table.  We would not pull the quantity required of each Ingredient for the recipe via a lookup field

[Tuxie]

I ended up doing this with just custom scripting. Four tables total, I made each ingredient a row as well for the recipe so I don't have dauntingly wide tables.

Now the script just pulls data from required tables and creates needed rows in to the usages tables and I can use rollup in the inventory table to get total usage per raw material from the usages table.