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;

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