4 Replies Latest reply on Feb 21, 2013 12:41 PM by Charley Saint

    Sql statement that returns current document info including description

    Lynn Alger

      I have been trying to figure out the tables in ePDM, so I can create queries (in access) for automated nightly reporting of checked out documents, documents not in released states... and i have been struggling with figuring out the joining of the appropriate tables to get one query with the current document information including its description.

       

      This gets me most of the information I am looking for but not the description:

      SELECT dbo_Documents.DocumentID, "\\file\d$\ePDM_Vault\CORTEC_ePDM" & "\" & Right(Hex([dbo_Documents].[DocumentID]),1) & "\" & Right("00000000" & Hex([dbo_Documents].[DocumentID]),8) & "\" & Right("00000000" & ([dbo_Documents].[LatestRevisionNo]),8) & Mid([FileName],InStr([FileName],"."),99) AS FilePath, Right("00000000" & ([dbo_Documents].[LatestRevisionNo]),8) AS Expr1, dbo_Projects.*, dbo_DocumentsInProjects.Deleted, dbo_Documents.*, dbo_Status.Name AS [File Status], dbo_Users.Username, dbo_Users.FullName, dbo_Users.Email

      FROM (((dbo_Documents INNER JOIN dbo_DocumentsInProjects ON dbo_Documents.DocumentID = dbo_DocumentsInProjects.DocumentID) INNER JOIN dbo_Projects ON dbo_DocumentsInProjects.ProjectID = dbo_Projects.ProjectID) LEFT JOIN dbo_Status ON dbo_Documents.CurrentStatusID = dbo_Status.StatusID) INNER JOIN dbo_Users ON dbo_Documents.UserID = dbo_Users.UserID;

       

      We use Solidworks configurations and I tried using this function to get the description:

       

      select dbo.acFindAnyVariableValueBOM(N'Description', 6506, 12)

       

      But am unsure how to combine what i have done with the above code.

       

      I guess what I am looking for is to create a view in sql to display the latest document information with the current description.

       

      Any help would be greatly appreciated.

       

      Lynn A.