Find Accidental Revisions or Stagnant Revisions

[lhoogeveen]

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?

[kaudibert]

Omg happens all the time here. Hope you find a solution for both of us

[joe_morton]

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

[STEVEG]

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

[mmeadows-3]

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

[STEVEG]

Yep.  You are correct.  Didn't even think about that.  It did upload to my system.  Thank you.

[Marco_Tosin]

Try to use this article and related QML

https://www.ptc.com/it/support/article/CS138336

[lhoogeveen]

Here's a good video about the updated Query Builder in Windchill 11.0 - https://community.ptc.com/t5/FlexPLM-Tips/PTC-Query-Builder-for-FlexPLM/m-p/440464 

Haven't figured how to build the query yet.

[lhoogeveen]

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

[STEVEG]

"Let's hope for a fix soon"

In two months from now it's been 8 years since that idea was posted.  I guess they have to do their due diligence.  😏

[lhoogeveen]

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-p/634178#M10926

[TomU]

Here is an SQL query to find orphan revisions.

• This is designed for Microsoft SQL server.
• It was built against Windchill 11.0 M030
• You will need to adjust the state being searched for and revision being excluded to match your configuration
• I make no guarantees on the performance.  (I'm not a SQL developer.)

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

Sample output: