How to Create List Views for Large Lists in Office 365

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:

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

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?

The Tools

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:

1. Upload your modified script below to your Site Assets library (or some other library)
2. Upload the jQuery.datatables.rowGrouping library to your Site Assets library (or some other library)
3. Create a Web Part Page
4. Add a Content Editor Web Part to the Web Part Page
5. 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.

JSON Light support in REST SharePoint API released

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.

The Video

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.

The Script

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…

<script type="text/javascript" src="//code.jquery.com/jquery-1.11.1.min.js"></script> 

<script type="text/javascript" src="//cdn.datatables.net/1.10.3/js/jquery.dataTables.min.js"></script>
<link  type="text/css" rel="stylesheet" href="//cdn.datatables.net/1.10.3/css/jquery.dataTables.min.css" /> 

<script type="text/javascript" src="../SiteAssets/jquery.dataTables.rowGrouping.js"></script>


Enter State Abbreviation: <input type=text id="stateID" onkeyup="GetZips();" size=5 maxlength="2"> 
<br>

<!-- MAKE SURE THAT YOU SPECIFY THE NAMES OF THE COLUMNS TO DISPLAY IN THE THEAD ROW -->
<h1 id='title'></h1>
<table cellpadding="0" cellspacing="0" border="0" class="display" id="ZipViewTable">
<thead><th>County</th><th>Zip Code</th><th>City</th><th>Timezone</th><th>Area Codes</th><th>Latitude</th><th>Longitude</th><th>Population</th></thead>
</table>


<style type="text/css">

#ZipViewTable
{display:none;}

    .expanded-group{
                background: url("../SiteAssets/minus.jpg") no-repeat scroll left center transparent;
                padding-left: 15px !important;
                font-weight:bold;padding:5px;margin:5px;
            }

            .collapsed-group{
                background: url("../SiteAssets/plus.jpg") no-repeat scroll left center transparent;
                padding-left: 15px !important;
                font-weight:bold;padding:5px;margin:5px;
            }


</style>

<script type="text/javascript">

    function GetZips()
    {
        var state = ($("#stateID").val());
        if (state.length ==2)
        {
        //
        // MODIFY THE REST QUERY TO RETRIEVE THE FIELDS YOU NEED FROM THE YOUR SPECIFIC LIST
        // YOU CAN TEST YOUR REST QUERY IN THE BROWSER URL TO KNOW THE CORRECT FIELD NAMES TO USE
        // AND TO MAKE SURE YOUR FILTERS WORK
        //
        var call = $.ajax({
            url: _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists/GetByTitle('MasterZipCode')/items?$select=Id,Title,zip,primary_city,state,county,area_codes,timezone,latitude,longitude,estimated_population&$filter=state eq '"+state.toUpperCase()+"'&$top=5000",
            type: "GET",
            beforeSend: function(){$("#overlay").show();},
            complete: function(){$("#overlay").hide();},
            dataType: "json",
            headers: {
                Accept: "application/json;odata=minimalmetadata"
            }
       
        });
        call.done(function (data,textStatus, jqXHR){
                $("#ZipViewTable").dataTable({ 
                            "bDestroy": true,
                            "bProcessing": true,
                            "aaData": data.value,
                            //MAKE SURE YOU SPECIFY THE FIELDS YOU WANT TO DISPLAY IN THE LIST VIEW
                            //THE VALUE OF EACH "mData" PROPERTY IS THE FIELD NAME AS IT IS RETURNED
                            //FROM THE REST QUERY.  
                            "aoColumns": [
                                { "mData": "county" },
                                { "mData": "zip" },                    
                                { "mData": "primary_city" },
                                { "mData": "timezone", "searchable": false },                    
                                { "mData": "area_codes" },                    
                                { "mData": "latitude" },                    
                                { "mData": "longitude" },                    
                                { "mData": "estimated_population" }
                            ],
                            "iDisplayLength": 100,
                            "dom": '<"top"iflp<"clear">>rt<"bottom"iflp<"clear">>',
                            "bLengthChange": false,
                            "bProcessing": true,}).rowGrouping({
                                fnGroupLabelFormat: function(label) { return "COUNTY: "+ label + ""; } ,
                                bExpandableGrouping: true});            
                                
            $("#ZipViewTable").show();
            $("#title").html("ZIP CODE INFORMATION FOR " + state.toUpperCase());    

            });
        
        call.fail(function (jqXHR,textStatus,errorThrown){
            alert("Error retrieving Tasks: " + jqXHR.responseText);
        });
        
        }
    }

</script>

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!

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 soundness problems in men turn on improbability to have an erection 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 taking Levitra or other medicament, speak to your druggist if you have any allergies. Talk to your soundness care provider for more details. Preparatory to ordering this remedy, tell your physician if you are allergic to anything.