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.
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
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.
Stay up tp date on the latest blogs and activities in the community News & Announcements.
Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST
Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.
Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!