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

Multiple DataTables in return from query

SOLVED
Highlighted
Participant

Multiple DataTables in return from query

Hello, this is my first post here so I hope I am posting in the correct location.

 

I'm having some issues with figuring out how to do joins and other filter functions on the Thingworx platform.

But I think I am getting the hang of it, at least a little bit.

 

I am having some issues with while doing an INNER join on two tables from the Operator Advisor addition to TWX.

in the output from my service I get both of the tables "mashed" together. I'll add an attachment to illustrate this.

 

I think I am getting the join and filter correct, but I am not sure. Here's that code:

 

var filter = {
"filters": {
"filters": [{
"dataShapeName": "PTC.SCA.SCO.JobOrderProcessingResourceRequirement",
"fieldName": "ProcessingResourceUID",
"type": "EQ",
"value": ProcessingResourceUID
}],
"type": "AND"
},
"joins": [{
"type": "INNER",
"sourceDataShapeName": "PTC.SCA.SCO.JobOrderProcessingResourceRequirement",
"sourceFieldName": "JobOrderUID",
"targetDataShapeName": "PTC.SCA.SCO.JobOrder",
"targetFieldName": "UID"
}]
};

var params = {
filter: filter,
dataShapeName: "PTC.SCA.SCO.JobOrderProcessingResourceRequirement",
};

var result = Things[me.GetDatabaseThingName()].Query(params);

 

 

I am trying to use the "ProcessingResourceUID" (which is my input) to get all JobOrders that are related to it through the "JobOrderProcessingResourceRequirement" table.

 

Output is set to infotable with datashape JobOrder.

 

Does this look correct to you? 

 

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: Multiple DataTables in return from query

Hello,

 

Tried finding some JSON filter information, but couldn't really find much.

 

But I did as you said. I queried everything from both tables into seperate infotables and then joined the tables with the code snippet called intersect.

 

Thanks for your help and time anyways!

View solution in original post

4 REPLIES 4
Highlighted

Re: Multiple DataTables in return from query

This looks like something provided by OA vs. the standard Query in Thingworx.

Since I do not know how that service works behind the scenes, could within reason query the raw from the two tables you are joining and join them within Thingworx?

Probably if you got this statement to work as is, likely much more efficient though.

Highlighted

Re: Multiple DataTables in return from query

Yes, this is from OA and it is on an external database. (so there's no "Data Table" entity for these tables).

I'd really want to use regular SQL queries but it doesn't seem like that's possible.

 

From my understanding "INNER Join" means that I get data for both tables, so it makes sense that it returns both in the output. But I've also tried both RIGHT and LEFT joins and they instead return nothing.

 

I guess I really need to figure out how this "Things[me.GetDatabaseThingName()].Query(params);" works.

 

I've tried reading up on these "functions" in the PTC help documentation. But maybe I've missed something.

 

Is this type of coding only used in Thingworx or is it some standard Javascript thing that I can research more?

Highlighted

Re: Multiple DataTables in return from query

Some of these functions are basic derivatives of what is available in java.

but the OA Query I'm not too sure how that works.

Like I said, you can pull in perhaps the raw from the two tables and join them once you have them in Thingworx.

There are infotable functions to help with that.

Highlighted

Re: Multiple DataTables in return from query

Hello,

 

Tried finding some JSON filter information, but couldn't really find much.

 

But I did as you said. I queried everything from both tables into seperate infotables and then joined the tables with the code snippet called intersect.

 

Thanks for your help and time anyways!

View solution in original post

Announcements

Thingworx Navigate content has a new home! Click here to access the new Thingworx Navigate forum!