(File – Data Import – Customers – Customers)
Supporting Tables
Some or all of the following tables must be created in advance before you can import your customers. Refer to the Startup Guide for details.
Tax Codes
In USA and Canadian tax environments, tax codes are assigned at the customer level, in which case you must set up all your tax codes in advance of importing customers. In VAT/GST environments, a system level default sales tax code is used, in which case no advance setup is required.
Customer Types
Each customer is assigned to a Customer Type.
Customer Terms
Payment terms are assigned to each customer, so you must set up your customer terms table.
Sales Regions
Sales regions are optional and help you organize your customers geographically.
Sales Reps & Commission Codes
Sales rep types, sales reps, and commission codes should be set up if you pay commissions to sales reps.
Lead Sources
Lead sources are optional and help you gather statistics on where your new customer leads are coming from.
Ship Methods
The ship methods table determines the preferred shipping method for each customer.
Price Levels
If you will be differentiating pricing among your customers using price levels, you must set up your price levels in advance.
Discount Codes
If you will be differentiating pricing among customers using discount codes, you must set up your discount codes in advance.
*.csv File Format
Make sure you save the locations spreadsheet as a *.csv file type, which is the format required by the mapping screen.
Spreadsheet Columns
Your spreadsheet can consist of some or all of the following columns. Be sure and insert a title in row one of each column heading so that the column can be identified during the import process.
Customer Name (Required, String, 50)
This is the unique name or code that identifies the customer.
Trading Name (String, 50)
This is the name that prints on quotes, acknowledgments, packing slips, and invoices. Unlike the Customer Name, duplicate values are allowed. If a value is not specified, the program will populate this field with the Customer Name.
Customer Terms (String, 50)
Any value in this column must correspond to a code in the Customer Terms table. If you leave this blank when importing a new record, the default terms from the Sales Setup – Customer Terms screen is assigned.
Customer Type (String, 15)
The Customer Type classifies customers for reporting purposes. If a value is not specified, the program assigns the system’s default Customer Type. Any value in this column must correspond to a code in the Customer Types table.
Sales Region (String, 20)
This field is optional and organizes customers by geography for reporting purposes. Any value in this column must correspond to a code in the Sales Regions table.
Commission Code (String, 20)
This is a code that identifies a sales rep or group of sales reps assigned to this customer. Any value in this column must correspond to a code in the Commission Codes table.
Lead Source (String, 10)
This is a code that represents the source of the initial sales lead for this customer. Any value in this column must correspond to a code in the Lead Sources table.
Address Line 1 (String, 50)
This is usually the street address or PO Box associated with the billing address.
NOTE: Each customer’s delivery addresses are imported separately in the Data Import – Customers – Delivery Addresses screen.
Address Line 2 (String, 50)
This can be used for an office, unit, or suite number.
City (String, 50)
This is the city associated with the billing address.
State (String, 20)
This is the state or province associated with the billing address.
Postal Code (String, 20)
This is the postal code associated with the billing address.
Country (String, 50)
This is the country associated with the billing address.
Phone (String, 20)
This is a company-level setting that is not for a specific contact person.
Fax (String, 20)
This is a company-level setting that is not for a specific contact person.
Mobile (String, 20)
This is a company-level setting that is not for a specific contact person.
Email (String, 100)
This is a company-level setting that is not for a specific contact person.
Web (String, 100)
This is the customer’s website address.
Tax Code (String, 10)
Populate this field as follows:
New Customer Import
When importing new customers, only give this field a value when the customer is to be assigned an exception to the Default Sales Tax Code designated in the Tax – Tax Defaults screen. Any value in this column must correspond to a value in the Tax Codes table.
Existing Customer Update
WARNING: When updating existing customers, be aware that if you map this field and leave it blank, a blank value will set the customer’s Tax Code Source to ‘Default’ and will override any tax code exception.
Notes (Blob)
These are unlimited, freeform customer notes.
Price Level (String, 20)
The customer can optionally be assigned to a Price Level. Any value in this column must correspond to a record in the Price Levels table.
Discount Code (String, 20)
The customer can optionally be assigned to a Discount Code. Any value in this column must correspond to a record in the Discount Codes table.
Credit Limit (Float)
The open aging balance plus new sales orders are not permitted to exceed this amount.
Customer PO Required (Boolean)
Enter a ‘T’ if this customer requires a purchase order number on all sales orders. Enter an ‘F’ if a purchase order number is optional.
Ship Method (String, 20)
This is the customer’s preferred shipping method. Any value in this column must correspond to a record in the Ship Methods table.
Tax Reference No (String, 50)
This is the customer’s tax ID code or number.
Bank Name (String, 25)
This identifies the customer’s bank.
Bank Account No (String, 50)
This identifies the customer’s bank account number.
User-Defined Fields (up to 6) (Date or String, 50)
The remaining mapping fields are user-defined fields specified in the Utilities – User-Defined Fields screen for customers. Up to six such fields can be defined in either text or date format.
Date Field Format
If the user-defined field is a date type, it must conform to the Short Date format defined in the Regional and Language Options found in the Windows Control Panel on your file server.
X-Ref Code (String, 30)
If you are using DBA with an outside accounting system, it may be that the customer is identified in the outside system with a short code instead of the full customer name as in DBA. For financial transfer purposes, you can store the short code in this field, which is one of the fields available for transfer in the AR Transfer screen. When you import the AR invoice transfer file into your outside system, this field is mapped to the customer code in your outside system.
Spreadsheet Import
The import process involves four screens that are presented in succession by clicking the Next button.
Screen 1 – Introduction
This first screen provides information notes on each particular import, a list of supporting tables that should be populated prior to running the import, and a listing of required fields.
Screen 2 – Select Import File
1. Column headings
This checkbox is selected by default and tells the program to skip row one of the spreadsheet because it contains column headings.
2. Choose the import file
Click the button in this field to navigate to your *.csv spreadsheet file.
Screen 3 – Map Fields
Om this screen you will map each DBA field, where applicable, to the corresponding column on your spreadsheet. Field explanations are as follows:
Your Data
Click the down arrow and select the column heading from your spreadsheet that matches the DBA field.
Required
This checkbox is selected when the field is required, which means all rows in your spreadsheet must have a value. Mapping is optional with non-required fields.
DBA Field
This is the descriptive name of the field in DBA.
Type
This indicates the field type, such as ‘Blob’ (freeform text), ‘Boolean’ (true/false), ‘Float’ (floating decimal), and ‘String’ (alphanumeric).
Length
This indicates the field length, when applicable.
Screen 4 – Import Data
When you are done with your mapping, click the Import button. The program will attempt to import your data. If any errors are found, the data will not be imported and a list of errors is presented on the screen.
