I have three organizations that use Quickbooks and some other process to track contributions. I would like to encourage all of them to move to Civicrm. (They are currently using out-of-date MS Access or Excel for tracking donations.) Each have set up Quickbooks to use classes and sub-classes in their accounting. Are you actually using Civicrm and Quickbooks, AND using classes and sub-classes in Quickbooks, AND have set up Civicrm to then transfer that data into Quickbooks effectively? I have read all over the Internet and the Civi site. What I am seeking is someone who is using both programs. Practically speaking, what did you do in the setup and execution to get classes and sub-classes to translate from Civicrm to Quickbooks? I have read a little of some people using custom fields and then others who adapt Campaigns or Financial Types to make this happen, but no practical setup or practical process is communicated in their postings. Help?
We use Civi & QuickBooks. Our QBs is only used for accounting. If we need to see donation details, we look in Civi. We do not record contribution detail in Quickbooks. We do not see a need to have that data stored in two places.
We just record the deposit in QBs. We use Class in QB for our Functional Expense allocations (Program, Restricted, and Unrestricted). Note, since most donations are not related to Functional Expense allocations (other than Program Income), we have created 2 additional Classes in QBs--Unrestricted and Restricted. So most deposits are recorded using these 2 QB Classes. We use Customer Job in QB for Line Item detail from Civi (General Fund, Building Fund, Scholarship). We do not have any donors entered as a Customer in QB.
We have our payment options in Civi to match our payment processor's deposit schedule. ACH, Visa/Mastercard, Discover. That way we can pull Line Item reports by Payment Type and date and they match our Processor Deposits. Then we record that deposit in QB with Class info (Program...) and Customer Job (We sum the Line Item Types from Civi and have one line on the Deposit entry for General Fund, Building Fund, etc).
FYI, we use the same Class and Customer Job options when recording Expenses in QBs. This allows us to run reports by Class to see Functional Expense (for expenses we do have QB Classes for Management & Fundraising). And reports by Customer Job to see Campaigns-showing income and expense for Capital Campaign.
I think I have a method that solves 90% of what you're trying to accomplish. Firstly though, I think the reason that some nonprofits want to have the full information in quickbooks is (1) so they can pull P&L reports on specific programs, which can only be done with classed contributions (2) many organizations want to have the full data in quickbooks, especially if they already have years of files in it.
This method does not use the "accounting batch" feature in civi, as this feature only brings transactions into the journal entry, not into sales receipts. I would recommend that the core team expand the options for "accounting batches to include more fields (like the ones later on in this answer)
Here's what you do:
There is a $200 software called "transaction pro importer" which can convert an excel sheet into sales receipts. (transactionpro.com)
If you create a customized bookkeeping report in civireports and export as spreadsheet (which needs to be saved as regular sheet, not csv), and use very specific fields, you can sort transactions by batch and export all the transactions and fields that you want to pull into quickbooks.
disclaimer: im still struggling to work out how processing fees show up on reports (i use stripe) because they are currently goofing up my report by including two (or more) line items for every transaction.
The mapping will look something like this: Civi>Quickbooks
- External ID>>>Customer
- Line item financial type>>>item
- campaign>>>class (please note you can't use subclasses)
- payment instrument>>>payment type
- date received>>>date
- contribution id>>>receipt #
- check #>>>check #
for those who want their quickbooks to have all the detailed transaction information, i think this is the way to go, even though it could use some improvement.