CiviCRM Community Forums (archive)

I spent some time this weekend with the BDO accountant of one of the Canadian non-profit organizations I work with. I gave him a 4.3.5-local upgrade of their current CiviCRM database [from 3.4.7 - upgrade went really - really - smooth]. Bottom line: on 4.3.5: accountant is very excited [could immediately see ways to start discontinuing deposit slips and to reduce book keeping hours] - and we got lots of things to work on the Income side - but we couldn't get the Receivable side of things to work. Wondering if anyone else had any success with that - and/or if that's even possible with 4.3.5 at the moment.

More details:

We can successfully export from CiviCRM -> import into their Quickbooks (Canada version):
- Accounts: eg: DONATIONS 4000
- Sub accounts (just need to be aware to drill down the description): eg: DONATIONS:General Donations 4100
Basically - things were working well on the INCOME side of things
- Anything from the Chart of Accounts we can get CiviCRM data into

But what we could not get to work is the scenario where a payment received in CiviCRM is then applied as a Receivable in Quickbooks (for an already existing CUST (customer) in QuickBooks). Accounts Receivables is just one account number - w/ (in their case 172 member organizations as) sub-ledgers. So the account Number is 1700 - that's it. There are no sub account (numbers). QuickBooks identifies someone as a CUST (customer) by Name and Reference Number; Account number is 1700 for all of them.

A QuickBooks Export of the sub-ledger for Accounts Receivable looks like:

..
!CUST NAME REFNUM TIMESTAMP BADDR1 BADDR2 BADDR3 BADDR4 BADDR5 SADDR1 SADDR2 SADDR3 SADDR4 SADDR5 PHONE1 PHONE2 FAXNUM EMAIL NOTE CONT1 CONT2 CTYPE TERMS TAXABLE SALESTAXCODE
CUST Michel Audy 672 1376765224 N E Michel Audy
..

The CiviCRM Export (for a contribution where CiviCRM Contact Michel Audy tries to make a $211 payment) looks like (attached: IIF file (renamed to CSV so that I can upload it here) - all of it - below the Transactions it's trying to import)

!ENDTRNS
TRNS GENERAL JOURNAL 2013/08/18 Deposit Bank Account La Leche League of Canada 211.00
SPL 36079 GENERAL JOURNAL 2013/08/18 Michel Audy -211.00 Contribution Amount
ENDTRNS

So the IIF export from CiviCRM does include CUST (for customer): it's the correct First Name and Last Name (it's identical to the name for the sub-ledger). Account Receivables is correct - 1700 in our case. But QuickBooks can't import it.

Are we missing something or is this not possible yet?

-- Karin

Hi Joe!

Thank you for this. I'll relay this back to the Accountant. My thoughts:

1. The example shown above wasn't a good use case example - sorry. They are not trying to credit a payment to an individual. Let me try explain this better:

LLLC has 172 Groups [sub-Organizations] that are affiliated with the National Office. Each sub-Organization pays an annual Group Affiliation Fee (Accounts receivable: $80 for each of these 172 sub-ledgers eg LLLC - Calgary). If a Member purchases a Membership and selects LLLC-Calgary as their affiliate then $7 of that Membership is credited to the sub-Organization. So the $7 becomes a credit on the Accounts Receivable for LLLC-Calgary. They now only owe $73. Etc. So when a Member pays $35 online via CiviCRM - I add two additional post_create_contribution: a $7 and a -$7 to reflect the monies/rebate workflow. The $7 adds up being assigned to the sub-Organization that is selected by the Member. Right now it's exported and manually recorded in QuickBooks. This is what we were looking to automate.

2. Not sure if this is a very specific use case or whether other organizations may use similar structure/accounting to keep track of affiliate payments?

3. I must note things were working very well on the INCome side! Don't mean to sound negative.

4. The names of the sub-Organizations are very rigid - I'm not worried about difficulty in matching that between CiviCRM and Quickbooks. I can see your point how this would be a nightmare for First Name, Last Name. It's difficult enough to control user input like that as is (even w/o QuickBooks).

5. Since the functionality isn't there [and understandably so at this point] to add to Accounts Receivable - should CiviCRM have stopped us? The IIF as we tried it didn't push into our Account Receivables 1700 either (1200 before we edited it).

-- Karin