How to get Child Version & Iteration for a given Assembly through SQL
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
How to get Child Version & Iteration for a given Assembly through SQL
Hi,
I am new to Windchill & trying to find out Child Version & Iteration information for the given Parent assembly through SQL. EPMMemberLink IDA3A5 points to IDA2A2 of EPMDocument which is Parent information. Since i have EPMDocument IDA2A2, i can easily get latest version & iteration of a Parent. However, for Child, EPMMemberLink stores EPMDocumentMaster IDA2A2 in IDA3B5, if my child Part has multiple version & iteration then quering EPMDocument based on IDA3MasterReference will fetch multiple records of different versions & iterations for a given component. I am stuck & not able to find a way to get child version & iteration information.
- Labels:
-
General Customization
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@Rocky wrote:
Hi,
I am new to Windchill & trying to find out Child Version & Iteration information for the given Parent assembly through SQL. EPMMemberLink IDA3A5 points to IDA2A2 of EPMDocument which is Parent information. Since i have EPMDocument IDA2A2, i can easily get latest version & iteration of a Parent. However, for Child, EPMMemberLink stores EPMDocumentMaster IDA2A2 in IDA3B5, if my child Part has multiple version & iteration then quering EPMDocument based on IDA3MasterReference will fetch multiple records of different versions & iterations for a given component. I am stuck & not able to find a way to get child version & iteration information.
In the epmdocument table there is EPMAsStoredConfig ida2a2. This is in column ida3c10. You use this and look for rows in the EpmAsStoredMember table in which it's ida3a5 column equals epmdocument.ida3c10. The EpmAsStoredMember has the EPMDocument ida2a2 in it.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi Rocky,
I'm not sure about SQL queries, but you can get child componets for a given Assembly by using a Query Builder Report. Refer the following article.
https://www.ptc.com/en/support/article?n=CS72503
Thanks,
Bhagirath
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
This isn't an exact match, but maybe it will give you some ideas. I built this to locate the drawings that were referencing a specific format.
SELECT edm_frm.CADName AS [Format_Name], edm.CADName AS [Drawing_Name], ed.statestate AS [State], ed.versionIdA2versionInfo AS [Rev], ed.iterationIdA2iterationInfo AS [Iter] FROM EPMReferenceLink AS [rl] JOIN EPMDocumentMaster AS [edm_frm] ON rl.idA3B5 = edm_frm.idA2A2 JOIN EPMDocument AS [ed] ON rl.idA3A5 = ed.idA2A2 JOIN EPMDocumentMaster AS [edm] ON ed.idA3masterReference = edm.idA2A2 AND ed.branchIditerationInfo = ( SELECT max(branchIditerationInfo) FROM EPMDocument WHERE idA3masterReference = edm.idA2A2 AND edm.idA2A2 = ed.idA3masterReference AND latestiterationInfo = 1 AND statecheckoutInfo = 'c/i' ) WHERE rl.referenceType = 'DRAWING_FORMAT' AND ed.latestiterationInfo = 1 AND edm_frm.CADName LIKE 'part_e_landscape.frm' -- set this equal to the format interested in. Comment out entirely to see all formats. ORDER BY edm_frm.CADName, edm.CADName
Here's another one that shows the models being referenced by a specifc drawing:
SELECT DISTINCT TOP(60) edm.CADName AS [CAD_Name], ed.statestate AS [State], ed.versionIdA2versionInfo AS [Rev], ed.iterationIdA2iterationInfo AS [Iter], ed.branchIditerationInfo, f_edm.CADName AS [CAD_Name_2], f_ed.statestate AS [State_2], f_ed.versionIdA2versionInfo AS [Rev_2], f_ed.iterationIdA2iterationInfo AS [Iter_2], f_ed.branchIditerationInfo FROM EPMDocumentMaster AS [edm] JOIN EPMDocument AS ed ON edm.idA2A2 = ed.idA3masterReference AND ed.branchIditerationInfo = ( SELECT max(branchIditerationInfo) FROM EPMDocument WHERE idA3masterReference = edm.idA2A2 AND edm.idA2A2 = ed.idA3masterReference AND edm.CADName LIKE @DocName AND latestiterationInfo = 1 ) JOIN EPMAsStoredMember AS eas ON eas.idA3A5 = ed.idA3C10 JOIN EPMDocument AS f_ed ON f_ed.idA2A2 = eas.idA3B5 JOIN EPMDocumentMaster AS f_edm ON f_edm.idA2A2 = f_ed.idA3masterReference WHERE edm.CADName LIKE @DocName AND ed.latestiterationInfo = 1 --AND ed.statestate = 'RELEASED' AND edm.CADName != f_edm.CADName AND LEFT(edm.CADName, (CHARINDEX('.',edm.CADName,1) - 1)) = LEFT(f_edm.CADName, (CHARINDEX('.',f_edm.CADName,1) - 1)) ORDER BY edm.CADName
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@Rocky wrote:
Hi Randy,Thanks for your reply. But from EPMMemberlink table we get Ida2a2 of document master which gives multiple version & iteration when queried in epmdocument against ida3masterreference. Now which is the child version & iteration used for the given parent is what we are not able to find.
That is why you need to use the EpmAsStoredMember table to get the as-stored epmdocument instead of the epmdocumentmaster like I said in my previous post.
Can you provide a sample sql code joining epmmemberlink table with others to find the child version & iteration?Thanks & Regards,Rocky
Tom has supplied some examples that show how to find the latest version. Take a look at his 2nd sample which shows using the EpmAsStoredMember table.
Also take a look here:
https://www.ptc.com/en/support/article?n=CS34307
and here
https://www.ptc.com/en/support/article?n=CS237842
Note in the CS237842 article PTC is selecting the max of versionsortida2versioninfo instead of branchIditerationInfo.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you all for your support & replies.. We have got the solution of finding respective child precise versions & iterations in context of the given assembly. Firstly, we joined EPMDocument, EPMDocumentMaster & EPMAsStoredMember & extracted all versions & iterations based on the IDA3B5 & Doc IDA2A2. Then we joined them to EPMMemberLink, EPMDocument & EPMDocumentMaster based on IDA3C10 & CADName. We got correct results. Thanks again for your help..
