New Customer/Supplier Import Tool


Purpose

The purpose of this document is to provide you with the Customer & Supplier mappings and aide in preparing your data for import into Khaos Control. The headers within the example files are written in a way that Khaos Control recognises; this document will provide you with an explanation of what each header means and what information you should include within your file, as well as where to put that information. If you are unsure of what a field means, or where to put specific information that is pertinent to your stock records, please ask your Project Control Officer (PCO) or email us.

Preparation

Firstly, you need to determine where you will be acquiring your Customer & Supplier data from. In most cases, the data is being extracted from another database, which may have similar mappings. It’s important that you take time to validate your data before importing this into Khaos Control.

If there are certain values within the mapping file, which are not relevant to your customers/suppliers, they can be deleted from the import file providing they are not a required field.

Excel will strip leading zeros from numeric numbers (telephone numbers), so please ensure that you take appropriate formatting steps, if you have any leading zeros in these fields.

When completed, you will need to either save the file as a TSV/TXT file, ready for importing.

Usage

Once a Customer or Supplier file has been created, it can now be imported into Khaos Control. Use the following steps to do so.

Import a file with customer data

  1. Open the Customer > List screen or the Supplier > List screen.
  2. From the action’s menu Select Import / Export > Import > Tab Separated Format.
  3. Select the Customer import file curated and press ok.
  4. A Dialog will be shown with the matching options available (Figure 1), select the matching options as appropriate.
    Customer/Supplier Import Matching Options Dialog


Preview a file import

  1. To test an import file without making the changes to the database an option, “Preview Mode”, has been provided in the Matching options dialog.
  2. When ticked the file will be checked via the import routine to check for any errors in the supplied file.


Note:

  • When creating the import file within Excel, a blank line is added at the bottom. You can open the file with Notepad and amend or import as it is, where this line will be skipped, and the rest will be imported.

Mapping Table


flashing light bulb!
All YES/NO fields should be done as Boolean values. YES: -1, NO: 0


Field Name
Mandatory
Description
Field Type
Character Limit
COMPANY_CODE
 red cross 
Use this option to define the unique reference number for the company record.
Free Text
30
COMPANY_NAME
 green tick 
The name of the company record being setup. For a retail customer this can be left blank and system will create it as: TITLE + FORENAME + SURNAME.
Free Text
100
COMPCLASS_ID
 green tick 
The company classification to be used. If this is blank then all customers will be Retail, and all suppliers will be Supplier.
Free Text
15
ADDRESS:ADDRESS1
 green tick 
The first line of the address information. Leaving this blank will cause the import to fail, therefore please use a full stop for blank address line 1.
Free Text
150
ADDRESS:ADDRESS2
 red cross 
The second line of the address information.
Free Text
150
ADDRESS:LOCALITY
 red cross 
The third line of the address information, or the locality of the address.
Free Text
150
ADDRESS:TOWN
 green tick 
The town of the address. Leaving this blank will cause the import to fail, therefore please use a full stop for blank towns.
Free Text
70
ADDRESS:COUNTY
 red cross 
The address county.
Free Text
70
ADDRESS:POSTCODE
 green tick 
The address postcode. Leaving this blank will cause the import to fail, therefore please use a full stop for blank postcodes.
Free Text
20
ADDRESS:COUNTRY2_CODE
 red cross 
The 2-character country code for the address. GB for the United Kingdom.
Free Text
2
ADDRESS:FAX
 red cross 
A generic address level fax number.
Numeric
25
ADDRESS:PREF_DELADDRESS
 red cross 
Whether the address is the preferred delivery address.
Yes/No
4
ADDRESS:TELEPHONE
 red cross 
A generic address level telephone number.
Numeric
25
ADDRESS:EMAIL
 red cross 
A generic address level email address.
Free Text
100
CONTACT:TITLE
 red cross 
The title of the contact.
Free Text
40
CONTACT:FORENAME
 red cross 
The forename of the contact.
Free Text
100
CONTACT:SURNAME
 green tick 
