Has anyone found a good way to find accidental revisions in Windchill or revisions that never made it back to a released state? It happens somewhat frequently that some objects accidentally get revised by someone and they didn't even know they did it. Or maybe a change was in progress and then the change was abandoned and the CAD was left. I've tried using advanced searches for this but I can't get the search to work - see screenshot below. Or is there a SQL query that would do the trick?
You could use Query Builder to make a query for this.
Your main object would be the CAD Document type (or whatever it is you want to search for)
There's a constraint you can add to get latest iterations.
To get latest revisions, you have to add a constraint that uses a Sub-Select. This should be helpful for that: https://www.ptc.com/en/support/article?n=CS1171&language=en&posno=1&q=query%20latest&source=search
From there, you would add your additional constraints to exclude revisions = 1 and so on
I opened a case with PTC and it looks like this problem has been reported before. CS58285 and CS157314 look like their directly related to why the Advanced Search query isn't working. There's a popular Windchill Idea to fix this: Enable Windchill search to support ‘pure’ latest iteration search when additional criteria is applie.... The idea was first reported in 2012 and acknowledged in 2018. Let's hope for a fix soon - keep voting for it! Here's a related article in the Windchill Help Documentation - Non-Latest Iterations Using Multiple Criteria.
@jmorton-2 The report manager does not work the same way starting in 11.0. The QML file in that article won't even import. I have been trying to figure out how to get it working in 11 but I have had no luck so far.
If you are referring to the QML in CS1171, it loaded into my 11.0 system.
There is a difference between import and upload. These picks should work for QML files.
Site > Utilities > Report Manager > Upload Report Template from QML
I created a Product Idea for people to vote on - https://community.ptc.com/t5/Windchill-Ideas/Find-Accidental-or-Stagnant-Revisions-in-Windchill/idi-...
Here is an SQL query to find orphan revisions.
SELECT --DISTINCT TOP(60) ISNULL(lib.namecontainerInfo,'') + ISNULL(prod.namecontainerInfo,'') AS [Context], edm.CADName AS [CAD Name], FORMAT(ed.modifyStampA2, 'MM/dd/yyyy') AS [Date], ed.statestate AS [State], ed.versionIdA2versionInfo AS [Revision], ed.iterationIdA2iterationInfo AS [Iteration], wt.fullName AS [Username] 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 latestiterationInfo = 1 AND statecheckoutInfo = 'c/i' ) -- Get the name of the user who created this version JOIN WTUser AS wt ON wt.idA2A2 = ed.idA3B2iterationInfo -- See if this drawing is in a Library LEFT JOIN WTLibrary AS [lib] ON ed.idA3containerReference = lib.idA2A2 -- See if this drawing is in a Product LEFT JOIN PDMLinkProduct AS [prod] ON ed.idA3containerReference = prod.idA2A2 WHERE ed.latestiterationInfo = 1 AND ed.statestate = 'INWORK' AND ed.versionIdA2versionInfo <> '-' AND ed.iterationIdA2iterationInfo = '0' --AND edm.CADName = 'My CAD File' --AND prod.namecontainerInfo = 'My Product' --AND lib.namecontainerInfo = 'My Library' --AND wt.fullName = 'Doe, John' --ORDER BY edm.CADName ORDER BY Date