Skip to main content

CiviCRM and Accounting in 4.3

The 4.3 version of CiviCRM that first came out in April 2013 addresses a key problem with CiviCRM for large organizations: namely, accounting integration.

So what exactly does that mean, and how does it work? Since I'm working on a big migration to CiviCRM, and the client has "accounting integration" needs, I've been diving in and trying to understand the nitty gritty. Since I started, 4.4 is now out, and 4.5 is almost out, and I understand they've made some improvements, so this is now a bit dated, but still might be helpful.

First off, "accounting integration" doesn't mean that CiviCRM will now replace your accounting package, but the goal is to make it play nicer. The key issue for my client is that reports coming out of their current system are used as input for their accounting system, so it needs to speak the same language - i.e. things like GL account codes, and double entry accounting for things like contributions where the donor is promising to send a cheque. I like to describe it as: making CiviCRM a trusted entity within the accounting system, instead of it's current status where reports generally involve some wishful thinking, caveats, and a checklist of adjustments.

Initially I've just been going along assuming everything will take care of itself under the hood as I use Eileen's brilliant civimigrate module in combination with the Drupal migration module. But after a few failed attempts at getting what I want, I've been forced to try and understand what's really happening, and so that's what I'm sharing here.

The most useful documentation I've found so far is here:

http://wiki.civicrm.org/confluence/display/CRM/CiviAccounts+Specifications+-+Financial+Items

The aha moment came after looking at the new civicrm_financial_* tables and reading that page.

My key insight was:

In the past, a row in the contribution table was playing too many roles: it was the transaction record as well as the accounting record. In this new, more sophisticated world of accounting, you still get one row in the contribution table per "contribution", but that really serves as a simplification of what are two collections of things: the collection of financial transactions that go into paying for it, and the collection of items that describe how that contribution is accounted for - where it went. In the simplest case, you get one of each (e.g. a check for a simple donation). But at it's most complicated, you might have a series of partial payments for an event ticket, some of which is receiptable as a tax donation and some of which goes towards the cost of the event. Yes, that's why they call it 'double entry'.

Implementing a system like this over top of an existing system is a bit tricky, and the developers seem to have been a little bit worried about the complexity implication for users that didn't want to know about this. So you have to dig a bit to see what's going on, and I'm probably missing some details. Patches welcome ...

One way of describing what we're doing is 'splitting' the contribution. The contribution becomes an container for a collection of inputs (financial transactions) and collection of outputs (attribution of the contribution to accounting codes). The original contribution table still contains a single entry per contribution, but the possibly multiple transactions that pay for it, and the possibly multiple attributions of that income, need to live in related tables.

One trick the developers used was to create something they call a 'financial type'. The point of this entity is to allow administrators to delegate the accounting to a set of rules for each 'financial type' - meaning, the way a contribution gets allocated to the account codes is determined by the nature of the transaction (i.e. income, expense, cost of sales, etc.), which is then looked up for the 'financial type', and that determines the accounting code. Fortunately, this is just a mechanism for calculating the actual accounting - the data gets stored fairly directly.

Now let's check out the new civicrm_financial_* tables.

civicrm_financial_item - this looks like the accounting for each entry in the contributions table. It includes entity_table and entity_id fields that link it to more information, e.g. an entry in civicrm_line_item. It doesn't provide you the accounting directly, but it gives you the financial_account_id, and you can look up the accounting code directly from the civicrm_financial_account table.

civicrm_financial_trxn - these are the financial transactions that make up the contributions. You'll see it has things like the from and to account fields (to allow for both external and internal transactions, like when a check is received and the transaction is transferred from accounts payable to your bank account), as well as transaction id's, currency, amounts, dates, payment instruments, etc., i.e. everything you need to follow the money.

civicrm_entity_financial_trxn - this is the table that joins the above two tables to the contributions table. A simple typical contribution will have two entries, one pointing to the financial item, and the other to the financial transaction.

Okay, now let's dig a little deeper:

