In essence what we need to do is change your existing data into a readable format for pc/MRP. To do this we will have to use Excel and perhaps Access to manipulate your data. Your existing data will have to be exportable into Excel or other spreadsheet formats. If you feel more comfortable with Lotus or a different spreadsheet program than Excel you are welcome to use that instead. The instructions are however written specifically for manipulating Excel files and importing them into pc/MRP. To begin this process we will first address how your data is stored in pc/MRP. The lesson will continue with how to manipulate your existing data so pc/MRP will recognize it as an acceptable format. We will then address how to import this data into pc/MRP.
This chapter is specifically written for importing Inventory and Bills of Materials. Most of the steps and recommendations however are applicable for every module. If you follow the steps, study the data structure, manipulate your legacy file as needed, and import using option 40, you should be successful every time.
One last note: the data you are importing must also be Y2K. Dates should be imported with a four digit year. Dates imported without a four digit year will not work.
15.11.2 Inventory File Structure
The Inventory Module is simple to understand. Everything that deals with inventory data is stored here. The BOM module stores the recipe while the Inventory module stores the ingredients. For complete details about the Inventory Module see the chapter on the Inventory Module. When importing data into the inventory module there is really only one key field, the part number. However, a person should try to import as many fields as possible to save time. Listed below are a few of the fields in Inventory and their use. For more details see the Inventory Module and or the Import Utility.
PARTNO This is your part number field. KEY FIELD (Limited to 15 characters)
DESCRIPT This field is your description.
MANUFACTER Part manufacturer Name
MODELNO Part manufacturer number
PART_ASSY This field labels this line item as a Part or Assembly. KEY FIELD
The rest of the fields can be imported if the data exists but they are not essential fields.
15.11.3 BOM File Structure
pc/MRP uses a very simple system to organize the Bill of Materials. Take a moment to view the picture below. The picture below depicts a simple bike assembly and the fields necessary to import a BOM into pc/MRP. (The reference designator field, Refdesmemo, is optional but will receive some special attention later.)
As we can see pc/MRP uses just a few key fields. There are more fields in the BOM.DBF, but these fields are the essential fields for importing. The Bomno field contains the BOM number. The Bomdescri field contains the BOM description. The Partno field contains the partnumber for each part in the corresponding BOM. The Itemno contains the item number of each part within that level of the BOM. The Qty field is the quantity required of each part or assembly to finish the top assembly. The Part_assy field labels that entry as a part or assembly. The Refdesmemo field stores all of the reference designators. To learn more about the fields in the BOM module you can read the chapter on the Bills of Materials Module.
Another way of showing how pc/MRP organizes entries in the BOM module is to see a report. Take a moment to view the example report below. The report below is the exact information as pictured above simply presented in a different format.
Now in the process of comparing the two reports notice that BOMNO 000000001 contains one subassembly and six parts. Within BOMNO 000000001 BOM 0000000002 exists. This BOM contains 3 parts and one BOM. The final BOM, 000000012 contains two parts. We distinguish between parts and assemblies by using the Par_assy field by using an A for assembly and P for parts. As you can see, a BOM can call an assembly. This BOMNO calling an assembly and later the assembly calling additional assemblies allows pc/MRP to have infinite leveled and indented Bills of Materials. On a final note each part has a BOMNO and each part has a BOM description.
Stated in a different way, the BICYCLE TOP ASSEMBLY calls for several parts and one assembly. The assembly it calls for is 000000002. We know 000000002 is an assembly because it has an A in the Par_assy field. Further down on the BOMNO column 000000002 is called for. This BOMNO calls for a few more parts (000000009, 000000004, 000000013, we know that these are parts because they have a P in the Part_assy field) and assembly 000000012. This final assembly calls for two additional parts, 0000000008 and 000000010.
If your intention is to import BOM data with multiple levels, you will need to format your bills of materials and subassemblies in the format exactly as described above. If you plan on importing your BOM data into pc/MRP with only one level, pc/MRP can use the same BOMNO for each PARTNO.
The BOM module uses three different files: partmast.dbf (Inventory), BOM.DBF, and BOM.DBT. All parts used in BOM data exists in the inventory module. An inventory entry must exist for every part that is used in the BOM module. In inventory this part can be only listed once. In the BOM module this part can be listed as many times as needed. All inventory data is stored in the partmast.dbf. All BOM data or recipe data is stored in the BOM.DBF and BOM.DBT.
Below is a list of the field name and use for each file in the BOM DBF. Key fields are fields that must exist and have contents for a successful import.
BOMNO BOM Number KEY FIELD
BOMDESCRI BOM description KEY FIELD
PARTNO Part Number KEY FIELD
ITEMNO pc/MRP organizes everything in lists. This numerically orders each item in a given level of a BOM KEY FIELD
QTY The quantity of the item needed in that specific level to finish that specific level. KEY FIELD
PART_ASSY This field labels this line item as a Part or Assembly. KEY FIELD
REFDESMEMO This field uses the DBT file and stores the reference designators or locators.
Take a few more minutes to absorb this. Understanding how pc/MRP organizes Bills of Materials is critical to having success in importing BOM data. From here we will use a sample where we wish to import BOM data from an Excel spreadsheet.
15.11.4 Manipulating the Excel File
Once again, our primary objective is to bring data over from an existing program or spreadsheet into pc/MRP. There are literally thousands of things to take into consideration while attempting to do this. However, if you follow the steps below you should have success.
pc/MRP as described above requires your Excel spreadsheet to exist in a specific format before it can be imported.
Take a moment to view the example spreadsheet below.
We will first make the changes necessary for an import into the Inventory Module. After that we will make the changes for an import into the BOM Module.
Take a few minutes manipulate the spreadsheet as outlined above.
You should end up with something that looks like the picture below.
Certainly if this spreadsheet had vendor information, more manufacturing information, and a plethora of other information we could also manipulate that and import it into the Inventory Module. With future imports simply apply the rules as outline above when importing into the Inventory module and you should be successful. However since we are importing this spreadsheet into the BOM module too, we will continue.
Taking into consideration what is needed in the BOM.DBF we need to add several columns.
Take a few minutes to add the necessary columns. In our example, since we saved the data from the top four rows we know our BOM number to be ZZ-123456-A. The BOM description is ZZ Chip Board. In the item number column we can simply right an expression = (above row)+1 or simply assign each part an item number incrementing by one, starting at one.
Finally, save this file a different name than the above file. And once again save it as the file type of your current Excel program.
After performing your edits, your spreadsheet should look similar to the picture below.
Once again, we are assuming that this BOM has only one level. If this BOM had multiple levels, we would have to change the BOMNO representative of the multiple levels. Each subassembly would be listed in the PARTNO field. This subassembly would also be listed in the BOMNO field with the specific parts it utilizes in the PARTNO field. This is exemplified in the first picture of this chapter.
We have now manipulated our files and are ready to save the spreadsheet into a format that is acceptable to pc/MRP.
15.11.5 Saving Your File As a Readable Format
The following file types are readable using the pc/MRP Import Utility.
All of these other formats are readable, but your best chances for success are in using the DBF or Excel 4.0 file type.
There is however, one more thing we need to take into consideration. Most of these file types do not accept lengthy fields. If we were simply importing things into Inventory and or the BOM module without the reference designators, after saving our file as a DBF file, we could go directly to the next section. However since we are importing large reference designators into pc/MRP, we need to use Access to import this data.
I will repeat this. If you are not importing reference designators, you do not need to take the steps listed below. At this time you can simply save your spreadsheet as a DBF file and proceed to the next section, Importing Data Into pc/MRP.
However, in this example we are importing reference designators. To successfully import lengthy reference designators we must use MS Access. Do not save your file as any other format. Having saved your file above as a regular Excel file is fine for now.
15.11.6 Importing Large Reference Designators
Follow the steps below if you are importing large fields and or large reference designators.
We have now created the files necessary to begin importing data into pc/MRP.
Having followed all of the steps listed above you have now manipulated and created the necessary files to allow you to import data into pc/MRP. We will first import data into the Inventory module. We will then search for duplicate data in the Inventory module and then import data into the BOM module. From there we will audit the BOM module to look for errors.
15.11.7 Importing Into the Inventory Module
Follow the steps below to import data into the Inventory Module.
15.11.8 Importing Into the BOM Module
Follow the steps below
15.11.9 Possible Problems With Importing Excel Files
You are done. You should by now at least understand that this is complicated. Importing however is easy to do if you follow the rules. The most important rule is to have back-ups. If you have questions or would rather have Software Arts import your data, call. Reading this is free. Having us walk you through this or do this ourselves is not.