Query Builder/Database Type Joins

[towen]

Hi all,

I am currently trying to create a Query Builder report that retrieves information on an EPM Document and the Life Cycle Template the EPM Document is using.

I am familiar with the use of Joins to capture the relationship between the types, however, I am unsure how to create the join for these two types.

To explain further, I already use a join to link between EPM Documents and WTParts:

From	Reference	To
EPMBuildRule	Role AObject Ref	EPM Document
EPMBuildRule	Role BObject Ref	WTPart

Similarly, I use EPMDescribe Link to create a join between EPM Document Master and the related drawing.

Can anyone help me identify the link between EPM Document and Life Cycle Template?

If there is a way of figuring out any of these links (perhaps by viewing the database structure) this would also be a great help.

Thanks for your time,

Tris

[satre-2 · Accepted solution]

See attached QML. I have created report in Windchill 10.2. so not sure if you are able to import it on Windchill 9.1.PFA attached screenshot of join.

[towen]

Hi Shreyas,

Thank you very much, that's great.

Do you know of any method to identify the correct joins for other types? Knowing how to identify them would be very useful for the future.

Thanks again,

Tris

[Spiff]

Hi Tristan,

can you provide a QML of the link between EPMDocuments and WTParts?

I am trying to get the corresponding WTPart ID from an EPMDocument ID.

I tried what I read in the first post:

FROM:

# wt.part.WTPart

# wt.epm.EPMDocument

# wt.epm.build.EPMBuildRule

JOIN:

# wt.epm.build.EPMBuildRule <---> Role AObject Ref <---> wt.part.WTPart

# wt.epm.build.EPMBuildRule <---> Role BObject Ref <---> wt.epm.EPMDocument

SELECT:

# Part ID

CRITERIA:

# AND: EPMDocument ID = <EPMDocID Parameter>

But I don't get any results.

I also tried to add an EPM Describe link, but this does not help.

Do I have to involve the Masters?

If you have any hint I'd be glad.

Thanks!

David

[towen]

Hi David,

I've attached a QML from Windchill 10.1 for you. The only difference to what you have described is that Role AObject Ref and Role BObject Ref should be the other way around!

The QML I've attached has a few extra criteria that I use regularly to return only the latest iteration of data I need. If you'd like to see each iteration then remove the top four criteria. I also wasn't exactly sure what you were referring to with EPMDocument ID, so the QML can search for an EPM Doc Number or an EPM Document Object Identifier.

The EPM Describe Link is used to capture the reference links between data, I primarily use it to link 3D Models with 2D Drawings. If you'd like an example of how to do that let me know!

Kind regards,

Tristan

[Spiff]

Hi Tristan,

thanks for your help!

With ID a was referring to the thePersistInfo.theObjectIdentifier.id (no branchID, no master ID). Not exactly sure where's the difference here.

Switching the AObject and BObject Refs worked for my own query - but:

It only gets the latest WTPart ID of the EPMDocument even if I search for a non-latest EPMDocument.

So I search for let's say Version 1.1, 1.2, 2.1,... and all give out the same WTPart ID: the latest one.

I only have set one search criteria: EPMDocument ID = Parameter, no rules for getting the latest.

I also tried to get over the Masters but since I don't really understand the relations between Masters and their corresponding "child", maybe it was nonsense:

FROM:

# wt.part.WTPart

# wt.part.WTPartMaster

# wt.epm.EPMDocument

# wt.epm.EPMDocumentMaster

# wt.epm.build.EPMBuildRule

JOIN:

# wt.epm.EPMDocument <---> Master Reference <---> wt.epm.EPMDocumentMaster

# wt.part.WTPart <---> Master Reference <---> wt.part.WTPartMaster

# wt.epm.build.EPMBuildRule <---> Role AObject Ref <---> wt.epm.EPMDocumentMaster

# wt.epm.build.EPMBuildRule <---> Role BObject Ref <---> wt.part.WTPartMaster

SELECT:

# Part ID

CRITERIA:

