BOM Import for Dynamics GP

BOM Import

for

Dynamics GP

For the builds shown below And higher:

Manual copyright © 2016 WilloWare Incorporated. All rights reserved. Your right to copy this documentation is limited by copyright law and the terms of the software license agreement. Only the software licensee may use this documentation. Making unauthorized copies, adaptations, compilations, or accessing the web-based version by direct linking, or sharing the web-based version with unlicensed users, or making derivative works for commercial distribution or other use is prohibited, and constitutes a punishable violation of the law.

Trademarks

Great Plains, Dynamics, and Dynamics GP are either registered trademarks or trademarks of Great Plains Software, Inc. in the United States and/or other countries. Great Plains Software, Inc. is a wholly-owned subsidiary of Microsoft Corporation. The names of actual companies and products mentioned herein may be trademarks or registered marks - in the United States and/or other countries - of their respective owners. The example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted herein are fictitious. No

association with any real company, organization, product, domain name, e-mail address, logo, person, place, or event is intended or should be inferred.

Warranty disclaimer

WilloWare Incorporated disclaims any warranty regarding the examples contained in this documentation, including the warranties of merchantability and fitness for a particular purpose.

Limitation of liability

The content of this manual is furnished for informational use only, is subject to change without notice, and should not be construed as a commitment by WilloWare Incorporated. WilloWare Incorporated assumes no responsibility or liability for any errors or

inaccuracies that may appear in this manual. Neither WilloWare Incorporated nor anyone else who has been involved in the creation, production or delivery of this documentation shall be liable for any indirect, incidental, special, exemplary or consequential damages, including but not limited to any loss of anticipated profit or benefits, resulting from the use of this documentation or sample code.

License agreement

Use of this product is covered by a license agreement provided with the software product. If you have any questions, please call WilloWare Incorporated at 866-726-5506.

Publication date

Contents

Title Page ... 1 Contents ... 3 Quick Start ... 4 Installation ... 5 Registration ... 6 Security ... 7 About Box ... 8 Overview ... 9 Setup ... 10

Planning Your Import... 12

Routings ... 15

Field Map ... 16

Quick Start

1. Install the software.

See

http://www.willoware.com/install

2. Log-in to GP. Run the Install process inside GP

to create tables.

See

http://www.willoware.com/install

3. Click REGISTRATION to open the Registration

window. Enter your Registration Key.

Installation

Registration

Go To Tools >> Setup >> System >> Registration. Open the Dynamics GP Registration window. Then go to Extras >> Additional >> Register BOM Import

When you have registered you will be sent a Registration File. The file is not human readable. It needs to be imported into the Registration window. You can also click the Get Key Online button to automatically retrieve your key over the internet.

To manually load they key, click the Folder button.

Locate where the file was saved. Select the file and click OPEN. Click VALIDATE.

If there are any problems validating the key, they will be displayed as shown on the window above. If they key cannot be validated, contact WilloWare.

Security

During installation a Security Task will be created called BOMIMPORT, and a Security Role will also be created called BOMIMPORT. The Task will have access to all windows in the module, and the Task will be assigned to the Role.

To ensure that users have access to the windows required by you need to assign the Task to one or more Security Roles, or assign the Role to one or more Users.

To assign the Task to Roles, Go to Tools >> Setup >> System >> Security Roles. Select a Role, then mark the box next to BOMIMPORT.

To assign the Role to Users, go to Tools >> Setup >> System >> User Security. Select a User, then mark the box next to BOMIMPORT.

Do not modify the BOMIMPORT Security Task. Each time a new version of BOM Import is installed it will overwrite any changes.

About Box

Navigation: Help >> About Microsoft Dynamics GP >> Additional >> BOMImport

Overview

BOM Import for GP Manufacturing is a highly flexible tool for importing Bills of Material into Dynamics GP from Excel. The import process can provide default values for most of the fields required to create the BOM, which streamlines the import process by requiring only a few key fields.

