Quick Invoice Import for E-Commerce Transactions

System Requirements: Connected Version 11.1 or greater is required.

Article Contents

• Quick Invoice Import Introduction
• Quick Invoice Import Window
• Importable fields include (in default field mapping order)
• Using Manual Field Mapping
• Possible Errors

Quick Invoice Import Introduction

Quick Invoices are a great way to quickly record a "retail" type transaction, in which the Invoice and Payment are all in one. Quick Invoices also respect inventory control and can be used in combination with inventory stock and sales tracking. This is a great way to record transactions from a third party, like an e-commerce site.

Quick Invoices can also be imported using an external counter, like a web order number, that will be used to auto-assign Connected Invoice Numbers in the correct unique sequence. The Web Order number field is searchable and can be used to filter Sales Invoices via the Sales Ledger.

Quick Invoice Import Window

To access the Quick Invoice Import, select Import from the File menu and choose Quick Invoice. The following screen will appear.

NOTE: Import file format can be a CSV (comma separated value) or a tab delimited text file.

TIP: Click the icon, as shown below, to export an import template with field names and definitions.

Importable fields include (in default field mapping order)

        NOTE: See following section for how to use "manual" field mapping to create a custom map.

1. Type - quick invoice type; required; valid entries: REGULAR or ONE TIME; related import records should have the same value
2. Invoice No - required (unless a web order number is supplied); nine digits max; must be a unique invoice number; related import records should have the same value
3. Web Order No - 25 characters max; entries converted to uppercase; see notes below for useage information
4. Invoice Date - required; must be in an open accounting period; related import records should have the same value
5. Customer Code - required for REGULAR invoices; must be a valid code for an active status customer; should be blank for ONE TIME invoices; related import records should have the same value
6. Customer Name - required for ONE TIME invoices; 30 characters max; for REGULAR invoices values in the import file are ignored; related import records should have the same value
7. Currency Code - required; must be blank or a valid currency code; for REGULAR invoices values in the import file are ignored and the customer's default currency used; for ONE TIME invoices if blank in the import file HOME currency is used; related import records should have the same value
8. Currency Rate - must be a positive number with a maximum of four decimal places; only applicable to non-HOME currency codes; if blank in the import file the current rate for the currency code in Connected is used; related import records should have the same value
9. Tax Code - required for ONE TIME invoices; must be a valid tax code; for REGULAR invoices if blank in the import file the customer's default tax code is used; related import records should have the same value
10. Sales Rep Code - required for ONE TIME invoices; must be a valid sales rep code; for REGULAR invoices if blank in the import file the customer's default sales rep code is used; related import records should have the same value
11. Bank Code - required for ONE TIME invoices; must be a valid bank code; for REGULAR invoices if blank in the import file the customer's default bank code is used; related import records should have the same value
12. Payment Method - required; must be a valid payment method; related import records should have the same value
13. PO No - 20 characters max; entries converted to uppercase; may be required by some customers; related import records should have the same value
14. Ship Via - 40 characters max; for REGULAR invoices if blank in the import file the customer's default ship via is used; related import records should have the same value
15. FOB - 12 characters max; for REGULAR invoices if blank in the import file the customer's default FOB is used; related import records should have the same value
16. Comments - 1024 characters max; for REGULAR invoices if blank in the import file the customer's default quick invoice comment is used; related import records should have the same value
17. Tracking No - 40 characters max; related import records should have the same value
18. Tracking Web Address - 200 characters max; if specified in the import file a tracking number must also be provided; related import records should have the same value
19. Analysis Code - must be a valid sales analysis code; related import records should have the same value
20. Quantity - required; must be a positive number with a maximum of two decimal places
21. Inventory Item No - required; must be a valid inventory item number
22. Description - 3200 characters max; if blank in the import file the inventory item default description is used
23. Price - required; must be zero or a number with a maximum of two decimal places
24. Discount - must be zero or a positive number with a maximum of two decimal places
25. Sales Tax 1 - must be zero or a positive number with a maximum of two decimal places; related import records should have the same value
26. Sales Tax 2 - must be zero or a positive number with a maximum of two decimal places; related import records should have the same value
27. Bill To Name - 40 characters max; related import records should have the same value
28. Bill To Attention - 30 characters max; related import records should have the same value
29. Bill To Street Line 1 - 30 characters max; for REGULAR invoices if blank in the import file the customer's entire default billing address is used; related import records should have the same value
30. Bill To Street Line 2 - 30 characters max; related import records should have the same value
31. Bill To City - 30 characters max; related import records should have the same value
32. Bill To Prov/State - 30 characters max; related import records should have the same value
33. Bill To Country - 30 characters max; related import records should have the same value
34. Bill To Zip/Postal - 10 characters max; related import records should have the same value
35. Bill To Contact - 40 characters max; related import records should have the same value
36. Bill To Phone - 40 characters max; related import records should have the same value
37. Bill To Fax - 40 characters max; related import records should have the same value
38. Bill To Email - 60 characters max; related import records should have the same value
39. Ship To Name - 40 characters max; related import records should have the same value
40. Ship To Attention - 30 characters max; related import records should have the same value
41. Ship To Street Line 1 - 30 characters max; related import records should have the same value
42. Ship To Street Line 2 - 30 characters max; related import records should have the same value
43. Ship To City - 30 characters max; related import records should have the same value
44. Ship To Prov/State - 30 characters max; related import records should have the same value
45. Ship To Country - 30 characters max; related import records should have the same value
46. Ship To Zip/Postal - 10 characters max; related import records should have the same value
47. Ship To Contact - 40 characters max; related import records should have the same value
48. Ship To Phone - 40 characters max; related import records should have the same value
49. Ship To Fax - 40 characters max; related import records should have the same value
50. Ship To Email - 60 characters max; related import records should have the same value
51. Ship To Account No - 30 characters max; related import records should have the same value
52. Custom Sales Field 1: Custom Alpha - 40 characters max; related import records should have the same value
53. Custom Sales Field 2: Custom Alpha - 40 characters max; related import records should have the same value
54. Custom Sales Field 3: Custom Alpha - 40 characters max; related import records should have the same value
55. Custom Sales Field 4: Custom Alpha - 40 characters max; related import records should have the same value
56. Custom Sales Field 5: Custom Alpha - 40 characters max; related import records should have the same value
57. Custom Sales Field 6: Custom Alpha - 40 characters max; related import records should have the same value
58. Custom Sales Field 7: Custom Num - floating-point number; related import records should have the same value
59. Custom Sales Field 8: Custom Num - floating-point number; related import records should have the same value
60. Custom Sales Field 9: Custom Date - date; related import records should have the same value
61. Custom Sales Field 10: Custom Date - date; related import records should have the same value
62. Custom Sales Field 11: Custom Text - 1024 characters max; related import records should have the same value
63. Custom Sales Field 12: Custom Alpha - 40 characters max; related import records should have the same value
64. Custom Sales Field 13: Custom Alpha - 40 characters max; related import records should have the same value
65. Custom Sales Field 14: Custom Alpha - 40 characters max; related import records should have the same value
66. Custom Sales Field 15: Custom Alpha - 40 characters max; related import records should have the same value
67. Custom Sales Field 16: Custom Alpha - 40 characters max; related import records should have the same value
68. Custom Sales Field 17: Custom Alpha - 40 characters max; related import records should have the same value
69. Custom Sales Field 18: Custom Alpha - 40 characters max; related import records should have the same value
70. Custom Sales Field 19: Custom Alpha - 40 characters max; related import records should have the same value
71. Custom Sales Field 20: Custom Alpha - 40 characters max; related import records should have the same value
72. Custom Sales Field 21: Custom Alpha - 40 characters max; related import records should have the same value

