How to join 2 Infotables on their common columns

    Sometimes you need the values from different ThingTemplate members in ONE grid. Therefore it would be great, if you can join 2 "GetImplementedThingsWithData" results into a common one. Here a script that works generally as long as you don't mess with datatypes on same column names.

     

    I'm very interested, if someone can find a much easier solution. The Union function was the only one I found suited for the task, but this needs preparation of the infotables upfront.

     

    Input:

    Table1 :Infotable

    Table2: Infotable

     

    Output: Infotable

     

    Here the "Snippet":

     

    // Define params for an Infotable to hold column names

    var params = {

      infoTableName: "field" /* STRING */

    };

     

    // Define column 1

    var newField = new Object();

    newField.name = "field";

    newField.baseType = 'STRING';

     

    // Two 1 columns Infotables to store the field definition;

    var field1 = Resources["InfoTableFunctions"].CreateInfoTable(params);

    field1.AddField(newField);

    var field2 = Resources["InfoTableFunctions"].CreateInfoTable(params);

    field2.AddField(newField);

     

    // Define the cell to add to Infotable

    var myField = new Object();

    myField.field = "";

    myField.baseType = "STRING";

     

    // Loop through Table1

    var dataShapeFields = Table1.dataShape.fields;

    for (var fieldName in dataShapeFields) {

      logger.debug('field1 name is ' + dataShapeFields[fieldName].name);

        myField.field = dataShapeFields[fieldName].name;

       field1.AddRow(myField);

    }

     

    // Loop through Table2

    var dataShapeFields = Table2.dataShape.fields;

    for (var fieldName in dataShapeFields) {

      logger.debug('field2 name is ' + dataShapeFields[fieldName].name);

       myField.field = dataShapeFields[fieldName].name;

       field2.AddRow(myField);

    }

     

    // Using inner join functionality to filter only the values that exist in both

    var params = {

      columns2: "field" /* STRING */,

      columns1: "field" /* STRING */,

    joinType: "INNER" /* STRING */,

      t1: field1 /* INFOTABLE */,

    t2: field2 /* INFOTABLE */,

      joinColumns1: "field" /* STRING */,

      joinColumns2: "field" /* STRING */

    };

     

    var commonFields = Resources["InfoTableFunctions"].Intersect(params);

     

    // Loop over the result to build a search string

    var commonColumns = "";

    var tableLength = commonFields.rows.length;

     

     

    for (var x = 0; x < tableLength; x++) {

      var row = commonFields.rows[x];

      commonColumns = commonColumns + row.field + ",";

    }

     

     

    // Reduce Table1 to match only common columns

    var params = {

    t: Table1 /* INFOTABLE */,

    columns: commonColumns /* STRING */

    };

     

    var result1 = Resources["InfoTableFunctions"].Distinct(params);


    // Reduce Table2 to match only common columns

    var params = {

      t: Table2 /* INFOTABLE */,

      columns: commonColumns /* STRING */

    };

     

    var result2 = Resources["InfoTableFunctions"].Distinct(params);

     

    // At the END JOIN the tables together (does not work if colums are different)

    var params = {

      t1: result1 /* INFOTABLE */,

      t2: result2 /* INFOTABLE */

    };

    var result = Resources["InfoTableFunctions"].Union(params);