cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PYGDL
Regular Visitor

Problems with delegation when filtering by date

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.

 

Regards!

14 REPLIES 14
v-xida-msft
Community Support
Community Support

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.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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

@xzDan 

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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
PYGDL
Regular Visitor

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.

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,239)