Quick Invoice Import for E-Commerce Transactions

Modified on Wed, Dec 6, 2023 at 2:52 PM

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.

Type - quick invoice type; required; valid entries: REGULAR or ONE TIME; related import records should have the same value
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
Web Order No - 25 characters max; entries converted to uppercase; see notes below for useage information
Invoice Date - required; must be in an open accounting period; related import records should have the same value
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
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
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
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
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
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
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
Payment Method - required; must be a valid payment method; related import records should have the same value
PO No - 20 characters max; entries converted to uppercase; may be required by some customers; related import records should have the same value
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
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
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
Tracking No - 40 characters max; related import records should have the same value
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
Analysis Code - must be a valid sales analysis code; related import records should have the same value
Quantity - required; must be a positive number with a maximum of two decimal places
Inventory Item No - required; must be a valid inventory item number
Description - 3200 characters max; if blank in the import file the inventory item default description is used
Price - required; must be zero or a number with a maximum of two decimal places
Discount - must be zero or a positive number with a maximum of two decimal places
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
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
Bill To Name - 40 characters max; related import records should have the same value
Bill To Attention - 30 characters max; related import records should have the same value
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
Bill To Street Line 2 - 30 characters max; related import records should have the same value
Bill To City - 30 characters max; related import records should have the same value
Bill To Prov/State - 30 characters max; related import records should have the same value
Bill To Country - 30 characters max; related import records should have the same value
Bill To Zip/Postal - 10 characters max; related import records should have the same value
Bill To Contact - 40 characters max; related import records should have the same value
Bill To Phone - 40 characters max; related import records should have the same value
Bill To Fax - 40 characters max; related import records should have the same value
Bill To Email - 60 characters max; related import records should have the same value
Ship To Name - 40 characters max; related import records should have the same value
Ship To Attention - 30 characters max; related import records should have the same value
Ship To Street Line 1 - 30 characters max; related import records should have the same value
Ship To Street Line 2 - 30 characters max; related import records should have the same value
Ship To City - 30 characters max; related import records should have the same value
Ship To Prov/State - 30 characters max; related import records should have the same value
Ship To Country - 30 characters max; related import records should have the same value
Ship To Zip/Postal - 10 characters max; related import records should have the same value
Ship To Contact - 40 characters max; related import records should have the same value
Ship To Phone - 40 characters max; related import records should have the same value
Ship To Fax - 40 characters max; related import records should have the same value
Ship To Email - 60 characters max; related import records should have the same value
Ship To Account No - 30 characters max; related import records should have the same value
Custom Sales Field 1: Custom Alpha - 40 characters max; related import records should have the same value
Custom Sales Field 2: Custom Alpha - 40 characters max; related import records should have the same value
Custom Sales Field 3: Custom Alpha - 40 characters max; related import records should have the same value
Custom Sales Field 4: Custom Alpha - 40 characters max; related import records should have the same value
Custom Sales Field 5: Custom Alpha - 40 characters max; related import records should have the same value
Custom Sales Field 6: Custom Alpha - 40 characters max; related import records should have the same value
Custom Sales Field 7: Custom Num - floating-point number; related import records should have the same value
Custom Sales Field 8: Custom Num - floating-point number; related import records should have the same value
Custom Sales Field 9: Custom Date - date; related import records should have the same value
Custom Sales Field 10: Custom Date - date; related import records should have the same value
Custom Sales Field 11: Custom Text - 1024 characters max; related import records should have the same value
Custom Sales Field 12: Custom Alpha - 40 characters max; related import records should have the same value
Custom Sales Field 13: Custom Alpha - 40 characters max; related import records should have the same value
Custom Sales Field 14: Custom Alpha - 40 characters max; related import records should have the same value
Custom Sales Field 15: Custom Alpha - 40 characters max; related import records should have the same value
Custom Sales Field 16: Custom Alpha - 40 characters max; related import records should have the same value
Custom Sales Field 17: Custom Alpha - 40 characters max; related import records should have the same value
Custom Sales Field 18: Custom Alpha - 40 characters max; related import records should have the same value
Custom Sales Field 19: Custom Alpha - 40 characters max; related import records should have the same value
Custom Sales Field 20: Custom Alpha - 40 characters max; related import records should have the same value
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.