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

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

Posted by on Thursday, October 17th, 2013  

 

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 voila.. 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 are 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!

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 field 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 are 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 following:

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!

Subscribe to RSS Feed

Sign Up for Newsletter

14

14

comments

    Oct 19
    2013

    Jack Fruh

    Mark, this is awesome!

    Oct 20
    2013

    russgove

    Hey Mark,
    I’ve been using datatables, jsrender, knockout, etc to render SharePoint lists but haven’t figured out how to render the edit control block(the dropdown menu for list items). Have you figured that out?
    Russell

      Nov 12
      2013

      Mark.Rackley

      I’ve not looked into that Russell, I don’t believe the edit control block is returned by the normal REST query, not sure if there is a way to retrieve it with REST or CSOM. If this is absolutely critical you might consider screen scraping a list view web part.

    Oct 31
    2013

    Matt Youngstrom

    Mark – Thanks for this! I’ve used DataTables in previous versions of SharePoint – but this really helps in getting a feeling for how it works in SharePoint 2013. Well done!

    Nov 05
    2013

    JeroenS

    Thank you so much, this is great information !!

    Tip: when using this from O365 / SP Online, I ran into trouble using the CDN links, I had to download the .js files to the local SharePoint in order to get it working.

    Thanks again!
    Jeroen

      Nov 12
      2013

      Mark.Rackley

      You should be able to use the CDN links just fine in O365 / SP Online plan.. that how I do most of my testing.

    Nov 05
    2013

    Brett Lonsdale

    Hey Mark. Thanks for the mention in the post!! Just an FYI, as well as the Lightning Conductor Web Part, we also have a Data Viewer Web Part that connects to BCS, Oracle, SQL, OData, SharePoint Lists and more. All the conditional formatting etc is done in the browser with no code. http://lightningtools.com/products/data-viewer-web-part/ Brett

    Nov 08
    2013

    Ali

    Mark,

    Great work – I have used Datatables.net for presentation when developing custom web parts, utilizing it with SharePoint REST API is an awesome idea.

    I have a News Archive with a CQWP that I want to replace with your logic, the only downside I see with this approach is loading all the items from the Pages library. Is there a way that instead of bringing in all the items (in my case its 1500 plus) at once, can we not perhaps bring results at the time the user clicks Next in pagination. Or for that matter load the page with perhaps first 100 items and then load the remaining on the basis of the number the user selects from the dropdown option of selecting how many pages we’d like to view?

    If you could provide us with some REST/JQuery code to achieve that it would be great.

      Nov 12
      2013

      Mark.Rackley

      DataTables does have event handling for paging and you can do paging in REST as well using the Skip parameter. I have not implemented this yet, but take a look at the DataTables site as well as some of the REST documentation at http://msdn.microsoft.com/en-us/library/fp142385.aspx to get started.

    Nov 08
    2013

    Ali

    Also anyone using SP 2010 and Fiddler, JSON objects for some reason do not show in Fiddler. Try pasting the REST API in the browser and investigate XML between each that will give you mData value

    I am referencing this method –

    call.done(function (data,textStatus, jqXHR){
    $(‘#example’).dataTable({
    “bDestroy”: true,
    “bProcessing”: true,
    “aaData”: data.d.results,
    “aoColumns”: [
    { “mData”: “Title” },
    { “mData”: “Column2” },
    { “mData”: “Column3” }
    ]
    });
    });

      Nov 12
      2013

      Mark.Rackley

      You won’t see the JSON object in Fiddler if you are just putting the URL in the browser, it returns XML not JSON.

    Jan 06
    2014

    Scott

    I am using SharePoint 2010 and have successfully made the above example work and then progressed to my own list. When i view the results for any person or look up values from other lists in the site i get the result [object Object] is there any to show the values from the lists?

    A006 [object Object] [object Object] [object Object]
    A007 [object Object] [object Object] [object Object]
    A015 [object Object] [object Object] [object Object]
    A022 [object Object] [object Object] [object Object]
    A026 [object Object] [object Object] [object Object]
    A027 [object Object] [object Object] [object Object]
    A028 [object Object] [object Object] [object Object]
    A031 [object Object] [object Object] [object Object]
    A036 [object Object] [object Object] [object Object]
    A044 [object Object] [object Object] [object Object]

    The code being used is

    Service:

    TitleOriginatorStorageSizeStatus

    function LoadRecords(service)
    {
    var call = $.ajax({
    url: “http://sccintranet.stoke.gov.uk/section/PaperRecordsCentre/_vti_bin/listdata.svc/ItemDetails?$select=Title,ItemOriginator,StorageSize,Status&$filter=(Team eq ‘”+service+”‘)&$top=5000”,
    type: “GET”,
    dataType: “json”,
    headers: {
    Accept: “application/json;odata=verbose”
    }
    });
    call.done(function (data,textStatus, jqXHR){
    $(‘#ItemDetails’).dataTable({
    “bDestroy”: true,
    “bProcessing”: true,
    “aaData”: data.d.results,
    “aoColumns”: [
    { “mData”: “Title” },
    { “mData”: “ItemOriginator” },
    { “mData”: “StorageSize” },
    { “mData”: “Status” }
    ]
    });
    });
    call.fail(function (jqXHR,textStatus,errorThrown){
    alert(“Error retrieving Tasks: ” + jqXHR.responseText);
    });
    }

      Jan 09
      2014

      Mark.Rackley

      You must use the expand parameter for lookups and person fields. I briefly explain this in the blog post as well as give a link to Andrew Connell’s blog for more information on the subject.
      Good luck!
      Mark