Who Needs a Data View Web Part? SharePoint REST and DataTables.net

October 17, 2013 Mark Rackley Uncategorized 23

Still sad about that missing design view in SharePoint Designer? Fighting with XSLT got you down??  Well, have I got a blog post for you!

I’ve been using the jQuery library DataTables since 2009. This awesome library has been critical for a few successful projects. We used DataTables to build custom data views in a structured table format, often joining data from multiple SharePoint lists and doing complicated business logic in the process. We were able to use this library in conjunction with SPServices to create some really detailed reports that clients have said “exceeded their expectations”.

All we needed to do was create an array of data in the format that DataTables expected, send it to the DataTables library and viola.. a formatted table was created that allowed for sorting, filtering, paging, and it performed really well on top of that.  The bad part, depending on how many rows of data you need to iterate through, the logic could get really messy and performance could start to suffer.

I never blogged about DataTables before because I really didn’t want to get into all the minutia of creating JavaScript arrays and iterating over lots of data and answering the flood of questions that would likely ensue. Oh yeah… and because I’m lazy. Anyway, just recently I started wondering if I could take advantage of SharePoint’s REST functionality and feed those results directly to DataTables. After all, I had been iterating over rows of XML and creating arrays of data and SharePoint’s REST functionality can return arrays of data? Might be some synergy here???

I started playing, experimenting, and in almost no time I was able to use SharePoint REST in both SharePoint 2010 and 2013 to query a list and send the results directly to DataTables without having to right ANY logic to iterate over the results.

Let’s do the video first this time

I usually end my blog posts with a video showing what I’ve previously explained, but since there is a few moving pieces here, I thought I’d begin the post with the video so you can get a better idea of what we are actually accomplishing.  So, take a look!

image

 

What’s in the video

So, from the video you saw me make a REST call to retrieve SharePoint list items as an array of JSON objects.  I then feed those results to the DataTables library and tell it which fields to display.  That’s pretty much all there is to it.  So, rather than ramble any further, let me give you the script and some important links:

The Scripts

Here’s the scripts for your viewing pleasure:

SharePoint 2013

<!-- jQuery -->
<script type="text/javascript" charset="utf8" 
src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.8.2.min.js"></script>
<!-- DataTables CSS -->
<link rel="stylesheet" type="text/css"
href="http://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/css/jquery.dataTables.css">
<!-- DataTables -->
<script type="text/javascript" charset="utf8"
src="http://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/jquery.dataTables.min.js"></script>

State: <input type="text" id="State" >
<input type="button" value="Get Zip Codes" onclick="LoadZipCodes($('#State').val());" >

<table width="100%" cellpadding="0" cellspacing="0" border="0" class="display" id="example">
<thead><th>Zip Code</th><th>City</th><th>State</th></thead>
</table>


<script type="text/javascript">

function LoadZipCodes(state)
{
        var call = $.ajax({
            url: _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists/GetByTitle('ZipCodes')/items?"+
"$select=Title,Column2,Column3&$filter=(Column3 eq '"+state+"')&$top=5000",
            type: "GET",
            dataType: "json",
            headers: {
                Accept: "application/json;odata=verbose"
            }
       
        });
        call.done(function (data,textStatus, jqXHR){
            $('#example').dataTable({
                "bDestroy": true,
                "bProcessing": true,
                "aaData": data.d.results,
                "aoColumns": [
                    { "mData": "Title" },
                    { "mData": "Column2" },
                    { "mData": "Column3" }
                ]
              });
        });
        call.fail(function (jqXHR,textStatus,errorThrown){
            alert("Error retrieving Tasks: " + jqXHR.responseText);
        });
}

</script>

SharePoint 2010

For SharePoint 2010 the REST api is slightly different.  So replace the ajax call above with the folllwing:

var call = $.ajax({
            url: "http://<url to site>/_vti_bin/listdata.svc/ZipCodes?$select=ZIP,City,State+
"&$filter=(State eq '"+state+"')&$top=5000",
            type: "GET",
            dataType: "json",
            headers: {
                Accept: "application/json;odata=verbose"
            }
       
        });

How about some Links?

DataTables

Be sure to check out the examples and ample documentation. You can even theme DataTables using jQueryUI themes.

Get started with the SharePoint 2013 REST service

