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

 A sample map file is attached on the right-hand side of the screen to use with your JOOR Invoice Import.

After downloading your order file from JOOR, log into QBDT. QBDT includes Pro, Premier, and Enterprise versions.

 

Open Importer and select the file that you wish to import into QBDT by clicking BROWSE and selecting your file from its saved location.

 

Importer can import any flat file format into QBDT such as .csv, .txt, and Excel. Depending on the file type being imported, the first window of Importer will vary.

 

If importing a .txt or .csv file, you will see an option to select the FIELD delimiter. The options are Comma, Tab, or Pipe. Select the one that corresponds to your file. Typically, that is COMMA for .csv files and TAB for .txt files. Leave RECORD as CR/LF.

 

 

 

If importing an Excel file, you will see the option to select the worksheet (SHEET) from within the Excel file that contains the data that you wish to import.

 

 

 

NOTE: If you make any changes to your import file after you have attached it to Importer, you will need to reselect it for Importer to recognize the changes.

 

Select INVOICE as the IMPORT TYPE.

 

Click OPTIONS button.

 

The suggested OPTIONS on the BASIC tab are:

 

• LET QUICKBOOKS AUTO-ASSIGN INVOICE NUMBERS: QBDT 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 UPDATE ADDRESSES ON EXISTING QUICKBOOKS LIST ITEMS: If the Address in the import file varies from the Address on file for the Customer in QBDT, the QBDT Customer account Address will not be changed to the import file Address. 
• DO NOT ADD NEW CUSTOMERS TO QUICKBOOKS: When importing your Invoice, this option will not allow a new Customer to be setup in QBDT if Importer finds a Customer in the import file that does not previously exist in QBDT.
• DO NOT ADD NEW ITEMS TO QUICKBOOKS: When importing Items on your Invoice, this option will not allow a new Item to be setup in QBDT if Importer finds an Item in the import file that does not previously exist in QBDT.
• GENERATE TRANSACTION LOG: This option should be selected for all imports. It will generate a log after the import is finished running that will show what transactions imported and any that failed and the reason that they failed.
• SKIP TRANSACTIONS WITH MATCHING REFERENCE NUMBERS: With this option, if Importer finds the same Reference Number when importing, it will skip that record and not import it. However, this only works if using the JOOR Order Number as the Invoice Number and not using the option for QBDT to auto-assign Invoice Numbers. This option does not take the Customer into consideration. It only looks at the Invoice Number. Selecting this option will slow down the import.
• REPLACE TRANSACTIONS WITH MATCHING REFERENCE NUMBERS:  Importer cannot amend or change existing transactions in QBDT. However, with this option, if an identical Reference Number is found, the existing transaction in QBDT will be deleted, and the new transaction in the import file will be imported in its place. This option only works if using the JOOR Order Number as the Invoice Number and not using the option for QBDT to auto-assign Invoice Numbers. This option does not take the Customer into consideration. It only looks at the Invoice Number. Selecting this option will slow down the import.

The suggested OPTIONS on the ECOMMERCE tab are:

 

• ENABLE TPI E-COMMERCE FEATURES: This will add the additional fields needed for Shipping, Handling, Discount, and/or Sales Tax.
• SEPARATE ROWS (RECORDS) FOR EACH ITEM SOLD: The JOOR file has the data in rows, meaning one order can have multiple detail rows.

 

 

   

        

 

Information on all other OPTIONS can be found in the HELP file included with Importer.

 

Click SAVE.

 

Click NEXT to proceed to Window 2 of Importer.

 

Window 2 of Importer displays how the application is reading the file that is being imported.

 

If the file type is .csv or .txt and on the second window of Importer the data does not appear in columns, then the wrong FIELD delimiter has been selected. Click BACK, select a different FIELD delimiter, and click NEXT to return to Window 2 of Importer. See images below for an example of data not lining up into columns correctly and data lining up correctly.

 

INCORRECT 

              

 

CORRECT 

     

     

 

If your import file has column headers as your first row, be sure that the box located in the lower left-hand corner is checked for FILE HAS FIELDS NAME IN THE FIRST ROW.

 

On Window 2, you may remove any rows from your file that you do not wish to import by clicking on the row and selecting DELETE SELECTED ROWS. It will not remove the data from your file, only from the import.

 

Click NEXT to proceed to Window 3 of Importer.

 

If you are mapping this file for the first time or if you have previously performed another import, you will see a message saying that the saved map does not match your import file. Click OK.

 

 

Window 3 of Importer is the mapping window. It allows you to tell Importer what columns in your import file contains what data for available QBDT fields.

 

Any fields that are in red are required fields that must either be included in your import file or have a static 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 FILE COLUMNS column.
• Any data that is not in your import file, needs to be included in the import, and has the same value for imported transactions can be entered as a static or default value under the STATIC VALUE/FORMULA column.

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

 

