Filtering a gallery based on information from a different Sharepoint list

[elbent]

I am building an app which will allow users to reserve vehicles. I have two sharepoint lists, a vehicle list and a reservation list. I want to filter a gallery of vehicles based on what vehicles are available during a time slot that the user enters via dropdowns. How do I filter the gallery based on data in a different Sharepoint list? I know how I would do this with databases. Do I have to create an intermediary list of vehicle-reservations or is there a simpler way? Thanks.

[Hassan_SZ_365 · Accepted solution]

Hi @elbent ,

Filtering a gallery in PowerApps based on availability from another list can be done directly without needing an intermediary list. You can achieve this by using a combination of Filter, LookUp, and Not functions to exclude vehicles that are already reserved within the selected time slot.

Here's a simplified approach in human-readable format:

1. Collect Time Slot: Gather the start and end times from the user via dropdowns or date pickers.
2. Filter Reservation List: Use these times to filter the reservation list for any entries that overlap with this time slot.
3. Create a Collection of Booked Vehicles: Extract the vehicle IDs (or names) from the filtered reservations.
4. Filter Vehicle List: Filter the vehicle list to exclude the IDs collected in the previous step.

In PowerApps, assuming you have a gallery (Gallery_Vehicles), and user inputs for start time (DatePicker_StartTime) and end time (DatePicker_EndTime), you could set the Items property of the gallery like this:

Filter(
    VehicleList,
    Not(
        Title in Filter(
            ReservationList,
            (StartDate <= DatePicker_EndTime.SelectedDate) &&
            (EndDate >= DatePicker_StartTime.SelectedDate)
        ).VehicleTitle
    )
)

Explanation:

• Filter(ReservationList, (StartDate <= DatePicker_EndTime.SelectedDate) && (EndDate >= DatePicker_StartTime.SelectedDate)): This gets reservations that overlap with the selected time slot.
• VehicleTitle: This assumes that 'VehicleTitle' is the field in the reservation list that relates to the vehicle's identifier in the vehicle list.
• Not(Title in ... ): This excludes vehicles found in the overlapping reservations from the vehicle list being shown in the gallery.

Replace VehicleList, ReservationList, StartDate, EndDate, Title, and VehicleTitle with the actual names of your lists and columns.

By setting the gallery's Items property with this formula, it should show only those vehicles that are not reserved within the selected time frame.

Best Regards,
Hassan Raza

[elbent]

Hi @Hassan_SZ_365 , thanks so much for this thorough explanation. My question is how do I set ReservationList to contain the entries from the reservations Sharepoint list? Thanks.

[Hassan_SZ_365]

Hi @elbent ,

To set ReservationList to contain entries from the reservations SharePoint list in PowerApps, you simply need to reference the SharePoint list directly by its name if you have already connected it to your app. If not, you'll first need to connect your app to the SharePoint list. Here's how you can do it:

1. Connect to SharePoint List:

   • On the left-hand menu, go to 'Data sources'.
   • Click on 'Add data source' and select SharePoint.
   • Connect to the SharePoint site where your list is hosted and then choose the reservations list to add it to your app.

2. Use the SharePoint List in PowerApps: Once connected, you can refer to the list directly by its name in your formulas. If the name of your reservations SharePoint list is "Reservations", you use it as follows: 

Filter(
    'Vehicle List',
    Not(
        'Vehicle Title' in Filter(
            'Reservations',
            (StartDate <= DatePicker_EndTime.SelectedDate) &&
            (EndDate >= DatePicker_StartTime.SelectedDate)
        ).'Vehicle Title'
    )
)

Remember to replace 'Vehicle List' and 'Vehicle Title' with the actual name of your vehicle SharePoint list and the column that contains the vehicle identifier, respectively.

1. Set Gallery Items: With the SharePoint list connected, set the Items property of the gallery (let's say your gallery is named Gallery_Vehicles) to the above formula. This will dynamically filter the gallery based on the reservations list.

Here is the code again, with the correct SharePoint list names applied: 

Gallery_Vehicles.Items = Filter(
    'Vehicle List',
    Not(
        'Vehicle Title' in Filter(
            'Reservations',
            (StartDate <= DatePicker_EndTime.SelectedDate) &&
            (EndDate >= DatePicker_StartTime.SelectedDate)
        ).'Vehicle Title'
    )
)

Make sure that the 'StartDate' and 'EndDate' fields are correctly named as they are in your SharePoint reservations list. This formula should now work for filtering the gallery based on the available vehicles during the selected time slot.

Please note that delegation warnings might occur if your SharePoint list is large, and the filtering criteria cannot be delegated to the SharePoint backend. In that case, you might need to pull in the entire list or use a different strategy for filtering.

Best Regards,
Hassan Raza

[elbent]

Thank you, I was able to make this work!