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
Solved! Go to Solution.
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.
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.
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
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.
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.
Check it out!
Fill out a quick form to claim your user group badge now!
Find out where you can attend!
Features releasing from October 2019 through March 2020
The largest Power BI, Power Platform, and Data conference in New Zealand