Page History: Excel BOM Import
Compare Page Revisions
Page Revision: Fri, 24 Jan 2014 09:41
General Description
Default Menu Location: Transaction >> Importers/Exporters >> MBS/QuestWare >> Excel BOM Import
Please note that the name of this screen may be different in your menu tree.
Screen Name: MBSGRID
Function: Imports a specifically formatted Excel spreadsheet into Adjutant as either a quote or sales order. This spreadsheet is specifically geared towards metal building/component parts.
When importing as a sales order, it is assumed that you are importing the "Bill of Material" or "Shipper List" for the building, thus you are required to select an existing project before importing.
![Choose a file to import](http://i.imgur.com/f7tpLHW.png) Choose a file to import |
Spreadsheet Format
The spreadsheet should be formatted as follows:
- At least two worksheets, with the item list starting on Sheet2. Sheet1 can be left blank or act as a title page. Additional sheets after sheet2 can also contain items.
- The item list must start on Row 13. Column headers are not required.
- Column A = Qty
- Column B = Piece Mark (SOC Report Code = A)
- Column C = Description
- Column D = Color Name
- Column E = Drawing (not used in import)
- Column F = Length in Ft-In (not used in import)
- Column G = Cut Length in Ft-In (not used in import)
- Column H = Punch Pattern (separate your leading and trailing punch with a dash; e.g. A-A)
- Column I = Length in Decimal Inches (this is converted to ft-inch length and stuffed into SOC Report Code = B)
- Column J = Class
- Column K = Part
- Column L = Thickness/Gauge
- Column M = Color Code
Columns J, K, L, and M are not required, but will speed up the import process if filled out. See the "Matching Logic" section for more info.
Acquire Excel Data
There are two options when importing from a spreadsheet:
- Quote
- Sales Order
If importing line items for a quote, you must first create the quote from the Quote screen and then enter it in the "Quote to Append To" field.
If importing line items for a sales order, you must select the corresponding Project. Adjutant will create the sales order for you with the corresponding Order Type you select.
Matching Logic
After you have selected your spreadsheet and clicked the Process button, the program will attempt to match the rows in the spreadsheet with Items in Adjutant.
It does this in a few different ways, but all of the data is stored in the "BOM Description to Item Master Xref" screen (aka mbsdescrip).
![Click to view larger](http://i.imgur.com/8WGXjB8.png) Click to view larger |
Item Match Up
Items with existing cross references are displayed in green, unmatched items are red.
Click here for a screenshot.
Selecting the green disk saves the cross references made during the session. Selecting the blue disk creates the sales order if all items have a matching cross reference.