Reconciling Onshape and IDB BOMs

Overview

This procedure enables the reconciliation of the Onshape model and item database (IDB) bills of material for any Aeon assembly, including top-level assemblies, which are commonly referred to as systems or projects. The reconciliation process utilizes a spreadsheet to compare the BOM items and quantities between the two representations and identify discrepancies.

Creation

Open Data\HACS\BOM Reconciliations.
Locate the BOM Reconciliation spreadsheet for the assembly to be reconciled. Alternatively, create a copy of an existing spreadsheet and name it for the new assembly.
Open the assembly model in Onshape.
- Open the BOM flyout and ensure the Structured format option is selected. Expand all the items in the list.
Clear the 'Onshape Paste' tab in the spreadsheet and then copy the Onshape BOM table. Use the Paste Special function to paste it into the 'Onshape Paste' tab of the spreadsheet.
Set the column type to "Text" for all columns that are not quantities. This prevents the misinterpretation of part numbers as numeric information or dates.

Note down the last row number of the list.
In the 'BOM' tab, adjust the rows to match the number of populated rows in the 'Onshape Paste' tab. Either copy the last populated row into the rows below or delete unnecessary rows.
Update the end of the range in the 'parent Qty' column to the last row number noted above.
In the 'pivot1' tab, right-click anywhere within the list and select Properties.
In the properties window, expand the 'Source and Destination' section.
Edit the source line to range from $a$1 to $g$(last row number from BOM). Click OK to update the pivot table.
Access the item database (IDB) and locate the desired assembly.
Open the 'eBOM' page for the assembly.
Clear the 'IDB eBOM Paste' tab in the spreadsheet and then copy the IDB eBOM table. Use the Paste Special function to paste it into the 'IDB eBOM Paste' tab of the spreadsheet.
Set the column type to "Text" for all columns that are not quantities. This prevents the misinterpretation of part numbers as numeric information or dates.

Note down the last row number of the list.
In the 'Onshape-IDB' tab, locate the top of the list.
In row 2 (the first row with data below the column headings), edit the formulas in columns E through J and update the ranges to end with the last populated row in the 'IDB eBOM Paste' tab. For example, if the last populated row in the 'IDB eBOM Paste' tab is 1284, change the range to end with $1284.
Copy row 2 into the rows below to match the populated rows in the 'IDB eBOM Paste' tab.
Review the 'Onshape-IDB' tab and identify and resolve any mismatches between the Onshape and IDB comparison.
In most cases, discrepancies are resolved by editing the IDB. Contact Jim if you are uncertain or if it appears that the Onshape model requires corrections.
After resolving all mismatches on this tab, proceed to the 'IDB-Onshape' tab.
In the first data row, edit the formula in column E (Qty (M)) and update the range to end with the last populated row in the 'Onshape-IDB' tab. For example, if the last populated row in the 'Onshape-IDB' tab is 863, change the range to end with $863. Copy the edited cell to overwrite all populated cells in column E below the edited one.
Focus on quantity mismatches in this tab and resolve any remaining discrepancies. Frequently, items with little or no physical significance are present in the IDB but are absent from the Onshape model. This is okay. One particularly common example occurs when the IDB correctly lists a stocked material (such as a sheet of plastic) in an item's BOM, while the Onshape model shows the part that is machined from the material but does not list the stock. This is because in Onshape, only assemblies have BOMs; parts do not.
Avoid spending much effort to resolve small errors for items with large quantities (e.g., 1/4-20 x 1/2 screws).

Most changes are corrected by editing the IDB. Confirm with Jim if it looks like the Onshape model needs correcting.
In the 'MP and PP Count' tab, paste the relevant data for all items with part numbers starting with "MP-" or "PP-".
Sort the list based on the machine used to create each part.