Unanswered

Filter dropdown to exclude selected items in date range

(0) Share

Report

Posted on by wbaldonado2008

I am creating a vehicle reservation app and need to create a dropdown with vehicle name that coming from vehicle list, and filter the dropdown so that when a user reserved a vehicle in a date range this vehicle is removed from the dropdown, then be available after date time is due, for example if I reserve a vehicle today from 9 am to 10 am the vehicle should not be available in that time range, but then be available from 10 for new reservations. se code below

I am using tow list, vehicle list and reservations list, thanks in advance

Sort(

Distinct(

Filter(

'Vehicle List',

Not(

Title in Filter( ReservationList , (DateTimeValue(Text(StartTime)) >= (DateValue(Text( ddStartDate.SelectedDate))+Time(Value(ddHour.Selected.Value),0,0))),

((DateTimeValue(Text(EndDate))) <= (DateValue(Text( drdEndDate.SelectedDate))+Time(Value(ddEndHour.Selected.Value),0,0)))

).Vehicle

)),

Title),Result,

Ascending)

Categories:

Building Power Apps

All responses (3)

Answers (0)

Sort by

Jcurtis260 23 on at

Like
a
(0)

Report
Copy link

Link copied!
The formula you provided is a good starting point for filtering the vehicles that are already reserved in a specific date and time range. However, it can be optimized for better performance and readability.
Here's an example of how you could filter the vehicles that are available for reservation in a specific date and time range:

Sort( Distinct( Filter( 'Vehicle List', Not( Title in Filter( 'Reservation List', And( DateTimeValue(Text(StartTime)) >= DateTimeValue(Text(ddStartDate.SelectedDate) & " " & Text(ddHour.Selected.Value)), DateTimeValue(Text(EndTime)) <= DateTimeValue(Text(drdEndDate.SelectedDate) & " " & Text(ddEndHour.Selected.Value)) ) ).Vehicle ) ), Title ), Result, Ascending )

In this example, the Filter function is used to filter the 'Reservation List' data source based on the start and end date and time values selected by the user, and the Not function is used to exclude the vehicles that are already reserved in that date and time range from the 'Vehicle List' data source. The Distinct function is used to remove any duplicate vehicles from the filtered list and the Sort function is used to sort the filtered list by the 'Title' field in ascending order.

This formula should provide no delegation errors as it's using the Filter and Distinct functions to filter the data and using the Sort function to sort the data, all of them are delegation-friendly functions.

Please note that this is just an example, and you might need to adjust it to fit your specific use case.
wbaldonadoh 2 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks for you help, I was able to resolve the issue
Maryola 2 on at

Like
a
(0)

Report
Copy link

Link copied!

@wbaldonadoh could you share how you resolved the issue?

I'm also trying to use the code above for the dropdown I have in my canvas app, containing cars which are about to be booked for a business trip, but it's not working for me at all.
The difference is that I'm using Dataverse instead of Sharepoint.
The 'Vehicle list' in my case is table called 'Cars', and the name of the car is not in Title but in Name column.
The 'Reservation List' in my case is table called 'Business trips'.
On Business trips form there's a lookup to Cars table, and this column is called 'Car'.
Can anyone help me to filter cars in the dropdown I have in my canvas app based on the start and end dates and time of the trips. @Jcurtis260