Using Datatable JS For Showing List/ Table Data With Action (Edit/ Delete) Buttons

What is DataTable JS?

Datatable is a plug-in for the jQuery JavaScript library. It is a highly flexible tool using which we can present our data in a table with features such as pagination, instant search, and multi-column ordering, exporting, etc.

In this blog, I am going to demonstrate how to show list data in a table with action (edit and delete) buttons using Datatables JS. Since it will be based on JavaScript, it can be used over multiple platforms like SharePoint Online, Project Online, ASP.NET Applications, and other platforms like Java, PHP, etc. I hope this will be helpful. Let’s get to the topic.

Required Libraries

First, we need to get the required libraries. We can download these libraries from jQuery CDN, Datatables CDN. To build this, we will be using jQuery 1.12.4 and Datatables 1.10.13. 

  1. <script src="https://code.jquery.com/jquery-1.12.4.min.js"></script>    
  2. <script src="https://cdn.datatables.net/1.10.13/js/jquery.dataTables.min.js"></script>    

Markups (HTML)

The Datatable plugin uses table to render the array/object. Thus we will be using <table> tag for this. 

  1. <div class="conBox">    
  2.     <table id="myTable" width="100%"></table>    
  3. </div>     
The table should have an ID, so that we can manipulate it with data and the Datatables plugin. Assign a desirable width to the table. It could be done with CSS class as well as table width property that is shown above the snippet.

JavaScript Code:

Dataset

We need to prepare the data set for the Datatables as shown below. We have to collect the data from the SP List, Database table, or any other source we are collecting from and keep it in the following JavaScript array format. We can pass this array to Datatables as a dataset or create row dynamically from the array and add it to the table.

Here, I am going to use second approach because we have to add action (edit/delete) to each row so that we can perform some action on it. 

  1. // This is the dataset format. you have to collect all your data from db table or sp list   
  2. // and then put the collected data in following js array format.  
  3. var dataSet = [  
  4.     [ "Tiger Nixon""System Architect""Edinburgh""1" ],  
  5.     [ "Garrett Winters""Accountant""Tokyo""2" ],  
  6.     [ "Ashton Cox""Junior Technical Author""San Francisco""3" ],  
  7.     [ "Cedric Kelly""Senior Javascript Developer""Edinburgh""4" ],  
  8.     [ "Airi Satou""Accountant""Tokyo""5" ],  
  9.     [ "Brielle Williamson""Integration Specialist""New York","6" ],  
  10.     [ "Herrod Chandler""Sales Assistant""San Francisco","7" ],  
  11.     [ "Rhona Davidson""Integration Specialist""Tokyo","8" ],  
  12.     [ "Colleen Hurst""Javascript Developer""San Francisco","9" ],  
  13.     [ "Sonya Frost""Software Engineer""Edinburgh","10" ],  
  14.     [ "Jena Gaines""Office Manager""London","11" ],  
  15.     [ "Quinn Flynn""Support Lead""Edinburgh","12" ],  
  16.     [ "Charde Marshall""Regional Director""San Francisco","13" ],  
  17.     [ "Haley Kennedy""Senior Marketing Designer""London""14" ],  
  18.     [ "Tatyana Fitzpatrick""Regional Director""London","15" ],  
  19.     [ "Michael Silva""Marketing Designer""London""16" ],  
  20.     [ "Paul Byrd""Chief Financial Officer (CFO)""New York""17" ]  
  21. ];  
Create rows dynamically for the table

Here, I have created a <tbody> element. Loop through the array, created row from the array, and bind it to the table. In this, we can dynamically create our rows for the table from JavaScript array/object.

  1. var tblId = document.getElementById("myTable");  
  2. // Create tbody element.  
  3. var bodyOfTable = document.createElement("tbody");    
  4. // create rows from the array.  
  5. var dataRow = '';             
  6. $.each(dataSet,function(index,value){  
  7.     dataRow += '<tr>'+  
  8.             '<td>'+value[0]+'</td>'+  
  9.             '<td>'+value[1]+'</td>'+  
  10.             '<td>'+value[2]+'</td>'+  
  11.             '<td>'+  
  12.                 '<a href="DatatableJSDemo.html?id='+value[3]+'">Edit</a> | '+  
  13.                 '<a href="DatatableJSDemo.html?did='+value[3]+'">Delete</a>'+  
  14.             '</td>'+  
  15.         '</tr>';  
  16. });  
  17. // bind the rows to the table body.  
  18. var addBody = bodyOfTable.innerHTML = dataRow;  
  19. // bind the table body to the table.  
  20. tblId.innerHTML = addBody;  

Render the table

So far, all we have is a table with rows, but to get the advantage of Datatables plugin such as pagination, search and others, we need to pass the table id to Datatables.

  1. $(document).ready(function(){  
  2.     $('#myTable').DataTable({  
  3.         // required for showing number of rows  
  4.         "aLengthMenu": [[8, 16, 30, 50, 75, 100, -1], [8, 16, 30, 50, 75, 100, "All"]],  
  5.         columns: [  
  6.             { title: "Name" },  
  7.             { title: "Position" },  
  8.             { title: "Office" },  
  9.             { title: "Action" }  
  10.         ],  
  11.         // Required for sorting a specific column by it's column index.  
  12.         order: [[ 0, 'asc' ]]                     
  13.     });  
  14. });  

Here, I have called DataTable() in document.ready() method and passed the table id. Datatable is highly configurable, and these reference can be found in the Datatable doumentation.

Result
 
 

I hope, this will help you. Please leave a comment if you face any issue in the above procedure.

For reference, find the attached zip file. It contains working code including CSS (not mentioned in blog to avoid too much content). In my forthcoming blog, I will describe how to use REST API, JSOM in SharePoint / Project Online and create our BI using JS libraries like ChartJS, Datatables JS. So stay tuned...

Happy coding!