The surname of the contact.
Free Text
100
CONTACT:TEL1
 red cross 
The contact’s telephone number.
Numeric
25
CONTACT:FAX1
 red cross 
The contact’s fax number.
Numeric
25
CONTACT:EMAIL
 red cross 
The contact’s email address.
Free Text
100
CONTACT:EMAIL_SUBSCRIBER
 red cross 
Is this contact an email list subscriber.
Yes/No
4
CONTACT:JOB_TITLE
 red cross 
The contact’s job title.
Free Text
200
CONTACT:PREF_DELCONTACT
 red cross 
Whether the contact is the preferred delivery contact.
Yes/No
4
CONTACT:PREF_INVCONTACT
 red cross 
Whether the contact is the preferred invoice contact.
Yes/No
4
CONTACT:CONTACT_NOTE
 red cross 
A note associated with this contact.
Free Text
150
CONTACT:CONTACT_NOTE
 red cross 
1 = Our use and any 3rd parties.

2 = Our use and selected 3rd parties.
3 = Our use and no 3rd parties.
4 = Our use only.
5 = Do not use.
6 = Deceased.

1-6
1
CONTACT:GENDER
 red cross 
The gender of the contact.
Free Text
6
OTHER_REF
 red cross 
Another reference that can be populated for the company record being setup.
Free Text
50
WEB_SITE
 red cross 
The company’s website.
Free Text
50
COMPANY_NOTE
 red cross 
A note for this specific company record.
Free Text
500
COMPANY_STATEMENT_NOTE
 red cross 
A note which will associate with the company statements.
Free Text
255
COMPANY_INVOICE_NOTE
 red cross 
A note which will associate with invoices raised for this company.
Free Text
255
COMPANY_PICKINGLIST_NOTE
 red cross 
A note which will associated with the picking and delivery note reports for this company.
Free Text
255
BANKACCOUNT_NAME
 red cross 
The name of the company’s bank.
Free Text
35
BANKACCOUNT_NUMBER
 red cross 
The bank account number for the company.
Free Text
15
BANKACCOUNT_CODE
 red cross 
The sort code for the company.
Free Text
10
CREDIT_AMOUNT
 red cross 
The amount of credit on sales to be extended to the company. If a supplier import, this will be your credit limit with this specific supplier.
Numeric
20
CURRENCY_ID
 red cross 
The name of the currency to use.

£ = Pounds Sterling
$ = US Dollar
€ = Euro

Free Text
3
DATAPROT_ID
 red cross 
1 = Our use and any 3rd parties.

2 = Our use and selected 3rd parties.
3 = Our use and no 3rd parties.
4 = Our use only.
5 = Do not use.
6 = Deceased.

1-6
1
EC_COMPANY
 red cross 
Companies that are marked as true will have their tax values posted into the EC portion of the tax register and appear in the relevant EC sections on the VAT return.
Yes/No
4
PROFORMA
 red cross 
Indicates if the customer is a proforma customer. This is a customer only value, it is not available for suppliers.
Yes/No
4
TAX_REFERENCE
 red cross 
The VAT Number for the company.
Free Text
20
TERMS_ID
 red cross 
1 = Invoice Date

2 = Month End
3 = Months

1-3
1
TERMS_PERIOD
 red cross 
Related to the terms ID. Usually the number of days/months, depending on your ID selection above.
Numeric
10
VEN_REFERENCE
 red cross 
The company’s vendor reference.
Free Text
20
AGENT_ID
 red cross 
The agents name. This has to be setup within System Data/Agents before importing.
Free Text
50
SUPPLIER
 green tick 
Controls whether Khaos Control should treat this company record as a supplier or not.
Yes/No
4
UDA:<NAME>
 red cross 
Used to define the UDA names and values of a stock item. The <NAME> should be replaced with the name of the UDA, with the values within the stock item rows. Speak to KCSL if you’re unsure about this.
Free Text
30


See Also

Visit KhaosWikiX at https://support.khaoscontrol.com/wiki_kcx/ to see the following:

Contact the Khaos Team