Skip to content

QuickBooks Online Integration (au.com.agileware.civiquickbooks)

This is a CiviCRM extension which integrates CiviCRM with QuickBooks Online. CiviCRM Contacts and Contributions are pushed to your QuickBooks Online account, QuickBooks Invoices are pulled into CiviCRM, updating the Contribution status. Synchronise all things!

Integration of the two systems saves you time by not having to do repetitive data entry in your accounting system and CiviCRM. This extension does the work for you!

QuickBooks Online provides different Tax APIs for US and non-US countries. This extension has been developed and tested with:

  1. QuickBooks Online, Australia (AU)
  2. QuickBooks Online, United States (USA)
  3. QuickBooks Online, United Kingdom (UK)
  4. QuickBooks Online, Canada (CA)

CiviQuickBooks will only synchronise contacts to Quickbooks Online from your CiviCRM when a Contribution is added to them, either through online payments or administrator action.

CiviQuickBooks version 2 implements the OAuth 2.0 protocol for QuickBooks Online - if you are upgrading from a previous version you will need to generate new keys. See the Configuration section below.

The extension is licensed under the terms in LICENSE.txt.

Requirements

  • CiviCRM 6.11+
  • The Account Sync extension, declared as a dependency in info.xml and required for CiviQuickBooks to function

Installation

  1. Ensure the CiviContribute component is enabled
  2. Download the latest version of this extension
  3. Unzip in the CiviCRM extension directory, as defined in System Settings / Directories.
  4. Download the CiviCRM extension, Account Sync.
  5. In CiviCRM, go to System Settings / Extensions and enable both extensions, Account Sync and QuickBooks Online Integration.
  6. QuickBooks will now be available in the Administer menu.
  7. Click on the QuickBooks menu to display the QuickBooks Online Settings page.
  8. Update the CiviQuickBooks Online configuration as detailed below.

Configuration

