Value List that changes as items are used up
Sorry if this has been posted before, but there are so many entries for value lists that don't help I gave up searching.
Here's the deal: I have a db with four tables: people, locks, lockers, and assign. All but assign have unique primary keys for each record. Assign will be for assigning locks and lockers to students. It was easy enough to set up the relationships that group the data together in assign. The hard part is providing a UI that is resistant to user error. To wit, I want the lock and locker number entry fields in the assign table to offer (using value lists?), only locks and lockers that are unassigned. In other words, the value list changes (shrinks) as locks are used up. For example if locks 101, 102, 103, and 104 are in the locks table but assign already has used 101 and 102 then the list that pops up for the next entry in assign will offer only 103 and 104.
I finally got a prototype to work by creating a calc field ("taken") in locks that uses a lookup to see if that lock number is already present in assign. If not, then another field in locks ("available") populates itself with the lock number, and that field is used to create the value list that is on the entry layout in assign.
The problem is that the lookup doesn't refresh and therefore keeps offering the same selection of locks even after some have been used. I tried making the lookup an unstored value to force it to refresh the lookup but the value list needs to work with indexed fields. I also tried manually refreshing the lookup, but got "There are no fields that look up values based on the field "taken". Okay, so I tried refreshing from the lock number in the assign table (where the lookup is looking). Same alert. The only way I can get it to refresh is by going into the field definition and changing something then changing it back.
So is there a better way to do this? It doesn't seem like rocket science to me, but then I've failed so far... :smileytongue: