Community Tip - If community subscription notifications are filling up your inbox you can set up a daily digest and get all your notifications in a single email. X
Hi Community,
im trying to figure out a way to find all EPMDocument without a created Visualisation. Which Database Fields hold the information about wheather a visualisation is created or not?
Regards
Stefan
Solved! Go to Solution.
Hello Stefan,
If you are using report manager, you can link epmdocument and wtderivedimage using "Derived From The Derived Image" link and reference epmdocument to epmdocument master to get the name and number.
If you are looking at SQLs you can use the below to get all representations.
SELECT edm.documentnumber, edm.name, edm.cadname, ed.versionida2versioninfo "Rev",
ed.iterationida2iterationinfo "Iteration", di.NAME
FROM epmdocument ed, epmdocumentmaster edm, derivedimage di
WHERE ed.ida3masterreference =edm.ida2a2
AND ed.ida2a2 IN di.ida3therepresentablereferenc;
Thank you,
Binesh Kumar
Barry Wehmiller
Hello Stefan,
If you are using report manager, you can link epmdocument and wtderivedimage using "Derived From The Derived Image" link and reference epmdocument to epmdocument master to get the name and number.
If you are looking at SQLs you can use the below to get all representations.
SELECT edm.documentnumber, edm.name, edm.cadname, ed.versionida2versioninfo "Rev",
ed.iterationida2iterationinfo "Iteration", di.NAME
FROM epmdocument ed, epmdocumentmaster edm, derivedimage di
WHERE ed.ida3masterreference =edm.ida2a2
AND ed.ida2a2 IN di.ida3therepresentablereferenc;
Thank you,
Binesh Kumar
Barry Wehmiller
To get this to run on my system, I had to fix the case, add the database name to each of the tables, and add parenthesis to the 'IN' clause. This is on Microsoft SQL 2012 (with case sensitive collation.)
SELECT edm.documentNumber, edm.name, edm.CADName, ed.versionIdA2versionInfo "Rev",
ed.iterationIdA2iterationInfo "Iteration", di.name
FROM windchill.EPMDocument ed, windchill.EPMDocumentMaster edm, windchill.DerivedImage di
WHERE ed.idA3masterReference = edm.idA2A2 AND ed.idA2A2 IN (di.idA3theRepresentableReferenc);
Can you tell me what table(s) I need to link this to in order to see any related additional files and their names? I'm interested in determining not just if an object was published, but whether or not the proper additional files were published as well. Thanks!
Hello Tom,
Sorry for the delayed response. Is this what you are looking for?
SELECT edm.documentnumber, edm.name, edm.cadname, ed.versionida2versioninfo "Rev",
ed.iterationida2iterationinfo "Iteration", di.NAME ,ad.filename, ad.role
FROM epmdocument ed, epmdocumentmaster edm, derivedimage di,APPLICATIONDATA ad, HOLDERTOCONTENT htc
WHERE ed.ida3masterreference =edm.ida2a2
AND ed.ida2a2 IN di.ida3therepresentablereferenc and htc.ida3b5 = ad.ida2a2 AND htc.ida3a5 = di.ida2a2 and ad.ROLE='ADDITIONAL_FILES';
/* Use ad.Role type PRODUCT_VIEW_* for Creo view, THUMBNAIL* from Thumbnails and ADDITIONAL_FILEs for Additional file types */
Thank you,
Binesh Kumar
Barry Wehmiller
Thanks Binesh Kumar! I've actually spent the last several days working on this. It took quite a while to locate the "Holder To Content" table. I wish I would have seen that sooner. Oh well. Here's what I've come up with so far.
In order to find documents that are missing either their representations or were published without additional files, I had to do multiple LEFT JOINs. To further complicate this we have a parameter that may exist in the CAD objects that is used to control publishing additional files (EXPORT_DATA yes/no).
This is just for drawings at the moment:
SELECT
edm.CADName "CAD Name",
ed.statestate "State",
ed.versionIdA2versionInfo "Rev",
ed.iterationIdA2iterationInfo "Iter",
ss_di.[Publish Name],
ss_di.[Publish Description],
ss_di.[File Type],
ss_di.[Additional Files Name],
ss_export.EXPORT_DATA
FROM
windchill.EPMDocumentMaster edm
JOIN windchill.EPMDocument ed
ON edm.idA2A2 = ed.idA3masterReference
LEFT JOIN
(
SELECT
ss_ad.[File Type],
ss_ad.[Additional Files Name],
ss_ad.idA3A5,
di.idA3theRepresentableReferenc "EPM idA2A2",
di.name "Publish Name",
di.description "Publish Description"
FROM
windchill.DerivedImage di
LEFT JOIN
(
SELECT
ad.fileName "Additional Files Name",
ad.role "File Type",
htc.idA3A5
FROM
windchill.HolderToContent htc,
windchill.ApplicationData ad
WHERE
htc.idA3B5 = ad.idA2A2
AND ad.role = 'ADDITIONAL_FILES'
) ss_ad
ON di.idA2A2 = ss_ad.idA3A5
WHERE
di.defaultRepresentation = '1'
) ss_di
ON ed.idA2A2 = ss_di.[EPM idA2A2]
LEFT JOIN
(
SELECT
bv.idA3A4 "EPM A2",
bv.value "EXPORT_DATA"
FROM
windchill.BooleanValue bv,
windchill.BooleanDefinition bd,
windchill.LWCIBAAttDefinition iba
WHERE
bv.idA3A6 = bd.idA2A2
AND bd.idA2A2 = iba.idA3A7
AND iba.name = 'EXPORT_DATA'
) ss_export
ON ed.idA2A2 = ss_export.[EPM A2]
WHERE
edm.docType = 'CADDRAWING'
AND ed.latestiterationInfo = '1'
AND ed.statestate = 'RELEASED'
AND (
ss_di.[Publish Name] IS NULL
OR ss_di.[Additional Files Name] IS NULL
)
AND
(
ss_export.EXPORT_DATA = 1
OR ss_export.EXPORT_DATA IS NULL
)
ORDER BY
edm.CADName,
ed.versionIdA2versionInfo,
ed.iterationIdA2iterationInfo,
ss_di.[Publish Description],
ss_di.[File Type];
I've also attached the SQL query itself (the formatting looks much better).
Thank you very much.
We came up with this query.
Select CADName,VERSIONIDA2VERSIONINFO, DERIVEDIMAGE.IDA3THEREPRESENTABLEREFERENC from EPMDOCUMENTMASTER, EPMDOCUMENT LEFT OUTER JOIN DERIVEDIMAGE on DERIVEDIMAGE.IDA3THEREPRESENTABLEREFERENC=EPMDOCUMENT.IDA2A2 where "EPMDOCUMENT".idA3masterReference = "EPMDOCUMENTMASTER".idA2A2 and "EPMDOCUMENT".statestate='RELEASED' and EPMDOCUMENTMASTER.CADNAME like '%.drw' and DERIVEDIMAGE.IDA3THEREPRESENTABLEREFERENC is null;
to find all released *.drw files without visualisation.
Thank you for your input
Sample QML - Find-EMPDocuments_without_visualization - QML similar Stefan's SQL that searches based on File Name.
Also Attached QML - EPMDocument-Find-Missing-Representations. This one is intended to Compliment https://support.ptc.com/appserver/cs/view/case_solution.jsp?n=CS211115 for having a CSV that can be sent to the publisher for generation of lightweight images. It can find missing representations based on Context, File Name and Date or any Combination of the three. The three columns needed for the input into the publisher are also included in the output. The column for finding out of date representations is also included...but would need some additional customization. It can also be used to find all the CAD documents checked in for the month that failed in the publisher.
Hi, could you do this by generating the log file under Site, Utilities, WVS Job Scheduler Administration?
Scott Morris, the scheduler can't check for state (Released), and least not without building a custom schedule (which requires Java coding).