cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rmanthey
Level 8

Filter A Gallery by DatePicker Delegation Error

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
cwebb365
Level 10

Re: Filter A Gallery by DatePicker Delegation Error

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

View solution in original post

10 REPLIES 10
cwebb365
Level 10

Re: Filter A Gallery by DatePicker Delegation Error

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

Re: Filter A Gallery by DatePicker Delegation Error

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.  

Screen Shot 2019-09-13 at 7.47.24 PM.pngScreen Shot 2019-09-13 at 7.48.27 PM.png

rmanthey
Level 8

Re: Filter A Gallery by DatePicker Delegation Error

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.  

cwebb365
Level 10

Re: Filter A Gallery by DatePicker Delegation Error

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

View solution in original post

rmanthey
Level 8

Re: Filter A Gallery by DatePicker Delegation Error

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

diego_marino
Level: Powered On

Re: Filter A Gallery by DatePicker Delegation Error

hi sorry for necroposting

this formula really interests me.

ow can i convert a date/time in number with minutes and seconds?

rmanthey
Level 8

Re: Filter A Gallery by DatePicker Delegation Error

@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.

diego_marino
Level: Powered On

Re: Filter A Gallery by DatePicker Delegation Error

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

rmanthey
Level 8

Re: Filter A Gallery by DatePicker Delegation Error

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.  

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

Top Kudoed Authors
Users Online
Currently online: 308 members 1,647 guests
Please welcome our newest community members: