Inventory Management

[DonkeyZA]

Hi everyone,

I'm working on building an inventory management system in AppSheet, and I need some help with automation.

Here's the scenario:

I have separate tables for Add Components, Component Levels (i.e. inventory of constituent components) and used components. 
When I add a finished product i will list it in "Used components", when new components arrive from suppliers i will document that in "Add Components", But now I need help on how to build the system such that when a product is completed and documented under used components i want the system to automatically deduct the relevant components used in each product. By using the analogy of a recipe l, i want the system to do the following. 
- List that I made 2 chocolate cakes and one vanilla cake. The system should then automatically deduct all the "ingredients" from our overall levels of ingredients. This becomes particularly challenging when the different cakes use some of the same ingredients (such as flour per example). Thee system should know by using product codes that if i made 2 different types of cake to deduct the quantity needed for each from the shared ingredients as well as the other ingredients used to make this.  I want AppSheet to automatically deduct the used component quantities from the overall inventory levels.
I'm unsure how to implement this automatic deduction using scripts or formulas. Has anyone achieved similar functionality in AppSheet and can share some guidance on how to set it up?

I've looked through the AppSheet documentation but haven't found a specific solution for this scenario. Any help or advice would be greatly appreciated!