Are you using a transaction table to track inventory changes? (each time you receive or manufacture new product you add a record to log the increase in order, each time you ship out the product, or any other event that reduces inventory, you add a record that logs that reduction in inventory...)
Yes I am using a Transaction table (I call it "inventory history")
One thing that might be somewhat unique about this solution is that I need to keep history on each Item in the inventory such as all testing that has been performed on an item and things like that. Given that when I add items to inventory, if there were 3 items ordered I do not add one inventory record with a quantity of 3, instead I add 3 records with a quantity of one each. This allows me to track each items separately, and the items have serial numbers that we need to track as well.
As long as you have "in" and "out" fields that log the addition and removal, whether you use one record for three items or 3 records doesn't make much difference.
First a very simple approach:
You can set up a summary field on the transactions table on a layout based on this table that does not have a body layout part but rather two sub summary layout parts. The first would be sorted by product name or ID to group your records by ID and provide a "sub header" for each part. A second sub summary layout part would be sorted by this status field. Summary fields can be placed inside both sub summary layout parts to show sub total type values for each group of records.
From your parts table, you can use ExecuteSQL to produce these totals: FMP 12 Tip: Summary Recaps (Portal Subtotals)
Without ExecuteSQL, there are several other approaches that combine the product ID in one field with this status value to match to only records of a given status and product ID in order to compute a total.