Answered

Filter A Gallery by DatePicker Delegation Error

(0) Share

Report

Posted on by Community Power Pla...

For the life of me I can not figure out how to filter a gallery by a data selected with a datepicker. I had it working last week and now I had to change some things and it is broke.

I was using this in the items property on the gallery:

Filter('[dbo].[Batch_Data]', Text(Roast_Date, "yyyy-mm-dd") = Text(roastDate.SelectedDate, "yyyy-mm-dd"))

However now I am getting a delegation warning. I will go over the 2000 record limit. I am unsure of how to get this working again.

Any help is appreciated

Categories:

Building Power Apps

I have the same question (0)

All responses (14)

Answers (1)

cwebb365 3,294 Most Valuable Professional on at

Like (0)

Report

any reason why you are using text? Try wrapping the first column in datevalue(Roast_Date) = datecontrol.selecteddate

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

That didn't return any filtered data. The issue with the delegation worries me as we will have more than 2000 records in about 4 months time.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Ok I somewhat figured out a work around. So my issue was I do the batch scheduling in Powerapps and it pushes to the backend (Azure SQL DB). I am then unable to filter the table in the DB without Delegation warning. So I added an excel table in OneDrive that the schedule info pushes to also, Then my gallery filters the OneDrive table, I get no delegation errors. Now I will admit that I will probably have 12 files (one for each Month) just becuase there is a 2MB limit on OneDrive but i'm not getting the delgation warning.

Was this reply helpful? Yes No
Verified answer

cwebb365 3,294 Most Valuable Professional on at

Like (0)

Report

Pretty sure you can only pull so many records from excel so not sure that will work. What most people do for date fields is convert them to numbers then they are delegable. I thought sql delegated dates but was wrong. Here is note on Microsoft doc about date fields for sql.

Direct date filters do not work for SQL Server. However, you can create a calculated column that will work. For instance, ALTER TABLE myTable ADD DateAsInt AS (YEAR([date]) * 10000 + MONTH([date]) * 100 + DAY([date])) and then filter on the calculated number column

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
Ok I did that for my Roast_Date and Pack_Date. I then filtered by doing :

Filter('[dbo].[Batch_Data]',RoastDateAsInt = Value(Text(roastDate.SelectedDate, "yyyymmdd")))
All works now.

Thank you

Was this reply helpful? Yes No
diego_marino 113 on at

Like (0)

Report

hi sorry for necroposting
this formula really interests me.
ow can i convert a date/time in number with minutes and seconds?

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
@diego_marino do you mean convert 2019/10/16 08:42:59 into an integer such as 20191016? If so follow @cwebb365 steps in the answers above. It is the best way to filter by date.

I took it one step further though as I was having issues capturing all my data for a specific day. I use an Azure SQL DB and it registers input in UTC time, even though my app pushed Local time. So when I wrote my time to the back end I used
{Date: Now()-.416}
The .416 comes from me being -10 hours from UTC (i'm in honolulu). I did 10/24 = .416. The formula subtracts those 10 hours from my local time but when it is written in the DB it is true with the local time because of the conversion to UTC that happens in the DB.

Was this reply helpful? Yes No
diego_marino 113 on at

Like (0)

Report

yes i want to convert a date in a number...but i'd like to include in the calculation even minutes and seconds, i think in this formula they are not included

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Have you tried :

ALTER TABLE myTable ADD DateAsInt AS (YEAR([date]) * 10000000000 + MONTH([date]) * 100000000 + DAY([date]) * 1000000 + Hour([date]) *10000 + Minutes([date]) * 100 + Seconds([date]))

Now I have no idea if the last 3 expressions are correct(that will take some research) but for every expression added you will need to add two zero's to all other number to ensure the integer is formatted as expected.

Was this reply helpful? Yes No
diego_marino 113 on at

Like (0)

Report

thanks, works great. i should have thought about this solution...

Was this reply helpful? Yes No