You are using an unsupported browser. Please update your browser to the latest version on or before July 31, 2020.
close

JOOR Sales Transaction Invoice Import into QBO

Below are the instructions to import JOOR sales transactions into QuickBooks Online (QBO) as an Invoice using Transaction Pro Importer.

After downloading your sales file from JOOR, log into QBO.

Log into Transaction Pro Importer and select to IMPORT.

CHOOSE IMPORT TYPE: Select INVOICES as your import type by clicking on the box, which take you to the next window.

UPLOAD FILES: On this window, either DRAG AND DROP into the gray box your file to import or click in the gray box to BROWSE AND ATTACH your import file. Importer can import any flat file format into QBO such as .csv, .txt, and Excel.

BEFORE ATTACHING FILE

AFTER ATTACHING FILE

   

Click SELECT FILE AND SHEET in the lower right-hand corner to proceed to the next window.

SELECT FILE AND SHEET: If your workbook has multiple worksheets (tabs) and/or if you have attached multiple files, you will see a list of the all available options to import. You can only work with one worksheet in one workbook at a time. Therefore, select the workbook and worksheet combination that contains the data that you wish to import by clicking the SELECT SHEET button next to the correct one.

MATCH COLUMNS: On this window, you will tell Importer what columns in your import file contains what data for available QBO fields.

Any field that has a blue asterick (*) is a required field that must either be included in your import file or have a default value assigned to the field.

Each field has two options for mapping. You can enter information in either column but never both.

  • The first column is to map the info that is in the import file. Your import file column headers will be available on the dropdown menu for each field under the IMPORT COLUMN column.
  • Any data that is not in your import file, needs to be included in the import, and has the same value for all imported transactions can be entered as a default value under the DEFAULT VALUE column.

(NOTE:  A predefined map that matches the mapping demonstrated below is available as an attachment to this article to download and use in Transaction Pro. Save the map download to your desired location and then when on the mapping window of Transaction Pro, click UPLOAD MAPPING OPTIONS to select the predefined map and add to your available saved maps).

The suggested fields to map at minimum to import your order information from JOOR are:

  • REFNUMBER (QBO FIELD-INVOICE NO): This is a required field. It is the number (can be alphanumeric) that is assigned to the imported Invoice in QBO. It is also the field that allows Importer to know where a transaction begins and ends, which allows Importer to group detail lines together that belong on the same transaction.
  • CUSTOMER (QBO FIELD-CUSTOMER OR PROJECT): This is a required field and will be the Customer who placed the order.
  • TXNDATE (QBO FIELD-INVOICE DATE): This is the date that will be assigned to the Invoice. This is not a required field; however, if no date is provided in the import file or as a default value, then the date will default to the date that the import is performed.
  • SHIPPING ADDRESS and BILLING ADDRESS fields are not required. If nothing is imported for either of these fields, QBO will use the default Addresses set up for the Customer in QBO on the Invoice.
  • DUEDATE (QBO FIELD-DUE DATE): If nothing is imported for this field, QBO will calculate the Due Date based on the date of the Invoice and the Terms.
  • SALESTERMS (QBO FIELD-TERMS): If nothing is imported for this field, QB0 will pull the default Terms from the Customer setup.
  • CURRENCY (QBO FIELD-CURRENCY): If you have turned on Multi-Currency in your QBO Company File, you will see an available field to import the Currency for the transaction.
  • SHIPDATE (QBO FIELD-SHIPPING DATE): This is not a required field but is available to record the date that the products were shipped for future reference.
  • SHIPMETHODNAME (QBO FIELD-SHIP VIA): This is not a required field but is available to record the shipping method used. It is not a pre-defined field so therefore, any alpha-numeric characters can be entered.
  • SHIPAMT (QBO FIELD-SHIPPING AMOUNT): If the order contains a shipping charge, map the Shipping Amount column to this field.
  • SHIPITEM (QBO FIELD-SHIPPING PRODUCT/SERVICE): If an amount has been mapped to the Shipping Amount field, you must provide the correlating Product/Service that has been set up for Shipping in QBO.
  • DISCOUNTAMT (QBO FIELD-DISCOUNT VALUE): If the order contains a discount, map the Discount column to this field.
  • TAX AMOUNT (QBO FIELD-SALES TAX): If you have auto sales tax calculation turned on in QBO, then you will not be able to import the Sales Tax amount.
  • LINEITEM (QBO FIELD-PRODUCT/SERVICE): This is a required field. It must match the Product/Service name setup in QBO. If the item is a Sub-Item, then your import file must contain the entire Product/Service name with the levels separated by a colon with no space before or after the colon.
  • LINEDESC (QBO FIELD-DESCRIPTION): Each line can contain a different description, and each description can be up to 2048 characters.
  • LINEQTY (QBO FIELD-QTY): A required field if the Product/Service is type Inventory. If nothing is imported for this field, QBO assumes a value of zero.
  • LINEUNITPRICE (QBO FIELD- RATE): This is the price per unit for the Item. If nothing is imported, QBO will pull the default price from the Product/Service setup.
  • LINETAXABLE (QBO FIELD-TAX): If you do not import NON or TAX for each line item, QBO will pull the default tax setup for the Product/Service in QBO.

