Sorting SPO lists with large number of items

Question

Hi, I do not know how I am having trouble.

I have a list that is tracking employee time entries. Currently it has 18000 items. In the new year we are going to append another 25k.

The problem is I have tried to sort the list using the column headings and I get an error message. Doesn't matter what column, date, name or other. Same thing happens when we try to group by employee.

I have only successfully made one sorted view. It is Employee Name = [Me].

If I try to sort using the list view I get an error. If I try to filter using the list view I get an error. This is the same for everyone within the agency.

The list is connected to a Power Automate Flow and has calculated columns.

If we do not get the error above, we get this view. This list is not Brand New in anyway.

Have a missed something? I have heard about the 5000 item threshold, but this is unreasonable. The only way to fix these errors is what is listed below.

Remove both sort and filter. The users need to be able to sort by Employee Name easily and the preference would be for the list view to group by employee.

Thanks for your help.
Tammy

Answer 1

Dear Tammy,

Greetings! Thank you for posting in Microsoft forum.

Behind the scenes, SharePoint is querying data from a database. It, like all systems, can do but so much at a time, and the Item Limit Threshold is that limit of items that are displayed in a given view.

If you've operated sites with SharePoint Lists or Libraries for any amount of time, you or one oof the other users will trigger the Item Limit Threshold in a List or Library. Either they've published a 300,000 row Excel spreadsheet as a new List, or they have uploaded the entire network drive's contents to a single Library. Views break. Sorting and filtering fall apart. Users report broken sites and missing data.

As a Site Owner, please note that when the threshold is exceeded, it's a problem with presenting the View and not the List/Library contents. All the data is still there, it just can't be displayed.    

For the detailed information on threshold, please see Living Large with Large Lists and Large Libraries | Microsoft Learn

There are a few things that you can do to prevent the error:

1. Add indexes
2. Remove Sorting, Grouping.

Note*: Displaying 12 or more columns of the following types can cause a list view threshold error: people, lookup, and managed metadata. Displaying columns of other types will not.*

To know how to manage a large list, see Manage large lists and libraries - Microsoft Support

If my understanding above is not consistent with yours, you can also post back and point that out.

Your understanding and patience will be highly appreciated. I hope that you are keeping safe and well!

Sincerely,

Neha | Microsoft Community Moderator

Answer 2

Thank you for replying. The good news is our Sharepoint lists are connected to Access, so the team has been told to tell the users that to view this data they must use Access.

I am just surprised that SPO has a harder time than SPP had.

Tammy