Retrieve Two Lists, Sort And Compare Values, Then Display All The Results

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 SQ

Solution 1:

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

functiongetListItems(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; 
             },{});    
        });
    });    
}


functionjoinListItems(left, right, key) {
    if(left.length == 0 || right.length == 0)
        returnnewError("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 List1getListItems( _spPageContextInfo.webAbsoluteUrl, "List1",['Title','Type']),
    // Get List2getListItems( _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) functiondisplayResults(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);
});

Solution 2:

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

functionprocessData(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 elementif (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 elementif (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