I have three tables:
event has a unique row per event.
available_class has the list of available class names.
event_class has the classes that are assigned to a particular event.
The "event_id/available_class_id" index in event_class is unique so an available_class can only appear once per event.
I have used the list wizard to show the list of names in available_class instead of the id when adding an event_class. That works great.
When adding a new event_class, I would like the list wizard to only show the list of names that have not already been used in the unique index so people cannot attempt to re-add the same class twice. Currently it gives an error when a duplicate is selected, but I would prefer not to have the duplicate choices at all.
Assuming we are adding an event_class for event #1, this MySQL query works to generate the correct list:
SELECT * FROM available_class WHERE available_class.id NOT IN (SELECT id FROM event_class WHERE event_class.event_id = 1)
I can't figure out how to make that type of query the pick list source in the list wizard.
I tried shoving "available_class.id NOT IN (SELECT id FROM event_class WHERE event_class.event_id = 1)" in the Where box as a version 1 test, but it grumped at me.
Any Ideas?
Thanks!