Scintillating thoughts about accounts - Part two

This is my second blog on the topic of integrating with CiviCRM with an accounting system. Those of you who haven't just run screaming from the room or suddenly discovered an urgent need to polish the inside of your car exhaust, re-organise your tupperware or push needles into your eyes ... read on. I was going to talk now about the second (conceptual) stage of Xero / CiviCRM integration - getting payment information out of Xero to confirm transactions in CiviCRM, but, some of the scenarios that are difficult to get my head around in automated integration are also the same scenarios that cause me problems under 'manual' integraion, so I'm going to focus for this blog on 'manual' integration between CiviCRM and Accounting systems. This also ties in with a suggestion from Dave that I specify possible future changes to the workflows around receipting multiple payments against events. Manual integration is an over-jargony way of describing a person sitting in front of a computer trying to make sure all the relevant payments showing up in their bank account and accounts system are correctly entered into CiviCRM. Basically the integrator is reading the details of a payment from a bank download and then finding the relevant contribution record and confirming it. In this blog I'm going to focus on event registrations. Mostly entering payments is tedious but straight-forward. The problem arises when the payments actually received into the bank account don't exactly match the pending contribution records? For example, what happens if a person signs up for a class based on an early payment price but doesn't pay in time and is honest enough to pay the full price? Here's a list of the main problem scenarios I encounter: 1) The payment is more than the participant record and no refund will be made 2) The payment is less than the participant record and is to be treated as the full payment 3) The payment is for multiple different records - possibly across several participants. 4) Payment is made in installments 5) The payment is more than the participant record and the difference will be refunded 6) A partial refund is to be made. 7) A partial refund is to be credited towards another event (for which they will make a partial payment to make up the difference - arrgh) 8) A contribution is made by credit card for an event but the person wishes to change event. If the event registration is cancelled in CiviCRM then so is the credit card payment even though it really wasn't. It just needed to be re-assigned to the new event. (This and scenario 6 are the two that I usually fix using SQL rather than the CiviCRM interface) Just looking at this list makes me feel tired! It also raises the question of whether CiviCRM payment information really does have to be accurate. The primary reason I go to such efforts to get my CiviCRM information to match my accounts is to make sure that payments are accurately matched and people who haven't paid are followed up. This means much coding, reporting, comparing, recoding, adjusting CiviCRM, re-reporting, re-comparing etc until the two absolutely match and we can say with confidence who needs to be followed up. If we didn't do this rigourously our revenue would fall by about 20%. I also do it so we are clear which classes made a profit and which made a loss. Anyway, back onto how to deal with my problem scenarios. The 8 scenarios above come down to three main issues. 1) Changes to the total amount to be paid, 2) payment scenarios which involve multiple payments (including negative payments) and 3) Changing which participation record ('invoice') a payment relates to. Issue 1 In an accounting application the usual way to deal with a change in the total amount to be paid is to issue a credit note and then re-invoice the event for the correct amount. If you take that approach then issue 1 is actually the same as issue 3. If the price changed you would cancel the existing registration and create a new one with the correct price. You would then go into the contribution and RE-ASSIGN that payment to the new event. One enhancement that would make this scenario much easier in practice is giving a CiviCRM administrative user the ability to override the total cost of an event registration. (This could just be giving them an extra line item to fill in with a positive or negative number that isn't visible to a normal user). For example, the price options available might be $100, $200, and $300 but an agreement may have been made to only charge Uncle Bob $50 in return for him handing out drinks on the night. The administrator might then cancel his registration and create a new one with an adjustment entered to make the total correct. This sort of one-off quid-pro-quo doesn't fit well with systems but it's the lifeblood of the charitable sector. Without an override the price set would need to be temporarily edited and then edited back afterwards. Either way, issue 1 potentially becomes a non issue once issue 3 is resolved. The partial refund situation is still a bit grey though. Issue 2 Payment scenarios which involve multiple payments. The database structure already supports this but the interface doesn't. To support this through the interface there would need to be a change to the workflow whereby when a contribution can be added with a chance to choose which 'invoice' (in this case an event registration) it should be associated with. There would probably be an option to show 'closed invoices' (completed registrations) but by default only pending and cancelled would show. There would probably also be an option to change the status of the registration involved. A useful extension to this would be the ability to select an 'invoice' for a different contact. However, in terms of dealing with one payment for multiple event registrations I am imagining that in both the manual and automatic integration this payment would simply be entered as multiple payments. Another important aspect of this is that contributions could be negative (ie. refunds) as well as positive. Issue 3 This is very similar to issue two. It involves changing rather than adding and the key difference is that there may be a need from this edit contribution screen to change the status of both the 'from'registration and the 'to' registration. (This could just be done manually though). Perhaps it could be triggered by changing the event registration (e.g. cancelling an event causes you to have the option of refunding the payment or transferring it to another event. These topics more or less cover off the scenarios that I described above. As mentioned - my focus in this blog is on where I hit problems making my accounts match the reports in CiviCRM. Ideally I could just match the accounts in my accounting package and the details would be transferred to CiviCRM but that's another blog.... While I realise that most people find accounts about as exciting

Comments

While I can't say I was following every twist and turn of the post I'm glad someone is looking at accounts integration with CiviCRM, even at these early stages as my club treasurer was asking me about this only yesterday and I didn't really have an answer.

Read every word of both - and have nothing useful to say (at this point) other than awesome job trying to disentangle the bits to get to the core issues - and to let you know that some people are reading this, without any thing to add as a comment!

Thanks so much for publishing your thoughts about this. I have a vested interest in CiviCRM both with respect to current clients who use it and express some frustration, and with respect to education-based projects that I am considering integrating with CiviCRM.

I have some questions about the receipting multiple payments against events issue that you raised above, and specifically to understand if this is still a zygote in someones eye apples, or if there is development happening around this that I can piggy-back on somehow and/or test. Idealists have this unreasonable need to be able to pay for almost everything on layaway. ;)

I appreciate your writing here, and keeping a records of your thought process as measured against current programming.

With regards to the education project, I would like to talk with you about your experience working with Civi, and particularly with regards to supporting forward thinking projects that utilize and integrate their platforms. Particularly with respect to the receipting multiple payments against events issue, I am wondering how the suggestion to change the workflow of the core of Civi was embraced. I think I am understanding that there were some modifications that would be required of the core in order to enable the processing of orders against a broader set of criteria (like accepting multiple payments for events). Is that something Civi is moving forward with? What is the time frame? Has it already happened and I am just a duffe and can't figure it out?

Thanks again.

ana@jellobrain.com