Consolidate transactions for QuickBooks Online

Cin7 Core by default syncs sales invoices, credit notes and payments individually and maintains a link to the invoice created in QuickBooks Online. For sales channels where daily sales number in the hundreds or thousands, this results in a very large quantity of information being sent to QuickBooks.

You may wish to reduce the amount of information sent to QuickBooks Online by consolidating transactions. This consolidates the individual transactions into journal entries containing the same information, which is then synced to QuickBooks Online. This functionality can make it easier to reconcile transactions by comparing consolidated transactions with payment processor receipts.

Consolidation can be turned on or off at the sales channel level. Consolidated transactions only apply to sales, and cannot be applied to purchases. Purchases are always synced as individual transactions with supplier contact details.

Prerequisites

Set up QuickBooks Online integration
Map payment clearing account and realized currency gains account

Configure required accounts

Configuring these required accounts are very important for merchants receiving payments in multiple currencies. The accounts are not automatically set up by QuickBooks Online, and are not automatically mapped to the correct accounts in Cin7 Core. Not setting up these accounts will cause your consolidated transactions to fail to sync to QuickBooks Online.

Accounts must be created and managed in QuickBooks Online and synced to Cin7 Core. Next, you can map the synced accounts to the correct accounts in Cin7 Core by going to Settings → Reference Books → Financial → Account mapping. The two required accounts are:

Realized currency gains account (Account type: Expense. Detail type: Exchange gain or loss).
Payment clearing account (Account type: Bank. Detail type: Clearing).

Realized currency gains account

With individual sync, when a foreign payment is synced to QuickBooks, the gain or loss amount is automatically recognized as a transaction of this type, and posted to the correct account automatically. When transactions are consolidated into manual journals, it is required to specify the general ledger account for each manual journal line item. Mapping this account in Cin7 Core is required for the FX gain/loss line items to sync to QuickBooks.

Payment clearing account

All journals are posted in the local currency, including payments. However, payment accounts and payments can be of a different currency, in which case, it cannot be posted in the same journal as accounts receivable (in the local currency). This is why a payment clearing account must be specified in your account mapping page. This account will currently only be used for QuickBooks transaction consolidation.

You can set up a payment clearing account in QuickBooks online using the following steps:

Sign in to your QuickBooks account and go to Accounting on the left-side panel.
Select Chart of Accounts.
On the chart of accounts screen, click the New tab.
Choose Bank for the Account type. You may choose any detail type you want for your clearing account. The account currency must match the local currency.
Type in a name for the clearing account.
Make sure you do not type in any amount for the balance before saving and closing.

Configure transaction consolidation settings

Transaction consolidation settings are configured from the QuickBooks Online integration in Cin7 Core.

Go to Integrations → QuickBooks Online and select the Sync options section.
Go to the Transaction consolidation section. Enable the Consolidate transactions slider to display consolidation options.
Select a consolidation customer. This customer will be used for the consolidated transactions and journal entries. The consolidation customer cannot be deprecated, please select another customer for consolidation first if you wish to deprecate this customer.
Each synced channel has the option to select No sync, Individual, or Consolidated for both invoices and COGS.
Save your changes. Changes will be applied for all pending transactions next time data is synced. Historical transactions will not be affected.

Transaction consolidation can be turned off by disabling the Consolidate transactions slider. You will need to change the sales channel sync settings to Individual or No sync to disable the slider.

How transactions are consolidated

Transactions will be created every day grouped by transaction date, invoice/credit note currency, and transaction type. The transaction types are:

Sale invoices and credit notes - One per day and per currency
Payments (including refunds) - One per day, currency, and payment account used, and one for FX gain/loss
COGS (including COGs from credit notes) - One per day and per currency

The consolidated transaction/journal will include individual transactions based on invoice date, credit note date, payment/refund date, and shipment/restock date for COGs. Consolidated transactions can be found by going to Sale → Sales and selecting the Consolidated transactions tab.

Consolidated transactions are not synced to QuickBooks Online, but are kept in Cin7 Core as a record of which individual transactions make up each consolidated transaction. Each consolidated transaction contains multiple journal entries, equivalent to the consolidated transactions, which will be synced to QuickBooks Online. See Review consolidated transactions for more information.

Transactions and their journal entries are always grouped by date. For example, an invoice is authorized on 30/01/2024, and a credit note for that invoice is authorized on 07/02/2024. The invoice will be consolidated into the consolidated transaction of 30/01/2024, and the credit note will be consolidated into the consolidated transaction of 07/02/2024.

Sale invoices and credit notes

Invoice and credit note transactions on the same date and of the same currency are grouped together in a single journal entry, based on the invoice or credit note authorization date.

Within the journal, invoice transactions with the same general ledger account will be grouped together and presented as a line item. Credit note transactions are also grouped together in the same way, and will be displayed in separate lines from the invoice lines.

When tracking categories is enabled in QuickBooks, and class and location have been selected in settings, transactions will be grouped together if they have the same general ledger account, class, and location.

Tax transactions are displayed as lines in the invoice and credit note journal, as above grouped according to general ledger account, and grouped by class and location if applicable. Taxes for invoices and taxes for credit notes will be displayed on separate lines.

