AnsweredAssumed Answered

How to access part/assembly files Data Cards using SQL

Question asked by Sameer Kelkar on Sep 4, 2020
Latest reply on Sep 8, 2020 by Sameer Kelkar

Like • Show 0 Likes0
Comment • 4

Hi,

I needed to extract BOM in excel format using SQL. I refereed Queying SQL Database For Return All Parts In A Vault and it is super-useful. But it gives me only Parent and Child filenames in the output.

We use "Data-card" to populate meta information (e.g. Item Code) pertaining to each assembly and part file which is useful for our ERP. I need to augment above query to include Item code from the data card for each file in a separate column.

I could not figure out how to access data card information per file. How to go about that? Any help is greatly appreciated.

Thanks

Sameer Kelkar

Wayne Matus Sep 4, 2020 11:33 AM

Correct Answer

Here is something that should help you out. It gets the Description variable for all files for @ configuration.

DECLARE @VariableName nvarchar(255)
DECLARE @ConfigName nvarchar(255)
SET @VariableName = 'Description'
SET @ConfigName = '@'
DECLARE @VariableID Int
DECLARE @ConfigID Int
SET @VariableID = (SELECT VariableID FROM Variable WHERE VariableName = @VariableName)
SET @ConfigID = (SELECT ConfigurationID FROM DocumentConfiguration WHERE ConfigurationName = @ConfigName)

SELECT d.Filename, @VariableName AS [Variable Name], @ConfigName AS [Configuration Name]
, vv.ValueText AS [Variable Value], d.LatestRevisionNo AS [Latest Version]
FROM VariableValue AS vv
JOIN Documents AS d ON vv.DocumentID = d.DocumentID
WHERE vv.VariableID = @VariableID
AND vv.ConfigurationID = @ConfigID
AND vv.RevisionNo = (SELECT MAX(vv1.RevisionNo) FROM VariableValue AS vv1
WHERE vv1.DocumentID = vv.DocumentID AND vv1.VariableID = @VariableID AND vv1.ConfigurationID = @ConfigID)

See the reply in context

No one else had this question

Outcomes

Wayne Matus Sep 4, 2020 9:09 AM

That is tricky for a novice. The values are stored in the VariableValue table. The Variable table is the related to get the VariableID for the variable by it's name. The variable values can be configuration specific, so you also need to identify what configuration you want the value for. The DocumentConfiguration table is the related table to get the ConfigurationID by it's name. Finally, the VariableValue table does not have a record for each version of the file, it only has record for changes. The RevisionNo column in the VariableValue table is the version number of the file where that variable changed it's value for that configuration for the file.

Actions

Sameer Kelkar @ Wayne Matus on Sep 4, 2020 9:50 AM

Thanks Wayne for the quick reply. I will try this out.

Actions

Wayne Matus @ Sameer Kelkar on Sep 4, 2020 11:33 AM

Here is something that should help you out. It gets the Description variable for all files for @ configuration.

Actions

Sameer Kelkar @ Wayne Matus on Sep 8, 2020 1:05 AM

Thanks Wayne. This works perfectly for me.

Actions

4 Replies

Wayne Matus Sep 4, 2020 9:09 AM
Mark Correct
Correct Answer
That is tricky for a novice. The values are stored in the VariableValue table. The Variable table is the related to get the VariableID for the variable by it's name. The variable values can be configuration specific, so you also need to identify what configuration you want the value for. The DocumentConfiguration table is the related table to get the ConfigurationID by it's name. Finally, the VariableValue table does not have a record for each version of the file, it only has record for changes. The RevisionNo column in the VariableValue table is the version number of the file where that variable changed it's value for that configuration for the file.
Like • Show 0 Likes0
Actions
- Sameer Kelkar @ Wayne Matus on Sep 4, 2020 9:50 AM
  Mark Correct
  Correct Answer
  Thanks Wayne for the quick reply. I will try this out.
  Like • Show 0 Likes0
  Actions
  - Wayne Matus @ Sameer Kelkar on Sep 4, 2020 11:33 AM
    Unmark Correct
    Correct Answer
    Here is something that should help you out. It gets the Description variable for all files for @ configuration.
    
    DECLARE @VariableName nvarchar(255)
    DECLARE @ConfigName nvarchar(255)
    SET @VariableName = 'Description'
    SET @ConfigName = '@'
    DECLARE @VariableID Int
    DECLARE @ConfigID Int
    SET @VariableID = (SELECT VariableID FROM Variable WHERE VariableName = @VariableName)
    SET @ConfigID = (SELECT ConfigurationID FROM DocumentConfiguration WHERE ConfigurationName = @ConfigName)
    SELECT d.Filename, @VariableName AS [Variable Name], @ConfigName AS [Configuration Name]
    , vv.ValueText AS [Variable Value], d.LatestRevisionNo AS [Latest Version]
    FROM VariableValue AS vv
    JOIN Documents AS d ON vv.DocumentID = d.DocumentID
    WHERE vv.VariableID = @VariableID
    AND vv.ConfigurationID = @ConfigID
    AND vv.RevisionNo = (SELECT MAX(vv1.RevisionNo) FROM VariableValue AS vv1
    WHERE vv1.DocumentID = vv.DocumentID AND vv1.VariableID = @VariableID AND vv1.ConfigurationID = @ConfigID)
    Like • Show 0 Likes0
    Actions
    - Sameer Kelkar @ Wayne Matus on Sep 8, 2020 1:05 AM
      Mark Correct
      Correct Answer
      Thanks Wayne. This works perfectly for me.
      Like • Show 0 Likes0
      Actions

How to access part/assembly files Data Cards using SQL

Outcomes

Related Content

Recommended Content