Post
Ask Question

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.

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

  1. <html xmlns="http://www.w3.org/1999/xhtml">  
  2. <head runat="server">  
  3.     <title></title>  
  4.     <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css" />  
  5.     <script src="https://code.jquery.com/jquery-3.3.1.js" type="text/javascript"></script>  
  6.     <script type="text/javascript" src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>  
  7.     <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.1/js/dataTables.buttons.min.js"></script>  
  8.     <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/1.5.1/css/buttons.dataTables.min.css" />  
  9.     <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.1/js/buttons.flash.min.js"></script>  
  10.     <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.1/js/buttons.html5.min.js"></script>  
  11.     <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>  
  12.     <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.1/js/buttons.print.min.js"></script>  
  13.     <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">  
  14.     <link rel="stylesheet" href="https://cdn.datatables.net/1.10.15/css/dataTables.bootstrap.min.css">  
  15.     <script src="https://pointerone.sharepoint.com/sites/SPFXDemo/SiteAssets/filterDropDown.js"></script>  
  16.   
  17. </head>  
  18. <body>  
  19.     <table id="table_id" class="display" role="grid" width="100%" cellspacing="0" style="width: 175%;">  
  20.         <thead>  
  21.             <tr>  
  22.                 <th>Employee Name</th>  
  23.                 <th>Location</th>  
  24.                 <th>Designation</th>  
  25.             </tr>  
  26.         </thead>  
  27.     </table>  
  28. </body>  
  29. </html>  

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

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

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,
 
