SharePoint Online cannot handle views involving more than 5 000 items, especially in Classic mode. Even if it does not have to display them all on the same screen. The view collapses under its own weight, unless you limit the number of items by filtering it down using indexed columns. And when the view does break, the end-users do not see any information and freak out. Ok, this is not new. But we still bump into this issue, so let me discuss it in this post.
I am not the first line of support for end-users, but I do speak to people in the business and they do know how to find me when they experience difficulties in Office 365. Recently, I spoke with a few people in the business, who thought that their document libraries had broken down irrevocably and who started to doubt the entire concept of their SharePoint site. Oh dear… The users got a message telling them that the view they were looking at exceeded the list view threshold of 5.000 items. And they interpreted this message as follows: our document library breaks when we put more than 5 000 items in it. But we have far more documents, so this is not working at all.. Help!!
Overweight view in the Classic experience
Overweight view in the Modern experience
These users have libraries with over 5 000 items, which are structured with document sets (so yes, they are still using the Classic experience). However, some of their most important views ignore the document sets. For example, one of the views displays the key documents from each set, which they need to list as input for some subcontractors. The key question for me was:
Do you want your view to display more than 5 000 items?
Or are you trying to distill a much shorter summary from a large library?
Fortunately, the desired view was much shorter than 5 000 items, so we only had to configure the view properly.
The following tricks helped us to slim down overweight views and get them to work for our end-users:
- Use a filter to narrow down the number of items, instead of the item limit.
Setting the item limit does not help. If you set the view to display, for example, 30 items at the time or display only 30 items, SharePoint still tries to get all of the items in one go, before displaying the first batch. - Filter by indexed columns.
Filtering by columns that have not been indexed does not help either. You need an indexed column: List settings > Columns: Indexed columns. For example, if the column Created has been indexed, you can create a working ‘Recent’ view by not only sorting by Created but additionally filtering Created is larger than [Today] – 365 (or a smaller number, if too many items were created this year). See Add an index to a SharePoint column.
Recent items, filtered to display only items created this year, with the newest items displayed at the top.
- Use a simple index, by one column only. A compound index including a secondary column does not help.
- You should set up the indexed columns while the list or library still has under 5 000 items.
In the early days of SharePoint Online, you were stuck if you had not indexed your filter columns before the list grew beyond 5000 items. These days, SharePoint Online is smarter, although you will still experience less problems if you set up the indices beforehand. - SharePoint Online starts to index a column automatically when you create a view sorting or filtering by that column. For example, if you create a ‘Recent’ view sorted by Created date, that column gets indexed automatically.
This only works if the list settings don’t block it. Stick to the default setting: List settings > Advanced settings > Allow automatic management of indices? = Yes.
And this only works if the list still has less than 20 000 items. So again, it pays to be proactive about these large lists. - You can still index a column manually too, even after the list has become overweight. In my experience, it may take some time for that index to actually appear. Lists that are only slightly overweight can create an index within minutes, but it hasn’t always been that quick.
Indexed columns: Created has been indexed automatically, The others have been indexed manually, after the list became larger than the threshold of 5 000 items
- If you filter by one column AND another column, the first one should already bring the number of items down to under 5 000. You may think that an AND filter is symmetrical, but it is important to put the right filter in first. For example, if there are many final documents but not too many key documents, filter your view ‘Final key documents’ as Key documents = Yes AND Status = Final, and not the other way around.
Filter by indexed columns: first by the one that restricts the number of items the most: Key document = Yes.
And then refining it by Status= Final to achieve the desired view. - Filtering by one column OR another column breaks the view. So don’t filter a view ‘My Documents’ as Modified by [Me] OR Created by [Me], even if there are only a few documents created or modified by each user.
- Is the view still broken? Try to make the view less complex (see Manage large lists and libraries in SharePoint)
- Sort by only one column.
- Don’t sort by “difficult” columns (people, lookup or managed metadata).
- Don’t group.
- Don’t use totals (which currently don’t work in the Modern view anyway).
- Don’t display more than 12 of those “difficult” columns.
Views in the Modern mode of SharePoint are more robust, but they still have their limitations. In the Modern experience, I have seen views with over 7 000 items that worked just fine. But the view with over 70 000 items still broke; 20 000 seems to be the new 5 000. And the Modern ‘All items’ view of my test list of just over 5 000 item is still broken too; maybe I have to wait a bit longer for the view to get its act together and start working…
All in all, large views still need attention, but we do have some tricks to help our end-users.
