Combining 2 infotable to update one of them

[emadar]

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:

ID	Name
1	Coca
2	Cola
3	Sprite

And this one:

ID	Name
1	Sprite
2	NotCola
3	DietSprite
4	HODOR
5	CantTalkAnymore

And I want to receive:

ID	Name
1	Sprite
2	NotCola
3	DietSprite

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?

[jkaczynski]

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]

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 · Accepted solution]

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.