Description
This document explains how to import Item Sales Orders.
Detail Steps
ALWAYS BACKUP PRIOR TO IMPORTING DATA INTO YOUR WORKSPACE
CREATE A BACKUP OF THE WMS Workspace before proceeding - you can not UNDO an IMPORT and will have to MANUALLY CANCEL SALE ORDERS if the process fails or does not produce the desired results.
Select FILE - IMPORT- REMOTE SALES. As you might expect this function can be used to batch import sales orders from remote sources - for example - Handheld Sales PDAs or Website Online Store sales.
Choose a Location to which all open sales will be applied if the file does not contain locations. Click Import Orders.
The TXT file will be read and checked for initial format errors - once verified the import will begin and may take several minutes.
Once the import completes a dialog will display noting: IMPORTED XX OF YY REMOTE SALE ORDERS.
Click OK and the Status Log should open - if the total number of ORDERS and/or number imported are not correct review the information shown for error messages.
If the issues can be managed manually make changes directly in WMS
Otherwise, RESTORE THE BACKUP - take corrective action as guided by the Status Reporter and Try Again.
If discrepancies occur, you can review the WMS Status Reporter - this will list any issues/line items that failed to import and provide warnings with information on how to correct potential issues. If a significant number of errors occur it is recommended you restore your backup, address the issues listed, and try again. If minor issues arise just edit the problem sales directly in WMS.
File Specifications
Field Header |
Example |
Required? |
|
Type |
Description |
Defaults |
Field Match lookup? |
Co./Last Name | Clothing Boutique | R | 30 | NVARCHAR | The Customers Name or Last Name for Individuals | N/A | Matched to WMS Customer Card Name, Fails if no match found |
First Name |
| O | 30 | NVARCHAR | The First Name of the Customer for Individuals | N/A | Matched to WMS Customer Card First Name, Fails if no match found |
Addr 1 - Line 1 | 1 Smith Street | R | 260 | NVARCHAR | The customers delivery address Fields | N/A | NO |
- Line 2 | Smithfield, NSW, 2000 | O | 260 | NVARCHAR | The customers delivery address Fields | N/A | NO |
- Line 3 |
| O | 260 | NVARCHAR | The customers delivery address Fields | N/A | NO |
- Line 4 |
| O | 260 | NVARCHAR | The customers delivery address Fields | N/A | NO |
Inclusive |
| R | 1 | INT | X = Yes or Blank field = No | N/A | NO |
Invoice # | abc12385 | O | 50 | NVARCHAR | The invoice number | Next available from WMS when Blank | Will fail if invoice number already exists |
Date | 20/02/2014 | R | 10 | DATE | The Ordered Date | N/A | NO |
Customer PO | 654dgh | O | 20 | NVARCHAR | The customers order reference or buyers Name | N/A | NO |
Ship Via | Best Method | O | 255 | NVARCHAR | The customers requested shipping method | When blank default for customer or WMS user is used | When populated must be in list in shipping methods in WMS. |
Delivery Status | N | N | 0 | NVARCHAR | The customers invoice delivery method, N = , P= ################# | If Blank, matched to Billing card preferences | NO |
Item Number | SKU234 | R | 50 | NVARCHAR | The Item Code/Number | N/A | NO |
Quantity | 2 | R | 16 | DBL | The Quantity Ordered | N/A | NO |
Description | Pink Jersey Sml | O | 255 | NVARCHAR | The Items description and any special requests for the line | N/A | NO |
Price | 45.00 | R | 16 | DBL | The unit price excluding taxes before discounts applied | N/A | NO |
Inc-Tax Price | 49.50 | R | 16 | DBL | The Unit Price including taxes before discounts applied | N/A | NO |
Discount | 20 | R | 16 | DBL | The discount Amount to Be applied - example shown is 20% discount | N/A | NO |
Total | 90.00 | R | 16 | DBL | The extended Non taxable amount QTY x Price | N/A | NO |
Inc-Tax Total | 99.00 | R | 16 | DBL | The Extended amount including Tax QTY x Inc-Tax Price | N/A | NO |
Job | staff Purchase | O | 50 | NVARCHAR | The job Code name - must exist in the WMS | N/A | NO |
Comment | will be collected from shop Tuesday | O | 250 | NVARCHAR | Sale Header Comment | N/A | NO |
Journal Memo | Sale From Seconds Warhouse to Melissa Markum | O | 250 | NVARCHAR | Sale Ship Note | N/A | NO |
Salesperson Last Name | Smith | O | 52 | NVARCHAR | Salesperson Last name - Must be in the WMS employee listing | If Blank or unmatched, defaults to the billing cards selection | Has to match an entry in the Employee listing |
Salesperson First Name | Eli | O | 20 | NVARCHAR | Salesperson First name - Must be in the WMS employee listing | If Blank or unmatched, defaults to the billing cards selection | Has to match an entry in the Employee listing |
Shipping Date | 15/02/2015 | O | 10 | DATE | Required Promise date can be Deliver not before date or Deliver by Date | Defaults to todays date when blank | NO |
Referral Source | Staff | O | 32 | NVARCHAR | The referral source for the sale | Defaults to billing customers referral source when blank or unmatched | Must Match an entry in the referrals list in WMS |
Tax Code | GST | R | 3 | NVARCHAR | The Lines Tax code (Item) | Defaults to Items default tax code when blank or unmatched | Must Match an entry in the Tax Code List in WMS |
Non-GST Amount | 90.00 | R | 16 | DBL | Entire Sales non GST Amount | N/A | NO |
GST Amount | 9.00 | R | 16 | DBL | Entire Sales GST Amount | N/A | NO |
LCT Amount | 0.00 | R | 16 | DBL | not in use always 0.00 | N/A | NO |
Freight Amount | 0.00 | R | 16 | DBL | Not in use always 0.00 - Freight should be entered as a line item only | N/A | NO |
Inc-Tax Freight Amount | 0.00 | R | 16 | DBL | Not in use always 0.00 - Freight should be entered as a line item only | N/A | NO |
Freight Tax Code | GST | R | 16 | DBL | Not in use always 0.00 - Freight should be entered as a line item only | N/A | NO |
Freight Non-GST Amount | 0.00 | R | 16 | DBL | Not in use always 0.00 - Freight should be entered as a line item only | N/A | NO |
Freight GST Amount | 0.00 | R | 16 | DBL | Not in use always 0.00 - Freight should be entered as a line item only | N/A | NO |
Freight LCT Amount | 0.00 | R | 16 | DBL | Not in use always 0.00 - Freight should be entered as a line item only | N/A | NO |
Sale Status | O | O | 1 | NVARCHAR | ######### | N/A | NO |
Currency Code | AUD | O | 3 | NVARCHAR | This sales and customers Currency Code | Defaults to Customers Setting | NO |
Exchange Rate | 1 | R | 16 | DBL | This sales exchange Rate | ######## | YES |
Terms - Payment is Due | ####### | R | 16 | DBL | ####### | ####### | Combine formula of these 5 items must result in antry in the terms list in the WMS |
- Discount Days | 14 | R | 16 | DBL | ####### | ####### | Combine formula of these 5 items must result in antry in the terms list in the WMS |
- Balance Due Days | 30 | R | 16 | DBL | ####### | ####### | Combine formula of these 5 items must result in antry in the terms list in the WMS |
- % Discount | 5 | R | 16 | DBL | ####### | ####### | Combine formula of these 5 items must result in antry in the terms list in the WMS |
- % Monthly Charge | 2.5 | R | 16 | DBL | ####### | ####### | Combine formula of these 5 items must result in antry in the terms list in the WMS |
Amount Paid | 99.00 | R | 16 | DBL | Payment amount will only populate the paid today field in WMS, not suggested for use by WMS users who use multipayment option | N/A | NO |
Payment Method | "" | N | 0 | NVARCHAR | Not in Use | N/A | NO |
Payment Notes | "" | N | 0 | NVARCHAR | Not in Use | N/A | NO |
Name On Card | "" | N | 0 | NVARCHAR | Not in Use | N/A | NO |
Card Number | "" | N | 0 | NVARCHAR | Not in Use | N/A | NO |
Expiry Date | "" | N | 0 | NVARCHAR | Not in Use | N/A | NO |
Authorisation Code | "" | N | 0 | NVARCHAR | Not in Use | N/A | NO |
BSB | "" | N | 0 | NVARCHAR | Not in Use | N/A | NO |
Account Number | "" | N | 0 | NVARCHAR | Not in Use | N/A | NO |
Drawer/Account Name | "" | N | 0 | NVARCHAR | Not in Use | N/A | NO |
Cheque Number | "" | N | 0 | NVARCHAR | Not in Use | N/A | NO |
Category | ######## | O | 50 | NVARCHAR | #### | #### | ### |
Location ID | Perth | O | 16 | LONG | Must be a matching entry in WMS locations list | Defaults to User preferences when blank or unmatched, or location selected on manual import | Yes matches to Location Cards in WMS |
Card ID | 321132 | O | 16 | LONG | ########## | ######### | ##### |
Record ID |
| N | 0 | LONG | Always Blank | N/A | NO |
After each sale there is a required blank line entry, please review the attached example file for details.
Please note that some “special characters” can cause the WMS Import to fail. If problems occur during an import try to reduce the number of sale orders being imported per TXT file. For example, create a TXT file of sales for each month or quarter and import these, one after the other checking the sales reports to verify all information was accepted by WMS correctly.
Related Information
A scheduled task can be set to poll a folder and import sales periodically, please review the scheduled tasks support note.
How to obtain TSV file?
TSV file can be exported from MYOB.
Login to MYOB.
File > Export Data > Sales > Item Sales.
Match All fields > DO NOT CHANGE Export File Format and First Record is. Select your preferred Invoice Status and Dated From > Continue.
File will be saved as ITEMSALE.TXT, attached below.
Sample file
