Database table for Resulting Objects?

[BrianToussaint]

I found the database table for Affected Objects and it is called AffectedActivityData.  But I cannot find the table for Resulting Objects.  Does anyone know what that table is called?  I'm learning how to join tables in SQL server and I have the results of a join that are duplicating rows if they part is in both the Affected Objects and Resulting Objects of a CN.

Thank you,

Brian

[MikeLockwood]

Best to attempt to create a query builder report and select directly.  From the report, select Tools, SQL to view the SQL generated.  note: Depending on some elements of the report, the SQL is not shown.

I usually attempt to build reports with a connection to the database open and go back and forth, getting clues.  Far better to run as a report than directly against the database in general.

[BrianToussaint]

@MikeLockwood, thank you for the tip!  I did not know that you could do that.  I will look into using that to get the tables that I need.  However when I look at the differences in the statements, the ones out of Windchill seemed a little more than the one that I created in SQL.

[MikeLockwood]

Yes, because the report is querying the java object model - which is translated into sql. Many times there are major differences, including especially the use of multiple fields as the primary key.

One other note, if the results are large, one can select to output the results to CSV directly rather than as a web page then dump to a file.

[aaronjlarson · Accepted solution]

What about ChangeRecord2 per the article here... 

[BrianToussaint]

@aaronjlarson, that is the table that I had in there and need.  The thing that was confusing me was that the same part number was showing up multiple times so I thought it was crossing with the affected items table. However, I figured out that for each iteration of the object it is listing that object in my report each time.  I just need to add a distinct to narrow down the numbers.

[aaronjlarson]

Yes, that is because each version of a "part number" is really like its own distinct object.  You can use the distinct setting on the report if you're not wanting to return any version specific information.  Things that are common across all versions like Name and Number are okay for this.  If you're wanting to obtain the latest iteration of the latest version for other attributes then you need more design in your query.  

To get latest iteration set a criteria statement for LatestIteration = True.  Note that this will still return the latest iteration in each revision level.  If you have Revs A & B, you'll get two rows - the latest iteration for each. 

To get the latest revision, you'll have to do a subselect to set the criteria of the returned revision equal to the max revision by using the versionsortId.  You can find more info on that here: https://community.ptc.com/t5/Windchill/query-builder-Latest-Revision/td-p/451237

Thanks to @Marco_Tosin and the community, there is lots of info on reporting here: https://community.ptc.com/t5/Windchill-Tips/Resource-for-reporting/td-p/448738