Sales Order Import Tool



Sales Order Import Tool is not enabled by default.
Training must be completed before using the sales order import tool.
Please email Development for more information.
Note: there will be additional costs involved if this option is enabled.

Overview

A Tab separated format allows users to import Sales Orders into Khaos Control. The headers as described in the table below, are written in a format that Khaos Control recognises. The table also has an explanation of what each header means and what information you should include in 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 email Development.

Configuration

  • In order to prepare the data for import, the file should be saved with a .txt or .tsv extension.
  • The top line of the file may be a header containing the specified field names.
  • 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.
  • The data below (Sales Order Item Line) should correspond to the headers.

Usage

Typical steps for usage would include:

  1. Open a Sales Order screen (show me how).
  2. Press F9 to open the Other Actions Menu.
  3. Select [ Import / Export | Import | Khaos Control | Tab Separated Format ] to open the file selection dialog with the App folder as default.
    • Successful Orders can now be searched for in [ Sales Order | List ] by selecting an appropriate date range or searching by the Associated Ref.
    • 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.

Full Set of Values


All YES and NO fields should be Boolean values.
YES = -1, NO = 0

Field Name Required? Description Type Characters
ASSOCIATEDREF (Required)Your order code from your old system which can be referenced back to if required. FREE TEXT 50
ORDERDATE (Required) The Date of order from customer. This should be provided in the format: DD/MM/YYYY. NUMERIC 15
SITE (Required)The Name of the site the order should go against. FREE TEXT 50
CUSTOMER_COMPANYCLASS (Required)The classification of the customer which needs to match an existing company class as defined in System Data. FREE TEXT 50
CUSTOMER_CURRENCY (Required)The currency name as defined in System Data, Currencies, for example Pounds Sterling, Euro etc. FREE TEXT 50
CUSTOMER_CREATENEW (Required)This determines whether a new customer record is created. That is input one of these three choices:
  1. IfNoMatch
  2. Always
  3. Never

IfNoMatch matches on Company Code.

FREE TEXT 50
ITEM_SKU (Required)Stock item reference FREE TEXT 50
ITEM_MAPPING (Required)How the import will try to match stock items. That is import one of these choices:
  1. StockCode
  2. OtherRef
  3. Barcode
  4. WebCode
  5. Automatic
FREE TEXT 20
ITEM_QUANTITY (Required)Order line Quantity NUMERIC 15
INVADDR_LINE1 (Required)Invoice address line 1 FREE TEXT 100
INVADDR_TOWN (Required)Invoice address town FREE TEXT 50
INVADDR_COUNTRY_CODE (Required)Invoice address country, as defined in System Data, Countries, for example United Kingdom FREE TEXT 50
ITEM_UNITPRICE_GROSS (Required)Gross unit value, including Tax in GBP NUMERIC 20
SALESOURCE (Recommended) The Sales source of the order, as defined in System Data / Sales Source. E.g. Website/Amazon/Telesales etc FREE TEXT 50
CALCMETHOD (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:
  1. Auto
  2. Net
  3. Gross
FREE TEXT 50
PAYMENT_AMOUNT (Recommended)The orders payment value 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)Delivery address country, as defined in System Data, Countries, for example United Kingdom FREE TEXT 50
DELCONTACT_TITLE (Recommended)Delivery contact's title. E.g Mr, Mrs, etc. 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_LINE2 (Optional)Invoice address line 2 FREE TEXT 100
INVADDR_LINE3 (Optional)Invoice address line 3 FREE TEXT 100
INVADDR_COUNTY (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 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)L 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
DESTINATIONSITEConditionalOnly useful for SO/PO Transfer orders, to set the Destination Site [ dev 016521 ] FREE TEXT 50
DISCOUNTCODES (Optional)\n Separated list of discount keycodes (codes) 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
SORDERCODEExportExport only FREE TEXT 20
SORDERIDExportExport only FREE TEXT 15
SORDERTYPEConditionalOnly to set SO/PO Transfer order; otherwise ignored FREE TEXT 35
ValueDiscount (Optional)Always gross FREE TEXT 50
Website (Optional)The website name FREE TEXT 50
Customer_MailingStatus (Optional) FREE TEXT 50
Customer_OtherRef (Optional) FREE TEXT 50
Customer_WebUser (Optional) FREE TEXT 50
Customer_CompanyType (Optional) 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_WebsiteUrl (Optional) FREE TEXT 50
Customer_SourceCode (Optional) FREE TEXT 50
Customer_TaxReference (Optional) FREE TEXT 50
InvAddr_Email (Optional) FREE TEXT 50
InvAddr_Telephone (Optional) FREE TEXT 50
DelAddr_Email (Optional) FREE TEXT 50
DelAddr_Telephone (Optional) FREE TEXT 50
DelContact_OptInNewsletter (Optional)Boolean FREE TEXT 50
Item_ExtendedDescription (Optional)Lines separated with \n FREE TEXT 50
Item_FreeItemReason (Optional) FREE TEXT 50
Item_ImportRef (Optional) FREE TEXT 50
Item_OrderItemDescription_DescriptionSource (Optional)Explicit,StockDesc,WebCategories FREE TEXT 50
Item_OrderItemDescription (Optional) FREE TEXT 50
Item_PackLink (Optional) FREE TEXT 50
Item_PercentDiscount (Optional) FREE TEXT 50
Item_PostponeAssignent (Optional)Boolean FREE TEXT 50
Item_Site (Optional) FREE TEXT 50
Item_UnitPrice_Net (Optional) FREE TEXT 50
Item_WebItemRef (Optional) FREE TEXT 50
Payment_Cash_Reference (Optional) FREE TEXT 50
Payment_Cheque_Reference (Optional) FREE TEXT 50
Payment_Voucher_Reference (Optional) FREE TEXT 50
Payment_Card_AuthCode (Optional) FREE TEXT 50
Payment_Card_AccountNumber (Optional) FREE TEXT 50
Payment_Card_CardNumber (Optional) FREE TEXT 50
Payment_Card_CardStart (Optional) FREE TEXT 50
Payment_Card_CardExpire (Optional) FREE TEXT 50
Payment_Card_CardIssue (Optional)The Card Issue number e.g. 07 FREE TEXT 50
Payment_Card_CardCV2 (Optional) FREE TEXT 50
Payment_Card_Cardholder (Optional) FREE TEXT 50
Payment_Card_IsPreauth (Optional) FREE TEXT 50
Payment_Card_TransactionID (Optional) FREE TEXT 50
Payment_Card_SecurityRef (Optional) FREE TEXT 50
Payment_Card_PreauthRef (Optional) FREE TEXT 50
Payment_Card_SecurityToken (Optional) FREE TEXT 50
Payment_Card_Last4Digits (Optional) FREE TEXT 50
Payment_Card_FraudData (Optional) FREE TEXT 50
Payment_Card_AAVCV2Results (Optional) FREE TEXT 50
Payment_Card_Timestamp (Optional) FREE TEXT 50
Payment_BankAccount_ID (Optional) FREE TEXT 50
Payment_BankAccount_Name (Optional) FREE TEXT 50

Caveats

  • 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.
  • 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.
  • It is considered good practice to append the Client and Brand to the import before presenting to the system so this is configured correctly.
  • If using the Extended Stock Description on the Sales Order to register a specific instruction for example where Sales Orders are gift-aided, use the wording 'GIFT AID' or similar, 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 GROSS and NET values are not imported together.


See Also


Did you find this article helpful?