Lastly, I reference in the video using $expand for lookup fields in your REST query.  Here is what that REST query looks like for an out of the box Task List if you want to get the “Assigned To” person’s name:

        $.ajax({
            url: _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists/GetByTitle('Tasks')/items?"+
"$select=Title,Status,DueDate,AssignedTo/Title&$expand=AssignedTo&$top=500",
            type: "GET",
            dataType: "json",
            headers: {
                Accept: "application/json;odata=verbose"
        }

And here is Andrew Connell’s brief post on the subject:

Applying Filters to Lookup Fields with the SharePoint 2013 REST API

Some final words about SharePoint REST

The SharePoint REST functionality is pretty impressive in 2013. I HIGHLY recommend you dig into and learn what you can do. Rob Windsor did an awesome Pluralsight course on using REST in SharePoint which is what I used to finally get started. I definitely suggest if you want to get more in depth than I’m doing here that you check out that course:

SharePoint 2013 Development: Client Object Model and REST API

So??? Who Needs a Data View Web Part???

Lots of people… If you aren’t a developer this is not an ideal solution AT ALL!!!  So, for all those people who need Data View Web Parts and miss their Design View in SharePoint Designer, here’s a quick plug for a great tool (I’m all about giving options).

SharePoint 2013 Lightning Conductor Web Part

Hope you learned something! Have a better idea or a better tool?? I’d love to hear about it!

Families in Germany who are facing divers soundness 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 soundness 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 include improbability to have an erection sufficient for sexual functioning. Certain medications may add to sex drive difficulties, so its substantial to cooperate with your soundness 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 health care purveyor for more details. Preparatory to ordering this remedy, tell your physician if you are allergic to anything.

23 Comments

    • Security for the REST api is determined by SharePoint security. A user cannot do something with the REST api that they are not allowed to do in SharePoint.

  2. Hi Mark, thanks for this great article. Datatables is definitelly very usefull and you explain these very well. I do have one question. Is it possible using the REST api to do roll up queries? So for example when you have multiple subsites that all contain a Contacts list. I would like to use the datatables to create a list of contacts from all those lists. Thanks, Eric

    • You could possibly use the REST Search API as a means to roll up content and then display those results in Datatables. Another option if you are using SharePoint 2013 is to use a Display Template and Search Results to display rolled up content in Datatables. Good luck!

  3. Hi this is great!
    I’m having trouble with trying to implement this. I get an error, when I look at the cosole i see this “XMLHttpRequest cannot load site/_api/Web/Lists/GetByTitle(‘Tasks’)/items?$select=Title&$filter=(Title%20eq%20%27FTP%27)&$top=6. No ‘Access-Control-Allow-Origin’ header is present on the requested resource. Origin ‘site’ is therefore not allowed access.” Do you have any suggestions? I’m very new to development :).

  4. Mark, this is so great! I like the ideal and I tested this successfully. I wanted to ask how you managed to display 40,000 zip codes without hitting the 5,000 view limits! We are planning our list to be 120,000 thousand records and, I wanted to have the user use this grid to view and filter quickly as jquery datatable is really fast. Although, as soon as the result is greater than > 5000 I get the error “… list view threshold enforced …”.

  5. Hi,
    Just got this mostly working on Office 365. One problem and one question. It doesn’t seem to be passing the parameter. Like it’s just blank. If I put a literal in the query it works fine. Is there something different for Office 365?

    Also, I’m hunting everywhere for how to combine multiple lists. I’ve got about 6 lists that all have common field where I’d like to get the common records from all lists and combine the results in the datatable. Can you point me to a resource for using REST to query multiple lists.

    Thanks very much for this demo you’ve done. If I can get multiple lists it will be absolutely invaluable.

    Jerry

    • Update,
      Fixed the parameter issue. Critical reading. Anyway, still appreciate a pointer on how to join multiple lists. I’ve seen mention of it but can’t find out how. I’m sure I’ll keep poking.

      Thanks again.

  6. Hey Mark, yes, pretty cool stuff. I have this working for the most part on my site. (using SP2010 REST API) I’m working on a list of 4500 items but the REST API will only pull 1000 items. Even when I put the link directly into a browser (IE11 or Chrome 38). it returns 1000 total items. I am including the $top=5000. Is there a RSS feed limit setting somewhere?

  7. Very nice post!. This has a great potential. I have a question though..
    I have multiple list in sharepoint online with each containing 5000 rows.

    With datatables can we do search in multiple list and can do a union of results and show in a single table. So idea is to search multiple list and show result in single table.

    If anyone can give some idea on how to do this would be great!

    Thank You

    • You COULD, but it would likely perform poorly and I’m not sure I would recommend it. Basically you’ll need to create an array of JSON objects and then iterate through your results for each list query joining them to the right object in the array. I’ve had to do this before and it wasn’t pretty, but it worked.

  8. Hi Mark,

    Thanks for your article, I have one issue I can’t retrieve any data except for Title, it keep showing ([object Object])

    • Use Fiddler to see what the data looks like coming back from the server. Sounds like your other field is a JSON object and you need to go deeper into the results.

  9. Mark, as usual an Excellent post…was successful across one site.

    Am desperate for an embellishment. Can you add code that aggregates data from all lists ( with the same list name) across a single site collection

    Am a huge fan of yours! And, those of us in higher ed are dedicated followers.
    Best,
    Laura

  10. I have the same ask as Laura. Can we use this to do a rollup of all subsites lists of the same name an columns to the parent site?

Comments are closed.