# AND: EPMDocument ID = <EPMDocID Parameter>

This ends in no results.

What I am actually trying to do:

Start the Creo View Session from Excel by putting in a EPMDocument Number and selecting the Version as additional criteria.

For this purpose I need at least the (default) Derived Image ID from wt.representation.representation.

For drawings I can just use the Representations Reference link to figure that ID out.

JOIN:

# wt.representation.Representation <---> Representable Reference <---> wt.epm.EPMDocument

But for 3D models I have to go over the WTPart ID because the 3D Model's Derived Image ID is linked to the WTPart.

In your attached report you also used the branch ID and the master ID. What are these IDs for?

I am sorry, I am really at the beginning of understanding this.

Thanks & regards,

David

[towen]

Hi David,

I'll try and explain from my understanding, I am largely self taught, with some programming experience and a few years experience working with Query Builder - there is not that much documentation out there to get help from! So apologies if something is unclear.

There are three main IDs, as you have listed:

Master Object ID: The unique ID for the EPM Document Master i.e. the whole EPM Document, encompassing all versions, etc.

Branch ID: The unique ID for the EPM Document Revision i.e. a specific revision of the EPM Document

Object ID: The unique ID for the EPM Document i.e. a specific iteration of the EPM Document

My criteria (excluding project iterations) retrieve the Latest Iteration of the largest Branch ID (defined in the Sub Select of the previously attached query), which results in only the very latest Iteration of the EPM Document/WTPart I need.

I believe your query didn't work for a two reasons:

• You were attempting to ID an EPM Document Master by it's iteration Object ID - these values are different.
• More critically, the link between EPM Documents and WTParts does not apply to EPM Document Master and WTPart Master.

I've attached a query that returns the WTPart Object ID based on the EPM Document Object ID, Version and Iteration. It's a bit more complex mainly because the link built in the EPM Build Rule does not reference one to one between EPM and WTPart iterations, to get round this:

• I join the EPM Document to the WTPart using the EPM Build Rule
• I join the WTPart object to the WTPart Master with a Master reference
• I join the WTPart Master to another WTPart object with another Master reference

Doing that returns all WTPart iterations from one EPM Document Object ID.

Assuming the WTPart version and iteration is the same in the EPM Document and WTPart, the report then uses those values to identify which WTPart ID to return.

You may be able to do this slightly neater depending on your input criteria though!

Tristan

[towen]

Hi David,

It's also possible to skip the required entry of Revision and Iteration and set the criteria so that the second WTPart Revision and Iteration are equal to the EPM document Revision and Iteration.

If I understand correctly, that gives you a report that reads in the EPM Object ID and returns the WTPart Object ID related, which is what is required for your excel.

Hope that helps!

Tristan

[Spiff]

Hi Tristan,

thank you very much for your explanations! It's more clear now.

The report you attached works! But:

I found out that not all WTParts seem to be connected to the EPMDocument (or vice versa) through the EPMBuildRule.

I was almost finished with my tests and everything seemed to work fine and then I accidentally found an EPMDocument which did not give out its WTPart Object ID.

Do you know the Visualization Debug page?

http://(server.com)/Windchill/wtcore/jsp/wvs/listcontent.jsp?oid=OR:wt.epm.EPMDocument:(EPMDocObjID)

You can also analyze WTPart Object IDs, Derived Image IDs and so on.

This page gives back all the informations I need - but I don't know how to build the equivalent reports.
Within this page the right WTPart ID of the EPMDocument is shown! Maybe there is another way instead of the EPMBuildRule?

With your last post you gave the right hint since I could just bypass the problem by searching for 3D models directly through the WTPart Number without starting from the EPMDocument because the search criterias (iteration, revision, number,...) should always be the same within our (or everybody's?) environment. I will try that now. Nevertheless it would be interesting why the EPMBuildRule does not work on some specific EPMDocument Object IDs.

Regards,

David

[NetOnline]

Hello!

You also have to look for the EPMBuildHistory in same way as the EPMBuildRule..

Regards,

Benjamin