Join Two Lists Using REST In SharePoint 2013

You can see the CRUD operations using REST here. In this article, we are going to discuss how to join two lists in SharePoint 2013 using REST.

I have two lists in the Office 365 SharePoint site.

  1. ListA
  2. ListB

ListA contains two columns -> Title and Code.

ListB contains four columns including Lookup column -> Title, EmployeeName, Salary, Dept (looks up to ListA).

Find the below screenshots of ListA and ListB.

SharePoint 2013

SharePoint 2013

Now, I want to join these two Lists and query the ListB.

The REST query will looks like the below.

https://server/Sites/SiteName/__api/web/lists/GetByTitle('ListB')/items?$Select=ListBField1,ListBField2 LookUpColumnInListB/ListAField,LookUpColumnInListB/ListAFiled&$expand=LookUpColumn

In our example, the REST query looks like the following after these setting values.

https://servername/Sites/SiteName/__api/web/lists/GetByTitle('ListB')/items?$Select=Title,EmployeeName,Salary,Dept/Title,Dept/Code&$expand=Dept

SharePoint 2013

SharePoint 2013

SharePoint 2013

Once you create the project, you will find default pages and scripts under Project Solution.

SharePoint 2013

On the default.aspx page, we will make some changes to display the data.

  • Add the below script tags after the jQuery script tag.
    1. <script type="text/javascript" src="_layouts/15/sp.runtime.js"></script>  
    2. <script type="text/javascript" src="_layouts/15/sp.js"></script>  
    SharePoint 2013

Write the below HTML code for User Interface under ContentPlaceHolderID tag.

  1. <table>    
  2.      <tr>    
  3.          <td>    
  4.     
  5.              <table id="tblEmployees" class="mytable">    
  6.              </table>    
  7.     
  8.          </td>    
  9.      </tr>    
  10.  </table>  

SharePoint 2013

  • Now open the App.js file. Here, we will write one method to get the data from two lists using joins in REST.

Method name - GetEmployeeDetails()

Call this method in Document.ready function.

  1. $(document).ready(function () {  
  2.         GetEmployeeDetails();  
  3.     });  
  4.   
  5. function GetEmployeeDetails() {  
  6.         $.ajax({  
  7.             url: _spPageContextInfo.siteAbsoluteUrl + "/_api/web/lists/GetByTitle('" + listName + "')/items?$Select=Title,EmployeeName,Salary,Dept/Title,Dept/Code&$expand=Dept",  
  8.             type: "GET",  
  9.             headers: { "Accept""application/json;odata=verbose" }, // return data format  
  10.             success: function (data) {                  
  11.   
  12.                 var table = $("#tblEmployees");  
  13.                 var html = "<thead><tr><th>Title</th><th>EmployeeName</th><th>Salary</th><th>DepartmentName</th><th>DepartmentCode</th></tr></thead>";  
  14.   
  15.                 for (var i = 0; i < data.d.results.length; i++) {  
  16.                     var item = data.d.results[i];                      
  17.   
  18.                     html += "<tr><td>" + item.Title + "</td><td>" + item.EmployeeName + "</td><td>" + item.Salary + "</td><td>" + item.Dept.Title + "</td><td>" + item.Dept.Code + "</td></tr>";  
  19.   
  20.                 }  
  21.                 table.html(html);  
  22.             },  
  23.             error: function (error) {  
  24.                 alert(JSON.stringify(error));  
  25.             }  
  26.         });  
  27.   
  28.     }   

Finally, App.js file looks like below.

  1. 'use strict';  
  2. var listName = "ListB";  
  3. ExecuteOrDelayUntilScriptLoaded(initializePage, "sp.js");  
  4.   
  5. function initializePage() {  
  6.     var context = SP.ClientContext.get_current();  
  7.     var user = context.get_web().get_currentUser();  
  8.   
  9.     // This code runs when the DOM is ready and creates a context object which is needed to use the SharePoint object model  
  10.     $(document).ready(function () {  
  11.         GetEmployeeDetails();  
  12.     });  
  13.   
  14.     function GetEmployeeDetails() {  
  15.   
  16.         $.ajax({  
  17.             url: _spPageContextInfo.siteAbsoluteUrl + "/_api/web/lists/GetByTitle('" + listName + "')/items?$Select=Title,EmployeeName,Salary,Dept/Title,Dept/Code&$expand=Dept",  
  18.             type: "GET",  
  19.             headers: { "Accept""application/json;odata=verbose" }, // return data format  
  20.             success: function (data) {  
  21.                 //console.log(data.d.results);  
  22.   
  23.                 var table = $("#tblEmployees");  
  24.                 var html = "<thead><tr><th>Title</th><th>EmployeeName</th><th>Salary</th><th>DepartmentName</th><th>DepartmentCode</th></tr></thead>";  
  25.   
  26.                 for (var i = 0; i < data.d.results.length; i++) {  
  27.                     var item = data.d.results[i];  
  28.                     //$("#tblEmployees").append(item.Title + "\t" + item.Salary + "\t" + item.Address + "<br/>");  
  29.   
  30.   
  31.                     html += "<tr><td>" + item.Title + "</td><td>" + item.EmployeeName + "</td><td>" + item.Salary + "</td><td>" + item.Dept.Title + "</td><td>" + item.Dept.Code + "</td></tr>";  
  32.   
  33.   
  34.                 }  
  35.                 table.html(html);  
  36.             },  
  37.             error: function (error) {  
  38.                 alert(JSON.stringify(error));  
  39.             }  
  40.         });  
  41.   
  42.     }  
  43.     // This function prepares, loads, and then executes a SharePoint query to get the current users information  
  44.     function getUserName() {  
  45.         context.load(user);  
  46.         context.executeQueryAsync(onGetUserNameSuccess, onGetUserNameFail);  
  47.     }  
  48.   
  49.     // This function is executed if the above call is successful  
  50.     // It replaces the contents of the 'message' element with the user name  
  51.     function onGetUserNameSuccess() {  
  52.         $('#message').text('Hello ' + user.get_title());  
  53.     }  
  54.   
  55.     // This function is executed if the above call fails  
  56.     function onGetUserNameFail(sender, args) {  
  57.         alert('Failed to get user name. Error:' + args.get_message());  
  58.     }  
  59. }  
Before deploying the solution, give the appropriate permissions under AppManifest file.

SharePoint 2013 
  • Deploy the solution. Right click on solution and select Deploy.
  • Trust the App.

    SharePoint 2013 
  • You can see the data after joining the two lists, ListA and ListB. In this way, you can add more columns in Lists and query the columns in REST URL.

    SharePoint 2013

Try from your side and please let me know if you have any queries. I am attaching the code for your reference.

X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now