5 Replies Latest reply on Dec 11, 2018 11:04 AM by Rodney Martin

    Using SQL Tables to get BOM

    Rodney Martin



      I have been doing some really cool stuff in Powerapps to consolodate data across our company.  I would like a little boost on this next function... I want to create a BOM from the latest released/approved assembly, just from using the SQL tables.  I have had great success using the tables for extracting other information, at this point, I am ready to attempt this.


      I am asking:


      Which tables I need.

      Any boost in the SQL selects & joins to set this up.


      Thank you!

        • Re: Using SQL Tables to get BOM
          Alex Burnett

          The closest I can get to helping is to provide you with the path to an example Report Generator file. I don't know what you need to do to connect to the SQL tables directly or if the tables are similarly named but this may give you a good start.


          C:\Program Files\SolidWorks Enterprise PDM

          Look for "Report Examples 1.crp" and edit with notepad to get the examples and syntax.


          This is intended to be used with the built in report generator.

            • Re: Using SQL Tables to get BOM
              Rodney Martin

              Yes, i'm deep into this.  I have been using Powerapps and using the SQL connectors (read only, of course!) to pull in data.  This has done some really great things for our ability to do change management.  I was wanting to build BOMs from the data directly as well.  Not using report generator, etc.

            • Re: Using SQL Tables to get BOM
              Rodney Martin

              For those that may be using PowerApps or similar to make APIs, I accomplished getting BOMs extracted from PDM by the following use of SQL coupled with Powerapps:


              SELECT DISTINCT

                                       TOP (100) PERCENT BomSheetValue_1.ValueText AS PartNo, BomSheetValue_2.ValueText AS Qty, BomSheetValue_1.RowNo, SWPDM_KelsoDrawingVault.dbo.BomSheets.RefDocumentID,

                                       BomSheetValue_3.ValueText AS DESCRIPTION, BomSheetValue_1.RowNo + 1 AS ItemNo, SWPDM_KelsoDrawingVault.dbo.Documents.DocumentID

              FROM            SWPDM_KelsoDrawingVault.dbo.BomSheets INNER JOIN

                                       SWPDM_KelsoDrawingVault.dbo.BomSheetValue AS BomSheetValue_3 ON SWPDM_KelsoDrawingVault.dbo.BomSheets.BomDocumentID = BomSheetValue_3.BomDocumentID INNER JOIN

                                       SWPDM_KelsoDrawingVault.dbo.BomSheetValue AS BomSheetValue_2 ON SWPDM_KelsoDrawingVault.dbo.BomSheets.BomDocumentID = BomSheetValue_2.BomDocumentID AND

                                       BomSheetValue_3.RowNo = BomSheetValue_2.RowNo INNER JOIN

                                       SWPDM_KelsoDrawingVault.dbo.BomSheetValue AS BomSheetValue_1 ON SWPDM_KelsoDrawingVault.dbo.BomSheets.BomDocumentID = BomSheetValue_1.BomDocumentID AND

                                       BomSheetValue_2.RowNo = BomSheetValue_1.RowNo INNER JOIN

                                       SWPDM_KelsoDrawingVault.dbo.Documents ON SWPDM_KelsoDrawingVault.dbo.BomSheets.RefDocumentID = SWPDM_KelsoDrawingVault.dbo.Documents.DocumentID AND

                                       SWPDM_KelsoDrawingVault.dbo.BomSheets.SourceDocumentVersion = SWPDM_KelsoDrawingVault.dbo.Documents.LatestRevisionNo

              WHERE        (BomSheetValue_1.ColumnNo = 1) AND (BomSheetValue_2.ColumnNo = 4) AND (BomSheetValue_3.ColumnNo = 2) AND (SWPDM_KelsoDrawingVault.dbo.Documents.Deleted = 0)

              ORDER BY SWPDM_KelsoDrawingVault.dbo.Documents.DocumentID, BomSheetValue_1.RowNo


              Then, in powerapps, Collecting your data via a gallery, and filtering as such:




              Where Latest_DocumentID_1.Text is the DocumentID from dbo.Documents of the slddrw of the assembly you wish to see the BOM.


              This is a cryptic message, but if you are looking for this, you will know what it means.


              Good luck!