New TSV Sales Order Import Tool

Overview

In order to import sales orders into Khaos Control, there are specific naming conventions and formats that must be followed so that the system recognises them and therefore the information is imported correctly. If you're not sure what field to use, please email Development.

Preparation

The Data

1. Make sure that the data you want to import is correct; as this may come from another database, you need to ensure that you validate it before attempting to import.
2. If there is information in the file that is irrelevant and is not a required field, then you can delete it.
3. Where mandatory fields of data are not included in the import file, the entire order line of respective data will be skipped. The rest of the orders will be imported provided there are no further errors.
4. It is strongly recommended that you do not import GROSS and NET values together.
5. 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, however, 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.
6. 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.
7. 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.
8. Data contained on the import sheet should be formatted appropriately.
9. You should ensure that existing customer supplied data is applied to the data sheet appropriately.
10. 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.

The File

1. Your import file must be saved as TSV.
2. When creating the import file in Excel, a blank line is added at the bottom. You can open the file with Notepad and amend or import as is. The line will be skipped and the rest of the stock items will be imported.
3. Excel will strip leading zeros from numeric stock codes and barcodes, so please ensure that you take appropriate formatting steps if you have any leading zeros in these fields.

Usage

Once a Sales Order file has been created, it can now be imported into Khaos Control.

1. Open a Sales Order screen (show me how).
2. Press F9 or clicking the down arrow immediately to the right of the to open the 'Other Actions' menu.
3. Select Import/Export > Import > Khaos Control > Tab Separated Format. The file selection dialog will open with the App folder as default.
   • Successful Orders can now be searched for in the [ Sales Orders | List ] screen by selecting an appropriate date range or searching by the Associated Ref.
   • Unsuccessful Orders will be reported, for example duplicate orders, or where the Ass Ref has been used on a previous import.

Mapping Table

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		Your order code from your old system which can be referenced back to if required.	Free Text	50
ORDERDATE		The Date of order from customer. This should be provided in the format: DD/MM/YYYY.	Numeric	16
SITE		The name of the site the order should go against.	Free Text	50
CUSTOMER_COMPANYCLASS		The classification of the customer which needs to match an existing company class as defined in [ System Data | Company | Company Classes ].	Free Text	50
CUSTOMER_CURRENCY		The currency name as defined in [ System Data | Currencies ] e.g. Pounds Sterling/Euro etc.	Free Text	50
CUSTOMER_CREATENEW		This determines whether a new customer record is created. The three choices are: IfNoMatch Note: IfNoMatch matches on Company Code Always Never	Free Text	50
ITEM_SKU		The Stock item reference.	Free Text	50
ITEM_MAPPING		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		Order line Quantity.	Numeric	15
INVADDR_LINE1		Invoice address line 1.	Free Text	100
INVADDR_TOWN		Invoice address town.	Free Text	50
INVADDR_COUNTRY_CODE		Invoice address country, as defined in [ System Data | International | Countries ] e.g. United Kingdom/France etc.	Free Text	50

Full Set of Values

Field Name	Mandatory	Description	Field Type	Character Limit
ASSOCIATEDREF		Your order code from your old system which can be referenced back to if required.	Free Text	50
ORDERDATE		The Date of order from customer. This should be provided in the format: DD/MM/YYYY.	Numeric	15
SITE		The name of the site the order should go against.	Free Text	50
CUSTOMER_COMPANYCLASS		The classification of the customer which needs to match an existing company class as defined in [ System Data | Company | Company Classes ].	Free Text	50
CUSTOMER_CURRENCY		The currency name as defined in [ System Data | International | Currencies ] e.g. Pounds Sterling/Euro etc.	Free Text	50
CUSTOMER_CREATENEW		This determines whether a new customer record is created. The three options are: IfNoMatch Note: IfNoMatch matches on Company Code Always Never	Free Text	50
ITEM_SKU		The Stock item reference.	Free Text	50
ITEM_MAPPING		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		Order line Quantity.	Numeric	15
INVADDR_LINE1		Invoice address line 1.	Free Text	100
INVADDR_TOWN		Invoice address town.	Free Text	50
INVADDR_COUNTRY_CODE		Invoice address country, as defined in [ System Data | International | Countries ] e.g. United Kingdom/France etc.	Free Text	50
SALES_SOURCE	RECOMMENDED	[ System Data | Sales Order Processing | Sale 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. Note: 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	Delivery address country, as defined in [ System Data | International | 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	35
INVADDR_POSTCODE	OPTIONAL	Invoice address post code.	Free Text	10
INVCONTACT_EMAIL	OPTIONAL	Invoice contact's email address.	Free Text	250
INVCONTACT_MOBILE	OPTIONAL	Invoice contact's telephone number.	Numeric	30
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	35
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 in [ System Data | Sales Order Processing | Agents ].	Free Text	30
COURIER	OPTIONAL	The Courier name used, if setup in [ System Data | Courier / Shipping | 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	250
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	50
CUSTOMER_COMPANYTYPE	OPTIONAL	The Company Type, as defined in [ System Data | Company | Company Types ] 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	50
CUSTOMER_SOURCECODE	OPTIONAL	The source keycode of the customer.	Free Text	50
CUSTOMER_TAXREFERENCE	OPTIONAL	The customer's TAX reference.	Free Text	20
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 | Sales Order Processing | Free Item Reasons ] 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. Note: 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 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

See Also

• Sales Orders screen