New TSV Sales Order Import Tool


Purpose

The purpose of this document is to provide you with the Sales Order mappings and aide in preparing your Sales Order 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, please ask your Project Control Officer (PCO) or email us.

Preparation

Firstly, you need to determine where you will be acquiring your 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 Sales Orders, they can be deleted from the import file providing they are not a required field.

Where system required mandatory fields of data are not included, the entire order line of respective data will be skipped. The rest of the orders will complete provided there are no further errors.

You should ensure that existing customer supplied data is applied to the data sheet appropriately. Data contained on the import sheet should be formatted appropriately.

The Stock Code field will be compared against the Stock Code in Khaos Control, if no match is found the system will then search the Barcode values against the Stock Codes to try to find a match. If no matches are found at this level, then the final search will be against the Other Ref field of the Stock Codes. Only once these levels have all been tried will the system fail the Order based on no matching Stock Code. It is important that the whole Order fails in this situation, so the Stock Code can be corrected, and a full Order imported. The rest of the file will still process if one order line fails on Stock Code matching.

If you decide to make use of the Extended Stock Description on the Sales Order, it is strongly recommended that this is the only text that exists in this field in Khaos Control.

Where no Invoice Address details are provided the system will use the Delivery Address fields for both Invoice and Delivery. Where partial Invoice Address or partial Delivery Address details are provided the Order will fail to import. The rest of the file should still process. Where only Invoice Address details are provided the system will make use of Invoice for both Delivery and Invoice Address details.

It is strongly recommended that you do not import GROSS and NET values together.

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

When completed, save the file as an TSV file, ready for importing.

Usage

Once a Sales Order file has been created, it can now be imported into Khaos Control. Use the following steps to do so. Ensure the file has been saved as a TSV file.

  1. Open a Sales Order screen.
  2. From the action’s menu Select Import/Export > Import > Khaos Control > Tab Separated Format.
    The file selection dialog will open with the App folder as default.
  3. Successful Orders can now be searched for in the Sales Order/List screen, by selecting an appropriate date range or searching by the Associated Ref.
  4. Unsuccessful Orders will be reported (for instance duplicate orders, where the Ass Ref has been used on a previous import.)


Note:

  • If you have multiple lines for an order (a line for each item), you only need to include the payment information on one of the lines, otherwise you end up with payment lines being duplicated. You only need to include payment information on multiple lines if you need multiple payment lines on the order.

Mapping Table


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


Minimum Set of Values

Field Name
Mandatory
Description
Field Type
Character Limit
ASSOCIATEDREF
 green tick 
Your order code from your old system which can be referenced back to if required.
Free Text
50
ORDERDATE
 green tick 
The Date of order from customer. This should be provided in the format: DD/MM/YYYY.
Numeric
15
SITE
 green tick 
The name of the site the order should go against.
Free Text
50
CUSTOMER_COMPANYCLASS
 green tick 
The classification of the customer which needs to match an existing company class as defined in System Data.
Free Text
50
CUSTOMER_CURRENCY
 green tick 
The currency name as defined in System Data/Currencies. E.g. Pounds Sterling/Euro etc.
Free Text
50
CUSTOMER_CREATENEW
 green tick 
This determines whether a new customer record is created. That is input one of these three choices:
IfNoMatch/Always/Never.


IfNoMatch matches on Company Code.

Free Text
50
ITEM_SKU
 green tick 
The Stock item reference.
Free Text
50
ITEM_MAPPING
 green tick 
How the import will try to match stock items. The value should be one of these choices:
StockCode/OtherRef/Barcode/ WebCode/Automatic.
Free Text
20
ITEM_QTY
 green tick 
Order line Quantity.
Numeric
15
INVADDR_LINE1
 green tick 
Invoice address line 1.
Free Text
100
INVADDR_TOWN
 green tick 
Invoice address town.
Free Text
50
INVADDR_COUNTRY_CODE
 green tick 
Invoice address country, as defined in System Data/Countries. E.g. United Kingdom/France etc.
Free Text
50


Full Set of Values

Field Name
Mandatory
Description
Field Type
Character Limit
ASSOCIATEDREF
 green tick 
Your order code from your old system which can be referenced back to if required.
Free Text
50
ORDERDATE
 green tick 
