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?

Nope…

How about that filtered list view?

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:

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. 

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”.

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.

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:

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!!

Families in Germany who are facing divers health problem, such persons can buy drugs from the Web without prescription. With the market flooded with divers web-sites selling sundry medicaments, purchasing medicines from th WEB is no longer a trouble for common man. Certain medications are used to treat infections caused by dental abscesses. Of course it isn’t all. If you’re concerned about erectile health problem, you probably know about Xenical and Hoodia. Probably each adult knows about Garcinia. (Read more PhentermineXenical). The symptoms of sexual health problems in men switch on incredibility to have an hard-on sufficient for sexual functioning. Certain medications may add to sex drive difficulties, so its essential to cooperate with your heartiness care professional so that the prescription can be tailored to your needs. Preparatory to capture Levitra or other medicament, speak to your dispenser if you have any allergies. Talk to your health care provider for more details. Preparatory to ordering this remedy, tell your doctor if you are allergic to anything.