The purpose of of this program is to compare Bills of Materials (BOMs) derived from a CAD program (like SolidWorks) and compare them to BOMs stored in an ERP database program (like that of SyteLine). The CAD and ERP programs must be able to export to excel files because that is where this program gathers BOMs from for comparison.
The file names for BOMs that come from the CAD program should be the part no. of the BOM followed by _sw.xlsx. That is names like 0300-2010-005_sw.xlsx, 093352_sw.xlsx, and 35K2445_sw.xlsx are all legitimate file names. The names of the files from the ERP database will end with _sl.xlsx. Thus the names will look like 0300-2010-005_sl.xlsx, 093352_sl.xlsx, and 35K2445_sl.xlsx. The program will match the 0300-2010-005_sw.xlsx file to the 0300-2010-005_sl.xlsx file, and so forth.
A file can contain a mulilevel BOM. For a BOM from the ERP program to be recognized as a multilevel BOM, a column named "Level" must exist (can be altered with bc_config) that gives the relative level of a subassembly to the main assembly. The Level column starts out with "0" for the top level assembly, "1" for part/subassemblies under the main assembly, "2" for a part/subassembly under a Level "1" subassembly, and so forth. From the CAD program, it is similar, but item nos. indicate the Level, e.g. item nos. like 1, 2, 3, 3.1, 3.2, 3.2.1, 3.2.2, 3.3, 4, etc., where item 3 is a subassembly with parts under it.
Column headers of BOMs (e.g. ITEM, QTY, DESCRIPTION, etc.) may vary from company to company. There is a file that you can configure that may be of help. See the file named bc_config.py.
To run the program from the command line, run like this:
python bomcheck.pyimport bomcheck as bcbc.bomcheck(myarguments)To see the arguments (myarguments) that bomcheck takes, in the Jupyter Notebook cell that contains the bc.bomcheck() function, hit Shift-Tab. Or create a new cell and run:
help(bc.bomcheck)If an error occurs when checking your BOMs, or if bomcheck is confused about some data that was entered, a message will be shown that describes the problem. Otherwise look for something you may have done wrong like failing to attach the _sl or _sw characters to the file name, or like attaching the characters _sw to a file name other than that for a SolidWorks BOM.
Tip: If the SyteLine BOM is a multilevel BOM (has a column named "Level"), then the name of the Excel file for that BOM is irrelevant as long as it has the _sl characters attached. Part numbers of multilevel SyteLine BOMs are extracted from the BOM data and not from the file name. So then a file name like 094433_initial_sl.xlsx will work, or even a silly name like ANYNAMEUSA_sl.xlsx will work.
Shown below is an example of a few lines of what a bom check looks like:
| assy | Item | i | q | d | u | Q_sw | Q_sl | Description_sw | Description_sl | U_sw | U_sl |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 730322 | 130031 | X | X | X | X | 1 | HOUSING | EA | |||
| 130039 | X | X | X | X | 1 | HOUSING | EA | ||||
| 220978 | - | - | - | - | 1 | 1 | SPUR GEAR | SPUR GEAR | EA | EA | |
| 275000 | - | - | - | - | 0.35 | 0.35 | TUBE | TUBE | FT | FT | |
| 380000 | - | - | - | - | 2 | 2 | BEARING | BEARING | EA | EA | |
| 441530 | - | - | - | - | 1 | 1 | SHIFT ASSY | SHIFT ASSY | EA | EA | |
| 799944 | - | - | X | - | 1 | 1 | SHAFT | AXLE | EA | EA | |
| 877325 | - | X | X | - | 3 | 1 | PLUG | SQ. HEAD PLUG | EA | EA | |
| 900000 | - | - | - | - | 0.75 | 0.75 | OIL | OIL | GAL | GAL | |
| 441530 | 433255 | - | - | - | - | 1 | 1 | ROD | ROD | EA | EA |
| 500000 | - | - | - | - | 1 | 1 | SHIFT FORK | SHIFT FORK | EA | EA | |
| K34452 | - | - | - | - | 1 | 1 | SPRING PIN | SPRING PIN | EA | EA |
The columns i, q, d, and u show Xs if Item, Quantity, Description, or Unit of measure don't match between the SolidWorks BOM and the ERP BOM. Q_sw and Q_sl are quanties in SolidWorks and the ERP. Desription_sw and Description_sl are a comparison of descriptions. U_sw and U_sl are a comparison of units of measure. The 1309031 part is in the ERP but not in SolidWorks, thus the blank space in columns for the SolidWorks category. Likewise, 130039 is in SolidWorks, but not in the ERP BOM
If a unit of measure (U/M) is not given for a value in the Length column of a SolidWorks' BOM, then the U/M is assumed to be Inches (unless changed in the bc_config.py file). However the U/M can be explicity specified, e.g. 336.7mm. The program will recognize the following abreviations for U/Ms:
in, inch, ”, ft, ', feet, foot, yrd, yd, yard, mm, millimeter, cm, centimeter, m, meter, mtr, sqin, sqi, sqft, sqf, sqyd, sqy, sqmm, sqcm, sqm, pint, pt, qt, quart, gal, g, gallon, ltr, l, liter.
When the program is run, values will be converted to the U/M given in the ERP database program. For example, 336.7mm will be converted to 1.10 ft, and 8 pints will be converted to 1.00 gal. Other names that can be substituted for the column name LENGTH in a SolidWorks BOM are: SIZE, Length, L, SIZE, AMT, AMOUNT, and MEAS. Names are case sensitive.
In the source files directory there is a file named bc_config.py. If that file is placed in the same directory as bomcheckgui.exe (or of bomcheckgui.py), it can be used to alter the operating characteristics of the bomecheck program. bc_config.py was created to allow bomcheck to adapt, as much as possible, for use by other companies. For example, it allows diffent column headings to be used; e.g. "Numero de pieza" instead of "Part Number".
If no unit of measure is specified for a length in a CAD BOM, bomcheck assumes that the value is in inches. If required, update bc_config.py to specify another unit of measure.
Open bc_config.py with a text editor and you'll see instuctions on its use. (Microsoft's Wordpad program works well since it correctly recognizes end-of-line charcters used by Windows. Otherwise, text may be scrambled. Notepad++ is a good alternative.)
If you are using Jupyter Notebook to run bomcheck, you may want to take a look at the setcfg function. It is a function within the bomcheck program, and it is an alternative to using the bc_config.py file. It is meant for altering settings on a less permanent basis than that of the bc_config.py file. For more about this, in a Jupyter Notebook cell, do: help(setcfg), or: help(bc.setcfg)