Which report would you use to show top-selling and/or worst selling products in quickbooks online

Integrating your DEAR Inventory and QuickBooks Online (QBO) accounts allow DEAR Inventory to become the central point of all inventory management within your business. This means that all inventory-related purchases, sales, and manufacturing activities are performed in DEAR Inventory and then synchronised over to QBO.

We recommend reading the Getting Started Guide in conjunction with this setup article. On the QuickBooks side, inventory-related resources can be found here.

NOTE: If necessary, you can easily switch between accounting apps, such as changing from Xero to QuickBooks Online, switching from one QBO organisation to another, or disconnecting Xero/QBO to use DEAR as a standalone accounting system. For more information, refer to Switching Accounting Integrations.

DEAR Inventory supports integrating with:

• QuickBooks Online Plus or Advanced

DEAR Inventory DOES NOT support integrating with:

• QuickBooks Desktop
• QuickBooks Simple Start

DEAR Inventory DOES NOT recommend integrating with:

• QuickBooks Essentials - DEAR can connect to QuickBooks Essentials however some features may not work correctly

NOTE: For QuickBooks Online Plus, turn off quantity tracking for products. If quantity tracking is on, data will not sync from DEAR to QBO. 

IMPORTANT! If you encounter errors after following this guide and completing DEAR-QuickBooks integration, you may check out this article on common QBO errors and how to troubleshoot them.

Prerequisites

• Open a QuickBooks Online Plus or Essential account (trial account is sufficient)
• Users will need the Integration: Accounting - Quickbooks permission in order to use this feature. 

Table of Contents

• Setting up Accounts in QBO
  • Revenue account mapping
• Turning off Inventory Tracking in QBO
• Connecting DEAR Inventory and QBO
  • Connecting QBO when you create a DEAR account
  • Connecting an existing DEAR account to QBO
  • More Account Mapping
• Using QBO Integration Settings
  • Auto-Synchronisation Settings
  • Class and Location Tracking
• Maintaining Cost of Goods Sold
• Replacing Products in QBO
• Connecting to Another QBO Organisation
• Disabling Your QBO Integration
• Exporting Historical Data from DEAR to QBO
• Find incorrect duplicate manual journals
• Refunds and credit notes sync to QBO

Setting up Accounts in QBO

[back to top]

The chart of accounts is the list of all accounts your organisation has available to record your transactions and is important for classifying your transactions correctly and keeping your accounts accurate.

NOTE: You will need a QuickBooks Online PLUS or ESSENTIALS account to correctly integrate with DEAR.

Before starting your integration, there are some accounts that must be created in QBO first. In the table below we have provided the recommend account type settings to use. You can add more accounts or make changes to suit your business requirements – if you are unsure, consult with your accountant or bookkeeper before adding or editing any accounts.

NOTE: If you are using QBO, you must create/manage your accounts from QBO. They will then be imported into DEAR when you next synchronise your accounts. You can view your Chart of Accounts in DEAR by going to Settings → Reference Books → Financial → Chart of Accounts, but it will be read-only.

The accounts needed for DEAR to function correctly are listed below with their required settings.

Already created by QBO:

• Sales Tax (GST, VAT)
• Sales/Revenue
• Inventory/Inventory Control.

Required accounts

You may not be asked to map all of these accounts when connecting to QBO for the first time. However, since they allow you to fully use DEAR features, you should create these accounts in QBO now. 

Optional Accounts

Inventory Accrual/Stock in Transit (requires Inventory Accrual to be enabled)

Gift Card Liability (requires Gift Cards to be enabled)

Creating accounts in QBO:

1. In QBO, navigate to Settings → Your Company → Chart of Accounts. The Chart of Accounts will be loaded based on the industry that you specified when initially setting up QuickBooks Online and you may find that some of the accounts may already be present. Make sure there is an account for each of the ones listed above and that they have the correct settings. You may also create any other accounts required for your business needs. 
2. Select New. Enter the account details according to the required settings above, then click Save and Close to finish (or Save and New if you want to add another account straight away).
3. Once you have added all the relevant accounts, you're ready to begin the integration with DEAR Inventory.

Revenue account mapping

[back to top]

All products and services in QBO must have an account to which they are mapped. This mapping will always override the revenue account mapping in DEAR. Unfortunately there is no way to override this from DEAR. Please note:

