Answered

Trying to filter or lookup on a date field

(0) Share

Report

Posted on by Spreck76

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,

Categories:

Building Power Apps

I have the same question (0)

All responses (12)

Answers (1)

Sort by

aprildunnam 704 on at

Like (1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Appsessed 10 on at

Like (0)

Report
Copy link

Link copied!
Have you tried using LookUp instead of Filter?
LookUp('[dbo].[GSP_DailyPrices]', Load_Date = DatePicker1.SelectedDate)

Was this reply helpful? Yes No
Meneghino 6,949 on at

Like (0)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
Spreck76 29 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Spreck76 29 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Spreck76 29 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Verified answer

Meneghino 6,949 on at

Like (3)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Spreck76 29 on at

Like (0)

Report
Copy link

Link copied!

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

Thanks,

Was this reply helpful? Yes No
Meneghino 6,949 on at

Like (1)

Report
Copy link

Link copied!

Here is a somewhat old post that still has partial value:
https://baizini-it.com/blog/index.php/2017/09/28/powerapps-and-azure-sql-database-current-issues/

Was this reply helpful? Yes No
johnsonjohn 58 on at

Like (1)

Report
Copy link

Link copied!

Changing both dates to text with specifying format can solve the issue.

Text(DataCardValue7.SelectedDate,"[$-en-US]mm-dd-yyyy")=Text(attendance_date,"[$-en-US]mm-dd-yyyy")

DataCardValue 7 --> for attendance_date--> SQL

Was this reply helpful? Yes No