hot to get vaultFileName of Family Table

[Gucio]

Hi All,

How to create query to get vaultFileName of FT?? 
There are articles on PTC 
https://www.ptc.com/en/support/article/cs39037

https://www.ptc.com/en/support/article/cs337082

but I'm not able to create such query.

Any idea??

regards,

[HelesicPetr]

Hi @Gucio 

here is MSSQL example how to get vaultFileName with CAD Model file name

this works only for normal CAD Documents

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.HolderToContent CONTENT
 ON CONTENT.idA3A5 = EPM.idA2A2
 inner join wcuser.ApplicationData apData
 ON apData.idA2A2 = CONTENT.idA3B5
 inner join wcuser.EPMDocumentMaster MSTR
 on MSTR.idA2A2 = EPM.idA3masterReference
 inner join wcuser.FvItem FVI
 on apData.idA3A5 = FVI.idA2A2
 where MSTR.CADName like 'plech.prt' and EPM.latestiterationInfo=1

Following example shows how to get vaultFileName for FamiliTable

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='plech.prt'

Hope this can help.

PS:  the select function changes a RealNumber value stored in a database to the final FilaVaultName value that is used in a storage  

substring(convert(varchar(100),convert(varbinary(100), FVI.uniqueSequenceNumber, 256),2),3,14) vaultFileName

PetrH

[Gucio]

hi Petr,

unfortunatelly I receive "Error at Line: 1 Column: 26". But in the meantime I used bello query:

SELECT
epdm.name,
epd.versionIdA2versionInfo||'.'||epd.iterationIda2iterationInfo VERSION,
to_char(fvi.uniqueSequenceNumber,'FM0XXXXXXXXXXXXX') "VAULT FILE",
fvm.path

FROM
EPMDocumentMaster epdm,
EPMDocument epd,
HolderToContent htc,
ApplicationData ad,
FvItem fvi,
FvFolder fvf,
FvMount fvm,
EPMSepFamilyTable sft,
EPMContainedIn ci

WHERE
epdm.idA2A2=epd.idA3MasterReference
AND htc.idA3B5=ad.idA2A2
AND htc.idA3A5 = sft.idA2A2
AND epd.idA2A2 = ci.idA3A5
AND ci.idA3B5 = sft.idA2A2
AND ad.idA3A5=fvi.idA2A2
AND fvi.idA3A4=fvf.idA2A2
AND fvf.idA2A2=fvm.idA3A5
AND epdm.cadname='ahu03-3-81453.prt'

[HelesicPetr]

Hi @Gucio 

It is normal if your database has different name in my case wcuser

So you need to change all wcuser. to your databse name.

the SQL is written for MS SQL so for Oracle you need to change it to correct form.

I run the SQL script under database admin user so I always use nameDatabase.TableName in my SQL examples.

PetrH