Trying to deduct inventory across the BOM Database
I have built a database that represents our BOM, including every screw, fan, and circuit board. We build a medical device. What I'm trying to do is create a field so that the production manager can input in one place that he is going to pull materials for let's say 6 devices. Assume for example that 2 screws, 1 mother board, and 3 wires are needed. There is an entry in the database for each of those parts, with individual inventory counts.
Once the Production Manager somehow enters the number 6, then a calculation will be automatically run across the database to deduct the needed parts from each inventory record, 12 Screws (6 devices x 2 screws), 6 mother boards (1 Motherboard x 6 devices), and 18 wires (3 Wires x 6 Devices).
How do I do this? I can't use Global entries, because another part of my inventory formulas use stored numbers and Filemaker doesn't seem to like that so much...any ideas?
I've included a Screen shot for an explanation
Beginning Count = The inital count of the inventory
Purchased Units = Subsequent Additions to the Inventory
Returned Units = Inventory parts that are being returned or pulled out of inventory
Inventory Adjustment = allows for inventory adjustments during periodic counts
Cumulative Use Units = Cumulative Use Units + Build Count (this is the running total of builds for the product manager
Current Inventory = (Beginning Count + Purchased Units) - (Returned Units + Inventory Adjustment + Cumulative Use Units)
Build Count = Number of devices that need to be built.
Thanks for any help. Please forgive any spelling mistakes....I'm THIS close to the loony bin...