New TSV Sales Order Import Tool

New Stock Import Tool is not enabled by default.

Please email Development for more information.
Note: there will be additional costs involved if this option is enabled.

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

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 Text50
ORDERDATE green tick The Date of order from customer. This should be provided in the format: DD/MM/YYYY.Numeric15
SITE green tick The name of the site the order should go against.Free Text50
CUSTOMER_COMPANYCLASS green tick The classification of the customer which needs to match an existing company class as defined in [ System Data | Company | Company Classes ].Free Text50
CUSTOMER_CURRENCY green tick The currency name as defined in [ System Data | International | Currencies ] e.g. Pounds Sterling/Euro etc.Free Text50
CUSTOMER_CREATENEW green tick This determines whether a new customer record is created. The three options are:
  • IfNoMatch
    Note: IfNoMatch matches on Company Code
  • Always
  • Never
Free Text50
ITEM_SKU green tick The Stock item reference.Free Text50
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 Text20
ITEM_QTY green tick Order line Quantity.Numeric15
INVADDR_LINE1 green tick Invoice address line 1.Free Text100
INVADDR_TOWN green tick Invoice address town.Free Text50
INVADDR_COUNTRY_CODE green tick Invoice address country, as defined in [ System Data | International | Countries ] e.g. United Kingdom/France etc.Free Text50
SALES_SOURCERECOMMENDED[ System Data | Sales Order Processing | Sale Source ] e.g. Website/Amazon/Telesales etc.Free Text50
CALC_METHODRECOMMENDEDThis 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 Text50
PAYMENT_AMOUNTRECOMMENDEDThe orders payment value in GBP.Numeric20
ITEM_UNITPRICE_GROSSRECOMMENDEDGross unit value, including Tax in GBP.Numeric20
CUSTOMER_URNRECOMMENDEDCustomer URN (unique reference number) to match the order to.Free Text20
INVCONTACT_TITLERECOMMENDEDInvoice contact's title. e.g Mr, Mrs, etc. This does not have to match predefined values.Free Text40
INVCONTACT_FORENAMERECOMMENDEDInvoice contact's forename. This is required if the customer does not exist in Khaos Control.Free Text100
INVCONTACT_SURNAMERECOMMENDEDInvoice contact's surname.
Note: this is required if the customer does not exist in Khaos Control.
Free Text100
DELADDR_LINE1RECOMMENDEDDelivery address line 1.Free Text100
DELADDR_TOWNRECOMMENDEDDelivery address town.Free Text70
DELADDR_POSTCODERECOMMENDEDDelivery address post codeFree Text20
DELADDR_COUNTRYRECOMMENDEDDelivery address country, as defined in [ System Data | International | Countries ] e.g. United Kingdom, France.Free Text50
DELCONTACT_TITLERECOMMENDEDDelivery contact's title, e.g Mr, Mrs, etc.
Note: This does not have to match predefined values.
Free Text50
DELCONTACT_FORENAMERECOMMENDEDDelivery contact's forename.Free Text100
DELCONTACT_SURNAMERECOMMENDEDDelivery contact's surname.Free Text100
ITEM_STOCKDESCRIPTIONOPTIONALThe product description. This can be left blank to use the description against the product file.Free Text250
INVADDR_LINE1OPTIONALInvoice address line 1.Free Text100
INVADDR_LINE2OPTIONALInvoice address line 2.Free Text100
INVADDR_LINE3OPTIONALInvoice address line 3.Free Text100
INVADDR_COUNTRYOPTIONALInvoice address county.Free Text35
INVADDR_POSTCODEOPTIONALInvoice address post code.Free Text10
INVCONTACT_EMAILOPTIONALInvoice contact's email address.Free Text250
INVCONTACT_MOBILEOPTIONALInvoice contact's telephone number.Numeric30
DELADDR_LINE2OPTIONALDelivery address line 2.Free Text100
DELADDR_LINE3OPTIONALDelivery address line 3.Free Text100
DELADDR_COUNTYOPTIONALDelivery address county.Free Text35
DELCONTACT_EMAILOPTIONALDelivery contact's email address.Free Text250
DELCONTACT_MOBILEOPTIONALDelivery contact's telephone number.Numeric30
AGENTOPTIONALThe Agent used if setup in [ System Data | Sales Order Processing | Agents ].Free Text30
COURIEROPTIONALThe Courier name used, if setup in [ System Data | Courier / Shipping | Couriers ].Free Text100
_DELIVERYCHARGE_GROSSOPTIONALGross delivery charge, including Tax in GBP.Numeric10
_DELIVERYCHARGE_NETOPTIONALNet delivery charge, excluding Tax.Numeric10
DELIVERYDATEOPTIONALThe Date of delivery. This should be provided in the format DD/MM/YYYY.Numeric15
DESTINATIONSITECONDITIONALOnly useful for SO/PO Transfer orders, to set the destination site.Free Text50
DISCOUNTCODESOPTIONALSeparated list of discount keycodes.Free Text30
GROSSTOTALOPTIONALOptionally 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.Numeric20
INVOICENOTEOPTIONALA note which will associate with invoices raised for this company.Free Text250
INVOICEPRIORITYOPTIONALThe Sales Order Invoice Priority which should be assigned to the order.Free Text50
KEYCODEOPTIONALThe Keycode associated to the invoice.Free Text100
ORDERNOTEOPTIONALA note about the order.Free Text250
PONUMBEROPTIONALThe PO number linked to the invoice.Free Text20
REQUIREDDATEOPTIONALThe date the order is required. This should be provided in the format DD/MM/YYYY.Numeric15
SORDERCODEEXPORTExport only.Free Text20
SORDERIDEXPORTExport only.Free Text15
SORDERTYPECONDITIONALOnly to set SO/PO Transfer order, otherwise ignored.Free Text35
VALUEDISCOUNTOPTIONALAlways gross.Numeric8
WEBSITEOPTIONALThe website name.Free Text255
CUSTOMER_MAILINGSTATUSOPTIONALThe mailing status as per the customer record in Khaos Control.Free Text50
CUSTOMER_OTHERREFOPTIONALAn alternative reference to the customer.Free Text50
CUSTOMER_COMPANYTYPEOPTIONALThe Company Type, as defined in [ System Data | Company | Company Types ] e.g. Hospital/Hotel etc.Free Text50
CUSTOMER_COMPANYNAMEOPTIONALMust be specified if creating a new Customer, URN is new. Ignored if matching to an existing Customer.Free Text50
CUSTOMER_SOURCECODEOPTIONALThe source keycode of the customer.Free Text50
CUSTOMER_TAXREFERENCEOPTIONALThe customer's TAX reference.Free Text20
INVADDR_EMAILOPTIONALInvoice address's email address.Free Text250
INVADDR_TELEPHONEOPTIONALInvoice address's telephone number.Numeric30
DELADDR_EMAILOPTIONALDelivery address's email address.Free Text250
DELADDR_TELEPHONEOPTIONALDelivery address's telephone number.Numeric30
DELCONTACT_OPTINNEWSLETTEROPTIONALThe delivery contact mailing option.Yes/No2
ITEM_EXTENDEDDESCRIPTIONOPTIONALThe extended description for the stock item. Lines separated with \n.Free Text2000
ITEM_FREEREASONOPTIONALThe Free Item Reason, as defined in [ System Data | Sales Order Processing | Free Item Reasons ] e.g. Sample/Customer Service etc.Free Text60
ITEM_IMPORTREFOPTIONALThis is items import ref e.g. Amazon.Free Text100
ITEM_DESCRIPTIONSOURCEOPTIONALThis 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 Text100
ITEM_PERECENTDISCOUNTOPTIONALThe percentage discount on the item against the item line on the sales order; i.e. Disc% in the Sales Order screen's items grid.Numeric4
ITEM_UNITPRICE_NETOPTIONALThe net unit price for the item on the sales order, i.e. the amount for one item before VAT.Numeric4
ITEM_WEBITEMREFOPTIONALThis 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 Text50
REMAINDERONACCOUNTOPTIONALThis flags whether there is a remainder value on the customer's account.Yes/No4
PAYMENT_CASH_REFERENCEOPTIONALThe reference for the cash payment.Free Text20
PAYMENT_CHEQUE_REFERENCEOPTIONALThe reference for the cheque payment.Free Text20
PAYMENT_VOUCHER_REFERENCEOPTIONALThe voucher code of the voucher to use as payment. This voucher code must already exist in Khaos Control.Free Text20
PAYMENT_CARD_AUTHCODEOPTIONALThe authorisation code from the payment.Numeric15
PAYMENT_CARD_ACCOUNTNUMBEROPTIONALThe payment card's account number.Numeric10
PAYMENT_CARD_CARDNUMBEROPTIONALThe payment card's long card number.Numeric30
PAYMENT_CARD_CARDSTARTOPTIONALThe payment card's start date. This should be provided in the format MM/YY or MM/YYYYNumeric15
PAYMENT_CARD_CARDEXPIREOPTIONALThe Card Expiry Date. This should be provided in the format MM/YY or MM/YYYY.Numeric15
PAYMENT_CARD_CARDISSUEOPTIONALThe Card Issue number e.g. 07.Numeric2
PAYMENT_CARD_CARDCV2OPTIONALThe Card CV2 number.Numeric4
PAYMENT_CARD_CARDHOLDEROPTIONALThe Name of the card holder e.g. 'Mr J Bloggs'Free Text50
PAYMENT_CARD_ISPREAUTHOPTIONALWhether the payment is pre-authorised.Yes/No4
PAYMENT_CARD_TRANSACTIONIDOPTIONALThe card payments Transaction ID.Free Text50
PAYMENT_CARD_SECURITYREFOPTIONALThe security ref provided by the payment service provider for this transaction.Free Text50
PAYMENT_CARD_PREAUTHREFOPTIONALThe reference of the payment's preauthorisation transaction.Free Text50
PAYMENT_CARD_SECURITYTOKENOPTIONALThe Security token. Depending on the integration, this may be required for authorising payments.Free Text100
PAYMENT_CARD_LAST4DIGITSOPTIONALThe last 4 digits of the main number on the debit/credit card.Numeric4
PAYMENT_CARD_FRAUDDATAOPTIONALMay 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 Text4
PAYMENT_CARD_AAVCV2RESULTSOPTIONALThe results of the last AAV or CV2 check for this card.Free Text100
PAYMENT_CARD_TIMESTAMPOPTIONALThe transaction datetime of the payment, in the RFC 3339 format without the timezone offset:
YYYY-MM-DDTHH:MM:SS
2020-01-25T13:44:45
DATETIME50
PAYMENT_BANKACCOUNT_IDOPTIONALThe ID of the payment bank account. Only one or the other of the PAYMENT_BANKACCOUNT_ID and PAYMENT_BANKACCOUNT_NAME need be specified.Numeric15
PAYMENT_BANKACCOUNT_NAMOPTIONALThe name of the payment bank account.Free Text100

See Also


Did you find this article helpful?