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