I am making a custom BOM for my company, how do I add a sequential item number to the BOM? I have it setup where it's seperating line items by part number and including mulptiple ref des "marks" per part.
SELECT--Row numbers are applied to aggregated table here. ROW_NUMBER() OVER (ORDER BY itembom.PartNumber DESC) as ItemNo, itembom.Quantity as QTY, itembom.UoM as UM, itembom.PartNumber as PartNumber, itembom.Description as Description, itembom.AlternatePart as AlternatePart, itembom.PartNotes as PartNotes, itembom.Book as Book
FROM(--Master table is aggregated in this selection SELECT
COUNT(BOM.bom_reference) AS Quantity , BOM.use_data1 as UoM , BOM.use_data0 as CageCode , BOM.bom_reference AS PartNumber , BOM.tra_0 AS Description , BOM.use_data3 as AlternatePart , BOM.use_data4 as PartNotes , BOM.fun_bun_id as Book
--Master table selection FROM( SELECT * FROM tew_buildofmaterial ,tew_component ,tew_function ,tew_translatedtext ,tew_userdata WHERE (tew_buildofmaterial.bom_id = tew_translatedtext.tra_objectid) AND (tew_buildofmaterial.bom_id = tew_userdata.use_objectid) AND (tew_buildofmaterial.bom_objectid = tew_component.com_id) AND (tew_component.com_fun_id = tew_function.fun_id) AND (tew_function.fun_bun_id = %CUR_BUNDLE_ID%) ) BOM
GROUP BY BOM.bom_reference, BOM.tra_0, BOM.use_data0, BOM.use_data1, BOM.use_data3, BOM.use_data4, BOM.fun_bun_id) itembom
Plug this query into an empty parts list report. See if that works, tweak as necessary. Unfortunately I don't have a working copy of SWE to validate this with, but I think this is what you're after.
One thing I might mention: this report I developed filters for BOM components whose function matches the book associated with that function. If that isn't important, you can remove those conditions relating to function in the "where" statement.
If you don't have a book associated with a function, or components associated to the same function, then you probably won't get any parts to show up in the report.
Using the row count method is probably not a good idea.
I assume you want this for identifying items later one. Should you ADD anything to your project, run the report, the numbers will be reapplied.
I'm currently working on an external application that will connect to a project, let you apply a trackable item number automatically or manually and keep it with that part number unless you forcibly change it. Additionally, this tool will pull a BOM for you and remember it external of SWE and allow you to keep VERSIONS of it, and also filter out NEW parts from previous BOMs and or indicate parts that were deleted. These change reports will be of huge value to companies that enter parts into their ordering system against a job for costing. Huge time saver.
Retrieving data ...