SharePoint 2013 - Showing List Data In jQuery Datatable With Advanced Feature

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.

  1. <script type="text/javascript">  
  2.     var items = "";  
  3.     var itemcollection = "";  
  4.     var TotalItemCount = 0;  
  5.     SP.SOD.executeOrDelayUntilScriptLoaded(updateFile, 'SP.js');  
  6.     //Step 1. get total item count of threshold exceed list.  
  7.     function GetItemCount(siteurl, ListName) {  
  8.         var ItemCount = '';  
  9.         $.ajax({  
  10.             url: siteurl + "/_api/web/lists/GetByTitle('" + ListName + "')/ItemCount",  
  11.             method: "GET",  
  12.             async: false,  
  13.             headers: { "Accept": "application/json; odata=verbose" },  
  14.             success: function (data) {  
  15.                 ItemCount = data.d.ItemCount;  
  16.             },  
  17.             error: function (data) {  
  18.                 console.log(data);  
  19.             }  
  20.         });  
  21.         return ItemCount;  
  22.     }  
  23.     //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  
  24.     function createRestUrl(siteurl, ItemCount, ListName) {  
  25.   
  26.         if (ItemCount <= 5000) {  
  27.             //Item count less than 5000 so we limit it as usual 5000  
  28.             var listServiceUrl = siteurl + "/_api/web/lists/GetByTitle('" + ListName + "')/Items?$select=Id,EmployeeName,Location,Designation&$top=5000";  
  29.         } else {  
  30.             //Item count more than 5000 so we split it in 1000 item per call  
  31.             var listServiceUrl = siteurl + "/_api/web/lists/GetByTitle('" + ListName + "')/Items?$select=Id,EmployeeName,Location,Designation&$top=1000";  
  32.         }  
  33.   
  34.         processList(listServiceUrl, ItemCount);  
  35.     }  
  36.   
  37.     //Step 3: Rest call to procerss each items of list  
  38.     function processList(nextUrl, ItemCount) {  
  39.   
  40.         var dfd = new $.Deferred();  
  41.   
  42.         if (nextUrl == undefined) {  
  43.             dfd.resolve();  
  44.             return;  
  45.         }  
  46.   
  47.         //Step 4: Repetative call to getJSONDataFromUrl() to get Ajax Json object.  
  48.         getJSONDataFromUrl(nextUrl).done(function (listItems) {  
  49.   
  50.             TotalItemCount = TotalItemCount + listItems.d.results.length;  
  51.   
  52.             items = listItems.d.results;  
  53.             var next = listItems.d.__next;  
  54.   
  55.             $.when(processList(next, ItemCount)).done(function () {  
  56.   
  57.                 dfd.resolve();  
  58.   
  59.             });  
  60.             for (var i = 0; i <= items.length; i++) {  
  61.                 if (i == 0) {  
  62.                     itemcollection = itemcollection + '{"data": [[' + '"' + items[i].EmployeeName + '",' + '"' + items[i].Location + '",' + '"' + items[i].Designation + '",' + '"' + items[i].Id + '"],';  
  63.                 }  
  64.                 else if ((items.length > i) && (i != 0)) {  
  65.                     if ((items.length > i) && (i != 0))  
  66.                         itemcollection = itemcollection + "[" + '"' + items[i].EmployeeName + '",' + '"' + items[i].Location + '",' + '"' + items[i].Designation + '",' + '"' + items[i].Id + '"],'  
  67.                 }  
  68.                 else if ((items.length == i) && (i != 0)) {  
  69.                     itemcollection = itemcollection.slice(0, -1) + "]}";  
  70.                 }  
  71.             }  
  72.   
  73.         });  
  74.     }  
  75.   
  76.     //Step 4: Repetative call to getJSONDataFromUrl() to get Ajax Json object.  
  77.     function getJSONDataFromUrl(endpoint) {  
  78.         return jQuery.ajax({  
  79.             url: endpoint,  
  80.             method: "GET",  
  81.             async: false,  
  82.             headers: {  
  83.                 "Accept": "application/json; odata=verbose",  
  84.                 "Content-Type": "application/json; odata=verbose"  
  85.             }  
  86.         });  
  87.     }  
  88.   
  89.     function updateFile() {  
  90.         var siteurl = _spPageContextInfo.webAbsoluteUrl;  
  91.         var ItemCount = GetItemCount(siteurl, 'EmployeeInformation');  
  92.         var clientContext;  
  93.         var oWebsite;  
  94.         var oList;  
  95.         var fileCreateInfo;  
  96.         var fileContent;  
  97.         clientContext = new SP.ClientContext.get_current();  
  98.         oWebsite = clientContext.get_web();  
  99.         oList = oWebsite.get_lists().getByTitle("CustomLibrary");  
  100.         fileCreateInfo = new SP.FileCreationInformation();  
  101.         fileCreateInfo.set_url("Customfile.txt");  
  102.         fileCreateInfo.set_content(new SP.Base64EncodedByteArray());  
  103.         fileCreateInfo.set_overwrite(true);  
  104.         createRestUrl(siteurl, ItemCount, 'EmployeeInformation');  
  105.         //fileContent = JSON.stringify(items);  
  106.         fileContent = itemcollection;  
  107.         for (var i = 0; i < fileContent.length; i++) {  
  108.             fileCreateInfo.get_content().append(fileContent.charCodeAt(i));  
  109.         }  
  110.         this.existingFile = oList.get_rootFolder().get_files().add(fileCreateInfo);  
  111.         clientContext.load(this.existingFile);  
  112.         clientContext.executeQueryAsync(Function.createDelegate(this, successHandler), Function.createDelegate(this, errorHandler));  
  113.   
  114.         function successHandler() {  
  115.             $('#table_id').DataTable({  
  116.                 // Definition of filter to display    
  117.                 ajax: siteurl + "/CustomLibrary/Customfile.txt",  
  118.                 dom: 'Bfrtip',  
  119.                 buttons: [  
  120.                 'excel'    //Export to excel  
  121.                 ],  
  122.                 "columnDefs": [{  
  123.                     "targets": 0,  
  124.                     "render": function (data, type, row) {//Hyper link to Column First  
  125.                         if (type === "display") {  
  126.                             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>";  
  127.                         }  
  128.                         return data;  
  129.                     }  
  130.                 }  
  131.                 ],  
  132.                 filterDropDown: { //Multi-column Filtering  
  133.                     columns: [  
  134.             {  
  135.                 idx: 0  
  136.             },  
  137.             {  
  138.                 idx: 1  
  139.             },  
  140.                          {  
  141.                              idx: 2  
  142.                          },  
  143.                     ],  
  144.                     bootstrap: true  
  145.                 }  
  146.             });  
  147.         }  
  148.         function errorHandler() {  
  149.             alert("error");  
  150.         }  
  151.     }  
  152. </script>  

Final Output

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

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


Similar Articles