Customer Import Guide

General Import Template File Notes

• Template files are typically generated from their respective Import screen using an 'Export Template' button.
• Using the 'Export Template' button from an updated system will help ensure that you provide the customer with the most up-to-date import file, and will help avoid errors.
• Templates generally (but not in all cases) contain 3 rows of header information.
  • The first row is generally labelled with f1 through f## and is used to give a consistent column id to each data column. Do not remove this row unless instructed.
  • The second row is labelled with the column data/field name. The values in this row are used for the file mapping screen. Editing these values is generally not needed and may make the mapping process difficult.
  • The third row is labelled with the same values from row 2, an is available to create a customer-friendly name for the associated data column.
    • The third row should be removed from the source file prior to importing. It will cause errors since it will be treated as the first import record on the file.
• Import files MUST always be saved as 'Microsoft Excel 5.0/95 Workbook (*.xls)' format until further notice. The import screens will generate errors if the file type is not correct.

Customer Import Notes

Timing and Preparation

The Customer Import should occur after all Address Book setup items have been completed, and after the General Ledger Chart of Accounts has been imported. The Address Book Setup Guide covers all of the Rule Maintenance records that should be completed prior to importing customer records.

Salespersons and Account Managers should be established in the Contacts screen by assigning the appropriate attributes prior to importing customer records.

The Customer Import should be one of the earliest imports to complete since you need customer records in order to test many of the core applications.

Discuss the timing of the Customer import live file with the customer. Depending on the timing of the implementation, customers may choose to clean up their legacy system data and provide an updated customer import file prior to go-live, or they may choose to import an early file and then maintain their customer file in Adjutant through parallel testing and go-live. There are also possibilities for performing a difference conversion prior to go-live where only new customer records (not previously imported) from the legacy system are imported. Make sure that the plan is discussed and agreed to prior to finalizing the customer import.

General Notes

Customer Numbers - Discuss how customer numbers will be assigned with the customer. If the customer wishes to keep the existing numbers, make sure the import file has the Custno column filled out. Customer numbers can be automatically be generated during the import process if the Custno column is empty. If the customer wants to maintain some numbers, and allow Adjutant to generate the rest, confirm exactly which records should keep their numbers and which ones should have new numbers generated.

Customer Attributes - The customer import process can attach all of the required customer attributes and complete many of the data fields in each attribute screen. If additional attributes need to be assigned during import, the attribute must be flagged to display on the Import screen in the ATT_ENT rule.

Customer Import Screen (IMPORTCUST)

Menu Location: Transaction>>Importers/Exporters>>Address Book>>Customer Import

File Name/Browse: Use the Browse button to locate and select the completed customer import template file (in XLS format)

Export Template: Generates a blank Customer Import template file

Map Fields: Fields must be mapped prior to importing. If no changes have been made to the column headings, the mapping screen should show all green, and you can click OK to continue. If any of the Input Field Name columns on the left are red, single-click on the line on the left column, and then double-click the desired mapped field in the right column to complete the mapping. Repeat for any red lines on the left that should be mapped. If there are additional columns in the source file that should NOT be mapped, they can be left unmapped (displayed in red). Mandatory fields will require that they be mapped before clicking OK.

Try To Match Existing Orgs: Check this box to turn on additional logic that looks through your existing customers in Adjutant and tries to match each record in the spreadsheet with one in Adjutant. If the import program finds a match, it will update that customer's information with the info in the spreadsheet. The program matches on custno, then the first 5 characters of company name and the first 5 characters of address1, then city.

Validate Custnos: Leave this box unchecked if you have Custnos (Customer Numbers) in your spreadsheet and you want to update your existing customer records based on ID. This is commonly used if you export your customer database to do some mass updates in Excel and then want to import that data back in to Adjutant. Checking this box will validate each custno in your spreadsheet and verify it does not currently exist in Adjutant.

Organization Attributes: Select any attributes that should be assigned to ALL customers in the import file.

Customer Import File Definitions

f11 - Custno - Customer Number (Organization ID), Leave blank to auto-assign customer numbers

f1 - Company - Name of Company/Organization

f3 - Address1 - Physical Street Address

f13 - Address2 - Physical Address 2 (apt/suite number)

f74 - Address3 - Physical Address 3

f4 - City - City Name

f5 - State - Two-letter postal abbreviation for the state

f6 - ZipCode - Zip Code (you can use zip+4 if you have it)

f7 - Phone - Main phone number for the organization

f8 - Fax - Main fax number for the organization

f9 - Email - Main email address (use the address that invoices will typically be sent to)

f72 - WebSite - website address

f15 - AcctCode - Enter the customer ID for this customer's record on an outside accounting system. Updates the Acct. Cust Code value in the Bill To record. Alternatively, can be used as a miscellaneous code entry for reporting. Allows 6 alphanumeric digits.

f63 - IsBillTo - Enter a 'y' if this organization is a billing address.

f64 - IsSoldTo - Enter a 'y' if this organization is a customer.

f2 - Contact1 - Default Contact for the organization. This will list them as the contact on the organization and make a contact record listing them as the default contact.

