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

Database table for Resulting Objects?

SOLVED
Highlighted

Database table for Resulting Objects?

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: Database table for Resulting Objects?

What about ChangeRecord2 per the article here... 

View solution in original post

6 REPLIES 6
Highlighted

Re: Database table for Resulting Objects?

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.

Highlighted

Re: Database table for Resulting Objects?

@mlockwood-2, 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.

Highlighted

Re: Database table for Resulting Objects?

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.

Highlighted

Re: Database table for Resulting Objects?

What about ChangeRecord2 per the article here... 

View solution in original post

Highlighted

Re: Database table for Resulting Objects?

@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.

Highlighted

Re: Database table for Resulting Objects?

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

Announcements