-->

Tuesday, 15 September 2015

Get Data in JQuery DataTable from SharePoint List using $skip, $top, $inlinecount, $orderby parameters

In this article we will show how you can load the data from SharePoint List using REST API into JQuery DataTable using the Ajax Pagination.

In my previous article I showed you how to retrieve the data from the SharePoint list using REST API but it retrieves all the data from the list at once which is good for the list having less number of items/records but what if the list has large number of items as SharePoint client object model supports retrieval of 5000 items at once. 

In this case the SharePoint REST API provides the $TOP, $ORDERBY, $SKIP AND $INLINECOUNT parameters to retrieve only the records required to show on one page.

Parameter
Example
Description
$skip
$skip=n
Returns entries skipping the first n entries, according to the $orderby parameter
$top
$top=n
Returns only the top n entries, according to the $orderby and $skip parameters
$inlinecount
$inlinecount=allpages
It will add the  __count property to the results which indicates the total number of entries which match the $filter expression 
$orderby
$orderby = CustomerName
Returns the records ordered by the CustomerName field

Example: http://server/siteurl/_vti_bin/listdata.svc/Customers?$ $select=Id,CustomerName,Address,HomePhone,MobileNumber,Email,Organization,RolesValue &$inlinecount=allpages&skip=2&$top=2

We will use these parameters to retrieve the data required and bind it to our JSON

We will use the same customers list we used in my previous article series
I have created the CustomerJqueryDataTableAjax.js and CustomerJqueryDatatableAjax.txt for this article, which you can download it at the end of this post.

The rest query to get the top 10 items from the customers list would look like this,

../_vti_bin/listdata.svc/Customers?$select=Id,CustomerName,Address,HomePhone,MobileNumber,Email,Organization,RolesValue&$inlinecount=allpages&$top=10

Now in order to support pagination in DataTable the json results should have values of “sEcho”, “iTotalRecords”, “iTotalDisplayRecords in the json results which is not provided by the SharePoint by default so we will manipulate it in the fnServerData function of the JQuery DataTable.


We will place the below script in the fnServerData function of the JQuery DataTable

 $.ajax({  
            "dataType": "json",  
            "type": "GET",  
            "url": restQuery,  
            "data": aoData,  
            "success": function (json) {  
              json["sEcho"] = oSettings.iDraw.toString();  
              json["iTotalRecords"] = json.d.__count;  
              json["iTotalDisplayRecords"] = json.d.__count;  
              fnCallback(json);  
            }  
          });  

For sorting, the sort column and the sort direction are stored in the oSettings.aaSorting property
var sortCol = oSettings.aaSorting[0][0];
var sortDir = oSettings.aaSorting[0][1];

So now we can include the sorting as well, so lets say we want to sort based on the CustomerName we can have the REST Query as,

../_vti_bin/listdata.svc/Customers?$select=Id,CustomerName,Address,HomePhone,MobileNumber,Email,Organization,RolesValue&$inlinecount=allpages&$top=10&$orderby=CustomerName

Below will be my entire CustomerJqueryDatatableAjax.txt code,

 <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/CustomerJqueryDatatableAjax.js"></script>  
 </br />  
 <hr />  
 </br />  
 <div id="CustomerPanel">  
   <table style="width: 100%;">  
     <tr>  
       <td>  
         <table id="CustomerTable" style="width: 100%">  
           <thead>  
             <tr>  
               <th>Name</th>  
               <th>Address</th>  
               <th>Home Phone</th>  
               <th>Mob No.</th>  
               <th>Email</th>  
               <th>Organization</th>  
               <th>Role</th>  
             </tr>  
           </thead>  
         </table>  
       </td>  
     </tr>  
   </table>  
 </div>  

Below will be my entire CustomerJqueryDatatableAjax.js code,

 $(document).ready(function() {  
   var rowCount= 5;  
      $('#CustomerTable').dataTable({  
        "sProcessing": "Retrieving Customers...",  
        "bSort": true,  
        "bLengthChange": false,  
        "bFilter": false,  
        "iDisplayLength": rowCount,  
        "bServerSide": true,  
        "sPaginationType": "full_numbers",  
        "sAjaxDataProp": "d.results",  
        "bAutoWidth": false,  
        "aaSorting": [[1, 'desc']],  
        "aoColumnDefs": [  
          {  
            "aTargets": [0],  
            "mData": function (source, type, val) {  
              return  source.CustomerName   
              }  
          },  
          {  
                     "aTargets": [1],  
            "mData": function (source, type, val) {  
              return source.Address }  
          },  
          {  
                     "aTargets": [2],  
            "mData": function (source, type, val) {  
              return source.HomePhone}  
          },  
          {  
                     "aTargets": [3],  
            "mData": function (source, type, val) {  
              return source.MobileNumber}  
          },  
          {  
                     "aTargets": [4],  
            "mData": function (source, type, val) {  
              return source.Email}  
          },  
          {  
                     "aTargets": [5],  
            "mData": function (source, type, val) {  
              return source.Organization}  
          },  
          {  
                     "aTargets": [6],  
            "mData": function (source, type, val) {  
              return source.RolesValue}  
          }  
        ],  
        "fnServerData": function (sSource, aoData, fnCallback, oSettings) {  
          var restQuery = "../_vti_bin/listdata.svc/Customers?$select=Id,CustomerName,Address,HomePhone,MobileNumber,Email,Organization,RolesValue&$inlinecount=allpages&$top=" + rowCount;  
          if (oSettings._iDisplayStart > 0) {  
            restQuery = restQuery + "&$skip=" + oSettings._iDisplayStart  
          }  
          var restSortParams = "&$orderby=";  
          var sortCol = oSettings.aaSorting[0][0];  
          var sortDir = oSettings.aaSorting[0][1];  
          if (sortCol == 0) {  
            restSortParams = restSortParams + "CustomerName";  
          }  
          else if (sortCol == 1) {  
            restSortParams = restSortParams + "Address";  
          }  
          else if (sortCol == 2) {  
            restSortParams = restSortParams + "HomePhone";  
          }  
                else if (sortCol == 3) {  
            restSortParams = restSortParams + "MobileNumber";  
          }  
                else if (sortCol == 4) {  
            restSortParams = restSortParams + "Email";  
          }  
                else if (sortCol == 5) {  
            restSortParams = restSortParams + "Organization";  
          }  
                else if (sortCol == 6) {  
            restSortParams = restSortParams + "RolesValue";  
          }  
          restSortParams = restSortParams + " " + sortDir;  
          restQuery = restQuery + restSortParams;  
          $.ajax({  
            "dataType": "json",  
            "type": "GET",  
            "url": restQuery,  
            "data": aoData,  
            "success": function (json) {  
              json["sEcho"] = oSettings.iDraw.toString();  
              json["iTotalRecords"] = json.d.__count;  
              json["iTotalDisplayRecords"] = json.d.__count;  
              fnCallback(json);  
            }  
          });  
        }  
      });    
 });  

Happy SharePointing !