Difference between revisions of "Excel BOM Import"

Latest revision as of 19:30, 30 July 2024

General Description

Default Menu Location Transaction >> Importers/Exporters >> MBS/QuestWare Integration >> Excel BOM Import/BOM to Excel

URL: mbsgrid_s.htm

Step By Step Guide For Metal Building Creation (MBS to Adjutant)

1. Create Project for the Building using the Project Screen.

2. Create Sales Order from the Project screen for the building order by using the create SO button on the right side of the screen This sales order will have one line item. Line item will be "Metal Building" (additional line item with freight). Once added on the Sales Order, system will ask configuration questions for the building (sizes, footage, etc.)

 *NOTE Do not enter any costs on this Sales Order (cost will come from the BOM Sales Order, which is discussed below)

3. Open up the BOM TO EXCEL screen in Adjutant: TRANSACTION --> IMPORTERS/EXPORTERS --> MBS QUESTWARE INTEGRATION --> BOM TO EXCEL

4. Click on the option "Select Existing BOM_SHP.INI file". This is the file which has been created from MBS (have this file saved either inside the doc vault for the project or on your desktop)

5. Enter the Sales Order # (the one created in step 2) in the 'Job Number' box.

6. Click on the button next to the 'Select File' box at the bottom of the screen in order to open select the appropriate MBS file.

7. Once file has been selected, click the "Excel' button (Adjutant will begin creating Excel file).

8. Once the file has been generated, user can go into the file and make any edits to the BOM necessary. Save file on desktop or within the appropriate Project Document Vault, and CLOSE THE FILE.

*NOTE The Excel file must be saved as a XLXS type.

9. Open the Excel BOM Import screen TRANSACTION --> IMPORTERS/EXPORTERS --> MBS QUESTWARE INTEGRATION --> BOM EXCEL FILE IMPORT Fill in all corresponding information (Sales Order, Project, etc), then click the 'Process' button.

File:MBSBOMexport2.png

10. Use the 'BOM-Item Master Match Up' screen in order to map item codes from MBS to the item codes in Adjutant. Items with existing cross references are displayed in Green, unmatched items are Red/Pink. Selecting the green disk saves the cross references made during the session.

File:MBSMatchup2.png

Both item code(Adjutant) and item description(Adjutant) are searchable.

11. Once all items have been matched, click the BLUE save disk in the upper right hand corner. The GREEN save button allows you to save current matchups if user cannot finish the process in one session.

12. Upon saving, Adjutant will then create a BOM Sales Order that matches the existing Sales Order (created in step 2). The order will have the prefix 'BOM'. This order is the order which the user will send to production.

NOTE Adjutant will ask if user wishes to load pricing. Select 'NO' (prices will default to '0' on BOM order to eliminate possibility of 'double invoicing')

13. Once production has been completed, ship(invoice) both orders.

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

@@ Line 1: / Line 1: @@
+__NOTOC__
 ===General Description===
 '''Default Menu Location''' Transaction >> Importers/Exporters >> MBS/QuestWare Integration >> Excel BOM Import/BOM to Excel
-'''Screen Name''' MBSGRID
+'''URL''': mbsgrid_s.htm
 ===Step By Step Guide For Metal Building Creation (MBS to Adjutant)===
@@ Line 10: / Line 11: @@
 '''1.''' Create Project for the Building using the Project Screen.
+'''2.''' Create Sales Order from the Project screen for the building order by using the create SO button on the right side of the screen [[File:New_SO_button.png]] This sales order will have one line item. Line item will be "Metal Building" (additional line item with freight).  Once added on the Sales Order, system will ask configuration questions for the building (sizes, footage, etc.)
-'''2.''' Create Sales Order from the Project screen for the building order by using the create SO button on the right side of the screen [[File:New_SO_button.png]] This sales order will have one line item. Line item will be "Metal Building" (additional line item with freight).  Once added on the Sales Order, system will ask configuration questions for the building (sizes, footage, etc.)
 [[File:MBS_NEW_SO.png]]
+<pre style="color: red">
   *NOTE Do not enter any costs on this Sales Order (cost will come from the BOM Sales Order, which is discussed below)
+</pre>
@@ Line 31: / Line 33: @@
 '''7.''' Once file has been selected, click the "Excel' button (Adjutant will begin creating Excel file).
-[imageauto||{UP}/MBSbom2excel2.png]
+[[File:MBS_to_excel_2.png]]
 '''8.''' Once the file has been generated, user can go into the file and make any edits to the BOM necessary. Save file on desktop or within the appropriate Project Document Vault, and CLOSE THE FILE.
-{s:yellow
+<pre style="color: red">
-| text = *NOTE The Excel file must be saved as a 5.0/95 Workbook (or earlier)
+*NOTE The Excel file must be saved as a XLXS type.
-}
+</pre>
-'''9.'''  Open the [^mbsgrid|Excel BOM Import] screen '''TRANSACTION --> IMPORTERS/EXPORTERS --> MBS QUESTWARE INTEGRATION --> BOM EXCEL FILE IMPORT'''
+'''9.'''  Open the [[Excel BOM Import]] screen '''TRANSACTION --> IMPORTERS/EXPORTERS --> MBS QUESTWARE INTEGRATION --> BOM EXCEL FILE IMPORT'''
 Fill in all corresponding information (Sales Order, Project, etc), then click the 'Process' button.
-[imageauto||{UP}/MBSBOMexport2.png]
+[[image:MBSBOMexport2.png]]
 '''10.'''  Use the 'BOM-Item Master Match Up' screen in order to map item codes from MBS to the item codes in Adjutant.  Items with existing cross references are displayed in Green, unmatched items are Red/Pink. Selecting the green disk saves the cross references made during the session.
-[imageauto||{UP}/MBSMatchup2.png]
+[[image:MBSMatchup2.png]]
-{s:yellow
+Both item code(Adjutant) and item description(Adjutant) are searchable.
-| text = Both item code(Adjutant) and item description(Adjutant) are searchable.
-}
 '''11.'''  Once all items have been matched, click the BLUE save disk in the upper right hand corner. The GREEN save button allows you to save current matchups if user cannot finish the process in one session.
@@ Line 59: / Line 60: @@
 '''12.'''  Upon saving, Adjutant will then create a BOM Sales Order that matches the existing Sales Order (created in step 2).  The order will have the prefix 'BOM'.  This order is the order which the user will send to production.
-{s:yellow
-| text = *NOTE Adjutant will ask if user wishes to load pricing.  Select 'NO' (prices will default to '0' on BOM order to eliminate possibility of 'double invoicing')
-}
-'''13.''' Once production has been completed, ship(invoice) both orders.
+*NOTE Adjutant will ask if user wishes to load pricing.  Select 'NO' (prices will default to '0' on BOM order to eliminate possibility of 'double invoicing')
+'''13.''' Once production has been completed, ship(invoice) both orders.
+===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 (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
-===RELATED VIDEOS===
+Column M = Color Code
-'''MBS Match-Up'''
+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.
-<iframe width="853" height="510" src="//www.youtube.com/embed/qM4nNGI8hoQ" frameborder="0" allowfullscreen></iframe>
+[[category:Item Control]]