Sumac and QuickBooks: Setting Up Your Integration
Setting Things Up
Sumac can integrate directly with Quickbooks to send General Ledger Entries from Sumac to Quickbooks for financially significant types of data.
Before data can be transferred directly from the Sumac database to your accounting database, you need to specify how Sumac should connect to to QuickBooks. You need to tell Sumac about:
1) Define your Accounting API and Preferences
2) Match Account Names
3) Define Preferences for Ledger Entries
Defining Your Accounting API and Preferences
Sumac integrates with QuickBooks using an API (application programmer interface) that sends financially significant data from Sumac to QuickBooks. You need to tell Sumac how it should connect to QuickBooks for sending data.
In Sumac, the Accounting API is defined in Field Values. Log into your Sumac database as an Administrator. From your Sumac console, expand Utilities > Customize Database > Field Values. Choose the Area: Payments, then choose the Field Values: Accounting APIs. Click "New" to add a new Accounting API.
Type
Choose either:
- QuickBooks Online
- QuickBooks Desktop
Note: If you want Sumac to connect to QuickBooks Online, then your database connection needs to be encrypted using TLS.
Name - The name by which you will refer to this API connection. This is used within Sumac only (in drop-down menus), and is for your own reference.
User name - ID of a user of the accounting system. You can leave this field blank (it is not required by QuickBooks Desktop or QuickBooks Online).
Password - Password of a user of the accounting system. You can leave this field blank (it is not required by QuickBooks Desktop or QuickBooks Online).
Organization - This is used when connecting to the accounting system. For QuickBooks Desktop, your organization name defined within QuickBooks. For QuickBooks Online, your Company ID located in your Quickbooks Online Settings > Account and Settings > Billing & Subscription.
Sender - ID of who is sending data to the accounting system. You can leave this field blank (it is not required by QuickBooks Desktop or QuickBooks Online).
Sender Password - Password of who is sending data to the accounting system. You can leave this field blank (it is not required by QuickBooks Desktop or QuickBooks Online).
Export customer last name and first name only - This check box indicates the format of names for contacts being sent, as customers, to QuickBooks.
By default, when exporting a contact, the format Sumac will use is:
contactID-lastname, first name
If this checkbox is set, then the format becomes:
last name, first name
Export contact address as billing address - If this checkbox is set, Sumac will export the contact's address from Sumac as the contact's billing address in QuickBooks.
Export contact address as shipping address - If this checkbox is set, Sumac will export the contact's address from Sumac as the contact's shipping address in QuickBooks.
Tip: When exporting data to QuickBooks, Sumac often needs to connect data to a contact. Sumac first checks if the associated contact is already in the QuickBooks database and, if not, exports it. Telling Sumac how to export contact data to QuickBooks helps reduce duplication, and ensures that the data is sent to QuickBooks according to your preference.
Matching Account Names
Amounts being sent from Sumac to QuickBooks are identified by accounts. If the account code in Sumac matches the account name in QuickBooks, then Sumac can export these records into their appropriate accounts in QuickBooks. For example if a payment record has an account with the account code "Chequing" specified in Sumac, and there is also an account named "Chequing" in QuickBooks, then Sumac can export the payment to the "Chequing" account in QuickBooks.
In Sumac, Account Codes are defined in Field Values. Log into your Sumac database as an Administrator. From your Sumac console, expand Utilities > Customize Database > Field Values. Choose the Area: Payments, then choose the Field Values: Accounts. Click "New" to add a new account.
When adding an account to this Field Values, you enter two pieces of information:
- Account Code - This is the name of the account as it appears in your financial accounting software.
- Account Name - A name which is presented to users of Sumac so that they can make the appropriate choice when entering data.
Note: The Account Code field is what Sumac uses to send the data to QuickBooks. Ensure that the Account Code field contains the appropriate Account Name defined in QuickBooks. For example, if you have a Pledge Revenue account called "PledgeRev" in QuickBooks, enter "PledgeRev" into the Account Code field in Sumac.
You could also enter the same details into the Account Name field in Sumac if you prefer.
Accounts and QuickBooks Classes
In addition to Accounts, QuickBooks supports Classes – an extra way to classify transactions. When you export transactions from Sumac to QuickBooks, Sumac can tell QuickBooks which Class the transaction belongs to. To do this, in Sumac, specify an Account Code that includes both the name of an Account, and the name of a Class.
The Account Code field should contain first the QuickBooks Account name, then a vertical bar |, then the QuickBooks Class. For example, here are two accounts – the Pledge Revenue account, with one attributable to a Chicago class, and the other attributable to a Toronto class:
PledgeRev|Chicago
PledgeRev|Toronto
Note: A Sumac account can be defined as just a Class with no Account. For example, if you want to define just the Toronto Class, the Sumac Account Code could be defined as just:
|Toronto
In this situation, Sumac would tell QuickBooks about the class without an account.
Defining Preferences for Ledger Entries
Before setting up Preferences for Ledger Entries, ensure that your account codes have been defined to help you balance your ledger correctly. Usually, this is set up to correspond to the accounts in your Chart of Accounts in QuickBooks.
You also need to ensure that
- each payment type is linked to the appropriate account, since most receipts debit the payment type’s account code.
- each surcharge (tax) is linked to the appropriate account, since surcharge amounts credit the surcharge’s account code
- account codes are associated with the various items that you want entered using different account numbers. For example, if you want donations to a campaign to be entered against a different account, you need to put that account code in the record that defines the campaign.
We have several how-to videos that will help illustrate this if you are just getting started. You can access all of them here.
Once you have defined your debit and credit accounts correctly, you must tell Sumac your preferences for creating Ledger Entries.
In Sumac, log into your Sumac database as an Administrator. From your Sumac console, expand Utilities > Customize Database > Preferences. Click the "Ledger" tab.
This area allows you to click checkboxes indicating which types of transactions should be put into the ledger, and also specify some account information indicating how transactions should be added to the ledger. We have a specific how-to video that helps guide you through defining your Ledger Preferences here.
Regarding Net Ledgerizing
If the net-ledgerizing preference is on, then whenever a financial transaction (e.g. donation or membership) is edited, Sumac creates ledger entries that record the difference between what the old transaction created and what the new one requires. This enables
you to keep Sumac ledger entries synchronized with ledger entries in your accounting system.
Without this feature enabled, when a record that creates ledger entries is edited (e.g. a donation, an invoice), Sumac deletes all the ledger entries that relate to that record, then creates new ones.
Our products got an upgrade!
Some screenshots and help videos have been made using older versions of our products. Though your version's colours and themes may appear different than what you see here, the instructions, links and advice in this article are still accurate and useful!
Related Articles
How does Sumac integrate with accounting software? (QuickBooks, Simply Accounting, etc)
There are several ways that Sumac can integrate with accounting software: a. You can instantly get a total of donations in any time period, so if you just need to put a "total donations" figure into the accounting software, you can do this as often ...How does Sumac handle Penny Rounding Accounts?
Some countries have eliminated very small denominations coins. For example, Canada used to have one-cent coins (pennies), but now its smallest denomination coin is five cents. As a result, cash transactions are rounded up or down to the nearest five ...How-to Video: Ledger Entries 4 – How to Define Credit Accounts (2:45)
This video shows you how to define Credit Accounts to facilitate using Ledger Entries in Sumac. Script: https://sumac.com/assetswp/training-docs/Ledger%20Entries%204%20-%20How%20to%20Define%20Credit%20Accounts.pdfHow-to Video: Ledger Entries 3 – How to Assign Debit Accounts (2:32)How-to Video:
This video shows you how to assign the Debit Accounts you have set up in your Sumac database. Script: https://sumac.com/assetswp/training-docs/Ledger%20Entries%203%20-%20How%20to%20Assign%20Debit%20Accounts.pdfWhat happens if there are errors in the data Sumac exports to QuickBooks?
Before it sends any records to QuickBooks, Sumac needs to get QuickBooks account IDs. It does this by sending the codes of the accounts in Sumac, and getting the account IDs back from QuickBooks. If any of these fail, the whole export operation is ...
Societ Academy
Learn what you need to know, any time. Free!
- High quality training on demand - sessions are available every day, so you can access training that fits into your schedule.
- Training sessions that are easy to follow - helpful tips throughout each session, and access to training moderators ensure your questions get answered.
- A certificate of completion - you can request a certificate of completion for every session you attend.