QuickBooks integration: Accounts Receivable side

[KarinG]

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:

Code: [Select]

..
!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)

Code: [Select]

!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

[JoeMurray]

The 4.3 implementation of CiviAccounts was only trying to support Chart of Accounts accounts, not projects/jobs or donors/customer oriented payments.

IIF does not allow, as you may know, a payment to be linked to an invoice or other transaction (http://support.intuit.ca/quickbooks/en-ca/iq/Import-and-Export/Importing-or-exporting-data-with-IIF-files/HOW12778.html).

There may be a work-around / enhancement to support this. Looking at the attached IIF Reference.rft  (renamed to .txt to allow upload here) examples, the TRNS line of the .IIF files could optionally have the civicrm_contact.display_name put into the NAME field of the TRNS line of the transaction via a customization or extension.

Amongst the issues here are that .IIF imports are quite touchy in various ways. If there is not an exact match, then additional entries are created. For example, if the name in CiviCRM (possibly as updated by user on most recent visit) does not _exactly_ match the customer's entry in QuickBooks, a new customer will be created. Once imported, it can be difficult to find and eliminate imported data in order to redo the import with a corrected file. Re-importing a file with a single changed transaction will create duplicates of all of the other transactions.

Thoughts?

[KarinG]

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

[KarinG]

5. Since the functionality isn't there to add to Accounts Receivable - should CiviCRM have stopped us?

Right now - with 4.3.5 - if one attempts to credit a payment to an Account Receivable - even though the Payor’s name was EXACTLY the same as a Customer name in QuickBooks. When we tried to import the IIF file we received this QuickBooks message (attached).