Here goes nothing…
I’ve searched, read, searched some more, and tried to piece together what I thought would work… and now I’m here.
I have an app for managing events. There are several tables (not all listed):
- Reservation - ReservationID
- Family - FamilyID
- Event - EventID
- Location - LocationID
- Equipment - EquipmentID
- Site - SiteID
On the Reservation table I have 5 references to other tables (Family, Event, Location, Equipment, Site). There is a form that is presented to the users for them to submit a reservation (given they have setup their family and equipment, and an event exists with a location and sites).
I am attempting to filter the values returned in the Ref drop downs within the form as follows:
- Event - Return only those events that have an ‘Active’ status = TRUE and does not exist in the Reservation table for the FamilyID selected
- Location - Return only those locations that are linked to the selected Event
- Equipment - Return only the equipment linked to the selected Family
- Site - Return only those sites linked to the selected Location
A few key points:
Events must have a Location linked (reference between Event and Location)
Sites must be linked to a Location (reference between Location and Site)
Equipment must be linked to a Family (reference between Equipment and Family)
The validation to not allow more than one reservation for a given event is my #1 struggle right now. Let me know if I need to elaborate on anything further.