shutterstock_184473665.jpg

Summit 7 Team Blogs

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

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 -->
&lt;script type="text/javascript" charset="utf8" src="<a href="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.8.2.min.js">http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.8.2.min.js</a>"&gt;&lt;/script&gt;
&lt;!-- DataTables CSS --&gt;
&lt;link rel="stylesheet" type="text/css" href="<a href="http://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/css/jquery.dataTables.css">http://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/css/jquery.dataTables.css</a>"&gt;
&lt;!-- DataTables --&gt;
&lt;script type="text/javascript" charset="utf8" src="<a href="http://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/jquery.dataTables.min.js">http://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/jquery.dataTables.min.js</a>"&gt;&lt;/script&gt;
 
State: &lt;input type="text" id="State" &gt;
&lt;input type="button" value="Get Zip Codes" onclick="LoadZipCodes($('#State').val());" &gt;
 
&lt;table width="100%" cellpadding="0" cellspacing="0" border="0" class="display" id="example"&gt;
&lt;thead&gt;&lt;th&gt;Zip Code&lt;/th&gt;&lt;th&gt;City&lt;/th&gt;&lt;th&gt;State&lt;/th&gt;&lt;/thead&gt;
&lt;/table&gt;
 
&lt;script type="text/javascript"&gt;
 
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);
});
}
 
&lt;/script&gt;

 

SharePoint 2010

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

var call = $.ajax({
url: "<a href="http://%3curl%20to%20site%3e/_vti_bin/listdata.svc/ZipCodes?$select=ZIP,City,State&amp;$filter=(State%20eq%20'">http://&lt;url to site&gt;/_vti_bin/listdata.svc/ZipCodes?$select=ZIP,City,State&amp;$filter=(State eq '</a>"+state+"')&amp;$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!

SHARE THIS STORY | |