SharePoint 2013 - Showing List Data In jQuery Datatable With Advanced Feature

jQuery DataTable is an excellent plugin tool built on jQuery JavaScript library to build an HTML table with a lot of advanced interaction controls, like Pagination, Multi-column Filtering, Multi-column Ordering, Export to Excel, Hyperlinking, and Searching all data table content, etc.

Scenario

Users can’t filter a list item of particular column value or not showing the particular item details. A lot of data in the list is showing in the pagination format for fast rendering the page with data.

Objective

We overcome the limitation of the SharePoint list view using jQuery table along with advanced features like Hyperlink, pagination, Multi-column Filtering, Multi-column ordering and Search. We meet the user's expectation to follow the below procedure. Now, we move to the functionality part that suggests we need to follow CDN to get the required JS and CSS files that are required to be used in our functionality.

We use the following JS for Multi-Column Filtering functionality.

filterDropDown.js

For this, we need two files - one is an HTML file to render the data and another one is a JS file to get the data from SharePoint list and map the data table plug-in methods.

SharePoint 2013 - Showing List Data In jQuery Datatable With Advanced Feature

Create the HTML file

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css" />
<script src="https://code.jquery.com/jquery-3.3.1.js" type="text/javascript"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.1/js/dataTables.buttons.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/1.5.1/css/buttons.dataTables.min.css" />
<script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.1/js/buttons.flash.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.1/js/buttons.html5.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.1/js/buttons.print.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.15/css/dataTables.bootstrap.min.css">
<script src="https://pointerone.sharepoint.com/sites/SPFXDemo/SiteAssets/filterDropDown.js"></script>
</head>
<body>
<table id="table_id" class="display" role="grid" width="100%" cellspacing="0" style="width: 175%;">
<thead>
<tr>
<th>Employee Name</th>
<th>Location</th>
<th>Designation</th>
</tr>
</thead>
</table>
</body>
</html>

Create a document library (CustomLibrary) and Customfile.txt file.

In the above example, the filters are loaded before the data is bound to HTML. As a result, the filters appear empty or do not appear itself. For avoiding this issue, we are writing data (list items in JSON format) from the list on Page load to a file (Customfile.txt) in the document library (CustomLibrary) and pass this same list to the datatables on page load so that the data is available to bind for filters.

For List Items More than 5000

Create rest service url if item count is above 5000 then we split calling with 1000 item per call top=1000 for better performance. Otherwise we call as usual 5000 items top=5000,

Step 1

From this page, select Site Actions | Edit Page.

Edit the page, go to the "Insert" tab in the ribbon and click the "Web Part" option. In the Web Parts picker area, go to the "Media and Content" category, select the Script Editor Web Part, and press the "Add" button.

Step 2

Once the Web Part is inserted into the page, you will see an "EDIT SNIPPET" link; click it. You can insert HTML and/or JavaScript, as shown below.

