Struggling to filter my SharePoint List due to delegation issues and 2000 row limit

[Emily2]

I'm really struggling to get my head around and resolve this delegation issue / 2000 rows of data limitation.

Summary of my app:

• My app is to help a team manage their tasks (emails coming into a central mailbox)
• My data source is a SharePoint list
• When an email arrives into the central mailbox, a power automate flow creates a row in the SharePoint list with the email details and sets the stage to "Ready for triage"
• Each row in the SharePoint list is a separate task in the app
• Users check the app daily and allocate tasks to themselves to work on
• If they find the task isn't relevant (spam, marketing, etc.) they can set the stage as "Not relevant" and the row will be deleted from the SharePoint list. Otherwise they will work on the task
• The app has various different screens giving the user different views of the data. There are screens showing:
  • All open tasks (with a search function)
  • All tasks awaiting allocation
  • All tasks in progress
  • All tasks that have been handed over to another team
  • All tasks that have been returned from the other team for completion
  • Tasks that have been closed within 30 days, with a search function (in case they want to refer back to something they've done, after this time all "Closed" tasks will no longer appear in the app but can obviously still be found in the SharePoint list if needed)
  • The user their tasks only

Because of the way the data needs to be filtered for some of these screens, I got a delegation warning. To combat this I used a collection which removed the delegation warning and I therefore thought the issue with the 500 row limit was resolved. However, once my SharePoint list reached over 500 rows, the screens using collections stopped filtering for all the data and tasks "disappeared" from the app. I now understand that collections are non-delegable so even though the delegation warning disappeared it was actually still going to be an issue.

For the short-term I've increased the row limit to 2000 and the app is working like normal again, but this is only a short term fix. After just over 1month of use there are already 919 rows of data! I don't have long to find a fix.

I've tried using a ForAll but I couldn't get it to work. I've also tried creating a collection on a filtered dataset and that didn't work either. In both cases I didn't get an error on my formula it just didn't return any data even though it should have (at least, I was expecting it to).

I was going down the route of "there should never be more than 2000 open tasks" so I was trying to create a collection of just open tasks and then filtering on this in the gallery.

I've seen solutions where there are multiple collections collated into one large collection. So the formula looks at the row ID and creates a collection for rows 1-2000, then 2001-4000 etc. But this wouldn't work for me, I don't think, for various reasons.

The screens I need help with are:

• All open tasks (with a search function)
• All tasks in progress
• Tasks that have been closed within 30 days (with a search function)

For the two screens with the search function, it's including the search function results in the gallery items filter that's causing the delegation issue. With the 'All tasks in progress' screen it's the User.Email <> Blank() that's causing the issue.

Currently on all three screens the OnVisible property is set to:

ClearCollect(col_TaskData, TaskData)

Then the All open tasks Gallery Items filter is:

Filter(col_TaskData, Status = "Open", 'Search.Text'.Text in 'Sender Email' || 'Search.Text'.Text in Title || 'Search.Text'.Text in User.Email)

The All tasks in progress Gallery Items filter is:

Filter(col_TaskData, Stage.Value = "In progress", User.Email <> Blank())

The Tasks that have been closed for 30 days Gallery Items filter is:

Filter(col_TaskData, Stage.Value = "Responded" || Stage.Value = "Complete", 'Response Date'+31 > Today(), 'Search.Text_1'.Text in 'Sender Email' || 'Search.Text_1'.Text in Title || 'Search.Text_1'.Text in User.Email)

What can I do to ensure that once the data source is >2000 rows, these three screens still show the tasks they're supposed to?

[Rusk · Accepted solution]

Hi @Emily2 

Is the data set growing as items are completed?  You mentioned it seemed unlikely that your app would ever be needed for more than 2k items at a time, so it seems the main issue is that the list continues to grow.  As time passes, the "Completed" items will exceed 2k.

I've addressed a similar use case for several apps by creating an "Archive" SharePoint list to move all my completed items to via Power Automate:

Something similar could work for you.  You can create an automated flow that checks if an item has been completed for over 30 days.  If so, copy it to your new "Archive" SharePoint list by creating the item there.  Then, delete it from your main list (which is driving your app).

In this way you manage your data instead of reworking your app.  Just an idea!  If this won't work for you, please let me know and I'll look at your formulas instead

[Emily2]

Hi @Rusk 

Yes thank you for your answer. I had considered this as an option. It should work, I just wondered if it was the best way. There's a Power BI dashboard that uses the SharePoint list data to report back to management on tasks created / completed by day/month/quarter/year etc. So if I was creating two datasets I would need to combine them for the dashboard to still reflect the information they want to see. I can figure this out.

Aside from this, I wondered if there was an alternative solution that would work that I had overlooked, or already tried but implemented slightly wrong hence why it didn't work. It doesn't seem so, but also, I've only been using the Power Platform since summer last year so thought I'd put the problem out there for opinions from those more experienced than me! I've only got about a month to resolve this so might have to get started on the above solution sooner rather than later. Thanks for validating what I thought I might need to do!

[Rusk]

Hi @Emily2 

For your formulas, as "in" is not delegable, another potential option would be to use "StartsWith".  But this would only return true if your data text string actually starts with your search text string, while "in" returns true if your search text string is found anywhere within your data text.

All open tasks Gallery Items filter:

Filter(TaskData, Status = "Open" && (StartsWith('Sender Email', Text(Search.Text)) || StartsWith(Title, Text(Search.Text)) || StartsWith(User.Email, Text(Search.Text))))

Closed for 30 days:

Filter(TaskData, (Stage.Value = "Responded" || Stage.Value = "Complete"), 'Response Date'+31 > Today(), (StartsWith('Sender Email', Text(Search.Text_1)) || StartsWith(Title, Text(Search.Text_1)) || StartsWith(User.Email, Text(Search.Text_1))))

[Emily2]

Hi @Rusk 

Yes I looked into this too. This would probably work fine for if someone is searching for an email address, but with the "Title" of the task they will almost 100% of the time search for text within the title and not the start of the title. And although I've given them the option of searching by email, I would think the most common way of searching would be by text within the title. Thank you though.

[takolota]

@Emily2 

There is this work-around that allows users to search columns, including multiline columns, well beyond the 2000 delegation limit: https://powerusers.microsoft.com/t5/Community-App-Samples/Delegable-Large-SharePoint-List-App/td-p/2...