Friends,
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!
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.
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.
I was trying to point your toward that because it does use SQL to query the database. The format of the queries in that example file may point you in the right direction to get a BOM from the table data. I just don't know what table(s) to connect to in order to be able to use the variables from these queries.
Last time I researched this, there was very little documentation on the subject so it may take a lot of guess work.
I will just plow into it then. THat is the way I have gone up to this point. I was hoping to get some of the SQL hints up front this time. Thanks!
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:
SortByColumns(Filter('[dbo].[VIEW_TEST_SOLIDWORKS_BOM]',DocumentID=Value(Latest_DocumentID_1.Text)),"ItemNo",Ascending)
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!
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:
SortByColumns(Filter('[dbo].[VIEW_TEST_SOLIDWORKS_BOM]',DocumentID=Value(Latest_DocumentID_1.Text)),"ItemNo",Ascending)
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!