In a previous blog post we talked a little about SharePoint’s Pesky List View Threshold and I threw out some ideas for overcoming those limitations:
In this blog post we’ll take advantage of SharePoint 2013’s awesome REST API and a couple of free (I like free) third party libraries to create some fairly powerful list views for lists that go well above that 5,000 item threshold in Office 365. These same principles can also be applied to your On Premises SharePoint 2013 environment and even your SharePoint 2010 farms.
It’s important to note, that we are NOT retrieving more than 5,000 items at a time in these examples. We are creating dynamic List Views that are otherwise not possible or cumbersome to build as out of the box list views for large lists. If you REALLY wanted to work with more than 5,000 items at once, you COULD using these principles and caching batches of 5,000 items, but I just can’t see that being a good idea.
Got it? Are we on the same page now?
What tools will you need to accomplish this feat? Excellent question. Like I said we’ll be taking advantage of SharePoint 2013’s REST functionality and some free tools. Specifically you will need:
- A List or Document Library to create a list view against where any field you wish to filter against has been indexed (see above blog post for more information about indexed columns)
- The script at the bottom of this page which references a few third party libraries including jQuery and dataTables
- You’ll need to modify the REST Query in the script as well as the column fields for it to work with your particular list.
- The jQuery.datatables.rowGrouping library. There’s not a CDN for this as of the writing of this blog. So, you’ll need to store it somewhere your user has access to (I’ll be using my Site Assets Document Library)
To Implement the List View
The steps to implement the list view are super easy as well. The most difficult task you will have is modifying the script exactly for your needs. I’ll add some comments to the script so you can understand what you need to change. So, assuming your script is written correctly, the steps to implement the List View are as follows:
- Upload your modified script below to your Site Assets library (or some other library)
- Upload the jQuery.datatables.rowGrouping library to your Site Assets library (or some other library)
- Create a Web Part Page
- Add a Content Editor Web Part to the Web Part Page
- Link the Content Editor Web Part to your modified script that you uploaded in step 1.
Ta and Da… that’s all there is to it.
What makes this a plausible solution?
There’s a couple reasons this is a great alternative when it comes to creating list views for large lists (besides the fact you can’t create an out of the box list view and group by any fiend when you list exceeds the threshold).
SharePoint 2013 JSON Light Support
A few months ago Microsoft updated SharePoint 2013’s REST capabilities to work with JSON Light. Basically that means you can get your REST results back with less metadata. This means your large queries will be more efficient.
Rob Windsor also put together a nice video which explains JSON Light in SharePoint in more detail: Video: JSON Light Support in the SharePoint 2013 REST API
Now, instead of making REST queries that return several hundred rows of results we can return a couple thousand rows in just a few seconds. Yes, I realize that a “few seconds” is too slow for some people… but what’s slower? a few seconds or not at all? Perspective…
This acceptable performance level makes it possible for us to query a SharePoint List or Document Library for the data we need and then we can apply those results to a library that will helps us format the results as an effective list view.
DataTables jQuery plug-in
The DataTables jQuery plug-in allows us to take the raw results for our SharePoint 2013 REST query (an array of JSON objects) and create a List View with those results without having to iterate through the returned rows, this greatly improves performance as we don’t need to waste any more time parsing data.
DataTables Row Grouping Add-On
This DataTables Add-On library allows you to quickly and easily add grouping to your DataTables list view. You can even do sub-grouping of your data. This allows you to get back even closer to the List Views that you have grown to love.
Although these “List Views” will never be as easy for a non-technical user to implement, with just a little bit of Client Side Development knowledge and some elbow grease you can add style and click events to make these list views truly interactive.
Here’s a video that helps you understand how the script below works as well as walks you through how to implement it. In addition, I end the video showing you what it looks like to take the script a couple of steps further to make it even more usable for your users. I don’t show you all that code (I have to make a living somehow!). Enjoy.
And here we have the script. You WILL NOT be able to just copy and paste this script in your environment and have it “Just Work” unless you just for some reason have the same Zip Code list I have on my Office 365 site…
There you go… again, this is not a Copy and Paste solution, it’s important to have a basic understanding of SharePoint’s REST and jQuery if you hope to modify it for your needs.
As always, thanks for stopping by and good luck!