Load the Data in JQuery DataTable from SharePoint 2013 List using REST API

InfoPath alternatives for form designing SharePoint

In this article I will show how you can retrieve the data from the SharePoint List using the REST Api and bind it to the JQuery DataTable.

JQuery DataTable is an excellent plugin tool built on JQuery JavaScript library to build an HTML table with lot of advanced interaction controls like pagination, sorting, searching, etc.

You can download the js file for the data table from here.

For the purpose of the demo, I have created a customer list with the below columns and loaded it with some dummy data.

JQuery DataTable SharePoint 2013 List using REST API

JQuery DataTable SharePoint 2013 List using REST APIRelated:
– Creating and Configuring Machine Translation Service in SharePoint 2013

– Query Rules in SharePoint 2013

– SharePoint 2013 Crawls Full crawl Incremental crawl and Continuous crawl

First of all we will create 2 files viz CustomerJqueryDatatable.js and CustomerJqueryDatatable.txt files and place it under the Site Assets Library. Also make sure you add the jquery js file in your SiteAssets/js folder.

Add the content editor web part on your page and give the path of the CustomerJqueryDatatable.txt file from the Site Assets Library and Save/Publish the Page.

Open the CustomerJqueryDatatable.txt file in SharePoint Designer and add the reference to the “jquery-1.11.0.min.js”, “jquery.dataTables.min.js” ,”jquery.dataTables.min.cs” and “CustomerJqueryDatatable.js” file in the CustomerJqueryDatatable.txt file.

<script type=”text/javascript” src=”../SiteAssets/js/jquery-1.11.0.min.js”></script>
<script type=”text/javascript” src=”https://cdn.datatables.net/1.10.9/js/jquery.dataTables.min.js”></script>
<link href=”https://cdn.datatables.net/1.10.9/css/jquery.dataTables.min.css” rel=”Stylesheet” type=”text/css” />
<script type=”text/javascript” src=”../SiteAssets/CustomerJqueryDatatable.js”></script>

Add the below HTML content in the CustomerJqueryDatatable.txt file. This will act as a placeholder for your jquery datatable
<div id=”CustomerPanel”>
<table style=”width: 100%;”>
<tr>
<td>
<div id=”CustomerGrid” style=”width: 100%”></div>
</td>
</tr>
</table>
</div>

Open the CustomerJqueryDatatable.js file and add the below code

$(document).ready(function() {
var RestUrl = “../_vti_bin/listdata.svc/Customers”;
$.ajax({
url: RestUrl,
method: “GET”,
headers: {
“accept”: “application/json;odata=verbose”,
},
success: function(data) {
if (data.d.results.length > 0) {
//construct HTML Table from the JSON Data
$(‘#CustomerGrid’).append(GenerateTableFromJson(data.d.results));
//Bind the HTML data with Jquery DataTable
var oTable = $(‘#CustomerRecordsTable’).dataTable({
“iDisplayLength”: 5,
“aLengthMenu”: [
[5, 10, 30, 50],
[5, 10, 30, 50]
],
“sPaginationType”: “full_numbers”
});
} else {
$(‘#CustomerGrid’).append(“<span>No Customer Records Found.</span>”);
}
},
error: function(data) {
$(‘#CustomerGrid’).append(“<span>Error Retreiving Customer Records. Error : ” + JSON.stringify(data) + “</span>”);
}
});
function GenerateTableFromJson(objArray) {
var tableContent = ‘<table id=”CustomerRecordsTable” style=”width:100%”><thead><tr><td> ?</td>’ + ‘<td>Customer Name</td>’ + ‘<td>Address</td>’ + ‘<td>Home Phone</td>’ + ‘<td>Mobile Number</td>’ + ‘<td>Email</td>’ + ‘</tr></thead><tbody>’;
for (var i = 0; i < objArray.length; i++) {
tableContent += ‘<tr>’;
tableContent += ‘<td>’ + objArray[i].Id + ‘</td>’;
tableContent += ‘<td>’ + objArray[i].CustomerName + ‘</td>’;
tableContent += ‘<td>’ + objArray[i].Address + ‘</td>’;
tableContent += ‘<td>’ + objArray[i].HomePhone + ‘</td>’;
tableContent += ‘<td>’ + objArray[i].MobileNumber + ‘</td>’;
tableContent += ‘<td>’ + objArray[i].Email + ‘</td>’;
tableContent += ‘</tr>’;
}
return tableContent;
}
});

Once you save this file and refresh the page where you have added the content editor webpart you will be able to see the data loaded in the JQuery Datatable. As you can see it provides the inbuilt functionality for the pagination, search, filtering and sorting without any additional code.

 Load the Data in JQuery DataTable from SharePoint 2013 List using REST API

Load the Data in JQuery DataTable from SharePoint 2013 List using REST APICheck out more articles in authors web site here.

Similar SharePoint 2013 Tutorials


Leave a Reply