This is a common issue in the Manufacturing world. You have a list of products you produce (your beverages), a list of Materials used to produce those products and you need to link the materials used to each specific product so that you can list all the materials used and the relative quantities used in production for each product in your production schedule.
Manufacturers call this third list a Bill of Materials (BOM) and it is linked to your other two tables like this:
Products::ProductID = BOM::ProductID
Materials::MaterialID = BOM::MaterialID
In addition to the two ID fields, you'd add a field that records the amount of that material consumed to produce a single unit of the specified product. Other fields, such as an estimated waste factor might also be included in the BOM table.
On a Products layout, a portal to BOM can be used to list all materials needed to produce that product. On a Materials layout, a portal to BOM can be used to list all products that use that material in production.
An additional table can be used as an inventory log to track both material and product inventories. It works much like a bank account ledger where entries in one column increase the current balance (debit) and entries in a second decrease the current balance (Credit).
When you recieve a materials shipment, you'd add a record to this table for each material recieved and enter the amounts in the "In" field. At the end of each production shift you'd log the amount of product produced and you can either log the amount of materials consumed (More accurate) or a script can use the amount of product produced and the numbers in each product's BOM to compute the amount of materials consumed. Each of these numbers generates a new record in the invetory table with Production Quantities logged in the "IN" field and Material consumption logged in the "out" table.
Such an "Inventory Log" is discussed in a number of threads here in this forum. It would have at least the followng fields:
Date, ID (for both Product and Material ID's), Description (text with entries such as: Recieved, Shipped, Production, Consumption, Shrinkage, Etc.) In, Out, cBal (calculation: In - Out ) , sBalance (Summary: Running Total of cBal, restart totals with each ID)
In addition to production and receiving shipments, you can log other inventory changes in this table for Shrinkage and other reasons.)
thanks for your help. Now I understand how organize the database. But I would like to ask you to help me to sort out one more problem. I will try to explain you: now the database has 3 tables like you told me. Products, BOM and Raw Materials linked with them with ID's. When I make a new recipe, i use the portal to BOM where the fields are 3 raw material ID, name raw material, Quantitative used. But the raw material database doens't update for all the entries but just for the first one.
Quantitative remained: Raw material quantitative - BOM::Quantitativo parziale
I tried several time to sort out the problem, but really I am not able to find a good way. Have you got an advice please?
Thankyou very much!
You'll either need to use additional tables to track inventory levels for product produced and Raw materials on hand or a script to update values in your Raw materials table. There's no "automatic link" between the BOM and RawMaterials that would do this nor should there be.
The BOM documents the material quantities used to produce one unit of product and should not change from one production run to another except to document changes in your recipe. A different table would be needed to record how many units are produced each time and it's the product of unit rates from the BOM and Units produced that determines the amount of raw material likely to be consumed. (Actual amount consumed can vary due to incidental waste of material during the production run that can vary with each run.)
Thus, your BOM might have fields for: grams used per Unit, Percent Waste.
THen a script can loop down the BOM records and log an estimated material usage as:
let ( M = BOM::grams Used * $UnitsProduced ; M + M * BOM::PercentWaste )
A script can subtract this from a field in your raw materials table like this:
Set Field [Materials::QtyOnHand ; Materials::QtyOnHand - let ( M = BOM::grams Used * $UnitsProduced ; M + M * BOM::PercentWaste ) ]
or it can create a new record in a "log" table where you log material in and out much like you track deposits and withdrawels from a bank account, where it assigns this value to an "out" field to record the amount of this material consumed during this production run.
Dear PhilModJunk thank you very much for your help