Introduction:
Today we are going to learn an interesting article of how to use
DataTables.js JQuery 🔗 in SharePoint(2013, 2016, Online - O365).
Since SharePoint 2013 has released, the way how the data is created, updated & displayed has changed a lot - I mean Client Side scripting has become more powerful. Server Side Coding in SharePoint (JSOM - Java Script Object Model) has the following uses or advantages over server side coding which are:
1. Faster data rendering
2. Easy to debug from browser
3. Easy & faster validation response to end users
4. Just add a content editor web part and add the code 😁
5. No IIS reset required
6. Just use client desktop and no SharePoint installation required
7. No Visual Studio (why spent extra money on license?😎)
8. Just use a Editor to write and format the code.
💡 I have recently started using
Microsoft Visual Studio Code. Give a try, you will never use other editors. What a powerful editor!
Download from this link and it is FREE 🔗
Scenario:
What if you were given a task to display a list or a library in a SharePoint page which has 1000's of records in it. Also, it has to load faster, need a free text search, paging features, etc.? But you have no server side access for coding or server deployments allowed 😜
Solution:
Obviously, any SharePoint developer will go for the option SharePoint Java Script Object Model (JSOM). But how to create a table, searchable content, etc.
DataTables.JS is a Table plug-in for JQuery. It is used for display the HTML content as Table.
Let's not wait and create a custom SharePoint list(here named it as Finance Sample Data). Download the data used for this example
here 🔗
This is how the logic/solution for this scenario is implemented:
1. Upload the JQuery, DataTable.JS, CSS, Images required in Site Assets Library(best practice in SharePoint)
2. Create a simple text file in which add the reference to JS, CSS, Images, etc. Then add the DIV tags for creating a section and then add a TABLE tag for displaying the content, then document.ready() function and finally custom JS functions. You can also have HTML file and JS files separately.
3. In document.ready(), call the custom function which will query the data using CAML query (I haven't tried using REST API since there will be an issue when you query 1000 items at a time or when paging is enabled, if you have any thoughts share it in the command), load the JSON data as HTML tags and draw a data table using the data objects.
💡 Learn the basics of CAML query in SharePoint by reading this article:
CAML Query tutorial for SharePoint 🔗
Code for reference:
<!-- JS References -->
<script src="../../SiteAssets/Scripts/jquery-1.12.0.min.js"></script>
<script src="../../SiteAssets/Scripts/jquery.dataTables.min.js"></script>
<!--CSS References-->
<link href="../../SiteAssets/CSS/jquery.dataTables.min.css" rel="stylesheet">
<!--HTML TAGS-->
<div id="divFinanceReportTable">
<h3>
<div>
<p id="FinanceReportTitle"><b>Finance Sample Data</b></p>
</div>
</h3>
<table style="width: 100%;" id="idFinanceReportList" cellspacing="0">
</table>
<div></div>
</div>
<!--Document.ready()-->
<script>
var txtHTMLFinanceReport = "";
var tableFinanceReport;
var allFinanceReportData;
$(document).ready(function () {
//Load Finance Report List on page load
SP.SOD.executeFunc('sp.js', 'SP.ClientContext', loadFinanceReportList);
});
//Load Tools from Tools List
function loadFinanceReportList() {
var currentClientContext = new SP.ClientContext.get_current();
var currentWeb = currentClientContext.get_web();
var financeReportDataList = currentWeb.get_lists().getByTitle('Finance Sample Data');
var camlQuery = new SP.CamlQuery();
var loadFinanceReportQuery = new SP.CamlQuery.createAllItemsQuery();
var query = '<View><Query><OrderBy><FieldRef Name="ID" Ascending="TRUE"/></OrderBy></Query></View>';
loadFinanceReportQuery.set_viewXml(query);
allFinanceReportData = financeReportDataList.getItems(loadFinanceReportQuery);
currentClientContext.load(allFinanceReportData);
currentClientContext.executeQueryAsync(Function.createDelegate(this, this.success), Function.createDelegate(this, this.failed));
}
function success() {
var segment = null;
var country = null;
var product = null;
var unitsSold = null;
var manufacturingPrice = null;
var salesPrice = null;
var grossSale = null;
var sales = null;
var profit = null;
var USCurrency = { style: "currency", currency: "USD" };
txtHTMLFinanceReport = "";
var Header = "<thead>" +
"<tr>" +
"<th>Segment</th>" +
"<th>Country</th>" +
"<th>Product</th>" +
"<th>Units Sold</th>" +
"<th>Manufacturing Price</th>" +
"<th>Sales Price</th>" +
"<th>Gross Sale</th>" +
"<th>Sales</th>" +
"<th>Profit</th>" +
"</tr>" +
"</thead>";
txtHTMLFinanceReport += Header;
txtHTMLFinanceReport += "<tbody class='row-border hover order-column dataTable' role='grid'>";
var listEnumerator = allFinanceReportData.getEnumerator();
while (listEnumerator.moveNext()) {
var currentItem = listEnumerator.get_current();
if (currentItem.get_item('Title') != null) {
segment = currentItem.get_item('Title');
} else {
segment = "";
}
if (currentItem.get_item('Country') != null) {
country = currentItem.get_item('Country');
} else {
country = "";
}
if (currentItem.get_item('Product') != null) {
product = currentItem.get_item('Product');
} else {
product = "";
}
if (currentItem.get_item('Units_x0020_Sold') != null) {
unitsSold = currentItem.get_item('Units_x0020_Sold');
} else {
unitsSold = "";
}
if (currentItem.get_item('Manufacturing_x0020_Price') != null) {
manufacturingPrice = currentItem.get_item('Manufacturing_x0020_Price');
manufacturingPrice = manufacturingPrice.toLocaleString('en-US', USCurrency);
}
else {
manufacturingPrice = "";
}
if (currentItem.get_item('Sale_x0020_Price') != null) {
salesPrice = currentItem.get_item('Sale_x0020_Price');
salesPrice = salesPrice.toLocaleString('en-US', USCurrency);
} else {
salesPrice = "";
}
if (currentItem.get_item('Gross_x0020_Sales') != null) {
grossSale = currentItem.get_item('Gross_x0020_Sales');
grossSale = grossSale.toLocaleString('en-US', USCurrency);
} else {
grossSale = "";
}
if (currentItem.get_item('_x0020_Sales') != null) {
sales = currentItem.get_item('_x0020_Sales');
sales = sales.toLocaleString('en-US', USCurrency);
} else {
sales = "";
}
if (currentItem.get_item('Profit') != null) {
profit = currentItem.get_item('Profit');
profit = profit.toLocaleString('en-US', USCurrency);
} else {
profit = "";
}
txtHTMLFinanceReport += "<tr>" +
"<TD>" + segment + "</TD>" +
"<TD>" + country + "</TD>" +
"<TD>" + product + "</TD>" +
"<TD>" + unitsSold + "</TD>" +
"<TD>" + manufacturingPrice + "</TD>" +
"<TD>" + salesPrice.toLocaleString() + "</TD>" +
"<TD>" + grossSale + "</TD>" +
"<TD>" + sales + "</TD>" +
"<TD>" + profit + "</TD>";
txtHTMLFinanceReport += "</tr>";
}
txtHTMLFinanceReport += "</tbody>";
//Bind the HTML data to the Table
$("#idFinanceReportList").append(txtHTMLFinanceReport);
tableFinanceReport = $('#idFinanceReportList').DataTable(
{
"columnDefs": [
{ "targets": [0], "visible": true, "width": "15%" },
{ "targets": [1], "visible": true, "width": "8%" },
{ "targets": [2], "visible": true, "width": "8%" },
{ "targets": [3], "visible": true, "width": "8%" },
{ "targets": [4], "visible": true, "width": "8%" },
{ "targets": [5], "visible": true, "width": "8%" },
{ "targets": [6], "visible": true, "width": "8%" },
{ "targets": [7], "visible": true, "width": "8%" },
{ "targets": [8], "visible": true, "width": "8%" }
]
}
);
tableFinanceReport.draw();
}
function failed(sender, args) {
alert("Data Reterival Failed: " + args.get_message());
}
</script>
4. Now upload the text file either in a Document Library or preferably in Site Assets. Create a SharePoint page and then add a Content Editor web part. Refer the text file in the Content Editor web part and save it. That's it, we will see a very nice Table rendering the data as shown below:
5. The DataTables.JS has inbuilt functionalities which are listed below:
- Free text search option (very fast)
- Abililty of change the number of records display (25, 50, 100..)
- Paging with Previous and Next options
- Displays the number of items displayed and also changes the number according to paging
- Ability to sort the columns both ascending & descending
- Ability to query a column but hide it from view - if that specific column is required for internal data rendering logic or comparison or etc.
Cool, isn't it?
Download the Custom List Template with contents (.STP file), JS files, images, text file which has the entire code from this link.🔗
Please free to comment. Always. your comments help me to write more.😃 Share this post to others if this helps you!😇
Don't forget to subscribe to the posts to get notified about new tutorials by clicking the Subscribe button on the top right corner and follow us in social buttons as well.
Happy Shar(Point)ing!
Update 25/07/2017
After reading our blog post, one of our readers shared
Angular JS code for implementing the same concept in our
Facebook page. Here is the code for reference as a bonus to the readers 😊
////....ANGULAR JS CODE....../////
angular.module('SPapp',['datatables'])
.controller("Splist",function($scope,$http,DTOptionsBuilder){
$http(
{
method:"GET",
url: GetsiteUrl() +"/_api/web/lists/getbytitle('Test')/items?$Select=Title,Job_x0020_Number,Approvl,Username/FirstName&$expand=Username/Id",
headers: {
"Accept": "application/json;odata=verbose"}
}
).success(function(data,status,headers,config){
$scope.listdata = data.d.results;
}).error(function(data,status,headers,config){
});
$scope.dtOptions = DTOptionsBuilder.newOptions()
.withDisplayLength(10)
.withOption('bLengthChange', false);
});
function GetsiteUrl(){
var urlparts = document.location.href.split("/");
return urlparts[1] + "//" + urlparts[2] + "/" + urlparts[3];
}
////....END of ANGULAR JS CODE - SAVED as spapp.js....../////
////....SharePoint Code saved as Text File....../////
<!-- JS References -->
<script src="../../SiteAssets/js/jquery-1.12.0.js"></script>
<script src="../../SiteAssets/js/jquery.dataTables.min.js"></script>
<script src="../../SiteAssets/js/angular.min.js"></script>
<script src="../../SiteAssets/js/angular-datatables.min.js"></script>
<link href="../../SiteAssets/js/jquery.dataTables.min.css" rel="stylesheet">
<script src="../../SiteAssets/js/spapp.js"></script>
<!--HTML TAGS-->
<div ng-app="SPapp" ng-controller="Splist">
<table datatable="ng" dt-options="dtOptions" id="myTable" class="table table-striped table-bordered" cellspacing="0" width="100%">
<thead>
<tr>
<th>Title</th>
<th>Username</th>
<th>Approvl</th>
<th>Job Number</th>
</tr>
</thead>
<tfoot>
<tr>
<th>Title</th>
<th>Username</th>
<th>Approvl</th>
<th>Job Number</th>
</tr>
</tfoot>
<tbody>
<tr ng-repeat="listdatas in listdata">
<td>{{listdatas.Title}}</td>
<td>{{listdatas.Username.FirstName}}</td>
<td>{{listdatas.Approvl}}</td>
<td>{{listdatas.Job_x0020_Number}}</td>
</tr>
</tbody>
</table>
</div>
////....SharePoint Code....../////
