Working Around that Pesky List View Threshold in Office 365 & SharePoint 2013

Isn’t SharePoint great for storing all your information? Tasks, Contacts, Documents, Issues, Customer Information, Invoices, a list of celebrities hacked cell phone pictures, the possibilities are pretty endless.

But what happens when you are cruising along, doing your job, enjoying life and you store that 5,001st item? Yep… things start breaking… and pretty spectacularly…  That list view that you had grouped by a field?

image

Nope…

How about that filtered list view?

image

Dang it!!!

If I see “This view cannot be displayed because it exceeds the list view threshold (5000 items) enforced by the administrator.” one more time…

Oh!  I know.. I’ll just do a flat list view and then select a filter from the view itself:

image

DOH!

hmmmmmmmmmmmmmmmmmm…. okay…  there’s got to be a way around this. I mean, SharePoint can store MILLIONS of items in a list. How in the world can I recommend this platform to a customer if they can’t effectively manipulate more than 5,000 rows of data?

Ah HA! I’m a developer by trade. Developers write code. Let’s take advantage of that awesome SharePoint 2013 REST interface to filter our list. 

image

ARE YOU KIDDING ME? Gah… So frustrating! What in the world are we to do?

Okay, calm down. Rant over…  Maybe if you stopped to read the directions like I didn’t do you wouldn’t have found yourself in this predicament in the first place, but seeing as how you are here you either love my abrasive writing style or you find yourself in a situation where you are trying to work around SharePoint’s List View Threshold. 

What To Do

First of all, you should read the following link and put some planning and forethought into your list architecture:  Manage lists and libraries with many items

Microsoft has a lot of good advice in that article. I’ll break some of it down in my own language below if you don’t want to go and read it all. Still, go read it.

I should point out, if you have On-Premises SharePoint you can beg your Farm Admin with donuts and beer to increase your list view threshold in Central Administration, however, keep in mind there is very good reasons to have a list view threshold as mentioned in the above referenced article that I’m sure you haven’t read yet. Anyway, to change the list view threshold, go into Central Administration under “Application Management.” Click on “Manage web applications”. Then select the Web Application you wish to adjust the list view threshold for and click on the “General Settings” button in the ribbon followed by “Resource Throttling”.

image

image

From here you can set several options concerning List View Thresholds. Again, you do not have the option of overriding the List View Threshold in Office 365 / SharePoint Online. Sorry.

But what do I do if I can’t change the list view threshold?

Excellent question. First of all, stop being so stubborn and go read Microsoft’s guidance about the issue.  From the article I take away the following main points.

Plan ahead

Some people like to punt on this issue and say things like “You shouldn’t be storing more than 5,000 items in a list” or “Just break it up into multiple lists.”. However, for those of us that live in the real world we understand that there are times when you need to store more than 5,000 things! I would argue that unless you can effectively work with more than 5,000 items, you are NOT dealing with a platform that should see the light of day in an enterprise environment.

It is critical to understand the limitations of the list view threshold, these will start to impact you in multiple locations in your site. Just a few common pieces of functionality that will break include:

  • List Views that Group By a field
  • List Views that Total or Sum fields
  • Lookup Fields that use lists with more than 5,000 rows
  • Filtering a list view (unless fields are indexed)
  • Accessing data with the Client Object Model or REST

Probably the biggest obstacle from the list above is if you use a lookup field to a list with more than 5,000 items. In this instance I could see a very valid point being made that you probably shouldn’t be doing a lookup to a list that has so many records. That won’t be very usable to your clients.  Even THEN there are some ways around that issue with a little bit of client side development.

So, plan. Plan your lists and fields. Identify those areas where you may have a need for more than 5,000 items. If it makes sense, place some retention polices around those lists to archive and delete old items. Keep your lists clean of junk. An ounce of of prevention is worth about a ton and a half of cure here folks. If you know you are going to have a list that will contain more than 5,000 items plan for how you will retrieve that data.

Use Indexes

You can index up to 20 fields in your SharePoint Lists in Libraries in SharePoint 2013.  If you have a list that has more than 5,000 items you CANNOT filter (using List Views or Web Services) on fields that have not been indexed.  That REST error I was getting before? I had not indexed the field I was filtering against.

And guess what? You can’t add an index to a field once the list has gone above 5,000 items! Take this into account too during your planning. Index those fields before it becomes an issue.  What if you already have this issue and need to index a field? You have two choices as I see it my friend. You can either delete enough items from your list so that you have fewer than 5,000 items and then index your fields, or create a new list, index those fields, and migrate your content to the new list.

Plan ahead.

After you index your fields, you can then create custom List Views where you filter on those indexed fields. You can even filter on “begins with” which allows you to do some more dynamic views. 

Use Search, Content Types, and Display Templates

I’m a big fan of Search in 2013, and when you throw in Display Templates you can make search results look very similar to a list view. Now, if you know you are going to be stuck with a list of more than 5,000 items create a Content Type for it and use a Search Results Web Part that filters for your Content Type. Then you can style the results with a Display Template and use a Search Refiner Web Part to further filter your results. You can even aggregate data from multiple lists or sites which is pretty cool.

image

Roll your own

If you are have the time and the talent, you can always create you own list views using REST or the Client Object Model along with some JavaScript, Duct Tape, and a third party library or two. You have complete control at this point. Just make sure you index the fields you want to filter on and that your queries are written in such a way as to never return more than 5,000 items or you will have to do some paging and it will perform poorly.

Worst case scenario

Let’s say you have a worst case scenario. You have a list. It has more than 5,000 items. You can’t index any of the fields. How in the world can you find your data easily? Let’s not forget about the new filter box we get on List Views now. So, if you find yourself which such a list, remove all the groups, sums, totals, and filters. Then from the List View you can filter the results by any field using this handy filter box:

image

Ideal? no… Can you get to your data? Yes.

What have we learned here today?

To summarize. The list view threshold of 5,000 items is a pain in the butt. However, you can indeed store WAY more than 5,000 rows in a SharePoint list AND effectively interact with that data.  Just be sure to plan ahead, index your fields, and have a strategy for how to retrieve the content you need. It’s not always easy, but it’s usually possible.

Good luck!!

5 thoughts on “Working Around that Pesky List View Threshold in Office 365 & SharePoint 2013

  1. Pingback: How to Create List Views for Large Lists in Office 365 |

  2. Hello Mark,

    Thanks for putting that together.

    I’m just about to set foot in the big wide world of Sharepoint (2013) and doing a bit of pre-reading. I came across this 5000 threshold and wondered what it was all about.

    Thanks for explaining it all.

    Paul

  3. Mark,

    I don’t know if you ever respond to questions in your forum but I’ll throw one out there here – as far as I can find – has not been answered clearly not been answered clearly and directly anywhere else, even in MS articles that speak to LTV.

    I have circumstances where it may not be possible to create a column index on a list that I am creating before it is populated with about 45K items. (Does that peak your curiosity?) Can one safely ignore/reset the LTV for A & A to allow for creation of a column index? Assuming off-hours what are the possible consequences of attempting this? This is on-prem SP2013.

    • I would definitely try this in a dev environment first, but yes, you can index a field after you in crease your LVT and then roll it back afterwards. Depending on why the list won’t exist before the 45k items do, you could also create the list (I’m assuming you are importing a spreadsheet?), delete the items, index the fields.. then create another list and migrate the content from the newly created list to the one that you indexed… just a thought.. again, without knowing all the details.

Leave a Reply

Your email address will not be published. Required fields are marked *

*