Payments with customer credits will be included as part of the invoice journal, and not part of the payment journal.

Credit note allocations in Core cannot be exported in this format to QuickBooks Online journal. If a credit note is used against an invoice as 'payment', Core creates both an equivalent payment transaction against the invoice and a refund transaction against the credit note.

Payments and refunds

Payment and refund transactions authorized on the same date, of the same currency and same payment account will be grouped in single journal entry. Refund transactions are also grouped together in the same way, and will be displayed in separate lines from the payment lines.

NOTE: It is not possible for Cin7 Core to differentiate between payment methods. Creating separate payment accounts for different payment methods will allow you to group payment transactions by payment method if you require.

When tracking categories is enabled in QuickBooks Online, and class and location have been selected in settings, transactions will be grouped together if they have the same class and location.

Payment and refund transactions from the same payment account will be consolidated into a journal transaction representing the local currency part of the payment. If the original payment was made in a foreign currency, another journal will be posted for the currency gain or loss, see the next section for details. The journal comment will specify the original payment account for your records.

Journals for payments in local currency will debit the original account used to make the payment (credit accounts receivable). The payments clearing account is used if the payment was made in foreign currency. The journal transactions from the original payment accounts remain visible under the Financial tab of the individual sale order.

Transaction (Local currency)	Debit	Credit
Payment	Original payment account	Accounts receivable
Refund	Accounts receivable	Original refund amount

Transaction (Foreign currency)	Debit	Credit
Payment	Payment clearing account	Accounts receivable
Refund	Accounts receivable	Payment clearing account

Payments with customer credits will be included as part of the invoice journal, and not part of the payment journal.

Currency gain and loss

Foreign exchange gain or loss cannot be attached to a certain payment journal entry, as the system does not identify which entry is caused by which type of payment. All foreign exchange gain or loss transactions are grouped per date, per currency, into a separate payment journal. FX gain/loss from refunds will appear in the same journal, on a separate line.

The realized currency gains account holds the local currency value of the gain or loss part of the transaction when the payment is converted to local currency.

When payment total is less than invoice total (in local currency):

	Debit	Credit
FX gain/loss	Realized currency gains account	Accounts receivable

When payment total is more than invoice total (in local currency):

	Debit	Credit
FX gain/loss	Account receivable	Realized currency gains account

COGS

COGS transactions are grouped together in another journal, grouped by payment currency and date (shipment date for invoice COGS, restock date for credit note COGS). COGS for invoices and COGS for credit notes will be shown as separate line items of the journal. COGS journal lines are grouped by general ledger account, and by class and location if applicable. The foreign exchange gain or loss will be consolidated into the currency gain or loss journal, as described above.

Editing transactions after consolidation

Invoices, credit notes, payments, refunds, or COGS can be edited in Cin7 Core after the consolidated transaction which contains them have already been synced to QuickBooks Online. When a value is edited, the new value is synced to QuickBooks and the consolidated transaction is updated.

For example, consider an invoice authorized on the 18th March. The invoice is synced to QuickBooks on the 18th March, and a consolidated transaction created for the 18th March which includes this invoice. On the 21st of March, another invoice is authorized with the invoice date of 18th March. The consolidated journal from the 18th of March will be updated to take into account this new invoice whenever the next sync occurs.

Review consolidated transactions

A record of all consolidated transactions can be found from the sales list.

Go to Sale → Sales and select the Consolidated transactions tab. This will show all consolidated transactions, those which are pending, and those which have been synced to QuickBooks Online.

Clicking a transaction to open it will show you a summary of the consolidated transaction, along with its sync status in QuickBooks. From this page, you can view the transaction summary, list of journals, and transaction log.

Transaction summary

The transactions summary displays a list of all individual transactions making up the consolidated transaction, with its document reference number and other information.

Journals

The journals tab shows how the individual transactions have been consolidated into journal lines, showing debit/credit accounts, class, and location.

Transaction log

The transaction log shows the history of the consolidated transaction, showing when it was created and each time it was updated, with the associated values. The most recent updates are displayed first.

Undo or void consolidated transaction

Cin7 Core can perform undo or void actions for invoices, credit notes, COGs, and credit COGs but NOT for payments and refunds. This applies to both individual and consolidated transactions.

You will need to manually remove payments and refunds in QuickBooks Online before undoing or voiding the payment or refund in Cin7 Core.

Tax reporting

Users can always use the Sales Tax Transactions report to see which tax transactions are included within each consolidated transaction. Find this report in Reports Financial reports Sales Tax Transactions.

Within the report, entries can be filtered by Sync type: Consolidate to view all tax transactions included in consolidated sales. The tax transactions are grouped by tax rule, and displayed with the order information. This filtered view can be exported for easier reporting.

Disconnecting or reconnecting a sales channel

Sync type settings (consolidated, individual, no sync) are saved by the system even if the sale channel is disconnected (e.g. Shopify). When a user reconnects the same sales channel, the sync type settings for the channel will be the same as before the channel was disconnected.

Disconnecting or reconnecting QuickBooks Online

Sync type settings (consolidated, individual, no sync) are NOT saved by the system if QuickBooks Online is disconnected. When a user reconnects with QuickBooks Online, all sync type settings are reset to Individual.