Canvas App Gallery - filter items from one entity based on another

(0) Share

Report

Posted on by CrystalShardz

Hi,

I'm trying to create a pool car booking app where by it forces people to share cars where available.

I have a form which asks for the users:

collection point
destination
collection time
return time

This then needs to display a gallery which contains a list of available cars (if there are any) which the user can then select.

My entities are:

Pool Car

Registration

Base Site

Available (yes/no)

Pool Car Bookings

Pool Car (related entity)

Start Date(date/time)

End Date (date/time)

destination(text)

I'm struggling to filter the gallery items based on the form.

In SQL I'd look to do something like this:

SELECT * FROM poolCars c WHERE NOT EXISTS( SELECT * FROM
	bookings b
	WHERE 
	(DATE_FORMAT(b.startDate, "%Y-%m-%d") = CURDATE() 
		AND b.passengers >= c.seats
	)
	AND b.poolCar_id = c.id
)

however I am unsure how to do this in PowerApps. Any ideas?

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (1)

Verified answer

WarrenBelz 154,393 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
Hi @CrystalShardz ,
I will call the fields below
DestinationTxt
Pickup
Return
For the moment, I will assume you are using (for the last two) the standard three element (Date, Hour, Minute) controls generated by PowerApps when adding a Date/Time card. I have not done any error control as all three items would need to be completed. You have mentioned some other parameters in your SQL, but I will deal with the three above to give you an idea of the syntax required for displaying all a list of cars that are going to a destination and leaving withing 30 minutes either side of the selected date/time and arriving within the same tolerance. I believe also you would need to query PoolCarBookings
Filter( PoolCarBookings, Destination = DestinationTxt.Text && Abs( DateDiff( Pickup.SelectedDate + Time( Value(Pickup.SelectedText.Value), Value(Pickup.SelectedText.Value), 0 ), StartDate, Minutes )< 30 && Abs( DateDiff( Return.SelectedDate + Time( Value(Return.SelectedText.Value), Value(Return.SelectedText.Value), 0 ), EndDate, Minutes )< 30 )
Incidentally, if you use SharePoint as a backend, MS Access connects directly and all your SQL works perfectly - I use it extensively for generating Excel reports and also for dashboards.

Please click Accept as solution 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 Thumbs Up.

Was this reply helpful? Yes No
CrystalShardz 5 on at

Like (0)

Report
Copy link

Link copied!

Thanks for the code snippet 🙂 With your help i've managed to get what I was intending to work in my app.

Was this reply helpful? Yes No