Skip to main content
E
emadar5-Regular Member
5-Regular Member
February 9, 2017
Solved

Combining 2 infotable to update one of them

  • February 9, 2017
  • 1 reply
  • 3614 views

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?

Best answer by jkaczynski

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.

1 reply

J
jkaczynski1-Visitor
1-Visitor
February 9, 2017

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.

E
emadar5-Regular MemberAuthor
5-Regular Member
February 9, 2017

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.

J
jkaczynski1-VisitorAnswer
1-Visitor
February 9, 2017

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.

Terms & ConditionsAccessibility statement