Something good to know…
Some time ago, I wrote a blog post (SharePoint Online List View Threshold) on the dreaded list view threshold error many of us face from time-to-time and I demonstrated in that post how you could in fact exceed 5000 items in a grouped view. I even had the screenshot to prove it! Earlier this week, I got the familiar ice-cream cone Something went wrong message in a large library I was working with, so I set out to determine why I was getting the error even though there was a column index defined on the column being grouped on.
At times, I’ve seen any one of these images with the list view threshold error – all mean the same thing as far as I can tell:
My conclusion? It matters what data type the column is that you’re wanting to group and filter by to determine if you’ll receive the list view threshold error.
If you’re short on time, skip down to the Summary section where I list the column types allowing you to exceed 5000 items if you need to group and filter by them.
The Test
I had a document library with just over 6800 documents in it and no folders. The library had 2 pieces of metadata:
- Document type (lookup column)
- Fiscal year (managed metadata column)
Two column indexes were added for each of the above columns (You can add a column index after you exceed 5000 items without issue) and two views were created to group by Document Type and Fiscal Year respectively. Each of the views failed with this familiar exception:
I went back to the views from my previous post where it was working in a library with > 5000 items and as you can see in this image, there were definitely more than 5000 items returned…
I noticed the column I was grouping on in this example was a Choice data type. I immediately wondered if this could make a difference? It turns out… yes!
Summary
If you think you’ll exceed 5000 items in a list/library and you want to group your content in a flat view by one of your columns, you need to ensure the column is one of the following data types and a column index is created for it:
- Choice
- Number
- Yes/No
- Single line of text
However, if you attempt to group/filter by any of the following column types and the view will exceed 5000 items for the group, you will continue to get the list view threshold error even if a column index is created:
- Lookup
- Managed Metadata
- Person
This is important to know particularly for those organizations tagging content with managed terms in large libraries. The managed metadata column type is something I regularly recommend to tag content across a tenant/farm.
The Takeaway? Watch your column types if you think your list/library will exceed 5000 items. As stated in the Microsoft documentation referenced in the Something went wrong error message (Support link from Microsoft and excerpt below), there is reference to the people, lookup, or managed metadata columns (circled in red below) however not specifically listed for the group by setting. I can now confirm they’re to be avoided for sorting/grouping in large lists. 
Thanks for reading.
-JCK
So I gather that a datetime field is fine to index and group on?
Hi Maurice, I didn’t test that one. I’ll try it tomorrow and update the post. I suspect it will work
Thanks. I have a modern list that I set up an index on and a filter on a datetime field thinking that would be fine. And already thinking how to get around that if I have to 😁
Odd. I have a library with 25,000 + files and group by on an indexed managed metadata column works fine except it takes minutes to render the results . . . On the same library, I consistently get a ‘Something went wrong’ error as soon as I change the view to ‘show without folders’ (even though there were no folders). A user friendly workaround is to use search within the large library and then group by on the results, especially as group by and filter are readily available from the column heading in the modern UX. Alternatively, filter on the relevant managed metadata value(s) and then copy the URL to provide a link for end users to ‘view’ the library.
Thanks Keith, great feedback. I couldn’t get MM to work without the error. Agree on the alternatives. This is something that should be communicated to end-users working with lists/libs in general.
-JCK
Hi Joanne, do you know if this applies to SP On Prem, or is it only an issue with Online?
Hi MT, I believe it does, however I haven’t tested it there. I do know in on-premises, you have control over the # items it throttles the query at. (can be set above 5000 but do this with caution!)
-JCK
I indexed using the yes/no field in a modern sharepoint site with no success. Frustrating as we only have 30GB of data but a large amount of small files.
Any conclusion on datetime type? We have REST filter applied on datetitme column and it gives threshold error for result more than 90 records . Datetiem column is indexed. When datetime column is replaced by ID column it works are returns 1000 records too
Hi Rohit, unfortunately I don’t have any insight on this.
This is a test comment.