cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Spreck76
Helper I
Helper I

Trying to filter or lookup on a date field

Okay, been trying a few different solutions I've found on the boards but nothing seems to work. My app is pulling data from an Azure SQL database with a "date" data type. I have entered data using a DatePicker in PowerApps. I am trying to do a simple filter of said data based on a date chosen from a DatePicker. Essentially it is the same DatePicker used to enter the data I want to use to retrieve stored data. I have the Date time zone of the DatePicker set to Local.

 

When I use a simple label with the code DatePicker1.SelectedDate I see the correct date. When I use a filter on a Gallery for example using the formula:

 

Filter('[dbo].[GSP_DailyPrices]', Load_Date = DatePicker1.SelectedDate)

 

I get a blank gallery, no errors, no warnings. Not sure what I'm doing wrong here.

 

Appreciate the help.

 

Thanks,

1 ACCEPTED SOLUTION

Accepted Solutions
Meneghino
Community Champion
Community Champion

The obvious work-around which works 100% of the time is to add a calculated column to your table (and add that column to an index in order to safeguard performance) containing an int type of value yyyymmdd taking the values from your date column.

Then use that column for any filtering in PowerApps.

Once the issues are resolved in PowerApps you just drop that calculated column.

View solution in original post

12 REPLIES 12
aprildunnam
Power Participant
Power Participant

I would check the Load Date field and see what format it is expecting.  For example, if it's expecting Long Date but your Date Picker in PowerApps is a Short Date it might not work.  I would suggest taking a look at the Date Formatting options here:

 

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/show-text-dates-times

Appsessed
Frequent Visitor

Have you tried using LookUp instead of Filter?

LookUp('[dbo].[GSP_DailyPrices]', Load_Date = DatePicker1.SelectedDate)
Meneghino
Community Champion
Community Champion

Yes, definitely a bug!  Should be reported.  This functionalityused to work, but now seems broken.

 

Even this type of expression does not work now:

 

Filter('[dbo].[GSP_DailyPrices]', Load_Date = Date(2018,7,11))

 

Maybe @CarlosFigueira can kindly report this.

Yeah, I confirmed on the SQL server the data type is "date" so it is expecting short date only. I'll look at the link as well, thanks.

I don't think using a LookUp wouldn't work as it will only return the first record it finds. I need to filter a table based on the date but to return multiple records. Appreciate the idea though. 

You are correct, tried and while a call is made (I see the squares racing across the top of the screen) my gallery remains blank. No errors, no warnings, nothing. This is frustrating, seems like a pretty basic and straighforward thing to do. Oh well, hopefully it will get resolved soon. I'll keep plugging away at the rest of my app in the meantime.

 

Thanks for the help.

Meneghino
Community Champion
Community Champion

The obvious work-around which works 100% of the time is to add a calculated column to your table (and add that column to an index in order to safeguard performance) containing an int type of value yyyymmdd taking the values from your date column.

Then use that column for any filtering in PowerApps.

Once the issues are resolved in PowerApps you just drop that calculated column.

Yep, worked like a charm. Adjusted my SQL table and PowerApp code to accomodate.

 

Thanks,

Meneghino
Community Champion
Community Champion

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (3,595)