In this article we explore we can retrieve the data from the SharePoint List using the REST ApIand bind it to the JQuery DataTable.

We implement in JQuery  DataTable is an excellent plugin tool built on JQuery JavaScript library to build an HTML table with lot of advanced interaction controls like Pagination, Multi-column Filtering, Multi-column ordering, Export to Excel,Hyperlink and Search all Data Table Content etc.

 

Scenario:

In case the where the Users can’t filter list item of particular Column value or not showing the particular item details and lot of data in the list 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 feature like Hyper link, pagination, Multi-column Filtering, Multi-column ordering and Search. We meet the user's expectation to follow the below procedure given below. 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.

 The following JS we use for Multi-Column Filtering functionality:

For this, we need two files; one is HTML file to render the data and another one is JS file to get the data from SharePoint list and map the data table plug-in methods

 

 

 

Create the HTML file:

 

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title></title>

    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css" />

    <script src="https://code.jquery.com/jquery-3.3.1.js" type="text/javascript"></script>

    <script type="text/javascript" src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>

    <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.1/js/dataTables.buttons.min.js"></script>

    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/1.5.1/css/buttons.dataTables.min.css" />

    <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.1/js/buttons.flash.min.js"></script>

    <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.1/js/buttons.html5.min.js"></script>

    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>

    <script type="text/javascript" src="https://cdn.datatables.net/buttons/1.5.1/js/buttons.print.min.js"></script>

    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">

    <link rel="stylesheet" href="https://cdn.datatables.net/1.10.15/css/dataTables.bootstrap.min.css">

    <script src="https://pointerone.sharepoint.com/sites/SPFXDemo/SiteAssets/filterDropDown.js"></script>

 

</head>

<body>

    <table id="table_id" class="display" role="grid" width="100%" cellspacing="0" style="width: 175%;">

        <thead>

            <tr>

                <th>Employee Name</th>

                <th>Location</th>

                <th>Designation</th>

            </tr>

        </thead>

    </table>

</body>

</html>

 

code snippet:

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

 

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 document library (CustomLibrary)and pass this same list to datatables on page load so that the data is available to bind for the filters.

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.

 

   

