cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Spreck76
Level: Powered On

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
Level 10

Re: Trying to filter or lookup on a date field

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
Level 10

Re: Trying to filter or lookup on a date field

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
Level: Powered On

Re: Trying to filter or lookup on a date field

Have you tried using LookUp instead of Filter?

LookUp('[dbo].[GSP_DailyPrices]', Load_Date = DatePicker1.SelectedDate)
Meneghino
Level 10

Re: Trying to filter or lookup on a date field

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.

Spreck76
Level: Powered On

Re: Trying to filter or lookup on a date field

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.

Spreck76
Level: Powered On

Re: Trying to filter or lookup on a date field

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. 

Spreck76
Level: Powered On

Re: Trying to filter or lookup on a date field

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
Level 10

Re: Trying to filter or lookup on a date field

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

Spreck76
Level: Powered On

Re: Trying to filter or lookup on a date field

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

 

Thanks,

Meneghino
Level 10

Re: Trying to filter or lookup on a date field

Helpful resources

Announcements
New Ranks and Rank Icons in April

'New Ranks and Rank Icons in April

Read the announcement for more information!

Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (2,827)