Retrieve two lists, sort and compare values, then display all the results

Question

I have two lists in SharePoint 2013 Online. I need to get matching values for a user-entered key (string), sort, and display both lists as one. Easy enough if I were able to use SQL to create a view. Best solution seems to be to just display both lists.

I've tried using SPD Linked Sources, but the "linked field" option never displays and the no-preview SPD is awful (what were MS thinking?). Workflow isn't feasible. List items may be edited in 'datasheet view' (client requirement). Lookups require a selection to display the related field.

I can get both lists and display them separately.

What I have:

List 1                          List 2
fruit apple  type rome          fruit apple  state washington
fruit pear   type bartlett      fruit pear   state oregon
fruit grapes type red           fruit orange state florida

What I want:

fruit apple  type rome      state washington 
fruit grapes type red 
fruit orange                state florida
fruit pear   type bartlett  state oregon

I'm missing two things (maybe more) an array that I can use for sorting and a comparison to use for matching the fruit on both lists. The real lists may have 50-120 items (each) that need to be matched.

All items should be returned. If there's a match, the data should be in the same row. If not, blanks should display.

The code below displays via an html page where the ID for each of the table cells matches the cell references in the script below. It's not sorted and the rows don't match.

$(function() {
    $.ajax({
    url: "sharepointlist/_api/web/lists/GetByTitle('List1')/items",

    type: "GET",
    headers: { "accept": "application/json;odata=verbose"
      }, 
    }).success(function (data) {

        var title = '';
        var type = '';
         $.each(data.d.results, 
        function (key, value) {

        title += "Title: " + value.Title + "<br/>";
        type += "Type: " + value.Type  + "<br/>";
        }); 

    $("#tdtitle").html(title);
    $("#tdtype").html(status);

$.ajax({
    url: "sharepointlist/_api/web/lists/GetByTitle('List2')/items",

    type: "GET",
    headers: { "accept": "application/json;odata=verbose"
      }, 
    }).success(function (data) {

        var title2 = '';
        var state = '';
         $.each(data.d.results, 
        function (key, value) {

        title2 += "Title2: " + value.Title + "<br/>";
        city += "State: " + value.State + "<br/>";
        }); 

    $("#tdsecond").html(title2);
    $("#tdstate").html(city);

Answer 1

I would divide task in two parts.

First of all you need to combine data retrieved from GET calls.

You may consider using promises for that in a way:

$.when(
    // Get List1
    $.get("sharepointlist/_api/web/lists/GetByTitle('List1')/item").then( function(data) {
        return data.d.results;
    }),

    // Get List2
    $.get("sharepointlist/_api/web/lists/GetByTitle('List2')/items").then( function(data) {
        return data.d.results;
    })

).then(processData);

function processData(list1, list2) {
    var res = list1.concat(list2);
}

Now you need to process your data. First of all you sort your new array by Title.

newData = newData.sort(function(a, b){
    return a.Title.localeCompare(b.Title);
});

Now you need to loop through sorted data and combine objects with the same Title.

res = res.reduce(function(a, b) {
    var t = a.slice(-1)[0]; //it's like getting last element
    if (t && t.Title === b.Title) {
        if (b.State) {
            t.State = b.State;
        } else {
            t.Type = b.Type;
        }
    } else {
        a.push(b);
    }
    return a;
}, []);

Now just assign new data into DOM.

UPDATE:

The example of merging all properties while joining elements using jQuery $.extend().

res = res.reduce(function(a, b) {
            var t = a.slice(-1)[0]; //it's like getting last element
            if (t && t.Title === b.Title) {
                $.extend(t, b);
            } else {
                a.push(b);
            }
            return a;
        }, []);

PS: jQuery $.extend() ignores, properties that are null or undefined.

The link to working solution at Plunkr with hardcoded JSON files.

https://plnkr.co/edit/gYJjyT8lCCNTe6EAlSYB

Answer 2

It seems you are trying to perform the "join" operation on list items returned from REST queries. If so, you could consider the following approach

function getListItems(webUrl,listTitle,selectProperties){
   return $.getJSON( webUrl + "/_api/web/lists/GetByTitle('" + listTitle + "')/items?$select=" + selectProperties.join(','))
   .then(function(data){
        return data.value.map(function(item){
             return selectProperties.reduce(function(result, key) { 
                 result[key] = item[key]; 
                 return result; 
             },{});    
        });
    });    
}


function joinListItems(left, right, key) {
    if(left.length == 0 || right.length == 0)
        return new Error("No data was found");


    var columns = Object.keys(left[0]).concat(Object.keys(right[0]));

    var createRow = function(left,right){
        var row = {};
        columns.forEach(function(key){
          row[key] = null;
        });
        var values = left != null ? left : right;
        for(var name in values) row[name] = values[name];
        return row;
    };
    var updateRow = function(existingRow,values){
        for(var name in values) existingRow[name] = values[name];
    };

    return left.concat(right).reduce(function(result, current, index){ 

      if(index < left.length){ 
           result.rows.push(createRow(current,null));   
           result.keys[current[key]] = index;
      }
      else {
           var rowIdx = result.keys[current[key]];
           if(typeof rowIdx !== 'undefined'){
               updateRow(result.rows[rowIdx],current);
           }
           else {
               result.rows.push(createRow(null,current));
           }
      } 

      return result;
    },{rows: [], keys: {}}).rows;

}



$.when(
    // Get List1
    getListItems( _spPageContextInfo.webAbsoluteUrl, "List1",['Title','Type']),
    // Get List2
    getListItems( _spPageContextInfo.webAbsoluteUrl, "List2",['Title','State'])

)
.then(function(items1,items2){
    var key='Title';
    var result = joinListItems(items1,items2,key);

    result = result.sort(function(a, b){
        return a.Title.localeCompare(b.Title);
    });

    console.log(JSON.stringify(result,null,2));
    //displayResults(result);
});


//print results (from comment section) 
function displayResults(items){
   var title = ''; 
   var type = ''; 
   $.each(items, function (index, item) { 
       title += "Title: " + item.Title + "<br/>"; 
       type += "Type: " + item.Type + "<br/>"; 
   });
}

You also might find this thread helpful that specifically discusses join operation.

Result

[
  {
    "Title": "fruit apple",
    "Type": "type rome",
    "State": "state washington"
  },
  {
    "Title": "fruit grapes",
    "Type": "type red",
    "State": null
  },
  {
    "Title": "fruit orange",
    "State": "state florida",
    "Type": null
  },
  {
    "Title": "fruit pear",
    "Type": "type bartlett",
    "State": "state oregon"
  }
]

Update for sort function

Replace:

 result = result.sort(function(a, b){
    return a.Title.localeCompare(b.Title);
});

with

result = result.sort(function(a, b){
    if(!a.Title) a.Title = "";
    if(!b.Title) b.Title = "";
    return a.Title.localeCompare(b.Title);
});