To use this extension, you need to generate a QuickBooks Online consumer key and secret.

  1. You will need an Intuit Developer Account, sign up at (https://developer.intuit.com/). This account must be added as a user of Admin user type to the QuickBooks Online company being integrated.
  2. Once your developer account is created and linked, Go to the My Apps section and select Create new app.
  3. Use the Select APIs button in the Just start coding card, then select the Accounting API.
  4. From the App dashboard, select the OAuth 2.0 keys
  5. In the Production Keys section, copy the Client ID and Client Secret keys.
  6. Paste these values into the CiviQuickBooks Online Settings page and submit.
  7. Copy the Redirect URI that is present into the Production Keys section of your App in the Intuit Developer site.

CiviQuickBooks Online Settings page is shown below.

Note: changing the Client ID or Client Secret on this page clears any stored access/refresh tokens, so you will need to re-authorise (see below) after doing so.

Authorise CiviQuickBooks access to your QuickBooks Online account.

  1. Click the Authorize link as shown below the Access Token Expiry Date field.
  2. The QuickBooks Online Authentication page will now be displayed.
  3. Follow the instructions and to complete the authentication process.
  4. After authentication, you will be redirected back to CiviCRM.
  5. Open the QuickBooks Online Settings page.
  6. To confirm QuickBooks Online authentication a date will be shown in the Access Token Expiry Date field. If no date is shown then authentication has failed. Repeat the process.

Once authorised, the extension records which QuickBooks Online company country (AU, US, UK, CA, etc.) your connected company uses; this is detected automatically from QuickBooks and is not a setting you configure yourself.

Map your QuickBooks product/service to CiviCRM Financial account codes

Each QuickBooks Product/Service has a unique name. This is used in the CiviCRM Financial Account codes to correctly code each Invoice in QuickBooks Online.

  1. Open the QuickBooks Company, go to product/service settings page (https://sandbox.qbo.intuit.com/app/items).
  2. Identify each QuickBooks Product/Service that you want to sync with CiviCRM.
  3. Open the CiviCRM Financial Account setting page (civicrm/admin/financial/financialAccount) and update the Acctg Code of corresponding Financial account to be the same as each QuickBooks Product/Service name: Product Name -> Acctg Code
  4. When setting up Contributions in CiviCRM, ensure that the Financial Type for the Contribution is set to use the correct Financial Account as the Income Account.
  5. During sync, the Contribution line item will be set to the corresponding QuickBooks Product/Service.
  6. When a CiviCRM Acctg Code does not match any QuickBooks Product/Service name, which means that no product/service in QuickBooks has the same name, that particular line item will NOT be pushed through the invoice.

Map your QuickBooks tax account name to corresponding CiviCRM financial types Sales Tax Accounts acctg code.

When the extension pushes an invoice to QuickBooks Online, it requires every item to have a specified Tax account.

For AU Companies:

  1. Go to GST > Rates&Settings. There are many tax accounts listed there, with names in column Tax name, copy the tax name you want and paste it into the Acctg code field of corresponding Tax financial account.
  2. Open the CiviCRM Financial Account setting page (civicrm/admin/financial/financialAccount) and update the Acctg Code of corresponding Financial account to be the same as each QuickBooks tax account name.
  3. If a financial type does not contain any GST, a financial account also needs to be created with the corresponding Tax account name in QuickBooks filled out. For example, create a new financial account called NO GST with GST rate as 0, acctg codeas GST free (a tax account name which has GST rate as 0 also). And assign that financial account as corresponding line itemsSales Tax account`.
  4. When setting up Contributions in CiviCRM, ensure that the Financial Type for the Contribution/line item is set to use the correct Financial Account as the Sales Tax Account.
  5. During sync, the Contribution line item will be assigned with corresponding Tax account.
  6. When a CiviCRM Acctg Code does not match any QuickBooks Tax account name, which means that the there is no tax account in QuickBooks has the same name, that particular line item will NOT be pushed through the invoice.

For US Companies:

If none of your Contributions are taxable, you can skip this section entirely - whenever a financial type has no sales tax financial account configured, the extension automatically sends a NON (non-taxable) tax code for that line item, which already satisfies QuickBooks Online's requirement that every item have a tax code.

  1. For US companies, each line item or product/service in an invoice can only be marked with NON (for non-taxable) or TAX (taxable), and the entire invoice will have a single tax rate selected as a state tax or a combination tax rate.
  2. Users need to make sure that:
  3. In CiviCRM:
    • The financial type of each line item in the contribution is associated with a sales tax financial account
    • All those associated financial accounts need to have TAX or NON as the acctg code field.
    • All those associated financial accounts need to have the name of the desired QuickBooks TaxCode (not to be confused with a Tax Rate) as the account type code field in CiviCRM. e.g. California. For US companies using QuickBooks Online's Automated Sales Tax, this is usually the name of the state, and is generated automatically by QuickBooks rather than being listed anywhere for you to copy from - the QuickBooks API can be queried to confirm the exact name.
    • Make sure that all the financial types of line items have the same value of account type code in the sales tax financial account. The extension will pick the first line item that is taxable and with a account type code. And use that name to get the ID of tax account from QuickBooks Online.
  4. In QuickBooks Online:
    • Make sure there are matched products/services that have the name that is same with the values of the acctg code of all Income financial account of all financial types used in the contributions.
    • Make sure all used tax accounts have been created and have names recored in the account type code of the sales tax financial account of matched financial type.

Are you using a QuickBooks Online Production or Development account?

This extension currently assumes that it will be connected to a QuickBooks Online, Production account. If instead, you wish to connect this to a QuickBooks Online Development account then open the QuickBooks Online Settings page and change the Environment option from Production to Development. When you have finished testing in the Development account, then change the Environment option back to Production.

For more details on the QuickBooks Online environments, see: * https://developer.intuit.com/app/developer/qbo/docs/develop/sandboxes * https://developer.intuit.com/app/developer/qbo/docs/develop/sdks-and-samples-collections/php/configuration

Other settings

The QuickBooks Online Settings page also provides the following options:

  • Send Email for new Invoices - whether QuickBooks Online should email the customer when a new Invoice is created: Never, Only if the invoice is unpaid (the default), or Always.
  • Where should Invoice Numbers be generated? - CiviCRM (the default; the Invoice's DocNumber is set from the Contribution ID using the Invoice Prefix below) or QuickBooks (QuickBooks Online assigns the number automatically).
  • Invoice Prefix - the sprintf() format used to generate the Invoice DocNumber when Invoice Numbers are generated by CiviCRM, e.g. the default Civi-%07d produces Civi-0001234 for Contribution ID 1234.
  • Allow Online Credit Card Payment / Allow ACH Payment - whether the pushed Invoice permits the customer to pay online via credit card / ACH from within QuickBooks Online.
  • Customer Memo Override - optional text to use as the Invoice's Customer Memo instead of the Contribution's Source.
  • Activate QBO logging? - enables additional logging of QuickBooks Online API activity, for troubleshooting.

Usage

Scheduled Jobs

This extension registers four Scheduled Jobs, active by default once installed:

  • Civiquickbooks Contact Push Job - pushes CiviCRM contacts flagged as needing an update to QuickBooks Online as Customers.
  • Civiquickbooks Contact Pull Job - pulls Customers updated in QuickBooks Online since the previous day into CiviCRM, matching or creating Account Sync records for later review.
  • Civiquickbooks Invoice Push Job - pushes Contributions flagged as needing an update to QuickBooks Online as Invoices, including associated Payments.
  • Civiquickbooks Invoice Pull Job - pulls the current status of previously-pushed Invoices from QuickBooks Online, updating the matching Contribution's status (e.g. marking it Completed when the Invoice is paid in full, or Cancelled when the Invoice is voided).

These jobs only run if CiviCRM's own Scheduled Jobs / cron is configured on your site. You can review each job's configuration and run history under Administer > System Settings > Scheduled Jobs.

Sync status on the Contact page

A Contact who has been synchronised, or is queued to be synchronised, with QuickBooks Online shows a "QuickBooks Sync Status" summary on their Contact Summary page, along with a link to manually queue the Contact to be re-synchronised. If the Contact or any of their Contributions have a sync error, a "Contact Sync Errors" and/or "Invoice Sync Errors" indicator is also shown; clicking it shows the underlying error message(s) reported by QuickBooks Online.

Contacts whose push to QuickBooks Online fails are tagged with a reserved "Quickbooks Sync Error" tag, which is automatically removed once the Contact syncs successfully. A Contact that fails to push more than 3 times in a row is automatically marked as excluded from future syncing (do not sync) until an administrator investigates and re-queues it.

System-wide status check

A CiviCRM System Status message is shown (Administer > System Settings > System Status, also visible on the CiviCRM home/dashboard) when the stored QuickBooks Online refresh token has expired, prompting an administrator to re-authorise the connection via the QuickBooks Online Settings page.

QuickBooks Online API rate limits

If QuickBooks Online reports that its API rate limit has been exceeded, the extension records the time this occurred and blocks further CiviQuickBooks Push/Pull API calls for up to an hour, to avoid repeatedly hitting the limit. Invoice pushes are also throttled to roughly one request per second to reduce the likelihood of hitting QuickBooks Online's rate limits in the first place.

Special Notes:

  1. A line item whose Financial Type has no matching QuickBooks Product/Service name (via Acctg Code, see above) is excluded from the invoice, so an invoice can be pushed with fewer line items than the Contribution actually has. If every line item is excluded this way, the invoice will not be pushed at all, and the Financial Type(s)/Accounting Code(s) that failed to match are recorded as a sync error against that Contribution, viewable on the Contact's summary page.
  2. A line item whose Financial Type has no matching QuickBooks Sales Tax Account name (non-US companies only, see above) is still pushed, but without a Tax Code applied - there is currently no notification for this case, so review your Financial Account Acctg Code mappings carefully to avoid un-taxed invoice lines in QuickBooks.

Funding for this CiviCRM extension

Development and support for this Extension is funded solely by Agileware and CiviCRM community contributions. Agileware does not receive any funding at all for this Extension from the CiviCRM Partner program or CiviCRM LLC. Agileware welcome any funding to help continue paying Agileware staff to continue contributing to the CiviCRM community and Extensions.

:heart: Donate to Agileware to tell us how much you appreciate Agileware's CiviCRM open source work and community contributions.

About the Authors

This CiviCRM extension was developed by the team at Agileware.

Agileware provide a range of CiviCRM services including:

  • CiviCRM migration
  • CiviCRM integration
  • CiviCRM extension development
  • CiviCRM support
  • CiviCRM hosting
  • CiviCRM remote training services

Support your Australian CiviCRM developers, contact Agileware today!

Agileware