The Import Mapping tool adapts the import to whatever file layout is provided, be that from a CAD package or an export from another ERP system.

BOM Import can import multiple levels of a BOM, and multiple separate BOMs, from a single spreadsheet. At this time, Excel allows a maximum of 1,048,576 rows. BOM Import will allow 1,048,575 to allow for one row of headers.

New items can be created on the fly through the use of “Template Items” and an integration between BOM Import and WilloWare’s Item Copy utility in MFG PowerPack. You must have MFG PowerPack installed to use BOM Import. A free “Item Copy API” registration key is available for MFG PowerPack when BOM Import is purchased.

Setup

Navigation: Tools >> Setup >> Manufacturing >> BOM Import Map

An Import Map is required. The Map provides instructions for BOM Import on how to create the Bills of Material, what source fields to use from the Excel spreadsheet, and how to provide default values.

Map ID: Create an ID for the Map. You can create an unlimited number of Maps to

support different file layouts.

Description: Enter a description for the Map.

Additional Setup Options are available from the GoTo menu:

Data Start In Row: If the import spreadsheet has one or more header columns, enter the

Existing BOMS: When importing Bills of Material, if the file contains a BOM that already

exists in GP you can either delete the existing BOM from GP, or automatically Revision it. If you select Revision, the existing BOM will be moved into the BOM Revision History tables. As noted below, the default Revision Number is 1 (if none is provided). If you are Revisioning BOMs you must provide a Revision Level in the import file. Each time you import a new revision of the BOM, it must have a new Rev Level. The Parent Item’s Rev Level should be repeated on each line (i.e. repeat the “header” fields for each “line”).

Regenerate Low Level Codes: Since importing BOMs may add or remove levels from

existing BOMs, you may want the system to automatically recalculate LLCs after the import.

Set Item Eng. Rev to Imported BOM Rev: When marked, the Item Engineering

Revision Level field will be set to the Rev Level from the import. The Rev Level may be either provided in the Import File or as a Constant in the Map.

Field Mapping: The table in the Field Map section provides details on each field that you

can import, whether or not it is required (meaning it must be in the import file), and what the default value is. If you map to a Source Field, but the Column Letter in the Source column. For example, if the Parent Part Number is in Column A, put A into the Source field. If the Child Item Quantity is in Column AF, put AF into the Source field. The Import Mapping tool cannot validate this information until you attempt to run the import, so careful setup is important.

Template Items: BOMImport has an integration to WilloWare’s Item Copy utility in MFG PowerPack. A Template Item is used as a Source Item to create new items on-the-fly during the BOM Import. A Template Item should be completely set-up in GP (Item Maintenance, Item-Site Assignment, etc). During the import process, if the Parent Items and/or Component Items do not already exist in GP, and a Template Item is provided, the new Items will be created during the import by running the Source and Target items through the Item Copy utility. NOTE: If the Template Item has a Routing, it will be

copied to the new item. You can use this functionality to automatically create a default routing for new items during the import.

Planning Your Import

In general, BOMImport expects a file that resembles the GP Manufacturing BOM Lines table (BM010115).

For example, the Bill of Materials for 100XLG is shown below.

Exported to a spreadsheet, this BOM would appear as shown below (CBA100 and HA100 are not shown):

BOMImport will provide default values for most fields, with the minimum being the Parent Item, Child Item and Quantity (as shown above). Quantity could be left out, and it would result in a BOM structure containing the correct components, but all at zero quantity. BOMImport can also handle importing multiple exploded BOMs at the same time. In this scenario the same component could appear multiple times within a single BOM, or multiple times in the file because the same component appears in multiple finished goods. An example of this is shown below.

Note that BA100G appears at the first level below 100XLG, and also as a subassembly inside HA100G. When this type of BOM is flattened and exported to Excel it would look like this:

