Pubblicato
2009-01-29 12:43
I'm hoping by posting this I can get a feel for how useful a Sage plugin and associated module for CiviCRM would be. We have a client that needs to have transactions from the various portions of CiviCRM that tend money to be recorded in their accounting package. I personally think it would be a great addition and being a client requirement, it has been factored into the costs.
There are several considerations as to how to approach this, I have thought of a good few ways of achieving the goal but I’d like if I may to ask for a little help fleshing out the details.
The main things I’ve been thinking of are:
- Method
- Synchronisation
- Security
- Direction
- Method
- I think that to get the structure of the data across neatly, XML may be the best option. With a reasonably well thought out structure all financials and arbitrary custom data can be successfully translated to something sage can work with.
- Synchronisation
- My main issue here is numbering the transactions. I think my best option here is to have a numbering system purely for CiviCRM transactions and reference them within Sage as opposed to trying to use Sage’s invoice numbers. There is so much scope for duplication of the numbers and getting out of sync that the best option is to keep them separate. Also in synchronisation, we need to decide how data is passed around. The most logical solution here is for Sage to fetch the data. With Sage being a client-server app that means that a client will need to have a bespoke plugin installed. Once data has been retrieved, it needs to tell CiviCRM that it no longer needs the data that was prepared for it. Once again there are many ways of making sure that Sage only gets told about new transactions but which one is best? If CiviCRM provides an XML file i would mean that several Sage clients could have the plugin but if a Sage client were to ask for all transactions after a certain number we risk duplication of data.
- Security
- Obviously a concern when dealing with financial data. Although I see no need to pass card details around, the privacy of a user’s data is of great importance. If we are generating an xml file then it can easily be encrypted and hidden behind a secure area of a site for fetching by Sage.
- Direction
- That is, one-way or two-way data transfer. Do we really need to take data from Sage and add it to the CiviCRM system. I say no but I have a hunch that the client is saying yes. I can see a way of doing it but right now, I think I need to focus on getting the data out of CiviCRM and into Sage.
Filed under
Comments
Hi,
We have been working on a REST interface that can produce XML or JSON. It allows an external program to query (with the proper credential) the civicrm server and extract.
You can either fetch the various transactions (events, donation, membership...) separately and do the filtering grouping on the sarge bit, or develop your own php method to be called as a single call.
There is an api forum, with more details on it, and the wiki is mostly complete on that topic.
X+
Looks like an interesting idea. If you want to make it usefull for many, go for a simple and general interface.
Data exchange structure: Xml sounds reasonable.
Use, as far as possible, database field names as tags or attributes in xml . Conversion to the need of sage should be done outside of civicrm. If not possible just add a mapping function that can be modified for each user. In the same line make it one-way only (civicrm-sage).
As API provide a function that serves a html page with the requested data. Use POST parameters to specify the data displayed, use ACL to provide access restriction.
Security should be handled using https. Use standard authentication of civicrm.$
Happy coding!
1. Method: Use REST and XML
2. Direction: Since Sage is an accounting system, one way is more appropriate for now.
3. Security: https and api keys. Since all Sage requests come from one machine, you can add code to lock down by IP address. However you are transferrng credit card information (since we dont keep it). More like name/date/amount etc
4. I dont know the Sage API. However the code should be strong enough to handle the same data being returned. Contributions have a few unique identifiers that can/should be used (id, trxn_id, invoice_id). These can be used in conjunction with the contact_id
lobo
Use CiviCRM? Like CiviCRM? Then show your appreciation and donate to CiviCRM
As I mistakenly posted this comment to a different article... here's where it should be. I agree working on a Sage connection with CiviCRM is a good idea. We're currently planning to build a connection of some kind with Sage CRM and Sage Accpac software with CiviCRM and Drupal. Perhaps if many of us are working on this, we can compare notes in the future.
Over the next couple of days I'll be fleshing out my strategy and will probably report back here.
Hi Chris,
Did you make any progress on this? I'd really like to discuss your experiences as we are heading down this route ourselves currently.
I am also wondering how this went.
I just had a client sign up for Sage Pay for their virtual terminal. Now they want to connect CiviCRM to it, but I haven't found anything on it yet.