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 Lookup Lists. Log into your Sumac database as an Administrator. From your Sumac console, expand Utilities > Customize Database > Lookup Lists. Choose the Area: Payments, then choose the Lookup List: Accounting APIs. Click "New" to add a new Accounting API.
- 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 - Your organization name defined within QuickBooks. This is used when connecting to the accounting system.
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 Lookup Lists. Log into your Sumac database as an Administrator. From your Sumac console, expand Utilities > Customize Database > Lookup Lists. Choose the Area: Payments, then choose the Lookup List: Accounts. Click "New" to add a new account.
When adding an account to this Lookup List, 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:
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:
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.