====== Control - Text Based Importing ======

This document will discuss how to export customers and contacts from Control and also import into Control

===== Exporting Customers and Contacts =====

  * Choose the Company/ Contact explorer and use the column chooser for these fields to maximize the accuracy of the importing process

**Company Explorer**

  * Company Name
  * Status
  * Department
  * Address 1
  * Address 2
  * Primary Num
  * Secondary Num
  * City
  * State
  * Postal Code
  * Payment Terms
  * Credit Limit
  * Customer Credit Balance
  * Pricing Level
  * Company Pricing Plan
  * Active
  * Tax Exempt
  * Industry
  * Region
  * Tax Class
  * In the Company Search Criteria for the Company Explorer be sure to select all company types and also select Include Inactive Companies.

**Contact Explorer**

  * First Name
  * Last Name
  * Shipping Address 1
  * Shipping Address 2
  * Shipping City
  * Shipping State
  * Shipping Postal Code
  * E-mail
  * Contact Type
  * Primary Contact
  * Billing Contact
  * In the Contact Search Criteria for the Contact Explorer be sure to select Include Inactive Contacts, Full Contact, and Limited Contact.
  * In both explorers, make sure that you are viewing all the contacts/customers respectively

  - When finished, right-click anywhere on each grid then choose export grid
  - Make the export format of the export file a Comma-Delimited Text(CSV) and Export to File.

===== Formatting Contact and Customer List =====

Below are any columns that require changes and the changs that they need

  * Company Export File {{:importsample2.jpg?nolink&|importsample2.jpg}}* The status field is broken up into 3 columns: IsClient, IsProspect, IsVendor which are Boolean values
      * in this example the following formulas are used
        * Cell C2=IF(B2="Client", "true", "false")
        * Cell C3=IF(B2="Prospect", "true", "false")
        * Cell C4=IF(B2="Vendor", "true", "false")
      * To distribute the cell down each column, double click the bottom right corner of the cell you wish to duplicatete.
  * In the phone number field, remove all '(', ')', '-', ' '
      * Use text to columns to seperate into 3 fields: Area Code, Phone Number, Extension
  * Any Custom Payments, Primary Salespersons, Pricing Plans, or Tax Classes must exist in Control before the the import or they won't be mapped
      * Tax Exempt should be an IsTaxable column with a Boolean value.
        * Cell XX=IF(XX="Tax Exempt", "true", "false")
      * Active should be an IsActive column with a Boolean value.
        * Cell XX=IF(YY="TaxExempt", "true", "false")
  * Contact Export File
      * In the phone number field, remove all '(', ')', '-', ' '
      * Contact Type should be an IsFullContact column with a Boolean value
        * Cell XX=IF(YY="Full Contact", "true", "false")
      * Primary Contact should be an IsPrimaryContact column with a Boolean value
      * Billing Contact should be an IsBillingContact column with a Boolean value
        * Use text to columns to seperate into 3 fields: Area Code, Phone Number, Extension

===== Mapping Companies and Contacts Using Two Different Files =====

  - In the CompanyExport file, sort in ascending order based on the CompanyName field
  - In the companyexport file, create a CompanyID field in column A
  - Number the first 3 rows, 1, 2, 3, respectively
  - After instering "3", double click on the small box in the bottom right handed corner

  * Be sure to scan down the list of CompanyID's to make sure that any CompanyName has a CompanyID

  - In the ContactExport file, sort in ascending order based on the CompanyName field
  - Copy both the A(CompanyID) and B(CompanyName) columns from the CompanyExport file into the ContactExport file and put them in the A and B column without overwritting data
  - Leave column C blank and make sure that the CompanyName is in the D column
  - In row 2 of column C write this function which would assign CompanyID's to the contact list EVEN IF NOT ALL COMPANIES ON THE CONTACTEXPORT EXIST IN THE COMPANYEXPORT
      * =INDEX($A$2:$A$NUM,MATCH(D2, $B$2:$B$NUM,FALSE),1)
      * where A = CompanyID from CompanyExport
      * B = CompanyName from CompanyExport
      * NUM = The number of the row in which the LAST companyID from column A lies
  - After instering the function, double click on the small box in the bottom right handed corner
  - Copy all data in that column, right click and paste special
  - Paste special using values
  - Remove both columns A and B from the CompanyExport file
  - Compare the ID's from CompanyExport and ContactExport file and make sure that nothing is mismatched
  - On both files, "Save As" a tab delimited file

