Struggling to get correct code syntax to configure OnSelect property (Canvas)

(2) Share

Report

Posted on by CU06061114-0

Goal:

I am attempting to build a Vehicle Rental System in Canvas.

When the user selects a BeginDate and EndDate via the form, and then clicks "Search Available Vehicles," I would like for all vehicles available during that timefrime to populate in the gallery on the form.

I am currently running into issues determining the correct code syntax for the OnSelect property correctly. Could someone please take a look or direct me to resources which could help? Please see below.

OnSelect Code:

ClearCollect(
colUnavailableCars, // Temporary Collection to store IDs of unavailable cars
Filter(
'Rentals Calendar',
BeginDate <= EndDate_DatePicker.SelectedDate && EndDate >= BeginDate_DatePicker.SelectedDate // Check for date overlaps. VERY IMPORTANT!
)
);

ClearCollect(
colAvailableCars, // Temporary Collection to store available cars
Filter(
Cars,
!(CarID in colUnavailableCars.CarID) // Check if CarID is NOT in the list of unavailable CarIDs
)
);

Assumptions:

'Vehicle List' is the name of your SharePoint list containing vehicle information.
'Rentals Calendar' is the name of your SharePoint list containing rental information.
BeginDate_DatePicker is the name of your date picker control for the rental start date.
EndDate_DatePicker is the name of your date picker control for the rental end date.
CarIDTitle is the internal name of the CarID column in the 'Vehicle List'. Verify this in SharePoint list settings!
CarID is the name of the CarID column in the Rentals list.
You want to display the available cars in a gallery named AvailableCars_Gallery.
The CarID column in the Rentals List contains the ID of the Car.
BeginDate is the column name in the Rentals Calendar for existing reservations.
EndDate is the column name in the Rentals Calendar for existing reservations.

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (0)

Michael E. Gernaey 53,433 Super User 2025 Season 2 on at

Like (0)

Report

Hi @CU06061114-0

Apologies but other than your Delegation warning, what exact error are you seeing? I do not see anything wrong with what you have. I mean we can fix your delegation issue, but what isn't happening exactly?

Was this reply helpful? Yes No
Suggested answer

sumitkp 6 on at

Like (0)

Report
Hi. The Not..In isn't supported in Power Apps.

I have implemented a similar thing but using a lookup column.

I have a table 'SumitCarRentals' which has columns: CarID, BeginDate and EndDate.

I have another table 'SumitCarDetails' which has columns: Name, CarID (LookUp Column to SumitCarRentals table), CarType.

Schema for SumitCarDetails table:

Schema for SumitCarRentals table:

In the below image, I have a vertical gallery which display all the available cars after the StartDate.

In the gallery it shows the cars which are booked for the period shown. The Search button is for the new booking after the end date of current booking.

The formula for OnSelect would be:

ClearCollect(colAvailableCars, Filter(SumitCarDetails, CarID.EndDate <= Start_DatePicker.SelectedDate));

I hope this solves your issue.

Was this reply helpful? Yes No
Suggested answer

WarrenBelz 153,084 Most Valuable Professional on at

Like (0)

Report
Hi @CU06061114-0,

Firstly Not() certainly is available in Power Apps - it is just not Delegable with SharePoint. If you have less than 500 records inside the date filter, this will work. It also "masks" the Delegation problem as it is not relevant on numbers less than your Data Row Limit (which you can increase to 2,000 if required).
With( { _Unavailable, Filter( 'Rentals Calendar', BeginDate <= EndDate_DatePicker.SelectedDate && EndDate >= BeginDate_DatePicker.SelectedDate ), _Cars: Cars }, ClearCollect( colAvailableCars, Filter( _Cars, !(CarID in _Unavailable.CarID) ) ) )

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
MVP (Business Applications) Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No
CU06061114-0 19 on at

Like (0)

Report

I wanted to add that ALL vehicles are showing up, rather than only the filtered ones. Only the available vehicles should show, not all of them!

Was this reply helpful? Yes No
WarrenBelz 153,084 Most Valuable Professional on at

Like (0)

Report

Hi @CU06061114-0,

The code I posted should exlude any car where the CarID in colAvilableCars is also in the CarID of 'Rentals Calendar' between those dates.

Was this reply helpful? Yes No