Note:

• This field list will change based on whether or not the multi-currency plug-in is enabled, multiple locations is active in the Inventory control module, and/or Sales Reps are activate under A/R Defaults.
  
• Details belonging to the same invoice should be contiguous rows in the import file.
• Custom Sales Fields that are not in use cannot have values imported into them.
• If the default mapping is used, all values for tax 1 and tax 2 must be defined.
• If auto invoice numbering is enabled, AND no invoice number is provided, AND if a web order number is imported, the web order number will be used to group the detail rows and automatically assign an invoice number. CAUTION: Duplicate web order numbers are allowed. A web order number may be assigned to more than one invoice number, if imported multiple times.

Using Manual Field Mapping

Manual field mapping is helpful when the import file does not match the the default order. Fields can be mapped using a drag and drop interface. 

To manually map an import file:

1) Select the "Use Manual Mapping" radio button.

2) Click the "Load Import File Header" button, as shown below. 

3) Select a text file that contains the column headings of the data to be imported and click ok to populate the "Unmapped Import File Fields" column, on the left side of the window.

4) Drag the fields, from the left, to the appropriate Connected fields on the right side of the screen.  Ensure that all (required) fields are been mapped.

NOTE: Manual mapping will be remembered, even when Connected is closed, unless "Use Default Mapping" is selected and the warning agreed to.

Importing Data

1) After the fields have been mapped, click on the "Step 2: Preview and Import Data" tab at the top of the window.

2) Select the checkbox to the left of "Import File contains a Header Row", if applicable.

3) Click the "Preview from File" button to review the data that is to be imported.  Previewed data can be imported or cleared, using the "Import Previewed Data" and "Clear Preview" buttons.

4) If no preview is required, the text file can be imported directly by clicking the "Import from File..." button and selecting the import text file.  

5) Any Messages or Warnings and Errors will appear in the sections outlined below.

Special Character Handling: Descriptions containing single and double quote characters and carriage returns cannot be imported directly. Using a text editor application, these characters must be removed and substituted with the following strings prior to import. During the import process, the substitute strings will then be replaced with the original characters.  Substitute /dqt/ for double quotes, /sqt/ for single quotes and /rtn/ for carriage returns/line breaks. The forward slashes must be included.

Possible Errors

• Field mapping is incorrect
• Required fields do not match or are missing
  • Import file does not contain data that matches, customer code and/or sales reps, currency code and location (if applicable)
  • Missing Bank or incorrect terms and/or tax codes
  • Payment Methods
  • Inventory items sold do not match items in the data file.
• If inventory control is On, quantities on available must be greater than or equal to the amount being imported on the quick invoice. If the quantity on the invoice exceed the quantity available (On Hand - Committed) that invoice will not be imported.