===== Importing Customers and Contacts =====

  - In Control go to Tools–>Import Data–>Import from Text Files
  - In the Source field, browse for the location of your CompanyExport tab delimited file
  - If necessary, Select the "Use Secondary Source" and locate the ContactExport tab delimited fle
  - In the Key column, place a check next to the CompanyID field from the CompanyExport file and another by the ContactID from the ContactExport
  - Choose Companies as the template for the import
  - Map the fields from the Source Data to the Control field
      * CompanyExport CompanyName—> Company→Customer→Company Name
      * CompanyExport IsActive—> Company→Customer→Is Active
      * CompanyExport IsProspect—> Company→Customer→Is Prospect
      * CompanyExport IsClient—> Company→Customer→Is Client
      * CompanyExport Indsutry—> Company→Customer→Industry
      * CompanyExport Payment Terms—> Company→Customer→Payment Terms
      * CompanyExport IsTaxExempt—> Company→Customer→Is Tax Exempt
      * CompanyExport Tax Class—> Company→Customer→Tax Class
      * CompanyExport Origin—> Company→Customer→Origin
      * CompanyExport Credit Limit—> Company→Customer→Credit Limit
      * CompanyExport Credit Balance—> Company→Customer→Credit Balance
      * CompanyExport Primary Area Code—> Company→Customer→Phone Number→ Area Code
      * CompanyExport Primary Number—> Company→Customer→Phone Number→Phone Number
      * CompanyExport Primary Ext—> Company→Customer→Phone Number→Extension
      * CompanyExport Fax Area Code—> Company→Customer→Fax Number→Area Code
      * CompanyExport Fax Number—> Company→Customer→Fax Number→Phone Number
      * CompanyExport Address 1—> Company→Customer→Billing Address→Street Address 1
      * CompanyExport Address 2—> Company→Customer→Billing Address→Street Address 2
      * CompanyExport City—> Company→Customer→Billing Address→City
      * CompanyExport State—> Company→Customer→Billing Address→State
      * CompanyExport Address 1—> Company→Customer→Billing Address→Street Address 1
      * CompanyExport Zip Code—> Company→Customer→Billing Address→Postal Code
      * CompanyExport IsVenddor—> Company→Customer→Is a Vendor
      * Contact Export First Name—> Company→Customer→Contacts→First Name
      * Contact Export Last Name—> Company→Customer→Contacts→Last Name
      * Contact Export Shipping Address 1—> Company→Customer→Contacts→Shipping Address→Shipping Address 1
      * Contact Export Shipping Address 2—> Company→Customer→Contacts→Shipping Address→Shipping Address 2
      * Contact Export City—> Company→Customer→Contacts→Shipping Address→City
      * Contact Export State—> Company→Customer→Contacts→Shipping Address→State
      * Contact Export Zip Code—> Company→Customer→Contacts→Shipping Address→Postal Code
      * Contact Export Primary Area Code—> Company→Customer→Contacts→Phone Number→ Area Code
      * Contact Export Primary Number—> Company→→CustomerContacts→Phone Number→Phone Number
      * Contact Export Primary Ext—> Company→Customer→Contacts→Phone Number→Extension
      * Contact Export Fax Area Code—> Company→Customer→Contacts→Fax Number→Area Code
      * Contact Export Fax Number—> Company→Customer→Contacts→Fax Number→Phone Number
      * Contact Export Email—> Company→Customer→Contact→Email Address
      * Contact Export IsPrimaryContact—> Company→Customer→Contact→Is Primary Contact
      * Contact Export IsAccountingContact—> Company→Customer→Contact→Is Accounting Contact
        * After mapping these fields Save Mapping
  - Select the Advanced Settings button
  - Make sure that Source Files Delimiter = Tab( ) and that Source File String Delimiter = Double Quotes (")
  - Use All Lines of the Source Files, Heading Row in Source Files, and Skip Blank Lines in Source Files all need to be checked.
      * IF YOU ARE IMPORTING INTO A DATABASE THAT ALREADY HAS COMPANY AND CUSTOMER INFORMATION, BE SURE TO CHECK THE "LOOKUP RECORD BEFORE IMPORTING"
        * Match this field with "Company Name"
  - Hit the Save button when ready and monitor the import for any errors

  * Topic Item