Good day to all.
I have a problem with an application in which users register hours invested in work. In a single day they can make several registrations and at the end of the month or the fortnight in the application they could see how many hours they registered.
The records are stored in a Sharepoint list, currently there are around 15k.
The problem is that users for a while can no longer see their records made in the gallery, the data appears blank. The formula indicates a delegation error.
My formula what it does is filter the gallery according to the month that the user chooses and is the following:
If (Month_1.SelectedText.Value = "January"; Filter ('Hours_Inverted'; DateValue (Text ('Activity date'))> = DateValue ("25/12/2018") && DateValue (Text ('Date of activity ')) <= DateValue ("24/1/2019")))
The formula marks the DateValue part in blue. The funny thing about this is that the application works correctly in Team or powerapps web version, but in the desktop or mobile version it does not.
I have tried in many ways applying and modifying the filtering to apply the delegation but I have not had much success 😞
I would really appreciate it if anyone had any idea that I'm running away to be able to filter 15k records by date.
Solved! Go to Solution.
Hi @PYGDL ,
It would not affect the result you want to achieve. Please make sure you have set the "Data row limit for Non-delegable queries" option to maximum vlaue -- 2000 within Advanced settings of App settings of your app already.
If you have changed above Delegation limit, the Filter('reporte de Tiempos', ID >= 19000 && ID<=21000) formula would return 2000 records at most (from ID=19000 to ID=21000).
Please take a try with above solution I provided, then check if the issue is solved.
@v-xida-msft ID is not delegable for any operation other than = (equal). Using the greater than and less than operators will not return valid results from SharePoint.
@PYGDL You'll need to consider some of the other options presented in the replies to achieve your goal.
To sort this on my end, I made a number column that is in the form yyyymmdd, i.e. 20190501, a unique number.
This can be fully delegated then, and for formatting, you could have a calculated column
Yes...works well, that and the other choice of converting to string are options as I mentioned in reply #3. But definitely not by ID or Date alone as they are nondelegable currently in SharePoint.
Thank you all so much for your help.
In the end, I applied the @RandyHayes solution and it worked great.
I created a new column in the SP list where the date is saved in numerical format, example: 20190720, and I use these number in the filter of each gallery.
To fill in this column in the existing records, perform a Flow that did all the work taking 5000 records each time.
There is even an improvement in the performance of the application.
Again, thank you all for your answers.
Check out new user group experience and if you are a leader please create your group
Did you know that you can visit the Power Query Forum in Power BI and now Power Apps
Participate in the Power Virtual Agents Community Challenge
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.