Delegation - possible to filter before the 2000 limit

[ccant]

Hi, I have a filter in a gallery returning data from a SP document library (see below). I need to filter on the path and this would return only a handful of records. However, because of the 2000 delegation limit I am not able to return the 5 document paths I need. I have tried putting it into a collection but I still need either filter, sort, lastN which are all truncated at 2000 before running. I have also tried to Set a variable as suggested on other posts but the {path} bit is still throwing a delegation warning. Is it just going to have to be a case of archiving old attachments or is there a workaround that would work for a document library where I search on path? Thanks

Filter('Ticket Attachment', {Path} = "Lists/Ticket Attachments/2018/05/Ticket_2007/")

[RandyHayes · Accepted solution]

@ccant 

The issue is that {Path} is a calculated column in SharePoint. And as such, they are non-delegable.  

To get around this, you'll need to make the path a real text column in your library.  You can do that with either Flow or Workflows in SharePoint.  The goal is to, on each item change, get the {Path} value and store it in the new text column.

Then, you can use delegable functions on the text column such as StartsWith or even '='

I hope this is helpful for you.

[v-yuxima-msft]

Hi @ccant 

Could you please refrence @RandyHayes 's suggestion?

And your issue is solved.Please accept it as solution.

More information:

delegation-overview

Best Regards.

Yumia

[ccant]

Thank you @RandyHayes! Your explaination really clear and helpful. I think it is working (the delegation warning has gone and I assume this will then work properly when we go over 2000 items again later this week)

[RandyHayes]

@ccant 

Very good!

You can always test if delegation will be an issue or not by lowering the number of records returned in the Advanced settings.  If you lower the number to something less than your current list size, then you can see if there will be issues.

Remember - delegation and record limits are two different things, but record limits impact when delegation is needed and working.  So, you can test by lowering the limits.