0

I followed this article http://www.c-sharpcorner.com/article/display-sharepoint-list-data-in-jquery-data-table/ to retrieve SharePoint list items into jquery data-table.

I modified my code a little to retrieve items from 2 separate lists and in 2 separate datatables.

this 2 lists has one-many relationship but without lookup column. There is a common column which keeps the relation between 2 butu its not lookup column.

I want to join this 2 lists using above code or any other client side technology. Javascript or jquery is preferred though

is it possible?

Thanks

  • The 'above code' is only for displaying using the dataTable plugin. You need to write the join code yourself.. Loop both arrays and .push() every item into one new array. If you don't know how to handle Arrays, start with: w3schools.com/js/js_arrays.asp – Danny '365CSI' Engelman Dec 28 '16 at 14:48
  • Did you ever get any solution to merge the 2 lists with different columns? – Ravi Feb 21 at 12:04
0

Below can get you started...Assuming you have column which connects both the List, you list data is in Json... this is based on example at ref link

   var colors = {"COLORS":[ {"1": 'red'}, { "2": 'yellow'}, {"3":'orange'}]}

    var fruits = {"FRUITS":[ {"1":'apple'}, { "2": 'banana'}, {"3":'orange'}]}

    var newFruits = {"NEW_FRUITS": [] }

    if(colors.COLORS.length == fruits.FRUITS.length){
        var temp, first;
        $.each(fruits.FRUITS, function(i){
            for(first in this)break;
            temp = {};
            temp[first] = [];
            temp[first].push(this[first]);
            temp[first].push(colors.COLORS[i][first]);
            newFruits.NEW_FRUITS.push(temp);
        });
    }

OR Use AlaSQL

Alasql JavaScript SQL library does exactly what you need in one line:

<script src="alasql.min.js"></script>
 <script>
    var data = { COLORS: [[1,"red"],[2,"yellow"],[3,"orange"]],            
                 FRUITS: [[1,"apple"],[2,"banana"],[3,"orange"]]};

    data.NEW_FRUITS = alasql('SELECT MATRIX COLORS.[0], COLORS.[1], FRUITS.[1] AS [2] \
         FROM ? AS COLORS JOIN ? AS FRUITS ON COLORS.[0] = FRUITS.[0]',
         [data.COLORS, data.FRUITS]);
 </script>

You can play with this example in jsFiddle.

This is a SQL expression, where:

SELECT - select operator

MATRIX - modifier, whci converts resultset from array of objects to array of arrays

COLORS.[0] - first column of COLORS array, etc.

FRUITS.1 AS 2 - the second column of array FRUITS will be stored as third column in resulting recordset FROM ?

AS COLORS - data array from parameters named COLORS in SQL statement

JOIN ? ON ... - join

[data.COLORS, data.FRUITS] - parameters with data arrays

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.