The Date of order from customer. This should be provided in the format: DD/MM/YYYY.
Numeric
15
SITE
 green tick 
The name of the site the order should go against.
Free Text
50
CUSTOMER_COMPANYCLASS
 green tick 
The classification of the customer which needs to match an existing company class as defined in System Data.
Free Text
50
CUSTOMER_CURRENCY
 green tick 
The currency name as defined in System Data/Currencies. E.g. Pounds Sterling/Euro etc.
Free Text
50
CUSTOMER_CREATENEW
 green tick 
This determines whether a new customer record is created. That is input one of these three choices:
IfNoMatch/Always/Never.


IfNoMatch matches on Company Code.

Free Text
50
ITEM_SKU
 green tick 
The Stock item reference.
Free Text
50
ITEM_MAPPING
 green tick 
How the import will try to match stock items. The value should be one of these choices:
StockCode/OtherRef/Barcode/ WebCode/Automatic.
Free Text
20
ITEM_QTY
 green tick 
Order line Quantity.
Numeric
15
INVADDR_LINE1
 green tick 
Invoice address line 1.
Free Text
100
INVADDR_TOWN
 green tick 
Invoice address town.
Free Text
50
INVADDR_COUNTRY_CODE
 green tick 
Invoice address country, as defined in System Data/Countries. E.g. United Kingdom/France etc.
Free Text
50
SALES_SOURCE
RECOMMENDED
Sales Source. E.g. Website/Amazon/Telesales etc.
Free Text
50
CALC_METHOD
RECOMMENDED
This determines how a sales order line is calculated in terms of its Tax and Rounding. That is input one of these three choices:
Auto/Net/Gross.
Free Text
50
PAYMENT_AMOUNT
RECOMMENDED
The orders payment value in GBP.
Numeric
20
ITEM_UNITPRICE_GROSS
RECOMMENDED
Gross unit value, including Tax in GBP.
Numeric
20
CUSTOMER_URN
RECOMMENDED
Customer URN (unique reference number) to match the order to.
Free Text
20
INVCONTACT_TITLE
RECOMMENDED
Invoice contact's title. E.g Mr, Mrs, etc. This does not have to match predefined values.
Free Text
40
INVCONTACT_FORENAME
RECOMMENDED
Invoice contact's forename. This is required if the customer does not exist in Khaos Control.
Free Text
100
INVCONTACT_SURNAME
RECOMMENDED
Invoice contact's surname. This is required if the customer does not exist in Khaos Control.
Free Text
100
DELADDR_LINE1
RECOMMENDED
Delivery address line 1.
Free Text
100
DELADDR_TOWN
RECOMMENDED
Delivery address town.
Free Text
70
DELADDR_POSTCODE
RECOMMENDED
Delivery address post code
Free Text
20
DELADDR_COUNTRY
RECOMMENDED
Countries. E.g. United Kingdom/France.
Free Text
50
DELCONTACT_TITLE
RECOMMENDED
Delivery contact's title. E.g Mr, Mrs, etc.
Note: This does not have to match predefined values.
Free Text
50
DELCONTACT_FORENAME
RECOMMENDED
Delivery contact's forename.
Free Text
100
DELCONTACT_SURNAME
RECOMMENDED
Delivery contact's surname.
Free Text
100
ITEM_STOCKDESCRIPTION
OPTIONAL
The product description. This can be left blank to use the description against the product file.
Free Text
250
INVADDR_LINE1
OPTIONAL
Invoice address line 1.
Free Text
100
INVADDR_LINE2
OPTIONAL
Invoice address line 2.
Free Text
100
INVADDR_LINE3
OPTIONAL
Invoice address line 3.
Free Text
100
INVADDR_COUNTRY
OPTIONAL
Invoice address county.
Free Text
70
INVADDR_POSTCODE
OPTIONAL
Invoice address post code.
Free Text
20
INVCONTACT_EMAIL
OPTIONAL
Invoice contact's email address.
Free Text
250
INVCONTACT_MOBILE
OPTIONAL
Invoice contact's telephone number.
Numeric
20
DELADDR_LINE2
OPTIONAL
Delivery address line 2.
Free Text
100
DELADDR_LINE3
OPTIONAL
Delivery address line 3.
Free Text
100
DELADDR_COUNTY
OPTIONAL
Delivery address county.
Free Text
70
DELCONTACT_EMAIL
OPTIONAL
Delivery contact's email address.
Free Text
250
DELCONTACT_MOBILE
OPTIONAL
Delivery contact's telephone number.
Numeric
30
AGENT
OPTIONAL
The Agent used if setup within System Data/Agents.
Free Text
30
COURIER
OPTIONAL
The Courier Code used, if setup within System Data/Couriers.
Free Text
100
_DELIVERYCHARGE_GROSS
OPTIONAL
Gross delivery charge, including Tax in GBP.
Numeric
10
_DELIVERYCHARGE_NET
OPTIONAL
Net delivery charge, excluding Tax.
Numeric
10
DELIVERYDATE
OPTIONAL
The Date of delivery. This should be provided in the format:
DD/MM/YYYY.
Numeric
15
DESTINATIONSITE
CONDITIONAL
Only useful for SO/PO Transfer orders, to set the Destination Site.
Free Text
50
DISCOUNTCODES
OPTIONAL
Separated list of discount keycodes.
Free Text
30
GROSSTOTAL
OPTIONAL
Optionally provide the gross total you expect the order to be; if Khaos fails to match this total, the user can configure what action should be taken.
Numeric
20
INVOICENOTE
OPTIONAL
A note which will associate with invoices raised for this company.
Free Text
50
INVOICEPRIORITY
OPTIONAL
The Sales Order Invoice Priority which should be assigned to the order.
Free Text
50
KEYCODE
OPTIONAL
The Keycode associated to the invoice.
Free Text
100
ORDERNOTE
OPTIONAL
A note about the order.
Free Text
250
PONUMBER
OPTIONAL
The PO number linked to the invoice.
Free Text
20
REQUIREDDATE
OPTIONAL
The date the order is required. This should be provided in the format:
DD/MM/YYYY.
Numeric
15
SORDERCODE
EXPORT
Export only.
Free Text
20
SORDERID
EXPORT
Export only.
Free Text
15
SORDERTYPE
CONDITIONAL
Only to set SO/PO Transfer order; otherwise ignored.
Free Text
35
VALUEDISCOUNT
OPTIONAL
Always gross.
Numeric
8
WEBSITE
OPTIONAL
The website name.
Free Text
255
CUSTOMER_MAILINGSTATUS
OPTIONAL
The mailing status as per the customer record in Khaos Control.
Free Text
50
CUSTOMER_OTHERREF
OPTIONAL
An alternative reference to the customer.
Free Text
100
CUSTOMER_COMPANYTYPE
OPTIONAL
The Company Type, as defined in System Data/Company Type. E.g. Hospital/Hotel etc.
Free Text
50
CUSTOMER_COMPANYNAME
OPTIONAL
Must be specified if creating a new Customer (URN is new.) Ignored if matching to an existing Customer.
Free Text
100
CUSTOMER_SOURCECODE
OPTIONAL
The source keycode of the customer.
Free Text
50
CUSTOMER_TAXREFERENCE
OPTIONAL
The customer’s TAX reference.
Free Text
30
INVADDR_EMAIL
OPTIONAL
Invoice address's email address.
Free Text
250
INVADDR_TELEPHONE
OPTIONAL
Invoice address's telephone number.
Numeric
30
DELADDR_EMAIL
OPTIONAL
Delivery address's email address.
Free Text
250
DELADDR_TELEPHONE
OPTIONAL
Delivery address’s telephone number.
Numeric
30
DELCONTACT_OPTINNEWSLETTER
OPTIONAL
The delivery contact mailing option.
Yes/No
2
ITEM_EXTENDEDDESCRIPTION
OPTIONAL
The extended description for the stock item. Lines separated with \n.
Free Text
2000
ITEM_FREEREASON
OPTIONAL
The Free Item Reason, as defined in System Data/Free Item Reason. E.g. Sample/Customer Service etc.
Free Text
60
ITEM_IMPORTREF
OPTIONAL
This is items import ref e.g. Amazon.
Free Text
100
ITEM_DESCRIPTIONSOURCE
OPTIONAL
This relates to the question of where the stock description is being pulled from e.g. is it from inside Khaos Control or from the TSV import file.

