[QML] How to create a Query Builder Report which will list the Instances associated with Generics of Family Tables

MN_9999219 · ‎Feb 01, 2023

How to find instances from familytable via SQL?

HelesicPetr · ‎Feb 01, 2023

Generally i think information is stored in a EPMSepFamilyTable

Here is example how to get filevault of familytable so if you extract the EPMSepFamilyTable you can build our select to get family instances.

 select substring(convert(varchar(100),convert(varbinary(100), FVI.uniqueSequenceNumber, 256),2),3,14) vaultFileName,apData.idA3A5 as FvItem, apData.fileName ,MSTR.documentNumber, EPM.versionIdA2versionInfo,EPM.iterationIdA2iterationInfo, MSTR.CADName from wcuser.EPMDocument EPM 
inner join wcuser.EPMDocumentMaster MSTR
 on MSTR.idA2A2 = EPM.idA3masterReference
 inner join wcuser.EPMContainedIn CONTIN
 on EPM.idA2A2 =  CONTIN.idA3A5
 inner join wcuser.EPMSepFamilyTable FT 
 on FT.idA2A2 = CONTIN.idA3B5 
 inner join  wcuser.HolderToContent CONTENT
 on CONTENT.idA3A5 = FT.idA2A2
  inner join wcuser.ApplicationData apData
 ON apData.idA2A2 = CONTENT.idA3B5
 inner join wcuser.FvItem FVI
 on apData.idA3A5 = FVI.idA2A2
 where MSTR.CADName='m5x25.prt'

PetrH

HelesicPetr · ‎Feb 01, 2023

ps> example is for MS SQL.

PetrH

HelesicPetr · ‎Feb 01, 2023

Hi @MN_9999219

Better example for MS SQL search by CAD Model name. You can use any instance or generic cadname

select mstr.CADName,* from wcuser.EPMDocument epm
inner join wcuser.EPMDocumentMaster mstr on mstr.idA2A2=epm.idA3masterReference 
inner join wcuser.EPMContainedIn contain on contain.idA3A5=epm.idA2A2
inner join wcuser.EPMSepFamilyTable sep on contain.idA3B5=sep.idA2A2
where sep.idA3masterReference in (select sep.idA3masterReference from wcuser.EPMDocument epm
inner join wcuser.EPMDocumentMaster mstr on mstr.idA2A2=epm.idA3masterReference 
inner join wcuser.EPMContainedIn contain on contain.idA3A5=epm.idA2A2
inner join wcuser.EPMSepFamilyTable sep on contain.idA3B5=sep.idA2A2
where (mstr.CADName='m5x15.prt'))

m5x15.prt is CAD fila name

PetrH

MN_9999219 · ‎Feb 01, 2023

How do I get the data for entire Familytable rather than any instance or a cadname?

HelesicPetr · ‎Feb 01, 2023

@MN_9999219

I don't understand your question

What kind of data?

How do you identify family table? number name filename what ever so use it.

by select you can define exactly what you want.

Use specific column and table identification instead of "*" asterix

PetrH

HelesicPetr · ‎Feb 01, 2023

@MN_9999219

aditional information

cadname is filename in windchill

and only generic family table has primary content with filename so if you use filename of generic family table as the CADName you will get "all" information about family table with instances.

PetrH