-->

Monday, 14 September 2015

Custom DropDown Column Filter in JQuery Datatable


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 

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>';  
Let’s pick up our CustomerJqueryDatatable.txt and add the panel to hold our dropdowns for organization and roles column. You can place this code above our CustomerPanel div.
 <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();  
               });  
Now once we have added the dropdown in our HTML we will get the unique values which is stored in the columns.data()
 //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.
On selecting the filters it will filter the results in DataTable.







Happy SharePointing !