• Customer-level revenue account mapping in DEAR will be overridden by the product-level mapping in QBO
• Product level revenue account mapping in DEAR will be overridden by the QBO account mapping.
• Revenue account mapping for eCommerce integrations in DEAR will also be overridden by the QBO account mapping. 

Turning off Inventory Tracking in QBO

[back to top]

DEAR does not support quantity or inventory tracking in QBO. If quantity tracking or inventory tracking is turned on in QBO, DEAR will not sync data to QBO. 

1. Navigate to Settings → Your Company → Accounts and Settings, then click the Sales tab. 
2. In Products and Services, ensure Track quantity and price/rate and Track inventory quantity on hand are turned Off.
3. Save your changes to finish.
   
   

NOTE: The inventory type products previously tracked in QBO remain tracked even after tracking is turned off. To resolve the issue, delete the old products and create new products. See Replacing Products in QBO below for more details. 

Connecting DEAR Inventory and QBO

[back to top]

Once you have added all the relevant accounts, you can either connect to QBO (if you already have an account) or complete the DEAR signup process (if you don't). Consult the Getting Started Guide for help setting up your DEAR account.

Connecting QBO when you create a DEAR account

[back to top]

If you don't have a DEAR account yet, you can start the integration process from QBO or from DEAR. 

NOTE: You will need a QuickBooks Online PLUS or ADVANCED account to correctly integrate with DEAR.

Connecting from QBO:

1. On your QBO Dashboard, click Apps, then on the Find Apps page, search for DEAR Inventory.
2. On the DEAR Inventory page, you can review the features of DEAR Inventory. Once you're ready to integrate the two apps, click Get app now.
3. On the window that appears, click Connect. This will grant DEAR Inventory access to your QBO account and start the integration process between the two applications.

4. You will then be taken to the DEAR Inventory Trial Account setup page. Enter your information on the page, check the box signifying your agreement to the DEAR Inventory Terms of Service and Privacy Policy, then click Sign Up.

Connecting from DEAR:

1. On the DEAR website, register for a new account. 
2. On the sign-up screen, you will be asked to enter some information about your business. When asked to connect your accounting system, select QuickBooks Online.
3. You will receive an activation email from DEAR, click the link in the email. You will be redirected back to DEAR Inventory and asked to choose a password.
4. You will then be taken to the QuickBooks Connect Wizard. See QuickBooks Connect Wizard for detailed instructions. 
5. Choose which data to synchronise from QBO. The Chart of Accounts, Payment Terms and Tax Rules will be imported from QBO. Continue to set up your Products, Suppliers, and Customers. Refer to the Getting Started Guide for more help getting set up. Continue the set up process. 

If you are setting up DEAR for the first time, you will need to enter the opening balances from your QBO accounts into DEAR. See Manage Opening Balances for more information. 

Navigate to Reference Books → Account Mapping to finish mapping your accounts correctly. See More Account Mapping below for further information.

• DEAR can only map accounts between systems if they have the same settings. If you are missing an account, you can go back to QBO and create them (check the required account settings above), sync, then reload the Account Mapping page.

Connecting an existing DEAR account to QBO

[back to top]

If you already have a DEAR account and QBO account, you can use the QuickBooks Connect Wizard to integrate the two systems. 

NOTE: Some versions of QuickBooks Online (e.g. QuickBooks Online Australia) no longer support custom tax rates. In order for the tax mappings section of the integration to go smoothly, tax rules in DEAR must match the QBO-created tax rules in QBO. Check your DEAR tax rules and delete/edit any that do not exactly match the QBO-provided tax rules if your version of QBO does not allow custom tax rates.

Connect DEAR Inventory and QBO:

1. From the left menu, click Integrations, then click the QBO option. 
2. Click Connect to start the QuickBooks Connect Wizard. DEAR is set to keep your data by default. You may select No and choose which data to keep or discard. Transactional data includes sales, purchases, transfers, production orders, etc.
   
   
3. Click Continue. If you are not already logged into QBO, you will be redirected to log in. You will then be redirected back to DEAR. Click Allow Access to let DEAR Inventory access your QBO data.
4. Back in the QuickBooks Connect Wizard, on the Mapping page, the mapping of your QBO accounts with DEAR Inventory accounts is displayed. 
5. The system to match accounts where possible. Select QBO accounts from the dropdown menus in order to match them manually. You must fully map the accounts in order to continue. 
   • The Connect Wizard will only map accounts between systems if they have the same settings. If you are missing an account in QBO (you may have additional accounts created in your DEAR Chart of Accounts), you can go back to QBO and create them, then click Reload accounts from QuickBooks to continue mapping.
     
     
6. Next, you will be taken to the Tax Rules Mapping screen where DEAR will attempt to map your tax rules from QBO automatically. 
7. Some versions of QuickBooks Online (e.g. QuickBooks Online Australia) no longer support custom tax rates. In order for the tax mappings section of the integration to go smoothly, tax rules in DEAR must match the QBO-created tax rules in QBO. Check your DEAR tax rules and delete/edit any that do not exactly match the QBO-provided tax rules if your version of QBO does not allow custom tax rates. 
   • The Connect Wizard only allows tax rule mapping between QBO and DEAR if the tax rates and Sale/Purchase properties are equal. If DEAR has tax rules that do not have an equivalent in QBO, you will have to create these tax rules in QBO first. Then, click Reload tax rules from QBO to continue mapping.
     
     

1. Next, you will be asked to map the Payment Terms. If DEAR has payment terms that do not have an equivalent in QBO, you will have to create these tax rules in QBO first. Then, click Reload payment terms from QBO to continue mapping.
   
   

1. You will then be asked to confirm that you want the two apps to be integrated. Note that the data migration process is irreversible and that all DEAR Inventory transactions will be updated with new accounts from QuickBooks. To go ahead with the operation, enter I would like to update all transactions in the box, then click Confirm to start synchronising data between DEAR Inventory and QBO.  
2. A message informs you that the migration will continue in the background and that you will receive an email when the process is completed. Now your DEAR and QBO accounts are synchronised. 

While the mapping is ongoing, your DEAR account will be locked and all users, including you, as the master user, will not be able to access DEAR Inventory. After the migration is done, DEAR will send you an email that will contain a link to the page in DEAR where you can export historical data to QuickBooks Online. If any error happens during the migration, the email will contain information about the error, and you will be asked to contact DEAR Customer Support.

More Account Mapping

[back to top]

To fully enable all of the DEAR functionality, you can finish mapping the required and optional accounts. Create any missing accounts in QuickBooks Online if you haven't already, synchronise your data and navigate to Settings → Reference Books → Financials → Account Mapping. Select the equivalent accounts from the list and Save your changes.

DEAR can only map accounts between systems if they have the same settings. If you are missing an account, you can go back to QuickBooks and create them (check the required account settings above), sync, then reload the Account Mapping page.

NOTE: You can use DEAR without completing this account mapping. If you attempt to use functions that are linked to an unmapped account, DEAR will report an error and may be unable to complete the operation.

Using QBO Integration Settings

[back to top]

Once your DEAR Inventory account is set up, you'll be able to locate the QuickBooks Online Integration page by selecting Integration from the right-hand menu, then QBO.  

The QBO Integration page will contain the details of the integration (on the left-hand side of the page), which explain the flow of data between DEAR Inventory and QBO.

Created in QuickBooks, sent to DEAR

• Chart of Accounts: Any changes in the Chart of Accounts in QuickBooks will be synced to DEAR. The Chart of Accounts is read-only in DEAR.
• Tax Rules: Any changes in Tax Rules in QuickBooks will be synced to DEAR. Tax Rules can be edited in DEAR, but can only be created in QBO. See Tax Rules for more information. 
• Payment Terms: Any changes in Payment Terms in QuickBooks will be synced to DEAR. Payment Terms are read-only in DEAR.
• Company Addresses

Created in DEAR, sent to QuickBooks

• Purchase Orders: (Optional) All Purchase Orders authorised in DEAR will be exported to QuickBooks if the Blind Receipt box is not checked.
• Purchase Credit Notes: Purchase Credit Notes authorised in DEAR will be exported to QuickBooks as Vendor Credits.
• Sales Quotes/Estimates: (Optional) If the Skip Quote option is not checked, all Sales Quotes authorised in DEAR will be exported to QuickBooks as Estimates.
• Sales Credit Notes and Credit Memos: Sales Credit Notes authorised in DEAR will be exported to QuickBooks as Credit Memos.
• Customer Credits.
• Supplier Deposits.
• Journals.
• Transfers.

Can be created in both, synchronised both ways

• Vendors/Suppliers.
• Customers. 
  • Updated customers in DEAR will overwrite QBO data and vice versa
  • Deprecated customers in DEAR will not be re-imported from QBO
• Any Bill/Sale/Purchase Payments or Refunds.
• Products/Items: Creating inventory in both QBO and DEAR can cause inventory tracking issues. For more information, see QBO Troubleshooting Common Errors - Duplicates.
• Bills or Purchase Invoices: Purchase Invoices authorised in DEAR will be exported to QuickBooks as Bills. Purchase Invoices can be optionally imported from QBO.
• Sales Invoices: Sales Invoices created in DEAR are automatically exported to QuickBooks. Sales Invoices can be optionally imported from QBO. 

Within the QBO Integration page, you can control whether your integration is enabled or disabled and modify any other integration settings. We recommend taking a look at your settings before putting through new transactions. 

From the Integration Settings screen, set the following options:

• Export Purchase Orders to QuickBooks: Turn exporting of Purchase Orders to QBO off and on.
• Export Sales Quotes to QuickBooks: Turn exporting of Sales Quotes (as Sales Estimates) to QBO off and on.
• Export COGS: Allows you to enable or disable the exporting of cost of goods sold transactions to QBO. COGS transactions are created by DEAR automatically when the Ship stage is authorised on Sale. You can manually void/recall export of these transactions from QBO with the COGS Maintenance tool (see below). 
• Class: See Class and Location Tracking below.
• Location: See Class and Location Tracking below.
• Import Purchase Invoices: Optionally import purchase invoices (bills) from QBO (purchase invoices created in DEAR are automatically exported to QBO).
• Auto-receive stock when loading purchase invoices from QuickBooks: This option is only visible if Import Purchase Invoices is enabled. If enabled, stock will be received into your inventory automatically, once a purchase invoice is loaded from QBO. If disabled, stock receiving must be carried out manually. 
• Import Sales Invoices: Optionally import sales invoices from QBO (purchase invoices created in DEAR are automatically exported to QBO).
• QuickBooks fulfilment mode when loading sales invoices from QuickBooks: This option is only visible if Import Sales Invoices is enabled. Select whether picking, packing and shipping are set to auto on sales invoices imported from QuickBooks. 
• QuickBooks location to be used for purchases and sales loaded from QuickBooks: This option is only visible if Import Purchase Invoices or Import Sales Invoices is enabled. 
• Use actual Shipment Date when available for Invoice sync to QuickBooks: If a Shipment Date is listed on the Ship tab when the Invoice is synced to QBO, it will be imported to the QBO Shipping Date tab. If there is no Shipment Date available or this setting is turned off, the Required By date will be used instead.
• Auto-Synchronisation: See Auto-synchronisation Settings below. 

Auto-Synchronisation Settings

[back to top]

You can choose to sync DEAR systems with QBO manually or automatically. Toggle Auto-Synchronisation to Yes to display additional auto-sync options. You can set the start date, start time, sync frequency, and the alert type for any errors raised during the synchronisation process.

NOTE: If you have enabled auto-synchronisation, you can still perform manual synchronisation at any time, as long as an auto-synchronisation process is not running. If the auto-synchronisation process is running, DEAR will not allow manual synchronisation.

There are two ways the user can be notified of errors: 

• Internal: These error notifications will be available within the Notifications tab in DEAR. For more information, see Notifications.
• Email: Users in the specified mailing list will receive an email notification about failed auto-sync errors. For information on creating a mailing list, see Notifications - Mailing Lists.

NOTE: The status of each auto-sync is detailed by filtering for Integrations in the Audit Report, which is found under the Reports module.

Class and Location Tracking 

[back to top]

Classes and Locations are optional fields used to give extra information on QBO invoices. Location is applied to the entire document, while Class is applied either to line items or to the entire document. 

Class and Location mapping allows you to select fields from the DEAR sales/purchase document header to export to QBO and be displayed in these fields. 

You will need to enable tracking of classes and locations in QuickBooks to use this feature. If class/location tracking is not enabled, these fields will be left blank on the QBO invoice. 

The following fields can be mapped:

• Class: Sales Rep, Location, Job, Category, Brand, Family, Customer Attribute (1-10), Product Attributes (1-10), Supplier Attributes (1-10)
• Location: Sales Rep, Location, Job, Customer Attributes (1-10), Supplier Attributes (1-10)

Enable class and location tracking in QBO:

1. Navigate to Settings → Your Company → Accounts and Settings, then click the Advanced tab. 
2. In Categories, turn Track classes and/or Track locations On.
3. Save your changes to finish.

For more information, see Using Classes and Locations in QuickBooks Online.  

Maintaining Cost of Goods Sold

[back to top]

IMPORTANT! These features are for maintenance purposes only. Exporting COGS transactions is automatically performed daily.

You can manually export COGS transactions, or void (recall) previous exports, for a specific date range using the Cost of Goods Sold Maintenance section. Specify the period you want to adjust (the selected dates are included in the period), then click either the Export COGS to QuickBooks or Void COGS in QuickBooks button, depending on the action you're taking.

Replacing Products in QBO from DEAR

[back to top]

You may find that you are still experiencing duplicate COGS in transactions even if inventory tracking has been disabled (described above). This is possible if inventory tracking for a product has been enabled in QBO at any point. 

In this case, you can replace all products at once to fix the issue. All existing items in QuickBooks will be rewritten with products from DEAR. 

NOTE: Product Names must be unique in DEAR to be successfully exported to QBO.

Duplicate Entries

The process will start by checking for uniqueness of each product name in DEAR using the first 100 symbols of a name. 

1. If there are no duplicates, the replacing product process will begin.
2. If a single duplicate product name is found, an error message will pop up. If this happens, you shouldn't start replacing products in QBO until you are sure all product names are unique. 

Progress Status

 During the replacement process, the progress status will be displayed on the screen.

1. Removing all data from QuickBooks. 
2. Exporting products from DEAR.
3. Operation status –
   • Products in QuickBooks Online were replaced successfully.
   • Products in QuickBooks Online were not replaced, due to the following error: <Error message>.

Connecting to Another QBO Organisation

[back to top]

If you decide to connect DEAR to a different QBO organisation, click the Change Organisation button. You will be walked through the QuickBooks Connect Wizard to finish account and tax mapping. This is essentially the same process as connecting to QuickBooks for the first time. 

See Switching Accounting Integrations and QuickBooks Connect Wizard for more information.

Disabling your QBO Integration

[back to top]

If you decide that you want to remove the integration between DEAR and QBO permanently, click the Disable Integration button. You can continue to use DEAR as a standalone accounting system. 

Exporting Historical Transactions

[back to top]

You can export historical data from DEAR to QBO, which registers all documents and related items (products, vendors, customers, etc.). Use this feature to export all data to QBO for a particular date range. To start the export process, select a start date and then click the Export Historical Data button.

Find incorrect duplicate manual journals

[back to top]

This tool should only be used in the case of finding discrepancies between DEAR and QuickBooks Online trial balance. Enter a To Date and From Date and click Search to find all manual journals in QuickBooks for the period.  Compare journal entries that were created or not created from DEAR to try to find journals that were created by DEAR as duplicates. 

Once an incorrect duplicate manual journal has been found, click Delete for the incorrect journal line to void the journal in QuickBooks. 

Refunds and credit notes sync to QBO

[back to top]

A credit note in DEAR is synced as a Credit Memo document to QuickBooks. Only the part of the credit note that has not yet been refunded is synced to QBO as a Credit Memo (line totals reduced by refund amount). 

• Example: If a credit note has one line with a total of 500, and it has 1 refund with a total of 100, a Credit Memo in QuickBooks will have one line total of 400 (500-100). Each line will be proportionally reduced to reflect only the remaining not yet refunded total of the credit note.

When a refund is added, a different document (Refund Receipt) is synced to QBO. It also has lines, DEAR takes them from the credit note, then reduces them proportionally to math the refund amount. 

• In our example, two documents will be generated in QBO for a credit note of 500 with a refund of 100: 
  • 1 Credit Memo (total of 400) (500-100)
  • 1 refund receipt (total of 100)
• Both documents will have lines in QuickBooks, matching by content with lines in DEAR credit notes, but with proportionally adjusted amounts and taxes.

If the refund amount exceeds the credit note amount, the refund/credit note will not sync to QBO - QBO does not allow a negative credit note - and you will receive an error. If you are receiving an error when syncing credit notes or refunds, please check the sale and refund totals and adjust if necessary.