BlogQuickbooks Deal Export
Importing Sales Invoices Into Quickbooks
With just a few mouse clicks bookkeepers can create a deal file in Q-F&I Online that's easily importable from Quickbooks. Call Quantech today for information and pricing!
Exporting Data From QF&I
It is necessary to first set up a Data Map for the company. This is a one-time requirement unless any setup changes are made. The data map defines the appropriate posting location(s) in QuickBooks for each QF&I item. This must be done on the QF&I Admin level.
Setting up the Data Map
Preamble
Required Fields
1. Sales Tax Vendor Names
2. All QuickBooks Accounts
3. QuickBooks Item for all taxes used by the company
4. If the company is using the inventory module in QuickBooks, all other QuickBooks Items except “Balance Due or To Finance” Although the data map will allow you to save and exit without populating all of the required fields, failure to do so will yield the following error when generating an export file:
Notes About QuickBooks Accounts
1. “Use account numbers”
a. If you have selected “Use account numbers” in your QuickBooks company preferences, you should be able to use either the account number or the account name to populate the data map.
b. If you have NOT selected “Use account numbers”, you must use the account name. Otherwise the import will result in the following error.
c. Older versions of QuickBooks may handle this differently, so it is important to understand how your version behaves.
2. The accounts defined in the data map must already exist and be spelled exactly the same way as they are spelled in QuickBooks, although they are not case-sensitive.
a. If the account does not already exist in QuickBooks, some versions will automatically create a new account. Older versions may require that you have the account already present so it is important to understand how your version behaves.
b. If an account is created automatically, it will be created as a bank account with no account number (even if you have account numbers turned on). This will likely present a problem because a bank account cannot be changed to another account type later.
3. The account associated with the “Balance Due or To Finance” line must be your linked Accounts Receivable account in QuickBooks.
4. Other than the account associated with the “Balance Due or To Finance” line, you cannot use any accounts in the data map that have an Accounts Receivable (“AR”) or Accounts Payable (“AP”) account type in QuickBooks. This is because QuickBooks is unable to establish “links” between transactions that are imported by way of iif files. There can only be one AR or AP account per imported transaction. Use of these account types will yield the following error:
5. The use of inventory items does not negate the need to define accounts. The import file still requires that accounts be defined. QuickBooks will use the account from the import file/data map even if it differs from the account that has been set up for the corresponding inventory item in QuickBooks.
Notes About QuickBooks Items
1. In order to post transactions to inventory items that already exist in QuickBooks, the items defined in the data map must be spelled exactly the same way as they are spelled in QuickBooks, although they are not case-sensitive.
2. If the item does not already exist in QuickBooks, a new item will be created with a type of “Service”. Although the imported transactions can be edited, item types cannot be changed once transactions have been posted. Older versions may require that the item exist prior to importing, so it is important to understand how your version behaves.
In order to assist with the exact spelling requirement for accounts and inventory items, you may wish to export these details directly from QuickBooks.
If you are using inventory, the most useful report for doing this is the Item Listing report under Reports > Lists > Item Listing. In the upper left corner, click “Customize Report”. Under the Display tab, click to check columns that you wish to include in the report, and to uncheck columns that are already checked that you don’t wish to include. We recommend that you include Item, Description, Account and Tax Agency. Tax Agency is included to provide the names of the Sales Tax Vendors associated with each tax which is also required for the data map. When the report populates, you can click on Description and drag it to the beginning, thereby putting the data in the same order as it exists on the QF&I Data Map. Click the “Excel” button along the upper ribbon to “Create New Worksheet” in Excel. This will give you the data electronically so you can copy and paste into the data map if you wish.
If an item is set up as a subitem of another item, some versions of QuickBooks will display the account associated with the main item, followed by the account associated with the subitem. If this is the case (ie. two accounts are listed for one item), you need to select the second account in order to ensure that you use the account associated with the subitem and not the item.
If you are not using inventory in QuickBooks, or if any of the items from the data map don’t have an inventory item associated with them, you can follow the logic above to obtain the Account Listing report instead. Navigate to Reports > Lists > Account Listing. In this case, we recommend that you select the Account, Description and Accnt. # columns (if you have account numbering turned on). Again, you can drag the columns to re-sort them, and export the report to Excel.
Setup Steps
1. Log in to QF&I as an administrator.
2. At the Companies list screen, click on the appropriate company name to view the Company Details screen.
3. Click on the Integrations button at the top of this screen to view the Integrations screen.
4. If QuickBooks does not already have a checkmark next to it, click the check box to make the QuickBooks integration active.
5. Choose the QuickBooks item from the Integration dropdown to view the QuickBooks integration panel.
6. Select the Date Output Format that matches the user’s computer system date format:
You can check your computer system date format on your start menu:
7. Click on the Data Map button to view the QuickBooks Data Map screen.
8. If there are no QF&I Items populated in this screen, click the “Load Template” button to populate the QF&I Items that are relevant for this company. DO NOT CLICK this button if there are items listed, and your data map is already populated. It will clear existing data!
9. Fill in the Vendor Name for the vendor to whom you remit each of your taxes. These vendors will have a vendor type of Tax Agency in QuickBooks. Click Save.
10. Click each QF&I Item to add values for the related QuickBooks Account and QuickBooks Item, then click Save to close the input screen.
11. Once you have added values for all of the QF&I Items, close all screens that are open using the [x] button, and logout as the administrator.
Exporting Deal Information
1. Log in to QF&I as a regular user.
2. From the Home screen, select “Deals” from the row of tabs across the top of the screen.
3. From the Deals screen, select “Delivered” from the row of tabs immediately below. Only delivered deals can be exported.
4. From the list of delivered deals, click the checkbox on the far left for each of the deals that you wish to export. You may include multiple deals.
5. Once your deals have been selected, click the “QuickBooks” button. This will generate the QuickBooks import file.
6. Right click on the link to save the file as per the on-screen instructions. Remember where you have saved the file!
Importing Invoice Transactions into QuickBooks
Preamble
- You must login to QuickBooks as an administrator in single-user mode in order to import data.
- Unless you cancel the import, QuickBooks always gives you a message that the data has been imported, even if it has not. Receiving an import error prior to this message is usually a good indication that your import was at least partially unsuccessful. If your import file contains multiple deals and only one fails, the remaining deals will normally import correctly. Therefore, it is important to double-check what has imported, especially before reimporting the entire file.
- The customer name, address and tax status is defined in each import file. If the exact customer name does not already exist in QuickBooks, it will be added to your customer records with the information provided in the import file. If the exact customer does already exist in QuickBooks, the transaction will use that customer. Current versions of QuickBooks will recognize that a customer name spelled "FIRST LAST" and "LAST, FIRST" are the same.
- The sales transactions will not pull any details from the customer’s record such as address, terms, etc.
- QuickBooks’ inability to establish “links” between imported transactions may impact how customers recognize deposits. The import file will bring deposits across as a negative line item. If you have set up a clearing account to post deposits that were received previously, then your QF&I data map would reference this same clearing account, and importing the QF&I invoice would clear that account. If, however, you have this deposit set up as a prepayment that is waiting to be applied to your customer’s invoice, the link between the invoice and prepayment will not be made automatically. You may wish to open the imported invoice, delete the deposit row altogether, and click “Apply Credits” to apply your outstanding prepayment to the newly-created invoice that way instead.
Import Steps
1. From within QuickBooks, select File > Utilities > Import > IIF Files
2. Browse to the location where you saved the file that you exported from QF&I.
3. Select the file, and choose “Open”.
4. You should receive confirmation that the data has been imported.
5. There are a number of ways to find your imported invoices in order to review and/or edit them. Use your preferred method, or click “Find” from the Main tab of the invoice entry screen. Then choose your customer from the drop-down and/or enter your deal number in the Invoice # field (QF&I Deal # = QuickBooks Invoice #). Click “Find” again to display all invoices that match your qualifiers.
Please don't hesitate to call Quantech 877-611-0622 should you have any problems using the integration.
Quantech Q-F&I online / iCerts (FCI) Integration
For more information on Quickbooks Accounting Software for Small Businesses please visit: https://quickbooks.