f14 - Title1 - Job title for Contact1 in file.

f38 - AcctMgr - Account Manager's name. This must match the full name of a contact with the Account Manager attribute.

f22 - SP - Salesperson's name. This must match the full name of a contact with the Salesperson attribute.

f46 - FOB - The default FOB terms. This is only applicable for Ship To records. This must match a value in the FOB rule.

f47 - Freight - Freight payment terms. This is only applicable for Ship To records. This must match a value in the FREIGHT rule.

f20 - ShipVia - Shipping method. This is only applicable for Ship To records. This must match a value in the SHIPVIA rule.

f25 - Contact2 - Contact 2 name.

f29 - Contact3 - Contact 3 name.

f33 - Contact4 - Contact 4cname.

f28 - Title2 - Contact 2 job title.

f32 - Title3 - Contact 3 job title.

f36 - Title4 - Contact 4 job title.

f27 - Phone2 - Contact 2 primary phone number.

f31 - Phone3 - Contact 3 primary phone number.

f35 - Phone4 - Contact 4 primary phone number.

f26 - Email2 - Contact 2 email address.

f30 - Email3 - Contact 3 email address.

f34 - Email4 - Contact 4 email address.

f37 - Country - Name of country.

f59 - County - No longer used. Leave this field blank.

f10 - TaxExempt - Enter a 'Y' if the customer is tax exempt. A blank entry is the same as 'N'o

f40 - TaxExemptNum - Tax Exempt number.

f19 - Credit Hold - Enter a 'y' if the customer is currently on credit hold.

f44 - CreditLimit - Enter the customer's credit limit.

f18 - DNECredit - Enter a 'y' if nobody is allowed exceed the customer's credit limit.

f58 - TaxRate - No longer used. You can leave this field blank.

f12 - Terms - Default payment terms. This must match a value in the TERMS rule.

f16 - CustCat - Customer Transaction Category. This must match a value in the CUSTCAT rule.

f23 - Terr - Sales Territory. This must match a value in the TERR rule.

f73 - GShipTo - Enter a 'y' if the Ship To can be used by every customer. Typically used for customer pick up locations.

f54 - DefWhse - Name of the default shipping warehouse. This must match a warehouse name that's been set up in the Warehouses screen.

f24 - Pgroup - Customer Pricing Group. This must match a value in the ENTGRP rule.

f45 - AcctNumber - The account number associated with their shipping/freight company, if any.

f42 - ARAcct - An AR account number that is different than the default AR account.

f50 - BilledDT - Billed Drive Time. Only applicable if a Ship To and if you bill separately/differently for drive time.

f61 - Combo - The lock combination to use for this location. Only applicable if a Ship To and you need a lock combination to gain access.

f17 - CommentCode - Customer Comment Code. Must match a value in the CUSTCOMM rule.

f56 - CrossStreet - Name of the intersecting cross street for the Ship To.

f39 - DefShipTo - Enter a 'y' if this record is the default shipping location.

f43 - InvForm - Invoice form to use if the customer has a specific form that is different from other customers. Must match a value in the INVFORM rule.

f55 - KeyMap - Key Map number of the Ship To.

f60 - KeyNumber - The key to use for this location. Only applicable if a Ship To and you need a specific key to unlock a door/gate.

f51 - Latitude - Latitude. Only applicable to Ship To records.

f52 - Longitude - Longitude. Only applicable to Ship To records.

f21 - MiscCode - Miscellaneous code. 10 character max.

f62 - MiscCode2 - Miscellaneous code. 10 character max.

f48 - OneWayDT - One way drive time (in minutes). Only applicable to Ship To records.

f49 - OneWayMile - One way drive mileage. Only applicable to Ship To records.

f53 - WorkCat - Default work category for the Ship To. This must match a value in the WORKCAT rule.

f41 - Discount - ??????????????????

f57 - TaxTable - ?????????????????

f65 - IsShipTo - ???????????????????????

f66 - BillToCI - Organization ID of child customer (Bill To). This will make a Bill To child.

f67 - SoldToCI - Organization ID of child customer (Sold To). This will make a Ship To child.

f68 - ShipToCI - Organization ID of child customer (Ship To). This will make a Ship To child.

f69 - BillToPI - Organization ID of parent customer (Bill To). This will make a Bill To child.

f70 - SoldToPI - Organization ID of parent customer (Sold To). This will make a Sold To child.

f71 - ShipToPI - Organization ID of parent customer (Ship To). This will make a Ship To child.

f75 - mAddress1 - Mailing Street Address

f76 - mAddress2 - Mailing Address 2 (apt/suite number)

f77 - mAddress3 - Mailing Address 3

f78 - mCity - Mailing City

f79 - Mstate - Mailing State

f80 - mZipCode - Mailing Zip Code

f81 - Mcoutry - Mailing Country

f82 - Zone - ?????????

f83 - adduser - allows you to set the adduser in the database from the import file

f84 - adddate - allows you to override the system adddate with an entry in the import file

f85 - custclass - ??????????????????????????