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):
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:
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.
Solved! Go to Solution.
To perform filtering for the Ref column dropdown lists, you will want to add expressions in the Valid_If property of the column.
Events
The expression would be like similar to this (note this is not tested):
SELECT(Event(EventID),
AND([Status] = "TRUE",
NOT(IN([EventID], SELECT(Reservation[Event], [Family] = [_THISROW].[Family]))),
[EventID] = [_THISROW].[Event]
)
)
The last portion in the AND() clause is to account for Editing. It makes sure the Event selected on the record IS included should you dropdown the list while in Edit mode.
Locations
The expression would be similar to:
SELECT(Location[LocationID],
IN([LocationID],
SELECT(Event[Location], [EventID] = [_THISROW].[Event])
)
)
After typing it up, I think you can simply this to the inner most SELECT()…UNLESS you want to do something more sophisticated like removing an already selected item from the list. In that case, you’ll need to update the expression as written. I can help with that later if needed.
Others
The other filters will be similar to the above. I’ll leave it to you to implement BUT if you are still struggling, please just post back to here.
User | Count |
---|---|
35 | |
35 | |
28 | |
23 | |
18 |