Filtering a reference drop down in a form

[Matthew_Ronan]

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.

[WillowMobileSys · Accepted 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.

[Matthew_Ronan]

@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.

[Matthew_Ronan]

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.