You are describing the classic manufacturing inventory control database. You'll need several tables linked in relationships first before you tackle the layout design and any needed scripting to automate it.
Here are some tables you'll need:
Inventory: One record for each type of item you make and each material you use to make items. So you'll have a record for a widgit in here but also a record for a widgit top, widgit middle and widgit bottom if these are three distinctly different objects.
BOM (Bill of materials). This table functions like the list of ingredients in a cooking recipe. You create one record for each material used to build your widgit and a field in that record records the quantity of material needed to construct one widgit.
Item|Inventory::__pkInventoryID = BOM::_fkItemID
Item|Inventory and Material|Inventory would be two Tutorial: What are Table Occurrences? that have the same data source table.
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
This is just the "recipe" part of a manufacturing database. Other tables would be used to schedule and document the actual production run and the data generated from it would then be used to produce a continually updated inventory showing that each production run increases the number of widgits on hand (until they are shipped) and decreases each material consumed by the production run (until more material is received).
Thank you @PhilModJunk !
So what you've said there sounds like it should help me set up the structure of the database table(s). I am a craftsperson - I make, say, no more than a dozen widgets on any given day, and probably far less than that. And my widgets are made up of 3-6 components each. Does that change your answer at all?
And then where do I go after that to find a way to automate the process? Is it a scripting thing? Or a button with an "action" or something that I attach to it?
Thanks again for your help.
The basic tables, relationships should serve your needs as a starting point.
To this, you'll need to add a table where you can log the number of widgets produced each time. You can enter one record in that table for each king of item created. A script can then use the information in the BOM to log the consumption of materials and the production of items.
But there are two basic and different approaches to how you manage those inventory counts:
1) A number field in the Inventory table is simply updated each time inventory changes. Your script would simply add to and subtract from this field in different records in order to update your inventory.
2) A transactions table can be set up where each inventory change is logged much like bank account deposits and withdrawals. The sum of all transactions that increase inventory minus the sum of all transactions that reduce inventory for a given item then computes the new inventory count.
The first option is the easiest to set up. The second option allows you to see how a history of how your inventory counts change over time and that can be useful in determining how much of each material should be maintained without tying up too much revenue in yet to be used inventory.
The basic set up is the same in both cases:
So if you Make 5 "widget number 1's" today, you create a record in Production, select the ID for "Widget #1" from a value list and enter 5 as the quantity produced. Your script can then be performed to increase inventory for Widget #1 by 5 and then it pulls up the set of BOM records for that item, multiplying the quantities in the BOM records by 5 to compute the materials used and then reducing inventory for each item accordingly.
The exact details of how that script does it will vary depending on which method for managing inventory you want to use.