This sample file contains two Bills of Material (100XLG and 200XLG), both of which have the same components. In this case the file lists the same subassembly (BA100G) four times. BOMImport will recognize these duplicates and only import the BOM for BA100G one time. It is import to note, however, that BOMImport will keep only the first

instance of a BOM.

BOMImport checks for duplicates by grouping all imported rows by the following columns:  PPN_I  BOMCAT_I  BOMNAME_I  CPN_I  POSITION_NUMBER  QUANTITY  FIXED_QTY_I

This essentially translates to: If the same Parent Item-Component-Quantity combination is in the import multiple times, it is a duplicate, so ignore it.

IMPORTANT: One implication of this is that if your BOM does contain the same

subassembly multiple times with the same quantity, you MUST provide the Position Number. The Position Number will allow BOMImport to identify those as distinct lines. New Item Numbers can be added to GP during the import. This requires providing a “template item” for the new Item. A Template Item is an existing item in GP that will be used as the basis for creating the new item. BOMImport uses WilloWare’s Item Copy utility (in MFG PowerPack) to create new items.

For example, you might create a new item called BA200G by using the existing BA100G as the Template.

As shown above, the new CPN (BA200G) has a CPNTemplate (BA100G).

There are two methods you can use to provide Template Items. As shown above, one method is to have separate columns for the Parent Template and a Child Template.

Another method for providing Template Items is shown above. You can have a single column which provides the template. In this case the Template Item is provided on the first row where a new Parent Item appears. Note that BA200G is used as a component much earlier in the import file, and also multiple times as a Parent Item.

When importing the file, BOMImport will look first for Item-Template pairs anywhere in the file. It will then make sure that any new item in the file has a Template specified

somewhere in the file. As shown above, as long as there is one record that tells BOMImport that BA100G is the Template for BA200G, BOMImport will use that information to create the new item.

If the import file passes all validations (data validation is described later in this

document), the first step of the import process will be to create all new items. BOMImport will then proceed to import the Bills of Material.

NOTE: To create new Items during the import you must also install MFG PowerPack

from WilloWare so you have access to ItemCopy. WilloWare will provide an “API Key” for ItemCopy, which will allow BOMImport to use ItemCopy. There is no charge for the API Key.

Routings

A common need when importing Bills of Material is to create routings for the items. BOMImport has two methods to address this.

First, if the import file contains new Item Numbers, you will need to provide a Template Item. You can configure Item Copy to copy, among other records, the Primary Routing from the Template Item. When the new Item Number is created, the process will also create a routing by copying the routing from the template item.

Second, if the import file contains existing Item Numbers for which you are importing a BOM, or importing a new revision of a BOM, you can provide a Routing Source Item in the import spreadsheet.

Routing Source Item is the last field in the import map. As shown above it is mapping to column D. In this case, importing the new BOM for 100XLG would overwrite the existing routing by copying over the routing from CHAIR.

Another way to add a “default” routing to items is to create an item in GP whose sole purpose is to carry a default routing.

For example, the Item Number could be called ROUTING. The routing for this item might have a single step, with no time, or labor or machine costs, which serves the purpose of simply providing a routing because manufacturing requires one.

NOTE: Routing Source Item was added to BOM Import in April, 2016. If you have an Import Map created before that date, you will need to delete the map and re-create it to have access to the new map field.

If you use a Routing Source Item, BOM Import will create/re-create routings as the final step in the import process—after it has completed importing the BOMs. If the item (BOM) being imported already has a routing, it will be DELETED, and replaced with the routing from the Routing Source Item.

Field Map

Field

Required

Default Value

Notes

Parent Item

yes

Items must be <= 30 characters.

Parent BOM Type

1 (Regular)

On the MFG BOM Maintenance window the label for the BOM Type field is “BOM Category”.

1 = Regular 3 = Phantom

No other BOM Types are supported.

Parent BOM Category

1 (MFG)

On the MFG BOM Maintenance window the label for the BOM Category field is “BOM Type”. 1 = MFG BOM

