20 Replies Latest reply on Jun 25, 2013 5:36 PM by Brent Fanguy

    EPdm Relative path to files in the Vault SQL Statement with versions referencing other versions

    Brent Fanguy

      I have a document ID of 28490, i am trying to create a hyperlink to the actual vault file (mathcad won't allow files linked to the vault - I have to give it the actual file path)

       

      I created the following sql statement:

       

      SELECT   d.DocumentID, d.Filename, d.LatestRevisionNo,

       

                     '\\server\epdm_vault$\ePDM\' + RIGHT(sys.fn_varbintohexsubstring(0, CONVERT(binary(4), d.DocumentID), 1, 0), 1) + '\' + sys.fn_varbintohexsubstring(0, CONVERT(binary(4), d.DocumentID), 1, 0) + '\' + RIGHT(sys.fn_varbintohexsubstring(0, CONVERT(binary(4), d.LatestRevisionNo), 1, 0), 8) + '.' + fe.Extension AS Vault_Path

       

      FROM      dbo.Documents AS d LEFT OUTER JOIN  dbo.FileExtension AS fe ON d.ExtensionID = fe.ExtensionID

      WHERE    DocumentID = 28490

       

      DocumentID Filename                 LatestRevisionNo            Vault_Path

      28490          CDF_GEO_05.xmcd             3                        \\server\epdm_vault$\ePDM\a\00006f4a\00000003.xmcd

       

      This works sometimes.

       

      When it does not work, I open the html file within the folder path and if there is a ref="x" in the file, it does not work.

      If there are no ref="X" the link works.

       

      In this example there are only two files in this directory, the Index.xml and my file with file name of 00000001xmcd.  The actual contents of the xml index file is as shown below:

       

      <?xml version="1.0" ?>

      - <index version="9.0"> 

      <filename>YT4298-NIPPLE(5,C).xmcd</filename> 

      <version id="1" uid="177087" date="2012-01-31 12:47:34" />

      <version id="2" ref="1" uid="177088" date="2012-01-31 12:47:36" />

      <version id="3" ref="1" date="2012-01-31 12:47:38" />

      </index>

       

      What I would like to know, is there a way to find the ref= information in the database?

      i.e. what would i have to change in my sql statement to point to the referenced version id?

       

      Thank you in advance.