Use script triggers on your depletion and addition layouts that perform a script that computes the current inventory for that one item and updates either an inventory quantity field or a flag field that is set to 1 if inventory is greater than zero. Either method produces an indexed field that can be used with your conditional value list.
Don't use a value list. Set up a portal to your inventory with a filter on it to omit records with a balance on hand of zero. A relationship using the X operator instead of the default = sign will produce a relationship that matches to all inventory records. A button in this portal row can be used to select the desired inventory item for your build record when clicked. Note that a more sophsicated combination of filtering and scripting can be used with such a portal to allow you to type part of the inventory item's name and the portal can update keystroke by keystroke to list only items in inventory that start with or contain the text you enter.
Thanks Phil I think I am going to start with option one until I have the thing up and running then get fancy.
Note that there is a "safe but slow" option for script based computing of the inventory balance and a "fast but risky" way to compute it.
"fast but risky", simply adds the quantity from the newly added "Addition" record and subtracts the quantity from a newly added "depletion" record. You have to be very careful not to let such a script be performed on the same record twice. And things get especially tricky if you change the existing quantity field--say to correct a mistake as you then have to subtract the original value added in your "addition" table, then add in the new quantity. A single error in that and your total will be wrong on each subsequent record that adds or depletes the quantity.
"safe but slow" pulls up the related inventory record and computes a new inventory balance for that one item from the context of this one record by adding all the related "Addition" quantities and subtracting all the related "Depletion" quantities. This second method can be much slower than the first--especially if you have 1000's of change records logged for that one item, but wil be 'self correcting'--unlike the first method.
You may find you have to go with "fast but risky" to avoid unacceptable delays, but if you do, I suggest also implementing "safe but slow" as an alternative so that you can recalc the inventory balances for one or a group of inventory items should you discover that some of your script computed inventory balances are not correct.
What I have is a formula in a total field in "Inventory" which adds all the related "Inventory Additions" and subtracts all the related "Inventory Depletions" to derive a total. Does this fall into either category? I'm fairly well versed in Excel but not with programming which is why I thought to do it that way I suppose. I think this would be the reason I needed to create a flag field to indicate when the balance is zero and the lot should be taken off the list of available inventory since the total is a claculation and not indexed.
A script that copies the contents of that field to a stored, indexed field to make your conditional value list work would be an example of the "safe but slow" option. "Slow", as I am using the term here, is a relative term. "Slower" (than the "risky option) might be more accurate.