Excel BOM Import

Modified on Mon, 06 Mar 2023 16:35 by Taylor — Categorized as: Import- Export Manager

Table of Contents [Hide/Show]

   General Description
   Spreadsheet Format
   Acquire Excel Data
   Matching Logic
   Item Match Up
   FAQ

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

Spreadsheet Format¶

An example spreadsheet can be downloaded here.

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 (Make sure the column header for this is 'Quantity'. If the column header is something else - 'Qty', for example -- Adjutant will try to import the column headers onto the sales order.)
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). If ROOFSLOPE is active then the roof slope can be entered into this column (will be mapped to any configuration question where the report code = 'S').
Column G = Cut Length in Ft-In (not used in import). If MBSWT is enabled, the value from column G is imported as the weight for that line. If MBSTOTWT is active, the value from column G is divided by the number of pieces to determine the weight of the individual pieces. In other words, the weight on the import is treated as an extended weight. (The weight can be saved as the answer to a configuration question if the report code = 'W'.) If MBSTOTWT is active then MBSWT needs to be active as well.
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. You must make sure that project number and the existing sales order number for that project match; it there is already a sales order with the same number as the project number, this screen will import the sales order headers from the existing order, regardless of what it's linked to.

To maintain best practices, Sales Order numbers for projects should always be generated using the tools on the Project Master screen.

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).

If columns J, K, L, and M are NOT filled out, then the program will match on Description (column C) and Color Name (column D).

If J, K, L, and M are filled out, then it will match on Part#, Color Code, Class, and Thickness. It then tries to match on Part#, Color Code, and Thickness. It then tries to match on Part# and Color Code. If all of those fail, then it will revert to matching on Description and Color Name.

Using the Part#, Thickness, and Color Code in your file and cross-reference data should eliminate the need for many different descriptions to be stored in Adjutant. For example, an 8 X 3.5 16GA RED OXIDE CEE will only have one part number in Adjutant, but will have an unlimited number of descriptions when coming from your detailing software. If a referenced part# is used in the spreadsheet, e.g. 8X35C16, with a color code of RO, then this will always match to your Adjutant item on the first try. In order for this to work you will need to add an entry in the item's MBS X-ref screen with a part# that matches what will be in column K of the spreadsheet, a color code that matches what will be in column M and a description of 'MBS'. You will also need to enter a Color Name to save the record (but the matchup doesn't use this entry). Please see below for an example

Item Match Up¶

Items that are matched are displayed in green, unmatched items are red. Click here for a screenshot.

You can search within the Adjutant Item Code and Adjutant Item Description columns just as you would anywhere else.

Selecting the green disk saves the cross references made during the session. This will allow you to close the screen and not lose the work you've done matching items.

Selecting the blue disk creates the sales order/quote if all items have a matching cross reference.

FAQ¶

Q: Can a prefix or suffix be added to the BOM sales order based on the SO Type selected in this screen?

A: These can be added through the BOMSOTYPE rule

Q: Can I use the MBS description on some items, but use the Adjutant description on others?

A: Yes, you can enable the MBSADJDESC setup option to default the Adjutant description for all items, then add the KEEPMBSDESC item attribute to items so those particular items use the MBS description.