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.
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.
@WillowMobileSystems I wanted to circle back and provide a little update. First, thank you very much for putting the time in to help me with this. I was close, but yet so far away. I took your examples and had to modify a few small things, but none the less they worked great.
For FamilyID Ref:
SELECT( parent[FamilyID], OR( USEREMAIL() = [username], USERROLE()=Admin ) )
For EventID:
SELECT(Events[EventID], OR( AND( [Active] = "TRUE", NOT(IN([EventID], SELECT(Reservation[EventID], [FamilyID] = [_THISROW].[FamilyID])))), USERROLE() = "Admin" ) )
For LocationID:
SELECT(Location[LocationID], IN([LocationID], SELECT(Events[LocationID], [EventID] = [_THISROW].[EventID]) ) )
For EquipmentID:
SELECT(Equipment[EquipmentID], IN([EquipmentID], SELECT(Equipment[EquipmentID], [FamilyID] = [_THISROW].[FamilyID]) ) )
For SiteID (I added a validation to limit the SiteIDs to those that linked to a location AND not already reserved):
SELECT(Sites[Site ID], AND( IN([Site ID], SELECT(Sites[Site ID], [LocationID] = [_THISROW].[LocationID]) ), NOT( IN([Site ID], SELECT(Reservation[SiteID],TRUE) ) ) ) )
I am continuing to test to make sure everything works accordingly. I added the USERROLE() = Admin in some instances as I want to be able to manage reservations for people that may not be able to.
Thank you again for the help and guidance… I learned a ton actually working through the other implementations.
Thank you for the quick response… I am going to throw these into the valid_ifs and do a little testing… I will circle back with updates.
User | Count |
---|---|
40 | |
34 | |
29 | |
23 | |
17 |