Whether baking cakes or assembling automobiles, the same basic set of tables and relationships is required and you are correct that it is complicated.
The same table can be used to track both materials consumed and product produced. A given production run will reduce inventory levels for your consumed materials and increase inventory levels for products produced.
You'll need a number of different tables:
Inventory (both products and materials)
BOM (bill of materials, your "recipe" table where each material consumed is a record linked to inventory whith a quantity field that records how much material is consumed to produce one unit of product. (1 cake, 1 automobile, 1 Kilogram of Chemical....)
Production One record for each production run. A field in this table records the planned quantity produced--which when multiplied against the data in the BOM computes the required quantities of material needed to complete the run. Most often, there is a second field to record the actual quantity produced as a number of events can result in a production run varying from the planned target quantity.
Inventory Log Each record here records the result of an event that changes inventory for one item in Inventory. Produce 500 cakes and records are logged here to show that Inventory for Cakes has gone up and that inventory for flour, sugar, etc has gone down. Ship out produced product and a record in this table should log the reduction in inventory. Receive a shipment of material and a record is added to log the increase and so forth...
Summary fields and relationships can be used to subtract the total quantity produced/received from the Total quantity consumed/shipped to compute the total on hand for each product and each material in inventory. You keep these as separate records here in this table instead of updating number fields in the Inventory table so that you produce a history of your rising and falling inventory levels that can help you better manage how much material to keep on hand and to adjust specific re-order points for each material.
The basic relationships would look like:
And please note that these are just the basic tables and relationships. A full up production/inventory management system would have quite a few more tables, occurrences and relationships.
this is very helpfull. If I interpret your relationship graph towards the end the master file is the inventory log? Or am I misinterprting that? Maybe I have been at this to long to see clear but I would have thought you us the Production as the main governing file. If you add a production event the the product produced gets the info form the BOM to create the Inventory but if I interpret your graph correct you would do this differnt? How do you achive the one record (production run) creates multiple entries in the Inventory Log?
This is all one file, with multiple tables though FileMaker does support putting different tables in different files. The resulting funciton is the same though implementation details can get a bit more complex with separate files for each table.
Each table has it's own function and a large number of different types of "events" have to be handled byu different layouts on based on different tables so I'm not really clear on what you mean by the "governing file".
How do you achive the one record (production run) creates multiple entries in the Inventory Log?
A script would loop through the related records in the BOM for a given product and create one record in Inventory Log for each material consumed plus one additional record for the product to be produced. These would all be linked to the primary key of the production record. Please note that this often requires logging the quantities into the log table twice. First is the predicted consumption/production figures based on the projected Qty of product to be produced. The same script that adds the records to the log table can compute and record those quantities. The second time records the actual consumption/production figures from that production run. The differences between forecast and actual figures then help management better control waste and production overruns.
I think I am starting to see where my hangup is. I will look into this tomorrow after a good night sleep. Thank you for your help and you will hear back from me.