Once you have completed your map, click SAVE MAPPING OPTIONS on the left-hand side of the window. You may save the map as any name that you choose. You may save an infinite number of different maps for each import type and access the different maps by selecting the dropdown button for CHOOSE SAVED OPTIONS.

Click PREVIEW AND IMPORT to proceed to the next window.

PREVIEW AND IMPORT: This window provides you with a preview of your data, showing what data is importing into which QBO field.

You may deselect any data row that you do not wish to import by clicking the box in the first column for the row.

You may select OPTIONS for your import, if necessary

To select OPTIONS, click the last button along the top of the data grid.

The OPTIONS that you may want to select are:

  • LET QUICKBOOKS AUTO-ASSIGN INVOICE NUMBERS: QBO will ignore the value mapped to the REFNUMBER field and will auto assign the Invoice number for each transaction, starting with the first number that follows the number of the last manually entered Invoice.
  • DO NOT AUTOMATICALLY ADD ITEM FROM YOUR IMPORT DATA THAT ARE NOT IN QUICKBOOKS: This option will stop Importer from creating a new Product/Service if it encounters a Product/Service that does not currently exist in QBO. Instead, that record will fail to import, allowing you to manually set up the Product/Service in QBO.
  • DO NOT AUTOMATICALLY ADD CUSTOMERS FROM YOUR IMPORT DATA THAT ARE NOT IN QUICKBOOKS ONLINE: This option will stop Importer from creating a new Customer account if it encounters a Customer that does not currently exist in QBO. Instead, that record will fail to import, allowing you to manually set up the Customer in QBO.

Return to the TRANSACTION TO IMPORT by clicking on the tab along the top of the window.

Click IMPORT in the lower right-hand corner to begin the import into QBO.

A confirmation window will appear to confirm that you are ready to import. Click YES to import or NO to return to TPI to make changes.

If the data imports without any issues, the data will move to the IMPORTED TRANSACTIONS tab with a message displayed in green along the top saying your import was successful.

You may download an import log by clicking on either the EXPORT TO EXCEL tab or SEND TO EMAIL tab.

If Importer finds any issues with your import data while validating with your QBO data, a message will appear along the top of the TRANSACTION TO IMPORT tab in red, explaining what issue has been found. Be sure to click on one of the highlighted cells (highlighted either red or yellow) to receive the specific error message.

It could be possible that some data will import, and other data will not. If this is the case, all data that imported successfully will be moved to the IMPORTED TRANSACTIONS tab, and only the data that failed to import will be found on the TRANSACTION TO IMPORT tab.

You can make changes directly to the import data on the TRANSACTION TO IMPORT tab, correcting any data as necessary.

Once you have corrected the data that did not import, you may click IMPORT again to import only the data on the TRANSACTIONS TO IMPORT tab. The data on the IMPORTED TRANSACTIONS tab will not re-import.

Verify in QBO that your Invoices imported correctly. You can find the most recent transactions entered or imported into QBO by click on the magnifying glass in the upper right-hand corner of the QBO window.

  • 369
  • 09-Jun-2020
  • 2086 Views

Attachments