0

Can I join two SharePoint Lists and display in one DataTable?

I created my first dataTable but now trying to get data from another list. For example: I want the dataTable to display similar to a daily in/out board. The data for IN will come from the Staff Directory (columns: ID, StaffName, RegularHours), then OUT will come from the TimeOff list (columns: SDID, StaffName, StartDate, EndDate). Just trying to find another (without workflows) way to populate a Daily In/Out Board.

SAMPLE DATA (2 lists - 1 output): enter image description here Currently has the following code based on LOVEZ response but it returns no value?

$(document).ready(function(){
loadData();
});


function loadData() { 
var siteUrl = _spPageContextInfo.webAbsoluteUrl;
var dataSDUrl = siteUrl + "/_api/web/lists/getbytitle('Staff Directory')/items?$filter=AccountStatus eq 'Active'&$select=ID, title, TodaySchedule";
var dataARUrl = siteUrl + "/_api/web/lists/getbytitle('AbsenceRequest')/items?$filter=((EventDate le '" + new Date().toISOString() + "') and (EndDate ge '" + new Date().toISOString() + "'))&$select=SDID, calDisplayFN, EventDate, EndDate, RequestType, Status, ActivityType";

var ajaxSD = $.ajax({
    url: dataSDUrl,
    type: "GET", 
    dataType: "json", 
    headers: { "accept": "application/json;odata=verbose" }
});

var ajaxAR = $.ajax({
    url: dataARUrl,
    type: "GET", 
    dataType: "json", 
    headers: { "accept": "application/json;odata=verbose" }
});


$.when(ajaxSD, ajaxAR).done(function(a1, a2) {

    var data = a1[0].d.results.concat(a2[0].d.results);

    $('#table_id').DataTable({
        srollY: 400, 
        "aaData": data,
        "aoColumns": [
            { "mData": "title" },
            { "mData": "TodaySchedule" }
        ]
    });
});
}
0

We can use $.when to execute callback when both Ajax calls were successful.

The code like this:

function LoadData()
{
   var call1 = $.ajax({
      url: _spPageContextInfo.webAbsoluteUrl+"/_api/web/lists/getbytitle('list1')/items?$select=Data1,Data2,Data3,Data4,Data5",
      type: "GET",
      dataType: "json",
      headers: {
         Accept: "application/json;odata=verbose"
      }       
   });

   var call2 = $.ajax({
      url: _spPageContextInfo.webAbsoluteUrl+"/_api/web/lists/getbytitle('list2')/items?$select=Data1,Data2,Data3,Data4,Data5",
      type: "GET",
      dataType: "json",
      headers: {
         Accept: "application/json;odata=verbose"
      }       
   });

   // When both Ajax requests were successful
   $.when(call1, call2).done(function(a1, a2){
      // a1 and a2 are arguments resolved for the call1 and call2 ajax requests, respectively.
      // Each argument is an array with the following structure: [ data, statusText, jqXHR ]

      // Merge data from two Ajax calls
      var data = a1[0].d.results.concat(a2[0].d.results);

      $('#example').dataTable({
         "aaData": data,
         "aoColumns": [
            { "mData": "Data1" },
            { "mData": "Data2" },
            { "mData": "Data3" },
            { "mData": "Data4" },
            { "mData": "Data5" }
         ]
      });
   });
}   
1
  • Do I have to return (select) the same columns from both lists? For example: List1 returns ID, Name, Schedule. List2 is filtered by ((EventDate le datetime'" + today.toISOString() + "') and (EndDate ge datetime'" + today.toISOString() + "')) and returns SDID (ID of List1), Name, Schedule. In/Out Board: Employee1 - IN - 07:00 AM - 04:00 PM Employee2 - OUT All Day Employee 3 - OUT (08:00 AM - 10:00 AM) So basically if employee is out according to List2, I want the SCHEDULE column to indicate the time out . – Tamras Merin Dec 8 '17 at 19:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.