In this article we will show how you can add the custom column filters on the JQuery DataTable which retrieved the data from the SharePoint List using the REST Api.
In my previous article I showed you how to retrieve the data from the SharePoint list using REST api and bind it to the JQuery Datatable.
Articles on the Jquery DataTable and SharePoint REST API
- Load the Data in JQuery DataTable from SharePoint List using REST API
- Custom DropDown Column Filter in JQuery Datatable
- Free Text Search on Column in JQuery Datatable
- Get Data in JQuery DataTable from SharePoint List using $skip, $top, $inlinecount, $orderby parameters
We will use the same customers list we used in my previous article except that we will add two more columns to it viz, ‘Organization’ and ‘Role’. I have assigned some data to these columns for the existing records.
Make sure you add the organization and Role column in the js file to be retrieved from the SharePoint.
tableContent += '<td>' + objArray[i].Organization + '</td>';
tableContent += '<td>' + objArray[i].RolesValue + '</td>';
<div id="filterPnl">
<table style="width:100%">
<tr>
<td style="width:50%;">Organization : <span id="orgDropDown"></span></td>
<td style="width:50%;">Roles : <span id="roleDropdown"></span></td>
</tr>
</table>
</div>
</br /><hr /> </br /> CustomerJqueryDatatable.txt should look something like this now.
<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>
<div id="filterPnl">
<table style="width:100%">
<tr>
<td style="width:50%;">Organization : <span id="orgDropDown"></span></td>
<td style="width:50%;">Roles : <span id="roleDropdown"></span></td>
</tr>
</table>
</div>
</br /><hr /> </br />
<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 in SharePoint Designer. Now here we will use the initComplete function and this.api().columns(column index)of the JQuery DataTable to get the column values for the organization and roles to bind it to our dropdown.
this.api().columns(6) retrieves the column at the 6th index of our DataTable which will be our Organization.
var OrganizationColumn = this.api().columns(6);
var organizationDropDown = $('<select><option value=""></option></select>')
.appendTo($("#orgDropDown"))
.on('change', function() {
var val = $.fn.dataTable.util.escapeRegex(
$(this).val()
);
OrganizationColumn.search(val ? '^' + val + '$' : '', true, false).draw();
});
//get the unique value for the organization column
var orgDrpDwnValues = unique(OrganizationColumn.data());
$(orgDrpDwnValues).each(function(d, j) {
organizationDropDown.append('<option value="' + j + '">' + j + '</option>');
});
Repeat the above two steps again for the Roles column as well.
Your CustomerJqueryDatatable.js file should look like below now.
$(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({
initComplete: function() {
/*
Custom Column Filter for Organization
*/
//the index of the organization column in datatable is 6
var OrganizationColumn = this.api().columns(6);
var organizationDropDown = $('<select><option value=""></option></select>')
.appendTo($("#orgDropDown"))
.on('change', function() {
var val = $.fn.dataTable.util.escapeRegex(
$(this).val()
);
OrganizationColumn.search(val ? '^' + val + '$' : '', true, false).draw();
});
//get the unique value for the organization column
var orgDrpDwnValues = unique(OrganizationColumn.data());
$(orgDrpDwnValues).each(function(d, j) {
organizationDropDown.append('<option value="' + j + '">' + j + '</option>');
});
/*
Custom Column Filter for Role
*/
//the index of the Role column in datatable is 7
var RoleColumn = this.api().columns(7);
var roleDropDown = $('<select><option value=""></option></select>')
.appendTo($("#roleDropdown"))
.on('change', function() {
var val = $.fn.dataTable.util.escapeRegex(
$(this).val()
);
RoleColumn.search(val ? '^' + val + '$' : '', true, false).draw();
});
//get the unique value for the organization column
var RoleDrpDwnValues = unique(RoleColumn.data());
$(RoleDrpDwnValues).each(function(d, j) {
roleDropDown.append('<option value="' + j + '">' + j + '</option>');
});
},
"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>' + '<td>Organization</td>' + '<td>Role</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 += '<td>' + objArray[i].Organization + '</td>';
tableContent += '<td>' + objArray[i].RolesValue + '</td>';
tableContent += '</tr>';
}
return tableContent;
}
function unique(list) {
var result = [];
$.each(list[0], function(i, e) {
if ($.inArray(e, result) == -1) result.push(e);
});
return result;
}
});
Once you save this file and refresh the page where you have added the content editor web part you will be able to see the data loaded in the JQuery DataTable. As you can see it organization and Roles custom filters are added as below.
Happy SharePointing !
No comments:
Post a Comment