I would like to create a conditional value list that only populates with items that have not been sold yet.
The basic structure I am using is three related tables: a table for Buyers, a table for Items, and a join table with entries that are made when an item is sold (capturing BuyerID and ItemID).
I am trying to limit the Item value list to only those items not sold yet, based upon a global Filter field in the Buyer table (set to 0) and a calculated Count field in a related ItemAvailable TO that counts the number of occurrences for each item (ItemID) in the join table. Available items will have a count of 0 and should populate the Item value list, while sold items will have a count of 1 and should not populate the Item value list. The Item value list, however, is not populating with any items.
When I change the calculated Count field in the Item table to a number entry field and manually enter 0’s and 1’s for the items, all is fine, so the overall setup seems fine. As soon as I change back to the calculation to populate the field in the Item table with their actual counts from the join table, however, the item value list will no longer populate…even though the Count field in the Item table is still showing 0’s and 1’s just as when the values were manually entered.
Any idea what I'm doing wrong and how to get this conditional value list to work as desired?
Thanks in advance!