This has 3 possible values: Explicit/StockDesc/WebCategories

Free Text
100
ITEM_PERECENTDISCOUNT
OPTIONAL
The percentage discount on the item against the item line on the sales order; i.e. Disc% in the [Sales Order] screen’s items grid.
Numeric
4
ITEM_UNITPRICE_NET
OPTIONAL
The net unit price for the item on the sales order, i.e. the amount for one item before VAT.
Numeric
4
ITEM_WEBITEMREF
OPTIONAL
This is the web item reference for the channel order item which is saved against the item line on the sales order. Used by Khaos Control internally to send up shipping references for certain channels e.g. Shopify.

Unless you have a specific reason for populating this, it should be left blank.

Free Text
50
REMAINDERONACCOUNT
OPTIONAL
This flags whether there is a remainder value on the customer’s account.
Yes/No
4
PAYMENT_CASH_REFERENCE
OPTIONAL
The reference for the cash payment.
Free Text
20
PAYMENT_CHEQUE_REFERENCE
OPTIONAL
The reference for the cheque payment.
Free Text
20
PAYMENT_VOUCHER_REFERENCE
OPTIONAL
The voucher code of the voucher to use as payment. This voucher code must already exist in Khaos Control.
Free Text
20
PAYMENT_CARD_AUTHCODE
OPTIONAL
The authorisation code from the payment.
Numeric
15
PAYMENT_CARD_ACCOUNTNUMBER
OPTIONAL
The payment card’s account number.
Numeric
10
PAYMENT_CARD_CARDNUMBER
OPTIONAL
The payment card’s long card number.
Numeric
30
PAYMENT_CARD_CARDSTART
OPTIONAL
The payment card’s start date. This should be provided in the format:

