17 Replies Latest reply on Mar 17, 2017 5:25 PM by Tim Webb

    PDM Professional - Named Bill of Material in SQL

    Craig Merrifield

      Question for my fellow PDM Admins...

       

      Background:

      I am testing Named BOMs originating from Computed BOMs and originating from Item Structures. I've determined that they are stored in different folder structures in PDM (File Folder structure vs. Item Folder structure).

       

      Question:

      Does anyone know which SQL tables contain both Named BOMs indicated above? I'm database literate, but I'm a beginner with SQL and just starting my investigation for extracting this information. Any tips or information are greatly appreciated.

       

      Reason for asking:

      Named BOMs will likely be used to populate information in our ERP system and I'll eventually need to produce automated extracts for this purpose.

       

      Many thanks,

       

      Craig Merrifield

      aka - Merf

        • Re: PDM Professional - Named Bill of Material in SQL
          Tim Webb

          Hey Merf!

           

          Good to see you. I've fought with this one before and concluded the Named BOMs are not in any one table but actually assembled by a stored procedure at calculation time. Check out dbo.BomListProc# where # is not the same from vault to vault. It depends on a few factors. My dbo.BomListProc3 has like 8 parameters PLUS this SP outputs 3-4 datasets (can't remember).

           

          This has been covered on the forums before, you could probably learn more from Kent...? Can't remember his name.

           

          Hope this gets you going!

          Tim CEPA

          Believe in The Q!

          • Re: PDM Professional - Named Bill of Material in SQL
            Tim Webb

            Check out this thread by Christina Seay

            https://forum.solidworks.com/message/244900#comment-244900

             

            It provides guidelines and there is a lot of discussion there.

             

            Hope this gets you going!

            Tim CEPA

            Believe in The Q!

              • Re: PDM Professional - Named Bill of Material in SQL
                Craig Merrifield

                Hey Tim!

                 

                Always happy to see your name pop up on posts! ( I know good information is sure to follow...)

                 

                Thanks for the link and information above! I did some initial review, but will need to dig deeper to really absorb the content (I'm not quite where I want to be with SQL or the PDM API yet).

                 

                I reached out to my VAR earlier as well and they responded as follows:

                "To answer your question, A named BOM is a separate document with a DocumentID which is contained in the 'Documents' table.
                Information about a named BOMs is stored in the tables 'BomsInProjects' and ‘BomSheets’ (where, for named BOMs, ‘BOMType’ column = 2)."

                (So I apparently have more research to do...lol)

                 

                I'm trying to wrap my head around this...

                • I can understand where a Computed BOM is assembled (by a stored procedure...)
                • I believe that the Named BOM is stored as a file, in which the contents are based on a snapshot of the Computed BOM

                What I'm struggling with is:

                Are the "snapshot" contents of the saved Named BOM computed on demand by a stored procedure? Or are the contents stored and readily available in the SQL table(s)? Or are the contents stored only inside the file (if so, how do you access that)?

                 

                Thanks for any insight you can provide!

                  • Re: PDM Professional - Named Bill of Material in SQL
                    Tim Webb

                    Hey Craig,

                    Thanks, I learned something new today! I'll dig into the SQL server and see what I can find. There are a few named BOMs in my own vault and there are entries in BOMSInProject. I'll see what I can dig up bro.

                     

                    Tim CEPA
                    Believe in The Q!

                    • Re: PDM Professional - Named Bill of Material in SQL
                      Tim Webb

                      It's actually ObjectTypeID 3 in my vault.

                      select object type.png

                       

                      *I'm working on this today! This piqued my interest Craig... Thanks.

                       

                      Tim CEPA

                      Believe in The Q!

                        • Re: PDM Professional - Named Bill of Material in SQL
                          Craig Merrifield

                          Hey Tim,

                           

                          Awesome! Thanks for the lead. I've been picking around in a few tables as well, but have not had enough time to dig in yet.

                            • Re: PDM Professional - Named Bill of Material in SQL
                              Tim Webb

                              This...is deep. But I did get it working with hard coded values so it needs some cleaning up to make it work in your vault with your BOM columns. My BOM has the fields:

                              file name, configuration, part number, owner, quantity, state, description, and revision

                               

                              USE <your vault name>
                              
                              IF OBJECT_ID('tempdb..#TempValues') IS NOT NULL DROP TABLE #TempValues
                              IF OBJECT_ID('tempdb..#NamedBOM') IS NOT NULL DROP TABLE #NamedBOM
                              
                              declare @filename nvarchar(256)
                              set @filename = '<your named bom sldasm.1.bom>'
                              
                              declare @currentrow tinyint = 0,
                                @currentcol tinyint = 0,
                                @rowno int,
                                @colno int,
                                @TotalRows int,
                                @TotalColumns int,
                                @col0 nvarchar(4000),
                                @col1 nvarchar(4000),
                                @col2 nvarchar(4000),
                                @col3 nvarchar(4000),
                                @col4 nvarchar(4000),
                                @col5 nvarchar(4000),
                                @col6 nvarchar(4000),
                                @col7 nvarchar(4000)
                              
                              /*Make sure these vars are filtered to your named BOM otherwise it returns the complete total*/
                              select @TotalColumns = max(ColumnNo)
                              from BomSheetValue
                              where BomDocumentID like
                                (
                                select d.documentID
                                from documents d
                                where d.filename like @filename
                                and d.ObjectTypeID like 3
                                and d.deleted = 0
                                )
                              
                              /*Make sure these vars are filtered to your named BOM otherwise it returns the complete total*/
                              select @TotalRows = max(RowNo)
                              from BomSheetValue
                              where BomDocumentID like
                                (
                                select d.documentID
                                from documents d
                                where d.filename like @filename
                                and d.ObjectTypeID like 3
                                and d.deleted = 0
                                )
                              
                              print '@TotalRows = ' + cast(@TotalRows as nvarchar)
                              print '@TotalColumns = ' + cast(@TotalColumns as nvarchar)
                              
                              /*Select into ... will create a new table and inherit the data types of the incoming data from the select statement*/
                              select bsc.ColumnName, bsv.RowNo, bsv.ColumnNo, bsv.ValueText
                              into #TempValues 
                              from BomSheetColumn bsc
                              inner join
                              (
                                select bsv2.BomDocumentID, bsv2.RowNo, bsv2.ColumnNo, bsv2.ValueText
                                from BomSheetValue bsv2
                                where BomDocumentID like
                                (
                                select d.documentID
                                from documents d
                                where d.filename like @filename
                                and d.ObjectTypeID like 3
                                and d.deleted = 0
                                )
                              ) bsv on bsv.ColumnNo = bsc.ColumnNo
                              where bsc.BomDocumentID =
                              (
                                select d.documentID
                                from documents d
                                where d.filename like @filename
                                and d.ObjectTypeID like 3
                                and d.deleted = 0
                              )
                              
                              --select * from #TempValues
                              
                              /* Columns are hard coded in this query for now...need to update it to dynamic sql to adjust for various numbers of columns*/
                              create table #NamedBOM(filename nvarchar(256), configuration nvarchar(256), partnumber nvarchar(256), owner nvarchar(256), quantity nvarchar(256), state nvarchar(256), description nvarchar(256), revision nvarchar(256))
                              while (@currentrow < @TotalRows)
                                begin
                                select @col0 = (select ValueText from #TempValues where RowNo = @currentrow and ColumnNo = 0)
                                select @col1 = (select ValueText from #TempValues where RowNo = @currentrow and ColumnNo = 1)
                                select @col2 = (select ValueText from #TempValues where RowNo = @currentrow and ColumnNo = 2)
                                select @col3 = (select ValueText from #TempValues where RowNo = @currentrow and ColumnNo = 3)
                                select @col4 = (select ValueText from #TempValues where RowNo = @currentrow and ColumnNo = 4)
                                select @col5 = (select ValueText from #TempValues where RowNo = @currentrow and ColumnNo = 5)
                                select @col6 = (select ValueText from #TempValues where RowNo = @currentrow and ColumnNo = 6)
                                select @col7 = (select ValueText from #TempValues where RowNo = @currentrow and ColumnNo = 7)
                                insert into #NamedBOM(filename, configuration, partnumber, owner, quantity, state, description, revision)
                                values (@col0, @col1, @col2, @col3, @col4, @col5, @col6, @col7)
                                set @currentrow = @currentrow + 1
                                end
                              
                              select * from #NamedBOM
                              

                               

                              This is provided as-is without warranty, express or implied. Use at your own risk.

                              ...but I hope it helps you Craig Merrifield

                              Tim CEPA

                              Believe in The Q!