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!
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!