<script type="text/javascript">
var items = "";
var itemcollection = "";
var TotalItemCount = 0;
SP.SOD.executeOrDelayUntilScriptLoaded(updateFile, 'SP.js');
//Step 1. get total item count of threshold exceed list.
function GetItemCount(siteurl, ListName) {
var ItemCount = '';
$.ajax({
url: siteurl + "/_api/web/lists/GetByTitle('" + ListName + "')/ItemCount",
method: "GET",
async: false,
headers: { "Accept": "application/json; odata=verbose" },
success: function (data) {
ItemCount = data.d.ItemCount;
},
error: function (data) {
console.log(data);
}
});
return ItemCount;
}
//Step 2. create rest service url if item count is above 5000 then we split calling with 1000 item per call top=1000 for better performance. Otherwise we call as usual 5000 items top=5000
function createRestUrl(siteurl, ItemCount, ListName) {
if (ItemCount <= 5000) {
//Item count less than 5000 so we limit it as usual 5000
var listServiceUrl = siteurl + "/_api/web/lists/GetByTitle('" + ListName + "')/Items?$select=Id,EmployeeName,Location,Designation&$top=5000";
} else {
//Item count more than 5000 so we split it in 1000 item per call
var listServiceUrl = siteurl + "/_api/web/lists/GetByTitle('" + ListName + "')/Items?$select=Id,EmployeeName,Location,Designation&$top=1000";
}
processList(listServiceUrl, ItemCount);
}
//Step 3: Rest call to procerss each items of list
function processList(nextUrl, ItemCount) {
var dfd = new $.Deferred();
if (nextUrl == undefined) {
dfd.resolve();
return;
}
//Step 4: Repetative call to getJSONDataFromUrl() to get Ajax Json object.
getJSONDataFromUrl(nextUrl).done(function (listItems) {
TotalItemCount = TotalItemCount + listItems.d.results.length;
items = listItems.d.results;
var next = listItems.d.__next;
$.when(processList(next, ItemCount)).done(function () {
dfd.resolve();
});
for (var i = 0; i <= items.length; i++) {
if (i == 0) {
itemcollection = itemcollection + '{"data": [[' + '"' + items[i].EmployeeName + '",' + '"' + items[i].Location + '",' + '"' + items[i].Designation + '",' + '"' + items[i].Id + '"],';
}
else if ((items.length > i) && (i != 0)) {
if ((items.length > i) && (i != 0))
itemcollection = itemcollection + "[" + '"' + items[i].EmployeeName + '",' + '"' + items[i].Location + '",' + '"' + items[i].Designation + '",' + '"' + items[i].Id + '"],'
}
else if ((items.length == i) && (i != 0)) {
itemcollection = itemcollection.slice(0, -1) + "]}";
}
}
});
}
//Step 4: Repetative call to getJSONDataFromUrl() to get Ajax Json object.
function getJSONDataFromUrl(endpoint) {
return jQuery.ajax({
url: endpoint,
method: "GET",
async: false,
headers: {
"Accept": "application/json; odata=verbose",
"Content-Type": "application/json; odata=verbose"
}
});
}
function updateFile() {
var siteurl = _spPageContextInfo.webAbsoluteUrl;
var ItemCount = GetItemCount(siteurl, 'EmployeeInformation');
var clientContext;
var oWebsite;
var oList;
var fileCreateInfo;
var fileContent;
clientContext = new SP.ClientContext.get_current();
oWebsite = clientContext.get_web();
oList = oWebsite.get_lists().getByTitle("CustomLibrary");
fileCreateInfo = new SP.FileCreationInformation();
fileCreateInfo.set_url("Customfile.txt");
fileCreateInfo.set_content(new SP.Base64EncodedByteArray());
fileCreateInfo.set_overwrite(true);
createRestUrl(siteurl, ItemCount, 'EmployeeInformation');
//fileContent = JSON.stringify(items);
fileContent = itemcollection;
for (var i = 0; i < fileContent.length; i++) {
fileCreateInfo.get_content().append(fileContent.charCodeAt(i));
}
this.existingFile = oList.get_rootFolder().get_files().add(fileCreateInfo);
clientContext.load(this.existingFile);
clientContext.executeQueryAsync(Function.createDelegate(this, successHandler), Function.createDelegate(this, errorHandler));
function successHandler() {
$('#table_id').DataTable({
// Definition of filter to display
ajax: siteurl + "/CustomLibrary/Customfile.txt",
dom: 'Bfrtip',
buttons: [
'excel' //Export to excel
],
"columnDefs": [{
"targets": 0,
"render": function (data, type, row) {//Hyper link to Column First
if (type === "display") {
return "<a style=\"text-decoration: none; border-bottom: 1px solid #337ab7;\" href=\"https://pointerone.sharepoint.com/sites/SPFXDemo/Lists/EmployeeInformation/DispForm.aspx?ID=" + encodeURIComponent(row[3]) + "\">" + data + "</a>";
}
return data;
}
}
],
filterDropDown: { //Multi-column Filtering
columns: [
{
idx: 0
},
{
idx: 1
},
{
idx: 2
},
],
bootstrap: true
}
});
}
function errorHandler() {
alert("error");
}
}
</script>

Final Output

Note
A special thanks to Erik Kalkoken for providing the etension for the jQuery plug-in DataTables (Filter multi-column)

5 Comments

Join the conversation! Your thoughts help the community grow.

Abigail6y ago
Hi! for whatever reason if i leave the dom: 'Bfrtip', line the table not data loads. if i comment it out, the table loads but without the buttons nor the filters. any idea how to fix this?
shrikrishna Gupta6y ago
At line no 52 : items = listItems.d.results; only add last result set . Instead of this code it should be ( var items = [] $.extend(items,listItems.d.results)
shrikrishna Gupta6y ago
Hi Sagar ..I found this atrticle helpful . I also found a bug in the code placed in the article in step 4 :
Sunil Jogi6y ago
Hi Sagar, this isn't working with threshold exceeded list. returns with warning - invalid JSON response. please advise
Bhushan Band7y ago
Good Article