2 = ENG BOM 3 = ARCH BOM 4 = Config BOM 5 = Super BOM

Parent BOM Name

blank

Required if Parent BOM Category is Archived or Configured. If Parent BOM Name is provided but not Parent BOM Category, BOMImport will assume BOM Type= CONFIGURED.

BOM Name must be <= 15 characters.

Revision Level

1

Rev Level must be <=50 characters (this is an alphanumeric field).

Effective Date

System Date

Format must be mm/dd/yyyy or mm/dd/yy (10/20/15 or 3/20/2012)

Parent Template Item

blank

Items must be <= 30 characters.

Parent Item

Description

see note

blank

Must provide a description if Parent Template Item is used.

Item Description must be <= 100 characters.

Parent Backflush

0 (false)

This is the Backflush setting on the Parent Item side of the BOM window. 0=false

1=true

Parent Issue From

blank

Bill of Materials Defaults window. Sites must be <= 10 characters.

Parent Issue To

blank

Bill of Materials Defaults window. Sits must be <= 10 characters.

CPN

yes

Items must be <= 30 characters.

Position No

BOM Preference Defaults value for New Component Position Number Incremental Spacing.

Position Number must be an integer between 0 and 9999.

Sub Category

see note

MFG BOM (1) if available, or ENG (2) BOM if available, or MFG BOM (1) if CPN is a Make Item or Either in Item Engineering

If a Template is used, the Sub Category and Sub Name will be set based on the Template item.

1 = MFG BOM 2 = ENG BOM 3 = ARCH BOM 4 = Config BOM

No other BOM Categories are supported.

Sub Name

see note

Configured. If Sub Name is provided but not Sub Category, the import will set Sub Category = CONFIGURED. BOM Name must be <= 15 characters.

Qty Per

0 (zero)

May contain up to 5-decimals depending on Item Qty Decimals. Must be a positive value.

U of M

Item's Base UofM

UofM must be <= 8 characters.

Fixed Qty

0 (zero)

May contain up to 5-decimals depending on Item Qty Decimals. Must be a positive value.

U of M

Item's Base UofM

UofM must be <= 8 characters.

Issue From

blank

Sites must be <= 10 characters.

Issue To

blank

Sites must be <= 10 characters.

In Date

0/0/0000

Format must be mm/dd/yyyy or mm/dd/yy (10/20/15 or 3/20/2012)

Out Date

0/0/0000

Format must be mm/dd/yyyy or mm/dd/yy (10/20/15 or 3/20/2012)

Shrink Pct

0 (zero)

10 = 1%, 100 = 10%, 1000 = 100%. No decimals are allowed. Negatives are not allowed. Invalid values will be set to zero.

Option Pct

0 (zero)

10 = 1%, 100 = 10%, 1000 = 100%. No decimals are allowed. Negatives are not allowed. Invalid values will be set to zero.

Lead Time Offset

0 (zero)

Must be zero to 999. Any other value will be set to zero.

LTO From

1 (MO Start Date)

1 = MO Start Date, 2 = MO Due Date. Other values will be set to 1.

Floor Stock

0 (false)

1 = true, 0 = false. Any other value will be set to 0.

Backflush Item

0 (false)

1 = true, 0 = false. Any other value will be set to 0.

Alternate

0 (false)

1 = true, 0 = false. Any other value will be set to 0.

Alternate For

see note

0

If Alternate = 1 (true) then Alternate For must specify the Position Number of the alternate item. Position Numbers must be used if the Alternate Items functionality is used.

Reference Designators

blank

Source field may be up to 255 char in length. Must contain reference designators separated by commas, or ranges such as D2-D7. If it cannot be parsed, it will be split into 20-character segments.

Actual Consumed

Check

0 (false)

1 = true, 0 = false. Any other value will be set to 0.

Single Lot

0 (false)

1 = true, 0 = false. Any other value will be set to 0.

User Defined 1

blank

