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.

View solution in original post

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

 

Thanks,

Meneghino
Community Champion
Community Champion

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

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