Customer Import

Modified on Mon, 22 Jun 2020 14:11 by Curtis Riddle — Categorized as: Address Book, Import- Export Manager

Table of Contents [Hide/Show]

   General Info
   Customer Import File Definitions
   Customer Import Additional Steps
   General Import Template File Notes

General Info¶

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

Screen Name: IMPORTCUST

Function: Import new customers and update existing customers. This tool can also be used to import Ship To records.

Customer Import Screen

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. This feature can be used to update organization information other than the company name and address details, which will not be changed.

This box should be checked if you are attempting to update existing organizations with an import file that has custnos entered. If this box is unchecked, and the import file contains custnos that already exist, the import process will create new organizations for the duplicates using the default org numbering logic.

Validate Custnos: Checking this box will validate each custno in your spreadsheet and verify it does not currently exist in Adjutant. 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.

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

The list is limited to attributes in the Organization Attributes rule that have the Show in Import flag checked. Click here for an example.

Customer Import File Definitions¶

f1 - Company - Name of Company/Organization

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.

f3 - Address1 - Physical Street Address

f4 - City - Physical Address City Name

f5 - State - Physical Address two-letter postal abbreviation for the state

f6 - ZipCode - Physical Address Zip Code (you can use zip+4 if you have it) http://www.abiscorp.com/faq/index.php?title=Special:Preferences

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)

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

f11 - Custno - Customer Number (Organization ID), Leave blank to auto-assign customer numbers. If you are attempting to update existing organization records, make sure the 'Match Existing Orgs' option is checked - otherwise the import process will create new organizations for any existing, matched custnos.

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

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

f14 - Title1 - Job title for Contact1 in file.

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.

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

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

f18 - DNECredit - Enter a 'y' if nobody is allowed exceed the customer's credit limit. This checks the 'Do Not Exceed' box in the BillTo record.

f19 - Credit Hold - Enter a 'y' if the customer is currently on credit hold. This checks the 'Credit Hold' box in the BillTo record.

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

f21 - MiscCode - Updates the Misc Code in the BillTo and the SoldTo records. Accepts 10 character max.

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

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

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

f25 - Contact2 - Contact 2 name.

f26 - Email2 - Contact 2 email address.

f27 - Phone2 - Contact 2 primary phone number.

f28 - Title2 - Contact 2 job title.

f29 - Contact3 - Contact 3 name.

f30 - Email3 - Contact 3 email address.

f31 - Phone3 - Contact 3 primary phone number.

f32 - Title3 - Contact 3 job title.

f33 - Contact4 - Contact 4 name.

f34 - Email4 - Contact 4 email address.

f35 - Phone4 - Contact 4 primary phone number.

f36 - Title4 - Contact 4 job title.

f37 - Country - Name of country.

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

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

f40 - TaxExemptNum - Tax Exempt number.

f41 - Discount - ?

f42 - ARAcct - Updates the AR GL Account number in the BillTo record. Used to override the the default AR account.

f43 - InvForm - Updates the Invoice Form drop-down in the BillTo attribute, if the customer has a specific form that should be defaulted. Must match a value in the INVFORM rule.

f44 - CreditLimit - Updates the customer's credit limit in the BillTo record.

f45 - AcctNumber - The account number associated with their shipping/freight company. Updates the Account Number field in the ShipTo record.

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.

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.

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

f51 - Latitude - Numerical Latitude value, up to 5 decimals. Only applicable to Ship To records.

f52 - Longitude - Numerical Longitude value, up to 5 decimals. Only applicable to Ship To records.

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

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

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

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

f57 - TaxTable - Sets the Tax Table value in the ShipTo record in the format 77777A. This field is not required. Leave this field blank to allow the tax table to be filled in by the Ship To Tax Table Update.

f58 - TaxRate - Sets the TaxRate %% in the ShipTo record. This field is no longer used. Leave this field blank to allow the tax table to be filled in by the Ship To Tax Table Update.

f59 - County - No longer used. You can leave this field blank.

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

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

f62 - MiscCode2 - Updates the Misc Field in the ShipTo record. Accepts 99 characters alphanumeric.

f63 - IsBillTo - Enter a 'y' if this organization should be established with a BillTo attribute.

f64 - IsSoldTo - Enter a 'y' if this organization should be established with a SoldTo attribute.

f65 - IsShipTo - Enter a 'y' if this organization should be established with a ShipTo attribute.

f66 - BillToCI - Organization ID (Custno) of child customer (Bill To). This will make the line record a Bill To parent of the child organization entered in this field.

f67 - SoldToCI - Organization ID (Custno) of child customer (Sold To). This will make the line record a Sold To parent of the child organization entered in this field.

f68 - ShipToCI - Organization ID (Custno) of child customer (Ship To). This will make the line record a Ship To parent of the child organization entered in this field.

f69 - BillToPI - Organization ID (Custno) of parent customer (Bill To). This will make the line record a Bill To child of the parent organization entered in this field.

f70 - SoldToPI - Organization ID (Custno) of parent customer (Sold To). This will make the line record a Sold To child of the parent organization entered in this field.

f71 - ShipToPI - Organization ID (Custno) of parent customer (Ship To). This will make the line record a Ship To child of the parent organization entered in this field.

f72 - WebSite - Organization Website address

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

f74 - Address3 - Physical Address 3

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 - Updates the organization Zone entry. Must match a valid Zone Code entry from the LZONE rule.

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 - Updates the SoldTo Cust Class entry. Must match a valid code from the CUSTCLASS rule.

Customer Import Additional Steps¶

Once all data has been imported and reconciled, there are two steps that must be completed to complete the customer record setups.

Run the Update Blank County Records (UPDATECOUNTY) process to fill in the Organization County fields from the latest county tax table data.

Run the Update Blank Ship To Tax Tables (UPDATESHIPTOTAX) process to fill in Ship To Tax Tables from the latest tax table data.

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 are using 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 field mapping process difficult.
- The third row (if present) is labelled either with the same values from row 2, or with more detailed field descriptions. The third row 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.

It is a good idea to perform a Mapping Table Import using a current source list before beginning on any imports. Without a current mapping table, the quality of imports can be compromised. The Mapping Table Import only needs to be run once on a newly set up Adjutant system. It does not need to be run before each import.
- Export a current list from the Mapping Table Import screen from an updated system (or contact your Implementation Consultant for an updated file).
- Perform a Mapping Table Import on the target system with the XLS file from the step above.