-->

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.

Monday 14 September 2015

Free Text Search on Column in JQuery Datatable


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. And In another article I showed how you can perform a column filter using the dropdown. Jquery DataTable provides a free text search but it is for the entire table this article will help you implement the free text search on the particular column.

Articles on the Jquery DataTable and SharePoint REST API

In this article we will show how you can perform a free text search on the custom column particularly on the JQuery DataTable which retrieved the data from the SharePoint List using the REST Api.

We will use the same customers list we used in my previous article and perform a free text search on the Address column.



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.

Friday 4 September 2015

Load the Data in JQuery DataTable from SharePoint List using REST API



In this article I will show how you can retrieve the data from the SharePoint List using the REST Api and bind it to the JQuery DataTable.

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, sorting, searching, etc. 

You can download the js file for the data table from here

Articles on the Jquery DataTable and SharePoint REST API 

For the purpose of the demo, I have created a customer list with the below columns and loaded it with some dummy data.

Customer SharePoint List

First of all we will create 2 files viz CustomerJqueryDatatable.js and CustomerJqueryDatatable.txt files and place it under the Site Assets Library. Also make sure you add the jquery js file in your SiteAssets/js folder.

Add the content editor web part on your page and give the path of the CustomerJqueryDatatable.txt file from the Site Assets Library and Save/Publish the Page.

Open the CustomerJqueryDatatable.txt file in SharePoint Designer and add the reference to the “jquery-1.11.0.min.js”, “jquery.dataTables.min.js” ,”jquery.dataTables.min.cs” and “CustomerJqueryDatatable.js” file in the CustomerJqueryDatatable.txt file.

 <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>  

Wednesday 2 September 2015

Query User Profile Service for Multiple Users in SharePoint Using SPServices


Lot of people are using the SPServices these days. SPServices is a jQuery library which encapsulates SharePoint Web Services with jQuery to make it easy to call them.

In this post, I will show how to query the user profile service from the SharePoint (2010/2013/Office 365) using the SPServices. As it will retrieve the properties of multiple users and there is no batch query possible in SharePoint to retrieve the properties of all users in one shot, we will leverage the async and cache property of the SPServices to query properties of multiple users.

Before we begin let’s make sure that SPServices is loaded correctly and SPServices JS files are loaded.

Download the jquery.SPServices-0.6.2.min.js file from here and reference in the code as below. I will suggest to upload it in the Site Assets Library.

 <script type="text/javascript" language="javascript" src="../SiteAssets/jquery-1.6.1.min.js"></script>  
 <script type="text/javascript" language="javascript" src="../SiteAssets/jquery.SPServices-0.6.2.min.js"></script>  
 <script type="text/javascript" language="javascript">  
  $(document).ready(function() {  
   alert("jQuery Loaded");  
   alert($().SPServices.SPGetCurrentSite());  
  });  
 </script>  

If we get both the alerts that means our Jquery and SPservices javascript files are loaded and we can move further.
Now to begin with for the demo purpose we will retrieve the users stored in the SharePoint group and we will display the user profile properties from that user list.
We will store the usernames of all the users in the ‘usersList’ array and push their login name in it.
 var userDivPnl;  
 var usersList = [];  
 $().SPServices({  
   operation: "GetUserCollectionFromGroup",  
   groupName: 'Members Group',  
   async: false,  
   completefunc: function(xml, Status) {  
     $(xml.responseXML).SPFilterNode("User").each(function() {  
       var name = $(this).attr("Name").toUpperCase();  
       var accountname = $(this).attr("LoginName");  
       usersList.push(accountname);  
       //Replacing the special characters from the loginname  
       var login = accountname.split("|")[2].replace("@", "_").replaceAll(".", "");  
       //Generate the HTML Div structure to add users properties in each div  
       userDivPnl += '<div>' +  
         '<div > ' +  
         '<div class="profile_photo" style="width: 100px;"> ' +  
         '     <img height="96" width="96" id="profile_' + login + '" src="' + noProfileImg + '" style="border-radius: 100%;"/> ' +  
         '</div> ' +  
         '&nbsp;&nbsp; ' +  
         '<div > ' +  
         '<p id="dispname_' + login + '"></p> ' +  
         '<p id="jobtitle_' + login + '"></p> ' +  
         '<p id="country_' + login + '"></p> ' +  
         '<p id="contact_' + login + '"><a href="#"></a></p> ' +  
         '<p><a id="email_' + login + '"></a></p> ' +  
         '</div> ' +  
         '</div> ' +  
         '</div> ';  
     });  
     userDivPnl += '</div>';  
     $("#container").append(userDivPnl);  
   }  
 });