• CUSTOMER (QBDT FIELD-CUSTOMER:JOB): This is a required field and will be the Customer who placed the order.
• TRANSACTION DATE (QBDT FIELD-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 static value, then the date will default to the date that the import is performed.
• REFNUMBER (QBDT FIELD-INVOICE NO): This is a required field. It is the number (can be alphanumeric) that is assigned to the imported Invoice in QBDT unless the option to allow QB to auto-assign the Invoice number has been selected. 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.
• SHIPPING ADDRESS and BILLING ADDRESS fields are not required. If nothing is imported for either of these fields, QBDT will use the default Addresses set up for the Customer in QBDT on the Invoice.

• TERMS (QBDT FIELD-TERMS): If nothing is imported for this field, QBDT will pull the default Terms from the Customer setup.

• DUE DATE (QBDT FIELD-DUE DATE): If nothing is imported for this field, QBDT will calculate the Due Date based on the date of the Invoice and the Terms.

• ITEM (QBDT FIELD-ITEM CODE): This is a required field. It must match the Item name setup in QBDT. If the Item is a Sub-Item, then your import file must contain the entire Item name with the levels separated by a colon with no space before or after the colon. (ie. Item:Sub-Item)
• QUANTITY (QBDT FIELD-QTY): A required field if the Item Type is Inventory. If nothing is imported for this field, QBDT assumes a value of zero.
• PRICE (QBDT FIELD-RATE): This is the price per unit for the Item. If nothing is imported, QBDT will pull the default Item price from the Item setup.
• SHIPPING, HANDLING, or DISCOUNT: If the order contains these items, map the appropriate data column to the appropriate field.
• SALES TAX (QBDT FIELD-TAX): If you do not import the Customer’s Sales Tax Code or the Item’s Sales Tax Item or the SALES TAX amount, QBDT will pull the default Sales Tax Code from the Customer setup and the default Sales Tax Item from the Item Setup and calculate the Sales Tax Amount accordingly.
• CURRENCY (QBDT FIELD-CURRENCY): If you have turned on Multi-Currency in your QBDT Company File, you will see an available field to import the Currency for the transaction.

 

 

 

Once you have completed your map, click SAVE AS along the bottom to save the map for future use. You may save the map as any name in any location that you choose. It will be saved as a .dat file. If you map does not appear during a future import for any reason, you may click LOAD MAP and select your saved map to reload it. It is suggested to also save a second copy of the map file in a different location as a backup, just in case.

 

Click NEXT to proceed to Window 4 of Importer.

 

Importer will perform a validation of the data in the file compared to the field to which it is mapped between Windows 3 and 4.

 

Importer will look to see if Items and Customer being imported currently exist in QBDT if the OPTION to not allow new Items and/or Customers has been checked.

 

If any issues are found during the validation, a message will appear to let you know what issues were found.

 

 

 

 

• Click BACK to fix the mapping, if needed.
• Click BACK twice to return to the data review window and change any data, if necessary. Changes on this window will not make the changes to your import file, only the import.
• Click BACK three times to return to the first Window of Importer to re-attach the import file, if you need to make changes to the import file.

Once data is fixed, click NEXT the same number of times that you clicked BACK to return to the validation step so that Importer can check the validation of the corrected data. If no issues are found, Importer will continue to Window 5 of Importer.

 

NOTE: If you have previously performed an import during this Importer session, you will receive a message asking if you wish to overwrite the existing mapping grid.

 

• If you select YES, it will remove the data from the last import so that only the current data that you are importing will be re-validated and imported.
• If you select NO, it will add this import data to the last data that you validated and may result in duplicate entries being imported.

Always click YES.

 

 

Window 5 of Importer (if using e-Commerce) will allow you to tell Importer what QBDT Item is to be used for Discount, Sales Tax, Shipping, and Handling, if you are importing any of these Items. Be sure that the Item name entered here matches QBDT exactly.

 

If the e-Commerce option was not selected, then this window will not be part of your import.

 

 

Window 6 of Importer contains the setup of the default values that QBDT will use if a new Item must be setup during import.

 

If you have selected to not allow Importer to setup new Items during an import, no new Items will be setup. However, the setup of this window is still required.

 

The suggested selections (if you will not be setting up new Items) are to select OTHER CHARGE as the Item Type and select a GL Account for ACCOUNT such as Miscellaneous, Other, or Ask My Accountant.

 

 

 

Be sure that SAVE ALL SETTINGS is checked in the lower right-hand corner before clicking FINISH. This will ensure that the settings on this sixth window of Importer will be saved with the map.

 

Click FINISH.

 

A message confirming that you wish to begin the import will appear. Click OK.

 

 

While the import is processing, you will see a count of the import in the lower left-hand corner of Importer.

 

If any issues are encountered during the import, a message will appear. Since you have selected to have Importer generate a log at the end of the import, you may click YES to ignore future messages during the import.

 

 

Once the import is finish, one of two messages will appear.

 

If the data imported without any issues, the message will display saying that the transactions were imported.

 

 

 

Click OK to display the Import Log.

 

When the log opens, it is suggested that you check the box in the lower left-hand corner to FILTER FOR RECORDS THAT DID NOT IMPORT to be certain no records failed to import.

 

To save the log, click SAVE in the lower right-hand corner, and the log will be saved as an Excel file in the location that you indicate. Once you close the log, it cannot be retrieved within Importer to view or save later.

 

 

 

If the data imported with some records failing to import, the message will display saying that the transactions imported with exceptions.

 

 

 

Click OK to display the Import Log.

 

When the log opens, check the box in the lower left-hand corner to FILTER FOR RECORDS THAT DID NOT IMPORT to display the records that failed to import.

 

Under the STATUS column, it will display a reason that the data was rejected.

 

Click SAVE in the lower right-hand corner to save the file as an Excel file in the location that you indicate. Once you close the log, it cannot be retrieved within Importer to view or save later.

 

The saved log file will contain all the records that were imported but will be filtered to display the failed records only so that you may research the errors and re-import the failed records, if necessary.

 

 

Verify in your QBDT company Invoices that the data imported correctly.