Overview

This article will cover how to investigate reporting imbalances to allow Quickbooks Online to accept the Cloud Retailer journal entries.  

Cloud Retailer contains a series of Query Filters that manage every general ledger assignment.  You can view these query filters by navigating to Reports -> General Ledger Assignments in the back office.  By default, we generate query filters for the following which can be modified to include or omit all relevant data via report filters.

• Cost of Goods Sold (not by departments)
• Inventory sales (not by departments)
• Payment methods
• Register shift opening/closing amounts
• Register shift over/short amounts
• Returns (negative line items/transactions)
• Rounding differences
• Sales by each department
• Tax collected

For successful integration, at minimum, you will need to either track the cost of goods sold for all products OR reconcile your register shifts.  These events will make up the debit amounts while the sales by departments will make up the credit amounts.

To modify a specific query, simply click on the description in the Name column of the General Ledger Assignment report.  The name will be blue to identify it as a link that will open a new window.

The GL Export Report for Quickbooks Online allows you to view register shift data prior to exporting to Quickbooks Online.  The credit and debit amounts MUST balance before the data can be exported.  In most cases, the imbalance amount can be tied to specific departments, products, or transactions.  

What to check first

1. Log into the back office
2. Navigate to Reports -> General Ledger Assignments
3. If the discrepancy is a small amount, is the Sales Rounding Error query filter mapped?
4. If the COGS query filter(s) are mapped, are the Inventory Sales query filter(s) mapped as well?
5. If the Sales by Dept query filters are mapped, are the Closing Amounts query filters mapped as well?
6. If steps 4 and 5 are correct, was a new department or payment method created since the last time the report was balanced?  Note: Any time a new department, location, or payment method is created, you MUST navigate to Reports -> Generate Standard GL Query Mapping Filters and click the "Generate Standard GL Query Mapping Filters" button.  This will not affect existing query filters.

Suggested Procedure

1. Log into the back office
2. Navigating to Reports -> GL Export Report for Quickbooks Online to initiate the register shift calculations
3. If a register shift's credit and debit amounts do not balance, calculate the difference between the Credit and Debit amounts
4. Copy the register shift from the GL Export Report for Quickbooks Online that does not balance
5. Navigate to Tools -> End of Day Reconciliation
6. Set the search fields to the day of the register shift
7. Check the box to the right of the shift name to filter by the specific register shift
8. Search for any events that equal the difference from step 1
   1. If the Credit amount is less than the Debit amount, check the Department's sales
   2. If the Debit amount is less than the Credit amount, check the Negative sales reports
9. Modify the relevant query from the General Ledger Assignments report
   1. If you modify a query to omit a product, it is highly likely you will need to modify a second query to include the same product

If you modify a Query Filter, it is suggested to modify the date range of the GL Export Report for Quickbooks Online report to view at least 7 days of register shifts.  This will allow for a broader range of data to evaluate if the expected results were achieved.