pop up menu that filters options after they are selected in other records
I'm trying to setup a database for tracking seat assignments to multi-day events.
So for instance, let's say I have three seats:
And the event lasts three days:
I'd like to create a popup menu that shows the available seats on each day. So that if John Smith is using Seat 1 on Day 2, I won't be able to seat anyone else in Seat 1 on Day 2. Further, Seat 1 would still be available on Day 1 and Day 3.
Is there an easy way to do this?
Right now I've created three tables (Seats, Days, Assignments), and pop up menus for Days and Seats in the Assignments layout, but I can't figure out how to eliminate a Seat from the pop-up menu after it has already been used on a particular Day.