cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

How to join 2 Infotables on their common columns

Level 6

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

Tags (1)
7 REPLIES

Re: How to join 2 Infotables on their common columns

Not sure if it would be faster, but you could just add the two together and then run a Distinct

Something a tat crazy but would work too, set up a DataTable with the proper Primary key and just do AddOrUpdate with both tables, this of course means you have it permanently stored.

Re: How to join 2 Infotables on their common columns

Hi @PaiChung

Can I do it without a DataTable?

I have only one Infotable( created from a datashape) from which I have to create a lookup for  one of the fields...and then based on that particular field selection represent rest of the data in the mashup.

Also, explain how  I can do it with the help of list /dropdown widget...

 

 

Re: How to join 2 Infotables on their common columns

If all the info even the info after selection is in the same infotable, I would just return the whole infotable to the mashup.

And then you can show the Items in a drop down and based upon selection use selected row information to display the associated information.

Highlighted

Re: How to join 2 Infotables on their common columns

Thanks @PaiChung!

I tried the using my service and by calling  "QueryStreamentrieswithdata" and then triggering it with "SelectedRowschanged".

I got the required table.But now I want to customize the rows of the grid.

e.g if any one of the field of the table goes out of limit, that particular row should fill red(I don't want to show that deviated field in my grid)with all the texts showing up.

 

I have used state formatting(using"configure grid columns" of the grid widget).But, all the rows are showing red instead of the deviated ones

 

Also, I want to get the data every 5 min and display the data in Grid for every 5 min only.

Please, help me code the same in subscription.

 

Re: How to join 2 Infotables on their common columns

To highlight different rows or cells based on value in a grid, you use State Based Formatting.

To apply it to a full row, in the Grid Properties select the Formatter and add your definition.

If you want it for a cell, then go into the Grid configuration (hover over drop down) find the appropriate column and go to formatting.

Re: How to join 2 Infotables on their common columns

Hi! @PaiChung

I will look into it.I had been trying to do it through Grid Configuration.

Please also help me code in subscription so that I get my data every 5 min.

 

Thanks.

Re: How to join 2 Infotables on their common columns

For every 5 minutes

server side use the Timer ThingTemplate and create a Timer Thing and then subscribe to that event.

client side use the AutoRefresh widget to re-execute the service call to get the data