MM/YY or MM/YYYY

Numeric
15
PAYMENT_CARD_CARDEXPIRE
OPTIONAL
The Card Expiry Date. This should be provided in the format:

MM/YY or MM/YYYY.

Numeric
15
PAYMENT_CARD_CARDISSUE
OPTIONAL
The Card Issue number e.g. 07.
Numeric
2
PAYMENT_CARD_CARDCV2
OPTIONAL
The Card CV2 number.
Numeric
4
PAYMENT_CARD_CARDHOLDER
OPTIONAL
The Name of the card holder e.g. ‘Mr J Bloggs’
Free Text
50
PAYMENT_CARD_ISPREAUTH
OPTIONAL
Whether the payment is pre-authorised.
Yes/No
4
PAYMENT_CARD_TRANSACTIONID
OPTIONAL
The card payments Transaction ID.
Free Text
50
PAYMENT_CARD_SECURITYREF
OPTIONAL
The security ref provided by the payment service provider for this transaction.
Free Text
50
PAYMENT_CARD_PREAUTHREF
OPTIONAL
The reference of the payment’s preauthorisation transaction.
Free Text
50
PAYMENT_CARD_SECURITYTOKEN
OPTIONAL
The Security token. Depending on the integration, this may be required for authorising payments.
Free Text
100
PAYMENT_CARD_LAST4DIGITS
OPTIONAL
The last 4 digits of the main number on the debit/credit card.
Numeric
4
PAYMENT_CARD_FRAUDDATA
OPTIONAL
May be set to ‘HOLD’ (case sensitive) or left blank. If set to HOLD, this will set the hold_payment flag, which causes the payment to be held until a user manually uses the right click > View Payment details > Accept Authorisation button or Reject Authorisation button.
Free Text
4
PAYMENT_CARD_AAVCV2RESULTS
OPTIONAL
The results of the last AAV or CV2 check for this card.
Free Text
100
PAYMENT_CARD_TIMESTAMP
OPTIONAL
The transaction datetime of the payment, in the RFC 3339 format without the timezone offset:

YYYY-MM-DDTHH:MM:SS
e.g. 2020-01-25T13:44:45

DATETIME
50
PAYMENT_BANKACCOUNT_ID
OPTIONAL
The ID of the payment bank account. Only one or the other of the PAYMENT_BANKACCOUNT_ID and PAYMENT_BANKACCOUNT_NAME need be specified.
Numeric
15
PAYMENT_BANKACCOUNT_NAM
OPTIONAL
The name of the payment bank account.
Free Text
100

Contact the Khaos Team