Must be <= 30 characters

Child Template Item

blank

Items must be <= 30 characters.

Child Item Description see note

Must provide a description if Child Template Item is used

Routing Template

Item

Blank

You can create a routing for the Parent Item by providing a Routing Template Item.

Using BOM Import

Navigation: Tools >> Utilities >> Manufacturing >> BOM Import

There are several fields on the bottom of the window that are used by customized versions of BOM Import. If you have special requirements for an import which cannot be met through the existing functionality of BOM Import, please contact us to discuss creating a Custom Adapter to meet your needs.

Import Map ID: select the Map ID to use for the import.

Source File: select the source file. The import file must be an Excel spreadsheet, and

the first sheet in the workbook must contain the data to import.

Import: Click the Import button to begin the import. The import process will:

 Validate that each imported column has the correct datatype, length, and allowed value for the field.

 Provide default values as specified in the Import Map

 Perform the validations below. No changes are made in GP if ANY validation fails.

o Each row has a Parent Item Number and a Component Item Number. If Parent Item or Child Item is blank, the error is reported, and the rows are removed from the import file so they do not interfere with further error checks.

o New Parent Items do not have Template Items. The error is reported, and the rows are removed from the import file so they do not interfere with further error checks.

o New Child Items do not have Template Items. The error is reported, and the rows are removed from the import file so they do not interfere with further error checks.

o The Template Items exist in the Item Master. If the Template Item does not exist in the Item Master, the error is reported, and the rows are removed from the import file so they do not interfere with furth error checks.

o The Parent Items, Child Items, or their Templates, must have Item Engineering records.

o The Parent Items must be Make or Make/Buy in Item Engineering. o The Parent Templates must be Make or Make/Buy in Item Engineering.

o Parent Items, Component Items, or their Template Items, must have compatible valuation methods.

o If a U of M is provided it must exist on the corresponding Child Item, or Template Item, Unit of Measure Schedule.

o The Parent BOM Category and Child Item Sub-BOM Cateogry as set as follows. (Note--BOM Category is labeled as BOM Type on the Bill of Materials window in GP).

 If the Child Template Item is blank, and the Child Item does not have a BOM in GP or in the Import File, Sub-BOM Category is set to zero.

 If the Child Template Item is NOT blank, and the Template Item does not have a BOM in GP or in the Import File, Sub-BOM Category is set to zero.

 If the Parent Item is in the Import File as a Child Item with Sub-BOM Category = 2 (Engineering Sub-BOM), the Parent Sub-BOM Category is set to 2.

 If a BOM Name is provided, and the BOM Category is zero, the BOM Category is set to 4 (Configured).

 If a Sub-BOM Name is provided, and the Sub-BOM Category is zero, the Sub-BOM Category is set to 4 (Configured).

 If BOM Category is zero, and the Parent Template has a MFG BOM in GP, set BOM Category to 1 (MFG BOM).

 If BOM Category is zero, and the Parent Template has an ENG BOM in GP, set BOM Category to 2 (ENG BOM).

o Where the Sub-BOM Category is NOT zero, the Child Item must have a BOM in GP or in the Import file, or its Template Item must have a BOM in GP.

o If the option is selected to Revision Existing BOMs, the Rev Level must be provided in the Import File and the Rev Level must not already exist in GP. Otherwise the default Rev Level is set to 1.

o The Quantity and Fixed Quantity are compatible with the Component Item’s Quantity Decimals, or with the Quantity Decimals of the Template Item.

o If no U of M is provided, the Per Piece and Fixed Qty Units of Measure are set to the Base Unit of Measure for the Child Item, or the Template Item.

o Verify that all lines have a valid U of M.

o If BOM Position Number is not provided, lines are numbered using the default increment from MFG Setup.

If any errors are found the entire import will stop. Nothing will be imported if there are any errors, and no changes will be made in GP. An Error Log Report will print specifying the error and the Excel Row of the data containing the error.