Let ( v = Qty - Sum ( Molding Data::Quantity ) ;
If ( v < 0 ; 0 ; v )
But this strikes me as a "brute force" solution that may cause problems for how your business uses this info.
I recommend investigating using a "transactions" or "ledger" table to record each inventory change. Such a method allows you to both increase and decrease the "Qty on hand" totals for each item in inventory by adding new records to this "ledger" table.
An example of this table can be found in the FIleMaker 12 Inventory Starter Solution. If you don't have fmp12, you'd set up a table like this:
TransDate (Date field auto-enters creation date in most cases)
ItemID (Unique ID of each iventory item)
Description (text to describe inventory change, Product Received, manufactured, sold, shrinkage, Return to Vendor, etc.)
cBal (In - Out)
sBalance (Summary, total of cBal)
To increase inventory, you create a new record, specify the ItemID and enter the quantity in the "In" field. To decrease inventory, you do the same, but put the quantity removed from inventory in the out field.
Not only does this allow you to compute quantities on hand for every item in your inventory--both items you manufacture and items you consume during the manufacture process, You can generate reports from this table that show how inventory levels for each item are changing over time--which can you help you better manage your inventory levels.
This solution worked well.
We keep track of our "stock" orders differently than our custom orders. This database is essentially just to monitor the custom orders and make sure that the product is manufactured in it's entirety and that no orders get missed or short shipped. For the purposes of our immediate needs, your solution is absolutely perfect.
As we continue to expand the database to include more information and functionality, I'm sure that I'll need to consider alternative solutions and tables. I'm also sure that there are much better ways of handling it, however, in a pinch, this will suffice until I can devote more time to truly understanding FileMaker.
Thank you for helping me on both of the issues that I have posted in the forum!