In the financial_item table, which holds the accounting, it also has a reference to a 'more information' record, with entity_table and entity_id fields. In my install, it's pointing at the civicrm_line_item table most of the time, except for old imported from 4.2 entries that point at the civicrm_financial_trxn table.

civicrm_line_item - I'm not sure why you'd need a reference to this, but I guess it does help track back to how the financial_item got created. Specifically, it has a 'financial type id' field, which in combination with the transaction, could be used to calculate the financial account id that ends up in the financial item.

civicrm_financial_trnx - I'm guessing that the only time a financial_item references this table is when there's a direct correspondence between the transaction and the accounting. For an install that was migrated from 4.2, for example, that's the case of all the old transactions that assumed this and for which there is no intervening line item to split it up. Maybe "backend" administrative entries and adjustments end up here as well?

And now back to the other financial tables:

civicrm_financial_type - a list of these 'financial type' abstractions. There's no accounting codes in here, you have to find the connection to the  account id using something like:

select * from civicrm_entity_financial_account where entity_table = 'civicrm_financial_type';

civicrm_financial_account - the list of the accounting codes for each 'account', i.e. what you want to get from your bookkeeper when you set things up.

Conclusion: it's pretty complicated, and obviously you don't want to be manually mucking with these tables. In theory, the structure allows for some nice splitting up of income into different accounting categories, but at this stage, the interface is trying to hide most of the details to keep things simple for users.

Popular posts from this blog

What to do in the age of Trump?

Well, that's the question of the day. If you're part of an organization that does advocacy work, rather than waiting to see what happens first, might as well get yourself ready, even if the details are sketchy still. Here's one opportunity that's ready for you now, message courtesy of Steve Anderson of OpenMedia.

OpenMedia, David Suzuki Foundation, SumOfUs and a range of other organizations are supporting a new shared set of civic engagement tools.

Vancity Community Foundation is providing some support to subsidize some of the cost of the tools to select values-aligned organizations that sign up before February 28th.

Interested? You can learn more or book a demo from here: http://tools.newmode.net/

Here's some live examples of the tools you can take a look at:

1. Click to Call: http://www.davidsuzuki.org/blogs/healthy-oceans-blog/2016/11/to-help-protect-canadas-oceans-weve-made-it-easy-to-call-your-mp/#newmode-embed-4-266

Check out this video of David Suzuki's d…

Me and varnish win against a DDOS attack.

This past month one of my servers experienced her first DDOS - a distributed denial of service attack. A denial of service attack (or DOS) just means an attempt to shut down an internet-based service by overwhelming it with requests. A simple DOS attack is usually relatively easy to deal with using the standard linux firewall called iptables.  The way iptables works is by filtering the traffic based on the incoming request source (i.e., the IP of the attacking machine). The attacking machine's IP can be added into your custom ip tables 'blacklist' to block all traffic from it, and it's quite scalable so the only thing that can be overwhelmed is your actual internet connection, which is hard to do.

The reason a distributed DOS is harder is because the attack is distributed from multiple machines. I first noticed an increase in my traffic about a day after it had started - it wasn't slowing down my machine, but it did show up as a spike in traffic. I quickly saw that…

CiviCRM's invoice_id field and why you should love the hash

I've been banging my head against a distracted cabal of developers who seem to think that a particular CiviCRM core design, which I'm invested in via my contributed code, is bad, and that it's okay to break it.

This post is my attempt to explain why it was a good idea in the first place.

The design in question is the use of a hash function to populate a field called 'invoice_id' in CiviCRM's contribution table. The complaint was that this string is illegible to humans, and not necessary. So a few years ago some code was added to core, that ignores the current value of invoice_id and will overwrite it, when a human-readable invoice is generated.

The complaint about human-readability of course is valid, and the label on the field is misleading, but the solution is terrible for several reasons I've already written about.

In this post, I'd like to explain why the use of the hash value in the invoice_id field is actually a brilliant idea and should be embrac…