10 Replies Latest reply on Jul 26, 2018 10:46 AM by Tim Webb

    Using SQL Query Tables to get Descriptions

    Rodney Martin

      Friends,

       

      I have successfully logged into my SQL server from excel, and I have a list of the SW SQL tables.

      Let me start with what I am trying to accomplish...

       

      I want to be able to pull part parameter information into applications directly from the SW database.  For instance, via ODBE, I would like to type in a part number (one parameter) and the part description, and revision get pulled into the application automatically.  I want to do this first with excel.  Then, I am planning to develop API or use access as an intermediary, to do this data pull.  Any recommendations?

       

      Thank you,

      Rod

          • Re: Using SQL Query Tables to get Descriptions
            Rodney Martin

            Might be a possibility.  I do have some lower level programming capabilities.  I would like to see what I can do myself with SQL vie ODBE links to Microsoft products at least to start with.  Not sure what CADLink costs, but we are really limited by budget at this point.  I was hoping to develop something simple myself for now.  Thanks!

              • Re: Using SQL Query Tables to get Descriptions
                Tim Webb

                Engineers - The only college educated professionals who never have budget to buy a new product but have tons of labor budgeted to reinvent the wheel...

                 

                No offense to you Rodney, at all. honestly. You've got zeal and ideas! But your comment reminded me of something my boss sent me 7-8 years ago when I was learning to program in the API.

                 

                Seriously, consider CADLink

                 

                Believe in The Q!

                Tim CEPA

                  • Re: Using SQL Query Tables to get Descriptions
                    Rodney Martin

                    This I am sure CAD link is a great tool.  What I didn't mention is that I am putting together a three phase transition to a fully managed end-to-end system for our business, and what I'm trying to come up with is a stop-gap measure to keep us afloat until our business is mature enough financially.  I am pushing to have E2 as our PLM/ERP.  That product, so I'm told, will reach right into solidworks and pull what is necessary.  Maybe CADLink will be reuired to get us by, but I want to give it the ole college try first with ODBE and tables.

                     

                    Thanks!!

                     

                    Rod

              • Re: Using SQL Query Tables to get Descriptions
                Tim Webb

                Rodney, now that we've all had a good laugh. Your spirit is the same one I had 10 years ago when I formed my PDM consulting business so I get it. You want to learn. Good for you.

                 

                Try the following:

                 

                1. Create your data connection in Excel
                2. Use the code below in your query. Change the database name to your PDM database name on line 5. This is from the SW knowledgebase solution S-059164
                3. It queries all the variables in the list on line 14 per configuration
                4. Change the file filter to the filetypes you want to search for in line 16
                5. Bring the data into a sheet called "PDMData" or something similar in your workbook
                6. Use that PDMData sheet as the data source for a Pivot chart or Pivot table
                ------------------------------------------------------------------------------------------
                -- SQL script to show the latest values of mutiple variables by file and configuration
                -- This version displays each variable value in separate columns
                ------------------------------------------------------------------------------------------
                USE YourPDMDatabase
                IF OBJECT_ID('tempdb..#VariableList') IS NOT NULL DROP TABLE #VariableList
                IF OBJECT_ID('tempdb..#TempVariables') IS NOT NULL DROP TABLE #TempVariables
                
                
                DECLARE @VariableList NVARCHAR(255)
                DECLARE @FileFilter NVARCHAR(255)
                ---------------------------------------------------------------------------------------
                -- Alter the line below with the names of the variables in [] that you want to include
                SET @VariableList='[Part Number], [Description]'
                -- Alter the line below with a path/filename filter if required
                SET @FileFilter='%'
                ---------------------------------------------------------------------------------------
                
                
                DECLARE @SQLScript NVARCHAR(Max)
                
                
                CREATE TABLE #VariableList (VarID INT)
                SET @SQLScript='INSERT INTO #VariableList
                SELECT VariableID
                FROM Variable
                WHERE VariableName IN (' + REPLACE(REPLACE(@VariableList,'[',''''),']','''') + ')'
                
                
                EXEC sp_executesql @SQLScript
                
                
                CREATE TABLE #TempVariables( DocID INT, ConfigurationName NVARCHAR(255), VariableName NVARCHAR(255), ValueText NVARCHAR(Max) )
                INSERT INTO #TempVariables (DocID, ConfigurationName, VariableName, ValueText)
                SELECT DocumentID, ConfigurationName, VariableName, ValueText
                FROM VariableValue VV
                INNER JOIN
                (SELECT DocumentID AS DocID, ConfigurationID AS ConfID, VariableID AS VarID, MAX(RevisionNo) AS RevMax
                FROM VariableValue
                JOIN #VariableList ON #VariableList.VarID=VariableValue.VariableID
                GROUP BY DocumentID, ConfigurationID, VariableID) T
                ON VV.DocumentID=T.DocID
                AND VV.ConfigurationID=T.ConfID
                AND VV.VariableID=T.VarID
                AND VV.RevisionNo=T.RevMax
                INNER JOIN DocumentConfiguration
                ON DocumentConfiguration.ConfigurationID=T.ConfID
                INNER JOIN Variable
                ON Variable.VariableID=T.VarID
                where vv.ConfigurationID = (select Revisions.ActiveConfigurationID from Revisions
                where Revisions.RevNr = (Select Max(Revisions.RevNr) From Revisions
                Where Revisions.DocumentID = vv.DocumentID)
                and Revisions.DocumentID=vv.DocumentID
                )
                
                
                SET @SQLScript='SELECT FileName, ConfigurationName, ' + @VariableList + '
                FROM
                (
                SELECT Path + Filename AS FileName, ConfigurationName, VariableName, ValueText
                FROM Documents
                INNER JOIN
                (SELECT DocumentsInProjects.DocumentID, Projects.Path AS Path
                  FROM DocumentsInProjects LEFT JOIN Projects ON Projects.ProjectID=DocumentsInProjects.ProjectID) AS P
                ON P.DocumentID=Documents.DocumentID
                INNER JOIN
                #TempVariables T
                ON T.DocID=Documents.DocumentID
                WHERE Path+FileName LIKE ''' + @FileFilter +
                ''') AS SourceTable
                
                
                PIVOT
                (
                MAX(ValueText)
                FOR VariableName IN
                (' + @VariableList + ')
                ) AS PivotTable
                ORDER BY FileName, ConfigurationName'
                
                
                EXEC sp_executesql @SQLScript
                
                
                DROP TABLE #VariableList
                DROP TABLE #TempVariables
                
                  • Re: Using SQL Query Tables to get Descriptions
                    Rodney Martin

                    Nice! I will give this a go.  Perhaps it will suffice until we get a grown up PLM.

                      • Re: Using SQL Query Tables to get Descriptions
                        Tim Webb

                        Circle back when you get it setup and let us know how it goes.

                         

                        Believe in The Q!

                        Tim CEPA

                          • Re: Using SQL Query Tables to get Descriptions
                            Rodney Martin

                            Its been a while, but I thought I'd come back and report what I ended up doing.  I ended up using Powerapps to create an app for writing change control documentation against our revisions.  I created a view in SQL, to pull in data from the SW tables.  I then did lookups from my Powerapps app to get the info I needed.  The SQL View I created is this:

                             

                            SELECT   TOP (100) PERCENT SWPDM_KelsoDrawingVault.dbo.Documents.DocumentID, SWPDM_DrawingVault.dbo.Documents.ExtensionID, SWPDM_DrawingVault.dbo.VariableValue.ValueText, SWPDM_DrawingVault.dbo.VariableValue.VariableID, SWPDM_DrawingVault.dbo.VariableValue.RevisionNo,

                                         SWPDM_DrawingVault.dbo.Documents.Filename, SWPDM_DrawingVault.dbo.Documents.Deleted

                            FROM     SWPDM_DrawingVault.dbo.Documents INNER JOIN

                                         SWPDM_DrawingVault.dbo.VariableValue ON SWPDM_DrawingVault.dbo.Documents.DocumentID = SWPDM_DrawingVault.dbo.VariableValue.DocumentID

                            WHERE   (SWPDM_DrawingVault.dbo.Documents.ExtensionID = 3) AND (SWPDM_DrawingVault.dbo.VariableValue.VariableID = 54) AND (SWPDM_DrawingVault.dbo.Documents.Deleted = 0)

                            ORDER BY SWPDM_DrawingVault.dbo.Documents.DocumentID DESC, SWPDM_DrawingVault.dbo.VariableValue.RevisionNo DESC

                             

                            The goal with this table was to get the latest, not deleted, DocumentID number for the drawing file.  Since every part number has its own drawing, this ensures one part number and one valid DocumentID for it.  With that doument ID, I can now do lookups into other tables via Powerapps data connectors to SQL.  I was able to extract part name, drawing revision, material, and other parameters live from the tables. 

                             

                            Ive learned much from this exercise.  It was well worth the effort, because using Powerapps has eliminated the need for connecting APIs, becuase it is in essense an API itself, and is capable of connections to countless sources, sending notifications via MS Flow, etc.  We did have to commit to a higher level of the Powerapp service, which I believe is $9.99/mo.  That is the only investment on top of what we already have available with our Office cloud environment.  I welcome questions if anyone else wants to venture into this area.