JavaScript
Edit|Remove
 <script type="text/javascript"> 
 
        var items = ""; 
 
        var itemcollection = ""; 
 
        var TotalItemCount = 0; 
 
        SP.SOD.executeOrDelayUntilScriptLoaded(updateFile, 'SP.js'); 
 
        //Step 1. get total item count of threshold exceed list. 
 
        function GetItemCount(siteurl, ListName) { 
 
            var ItemCount = ''; 
 
            $.ajax({ 
 
                url: siteurl + "/_api/web/lists/GetByTitle('" + ListName + "')/ItemCount", 
 
                method: "GET", 
 
                async: false, 
 
                headers: { "Accept""application/json; odata=verbose" }, 
 
                success: function (data) { 
 
                    ItemCount = data.d.ItemCount; 
 
                }, 
 
                error: function (data) { 
 
                    console.log(data); 
 
                } 
 
            }); 
 
            return ItemCount; 
 
        } 
 
        //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 
 
        function createRestUrl(siteurl, ItemCount, ListName) { 
 
 
 
            if (ItemCount <= 5000{ 
 
                //Item count less than 5000 so we limit it as usual 5000 
 
                var listServiceUrl = siteurl + "/_api/web/lists/GetByTitle('" + ListName + "')/Items?$select=Id,EmployeeName,Location,Designation&$top=5000"; 
 
            } else { 
 
                //Item count more than 5000 so we split it in 1000 item per call 
 
                var listServiceUrl = siteurl + "/_api/web/lists/GetByTitle('" + ListName + "')/Items?$select=Id,EmployeeName,Location,Designation&$top=1000"; 
 
            } 
 
 
 
            processList(listServiceUrl, ItemCount); 
 
        } 
 
 
 
        //Step 3: Rest call to procerss each items of list 
 
        function processList(nextUrl, ItemCount) { 
 
 
 
            var dfd = new $.Deferred(); 
 
 
 
            if (nextUrl == undefined{ 
 
                dfd.resolve(); 
 
                return; 
 
            } 
 
 
 
            //Step 4: Repetative call to getJSONDataFromUrl() to get Ajax Json object. 
 
            getJSONDataFromUrl(nextUrl).done(function (listItems) { 
 
 
 
                TotalItemCount = TotalItemCount + listItems.d.results.length; 
 
 
 
                items = listItems.d.results; 
 
                var next = listItems.d.__next; 
 
 
 
                $.when(processList(next, ItemCount)).done(function () { 
 
 
 
                    dfd.resolve(); 
 
 
 
                }); 
 
                for (var i = 0; i <= items.length; i++) { 
 
                    if (i == 0{ 
 
                        itemcollection = itemcollection + '{"data": [[' + '"' + items[i].EmployeeName + '",' + '"' + items[i].Location + '",' + '"' + items[i].Designation + '",' + '"' + items[i].Id + '"],'; 
 
                    } 
 
                    else if ((items.length > i) && (i != 0)) { 
 
                        if ((items.length > i) && (i != 0)) 
 
                            itemcollection = itemcollection + "[" + '"' + items[i].EmployeeName + '",' + '"' + items[i].Location + '",' + '"' + items[i].Designation + '",' + '"' + items[i].Id + '"],' 
 
                    } 
 
                    else if ((items.length == i) && (i != 0)) { 
 
                        itemcollection = itemcollection.slice(0, -1) + "]}"; 
 
                    } 
 
                } 
 
 
 
            }); 
 
        } 
 
 
 
        //Step 4: Repetative call to getJSONDataFromUrl() to get Ajax Json object. 
 
        function getJSONDataFromUrl(endpoint) { 
 
            return jQuery.ajax({ 
 
                url: endpoint, 
 
                method: "GET", 
 
                async: false, 
 
                headers: { 
 
                    "Accept""application/json; odata=verbose", 
 
                    "Content-Type""application/json; odata=verbose" 
 
                } 
 
            }); 
 
        } 
 
 
 
        function updateFile() { 
 
            var siteurl = _spPageContextInfo.webAbsoluteUrl; 
 
            var ItemCount = GetItemCount(siteurl, 'EmployeeInformation'); 
 
            var clientContext; 
 
            var oWebsite; 
 
            var oList; 
 
            var fileCreateInfo; 
 
            var fileContent; 
 
            clientContext = new SP.ClientContext.get_current(); 
 
            oWebsite = clientContext.get_web(); 
 
            oList = oWebsite.get_lists().getByTitle("CustomLibrary"); 
 
            fileCreateInfo = new SP.FileCreationInformation(); 
 
            fileCreateInfo.set_url("Customfile.txt"); 
 
            fileCreateInfo.set_content(new SP.Base64EncodedByteArray()); 
 
            fileCreateInfo.set_overwrite(true); 
 
            createRestUrl(siteurl, ItemCount, 'EmployeeInformation'); 
 
            //fileContent = JSON.stringify(items); 
 
            fileContent = itemcollection; 
 
            for (var i = 0; i < fileContent.length; i++) { 
 
                fileCreateInfo.get_content().append(fileContent.charCodeAt(i)); 
 
            } 
 
            this.existingFile = oList.get_rootFolder().get_files().add(fileCreateInfo); 
 
            clientContext.load(this.existingFile); 
 
            clientContext.executeQueryAsync(Function.createDelegate(this, successHandler), Function.createDelegate(this, errorHandler)); 
 
 
 
            function successHandler() { 
 
                $('#table_id').DataTable({ 
 
                    // Definition of filter to display 
 
                    ajax: siteurl + "/CustomLibrary/Customfile.txt", 
 
                    dom: 'Bfrtip', 
 
                    buttons: [ 
 
                    'excel'    //Export to excel 
 
                    ], 
 
                    "columnDefs": [{ 
 
                        "targets"0, 
 
                        "render"function (data, type, row) {//Hyper link to Column First 
 
                            if (type === "display"{ 
 
                                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>"; 
 
                            } 
 
                            return data; 
 
                        } 
 
                    } 
 
                    ], 
 
                    filterDropDown: { //Multi-column Filtering 
 
                        columns: [ 
 
                                                { 
 
                                                    idx: 0 
 
                                                }, 
 
                                                { 
 
                                                    idx: 1 
 
                                                }, 
 
                             { 
 
                                 idx: 2 
 
                             }, 
 
                        ], 
 
                        bootstrap: true 
 
                    } 
 
                }); 
 
            } 
 
            function errorHandler() { 
 
                alert("error"); 
 
            } 
 
        } 
 
    </script>
 

 

Final O/P:

 

 

Note: Thanks to Erik Kalkoken for  Extension for the jQuery plug-in DataTables (Filter multi-Column)