Hey @MoniK, 

This is actually one feature in an ERP using Airtable. 

On your system you want to track the available quantity and the committed quantity. When you create the request for a specific quantity of an ingredient this can be shown on Ingredients table as a roll up. Then musing a formula you can have the calculation:

Available - committed -> new available quantity. 

Last step is to add an automation that will trigger when you run the request and the action will be the item update on available field by adding the new available quantity.

Furthermore if you want extra automations on workflow I would recommend to add the following settings: 
- Add the table with all your recipes so when you want to process the production of specific product then one automation will create the production document, update product availability
- Add the table of purchases to store every purchase like a submission on accounting system. This action will automatically update product availability again, while on Airtable you can create the financial data you may need for your accounting system. So then you could integrate the two systems to send data on this 😉 

Does it make sense for you? 

Yours sincerely, 
Dimitris Goudis

Hello Dimitris,

Thank you very much for your help! You make it sound so easy!

It is a little advanced for me, so before I attempt all your recommendations, please would you elaborate on the following a little more as I'm battling to understand:

- how do I run a request or 'create a request"? 

- what formula do I use to get my "stock received" to update the correct ingredient automatically?

- how do I ensure that older records don't affect the current stock available?

I could send you a link to my database or if you could direct me to a similar template I would be more than grateful.

I'll continue to attempt your suggestions in the interim.

Thanks again for your assistance.

Hey @MoniK,

Thanks for your response! After 5 years building similar stuff I am a little bit more familiar, but lets do it more clear 😉 

The time you want to update the product availability is the time when you want to create a new production request in your production line, is that correct? 

So when you add in your system this information then your trigger will start. 

For updating availability from purchases you have to build the while workflow not only one formula. 

The stock availability is a field on the item record, every time you perform an action that it need to be updated then this field will be updated. You old data will be the in workflows for updating the item's quantity. 

Wish my response helped you.

Your sincerely, 
Dimitris Goudis