Page 1 of 1

BOM Summary Excel export

Posted: 20 Jan 2022, 21:51
by gazmoz17
Hi Bruce,

Been trying to do something for hours and hours!...should of just watched the footy!

Current report works summarizing when makeup cost is not equal to the assembly sku's last cost price standard.

However, I wish to add the sub components into my report (Col H:R). I can return the component codes and component qty. However, codes are a bit meaningless I want to return the component descriptions? So I can check quickly line by line that all the BOMs have been mapped correctly. My colleague was going to check the mapping tomorrow so thought would be easier on an excel with one line per each assembly. I can make my report work where the components are pulling through component descriptions, but they fall below each assembly across multiple lines. So it slows down scanning/checking and manipulating the data filtering on my assembly cols in excel e.g. Supplier.

Col A:F is my assembly fields.

Could get around it manipulating my excel by adding helper columns next to my component codes then doing a lookup but its a bit messyeven if make an excel vba macro try and speed it up.

Or is this best done that OBDC method you shown me in the past?
BOM Summary.xlsx
Many Thanks

Re: BOM Summary Excel export

Posted: 21 Jan 2022, 11:37
by brucedenney
To add the assembly descriptions, you need to link the STOCK_COMP.ASSEMBLY_CODE to a second copy of the Stock Table.

When you add the second copy of the table, you can give it an alias eg Assembly this is the description you need to use in the report to show the component description.

If you look at the Joins in the Product Explosion Report in Product Explosion and BOM group in the Stock Reports you will see how it is done.


PS If you want the data to be in excel at the end, then I would do it in Excel using ODBC at the start. (It is the same method in MS Query, you add the stock table in a second time and link Stock_Code to the Assembly_Code)

Re: BOM Summary Excel export

Posted: 09 Feb 2022, 11:07
by gazmoz17
Thats great thanks for this Bruce :D