Ingredient stock rollup

[MoniK]

Hello,

We are a vitamin supplement company and we buy ingredients from all over the world. We have 178 different ingredients which are used to create 17 products.  Sometime the same ingredients will be used for five different products.

We want to create an inventory table that we will update manually when we receive or buy new ingredients, but decrease automatically when we dispatch ingredients that will be used to manufacture the product.

For example, if we bought 20kg of magnesium, we need to add this to the existing stock on hand, ie. 10kg.  A new total of 30kg in stock.  When we manufacture the product SleepEeze we'll send 25kg to our manufacturer, and the stock should automatically decrease to 5kg left as stock on hand.  But I'll also take 2.5kg to manufacture CalmEeze, which should remove from stock on hand.  And so on.   

I feel like this is so easy but I can't wrap my head around it. Thanks for your help in advance!

[Dimitris_Goudis]

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

[MoniK]

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.

[Dimitris_Goudis]

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