need help with automation record value with multiple values

[yok_mangan]

hello sir. Are you available to help? I'm new to airtable and need help..

so i have a recipe table which have these columns:
Recipes Table:
- "Resep Bahan 1/2 jadi", checkbox column, user will check this box if the recipe is for sub-ingredient that made from other ingredients.
- "Nama Resep", this is the name of the recipe
- "bahan", this is ingredient name
- "jumlah", this is the number of ingredient used
- "unit final", this is the unit (i.e.: kg, litre, etc.)

so every line (every row) is a single entry. for recipe that need more than 1 ingredients then will have multiple rows/lines with same name of the recipe (same "Nama Resep") for every ingredient ("bahan" and "jumlah").
for example:
Recipe A (sub ingredients) need 3 kg of "Bahan" B + 5 kg of "Bahan" C, so will have 2 rows ("v" is ticked checkbox for "Resep Bahan 1/2 jadi"):
v | A | B | 3 | kg
v | A | C | 5 | kg

Then I already have Inventory Table which consists of below column:
- "Tanggal" : Date
- "Nama Bahan" : Ingredient names
- "Dari Lokasi" : Location From
- "Ke Lokasi" : Location To
- "Jumlah Pindah" : Number of inventory moved
- "Unit" : the unit

Then i have intermediary table name COGS, which in this case helping to roll up the ingredients for recipes with multiple rows of ingredients.

so, that is for recipes table and inventory table. now i am confused in the making the 2 new tables (sub-Ingredients table and sales table) with automation (to update invetory table) and Form Interfaces:

so i already make the table but when running the automation to update the inventory table it seems cannot go through due to multiple values of the ingredients.

i am thinking to make another table to split the values, but found another problem for the number of ingredients used.

Thank you in advance.

[Dimitris_Goudis]

Hey @yok_mangan,

Have you considered splitting the ingredients list and purchases into separate tables? Your project essentially functions as an ERP (Enterprise Resource Planning) system, aiming to manage ingredient availability for each product and optimize purchases from your vendors.

I recommend organizing your system into distinct tables, such as the following:
- Ingredients
- Recipes
- Final Products
- Purchases
- Sales Orders

When a product is on a sales order, you can identify the ingredients needed based on the linked recipe. If any ingredient is missing, the product may be unavailable. By purchasing the necessary ingredients and using rollups, you can keep your product availability updated.

Additionally, you can generate a report for Cost of Goods Sold (COGS) based on your purchases over a selected period (day, week, month, etc.).

I've worked on similar projects for clients, where we optimized the purchasing process and integrated restaurants with online food marketplaces, increasing orders.

I would be happy to discuss your project further, either via email or a meeting, whichever you prefer, at no charge.

Looking forward to hearing from you!

Best regards,  
@Dimitris_Goudis