Value List Problem

[Database Designs]

I realize this has probably been asked numerous times but I can't find another thread that I understand. I have an Inventory table with items listed by "shelf", "category" & "Stock ID". I also have a field that shows it's status as either "reserved or "Available". When entering a line item in my Invoice file, I have value lists for "Shelf", "Category" & "Stock ID". My Stock ID Value list now lists all related items but I only want my Stock ID value list to show me just the "available" ones.

I don't know how I can do that. Any help would be sincerely appreciated.

[bcooney]

I'm a bit confused. On your invoice line item, why would you have the user select Shelf or Category. Wouldn't they only need to select StockID and the other two values would either display using a related field, or be the result of an auto-enter.

To show just "Available" StockIDs, create a new field in Inventory, StockID_avail = if (status = "Available" ; ID; "") and use this field for your value list.

[LaRetta]

I believe you are asking for 'conditional value list'. I would provide specific link to some demos but I don't have access right now. You can search here for some excellent samples. :-)

[Database Designs]

I thought your StockID_Avail was going to be the ticket but it won't work because my field that says "Available" is an unstored calculation based on a few other factors.

[bcooney]

You might consider a popup selector window. This will allow you to "find" the Available stockIDs and display them in a list. However, finding on unstored calcs will be slow.

Can you update a static field in Stock transactionally so that you have a stored flag for availability?