qml for Drawing >> related objects, Format

[MikeLockwood]

Anybody have a query builder report that returns exactly what you see from a Drawing, Related Objects, filtered for just the Drawing Format (.frm).  Would appreciate if you would provide (or hints on how to create).

thx

[RandyJones]

Don't have a query builder however have some java code that will get the format. Maybe this will get what you need to get started with the query builder:

EPMDocument drawing = <already set...>;

EPMAsStoredConfigSpec configSpec = EPMAsStoredConfigSpec.newEPMAsStoredConfigSpec(drawing);

QueryResult qr = EPMStructureHelper.service.navigateReferencesToIteration(drawing, null, false, configSpec);

while (qr.hasMoreElements())

{

    Persistable[] p = (Persistable[]) qr.nextElement();

    if(p[1] instanceof EPMDocument) {

        //we have an epmdocument

        EPMDocument epmRefDoc = (EPMDocument) p[1];

        if(epmRefDoc.getDocType().toString().equals("FORMAT"))

        {

            //we have a format

            System.out.println("Found drawing format: " + epmRefDoc.getCADName());

        }

    }

}

[MikeLockwood]

Randy, thanks very much.  I'm still just learning to deal w/Java code like this.  What's the best / easiest way to execute in order to get a result like one would get either using a report or direct sql against the database?  Also, actually kind of need the reverse logic.  We updated all drawing formats recently (size B, C, D - Inch/mm, Creo Parametric / SolidWorks).  Wanting to see the "where-used" for each of the formats as users gradually replace format on existing drawings.

[RandyJones]

One way would be to create a jsp page that would output the results. As far as where used for formats I don't have any java code that does that however here is a post dealing with finding the referencedby of an object by using EPMStructureHelper.service.navigateReferencedBy():

Re: How to retrieve drawing that references an EPMDocument from Java?

[TomU]

No idea from query builder, but this is what I use for SQL server:

select asStoredChildName from EPMReferenceLink where idA3A5 = '248028786' and referenceType = 'DRAWING_FORMAT'  -- set idA3A5 == to idA2A2 of EPMDocument

Keep in mind that the Related Objects tab (and this query above) always show the latest version of the format.  If you want to know exactly which version of the format was used, you have to go at it differently.  (Multiple joins between the EMPAsStoredMember table and the EPMDocument table.)

[MikeLockwood]

This is getting closer

How can I now input the CAD Doc Numbers (or maybe the IDA2A2 of) each Format and have it return the Drawings on which it is referenced?

[TomU]

There is an issue with this query.  It is using the "asStoredChildName" from the EPMReferenceLink table.  Unfortunately this is static text and is not updated when the an object is renamed.  It only show the name of the reference object at the time the record was originally created.  Instead it needs to link the entry back to the EPMDocumentMaster table.  Something like this:

SELECT

       edm.CADName AS [Name]

FROM

       EPMReferenceLink AS [rl]

       JOIN EPMDocumentMaster AS [edm]

              ON rl.idA3B5 = edm.idA2A2

WHERE

       referenceType = 'DRAWING_FORMAT'

       AND idA3A5 = '341603112'  -- set idA3A5 == to idA2A2 of EPMDocument

[P.S.  I'm still working on a query that goes the other direction - finds all CAD Docs that refer to a particular format.]

[TomU]

Here the other SQL version.  This will take a format and return all the latest version drawings that reference it.

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_c_landscape.frm'  -- set this equal to the format interested in.  Comment out entirely to see all formats.
ORDER BY
  edm_frm.CADName,
  edm.CADName

[DarrenStorey]

See attached .qml

Regards Darren

[TomU]

Darren Storey‌,

What is this?

I don't seem something similar on my list:

[DarrenStorey]

In 10.2 M030 this object has a "friendly" name in report manager. Try searching for "CAD Document Reference Link"

[DarrenStorey]

Not sure why my report manager is adding what looks like a soft type. I was expecting this

[TomU]

Found it.  Mine does the same thing (includes our local domain name.)

[DarrenStorey]

Tom

Sorry I was confusing epm reference link with cad reference link.

Please find corrected report attached.

[MikeLockwood]

Great info / exchange here - really appreciate it.  Gave me the needed clues.  I imported the last one supplied by Darren and then rearranged a bit to list the Format first, with the intent being to list all drawings which reference a given format.  It's fixed (Constant) for the 6 new Edwards Formats just released; if others use, substitute your format CAD Doc numbers.

thanks!