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

Community Tip - Did you get an answer that solved your problem? Please mark it as an Accepted Solution so others with the same problem can find the answer easily. X

Combining 2 infotable to update one of them

emadar
5-Regular Member

Combining 2 infotable to update one of them

Hi everyone, I'm working on Thingworx creating services using the Java Api. I wanted to join them depending on one table but I cannot find a fine way to do it.

Let's say I have the table:

IDName
1Coca
2Cola
3Sprite

And this one:

IDName
1Sprite
2NotCola
3DietSprite
4HODOR
5CantTalkAnymore

And I want to receive:

IDName
1Sprite
2NotCola
3DietSprite

Does someone has any ideas how to do it? I could actually pass on every row and check it but I guess there is probably something in the api that can help me with it isnt there?

1 ACCEPTED SOLUTION

Accepted Solutions
jkaczynski
4-Participant
(To:emadar)

Elie,

The only way of doing this that comes to my mind (since there is no possibility to instantiate InfoTableFunctions on the Edge with Edge SDK) is to invoke Intersect directly (you need to have permissions on your App Key of course):

// ValueCollection is object to hold input parameters to service Intersect

ValueCollection params = new ValueCollection();

params.SetInfoTableValue("t1", new InfoTable()); // here instead of new InfoTable() put your first IT

params.SetImageValue("t2", new InfoTable()); // here instead of new InfoTable() put your second IT

params.SetStringValue("joinColumns1", "ID");

params.SetStringValue("joinColumns2", "ID");

params.SetStringValue("joinType", "LEFT");

params.SetStringValue("columns1", "ID");

params.SetStringValue("columns2", "Name");

// client is instance of ConnectedThingClient, successfully started and CONNECTED (you should check if client.isConnected() before)

// the parameters are: (EntityType, EntityName, ServiceName, InputParameters, Timeout)

InfoTable result = client.invokeService(ThingworxEntityTypes.Resources, "InfoTableFunctions", "Intersect", params, 1000);

Hope that helps. If so, you can mark my answer as a correct one for the reference of others.

Regards,

J.

View solution in original post

3 REPLIES 3
jkaczynski
4-Participant
(To:emadar)

Hi Elie Madar​,

You can try to use Intersect with LEFT as a joinType. This service joins two tables, so you'd have a result InfoTable and can do with it pretty much anything, e.g. update your property.

// var t1 is first InfoTable

// var t2 is second one

var params = {

     columns2: "Name",

     columns1: "ID",

     joinType: "LEFT",

     t1: t1,

     t2: t2,

     joinColumns1: "ID",

     joinColumns2: "ID"

}

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

Notice, that t1 is your first InfoTable, so we want to LEFT join to this table, but take the Name value from the second one.

Hope that helps, in case of further question, don't hesitate to ask.

Regards,

J.

emadar
5-Regular Member
(To:jkaczynski)

Do you know if the same is available for the edge sdk? Because I'm actually trying to do this with Java. It's nice to know we can do it with JS though .

Thanks you for the help anyways !

Regards,

E.

jkaczynski
4-Participant
(To:emadar)

Elie,

The only way of doing this that comes to my mind (since there is no possibility to instantiate InfoTableFunctions on the Edge with Edge SDK) is to invoke Intersect directly (you need to have permissions on your App Key of course):

// ValueCollection is object to hold input parameters to service Intersect

ValueCollection params = new ValueCollection();

params.SetInfoTableValue("t1", new InfoTable()); // here instead of new InfoTable() put your first IT

params.SetImageValue("t2", new InfoTable()); // here instead of new InfoTable() put your second IT

params.SetStringValue("joinColumns1", "ID");

params.SetStringValue("joinColumns2", "ID");

params.SetStringValue("joinType", "LEFT");

params.SetStringValue("columns1", "ID");

params.SetStringValue("columns2", "Name");

// client is instance of ConnectedThingClient, successfully started and CONNECTED (you should check if client.isConnected() before)

// the parameters are: (EntityType, EntityName, ServiceName, InputParameters, Timeout)

InfoTable result = client.invokeService(ThingworxEntityTypes.Resources, "InfoTableFunctions", "Intersect", params, 1000);

Hope that helps. If so, you can mark my answer as a correct one for the reference of others.

Regards,

J.

Top Tags