Re: ledger-npo, configurable categories, and integration with civicrm & paypal

[brush <..hidden..>]

dear chris,

thanks for the response!  i've joined the -devel list, and am
cross-posting because i'm not sure which is best; guidance appreciated
re: netiquette here!

On Thu, Oct 11, 2007 at 02:04:45PM -0700, Chris Travers wrote:
> > sql-ledger was the only OSS accounting package available to us at the
> > time; we've been running a mildly (and badly) hacked version 2.4.11 since
> > then.  CRM and donor relationship management has also been important to
> > us, and we've been growing with the CiviCRM project on top of drupal.
>
> Understood.  There has been some talk about CiviCRM integration.  Most
> of this will probably need to wait for LedgerSMB 1.3 however.

ok.  but i know that development of both lsmb 1.3 and civicrm 2.0 are at
quite advanced stages.  can i help integration come early in the
release cycles in any way, probably not involving programming (i'm a
mediocre php programmer and pretty newbie at perl)?

> > most organizations use a nested hierarchy within the chart of accounts
> > to achieve adequate reporting and distinction between different kinds of
> > transactions.  so, for example:
> >
> > account 8110-022-123-ED07 might refer:
> >
> >   1) to the type of entry (8110 - supplies)
> >   2) to the program or department from whose budget it came (02x =
> > education; 022 = sustainability workshop series)
> >   3) to the grant or restricted asset to bill (123 - the 2007 whole
> > earth foundation "climate conditions" grant)
> >   4) to a budgeted project which may span multiple programs/grants (ED07
> > - the Earth Day 2007 set of outreach, fundraising, educational, and
> > demonstration activities)
> 
> Hierarchical accounts are somewhat of a problem on the current
> codebase.  The best time and place to address this is as we start to
> move towards 1.4.  If you are reasonably technical, please join us on
> the -devel list and help us work out how to make this happen.

i'd be happy to do so!  let me know what you'd like beyond this email...

> I am guessing that a nested COA hierarchy system would make life
> easier for a lot of people.  I have a few ideas.

indeed!  i'd be psyched to act as a sounding board.  fire away...

> > moreover, there is a major difficulty in using the "department"
> > field: each transactions, involving multiple entries, can only be
> > attached to a single department.  however, frequently we have expenses
> > which are split over multiple departments (ie. telephone, split evenly
> > between fundraising, programs, and management).  it is bulky and
> > confusing to create multiple transactions for a single bill.  ideally,
> > the "department" (and other, hierarchically reportable fields) would be
> > able to be set per entry, like the project is.
> 
> One of our major areas of effort is in separating workflow from
> accounting logic.  When you enter a telephone bill, if you need to
> track by department, this is going to mean a separate line item for
> each.  However, as we move forward, we want to make this a lot easier
> to automate.

understood.  and, indeed, automation will be very helpful!  but the
immediate concern i'm describing above is that, at least in the
sql-ledger we're using, we can't even get multiple departments on a
single transaction screen (with multiple line entries).  is that doable
in lsmb currently?  if not, a very desirable interim solution (1.3?) 
would solve the following use case.

telephone bill:

want to credit cash account $120 for whole check amount, but make three
separate telephone expense debits of $40 each applied to a separate 
department (edu program, farm program, admin).

the easiest way i can imagine this is if each AP expense line (or GL
entry line) included drop-downs for BOTH project (as now) and dep't.

on the UI this isn't too hard, but i don't know the db structure well
enough to know how big a change may be required.

thoughts?

> > B: for a variety of reasons, i think it makes sense for CRM modules to
> > be independent of accounting.  however, they will ideally coordinate
> > information with a minimum of re-entered data or data mismatch.  CiviCRM
> > is probably always going to reside on a different server than ledgersmb,
> > but we'd like transactions (probably entered first in CiviCRM) to be
> > smoothly uploaded into ledgerSMB.  any work on this?  thoughts?  links
> > to other examples of automated batch processing?
> 
> CiviCRM is also written in PHP which means we can't call eachother's
> functions directly.  There are other ways of doing this, and there has
> been some discussion on the CiviCRM lists on such integration.

SOAP is certainly a possibility, right?  i guess a core question is
which app would be client and which the server?

> Unfortunately the greatest obstacle at the momnt is that the relevant
> portions of both programs changing drastically between the current
> released branch and the next one.

hmmm.  do you mean (for lsmb) 1.3 or 1.4?  it seems that a civicrm 2.0
and lsmb 1.3 combo would be useful in the pretty near term.

> > [UPDATE] i just noticed that some folk at least on the CiviCRM side are
> > investigating the possibilities: see http://civicrm.org/node/239 .  any
> > thoughts on how we can support this happening?
> 
> Bradley Kuhn of the SFLC seems to be pushing this.  I am on the
> CiviCRM list in order to help provide some high-level guidance on
> making this happen easily.

thanks so much!  could massively amp up use of lsmb among npo's...

> > C: similarly, we'd like to be able to sync our PayPal transactions with
> > LedgerSMB (for now, just on a monthly basis).  we'd need some
> > dupe-checking probably on transaction code, since CiviCRM entries may
> > already include paypal payments.  any work on this?
> >
> 4)  For areas which are pending re-design, you can and should get
> involved in all discussions you feel comfortable with on both the
> -users (for usability/functionality/workflow issues) and -devel (for
> technical and implementation discussions).

ok.  in thinking this over further, i think that for a wide range of
applications (both civicrm and paypal, and any other source of
accounting data legacy or otherwise) the most flexible interim approach
would be an import infrastructure for lsmb.

the first step, it seems to me, would be a well-commented import script.
the following seem like useful aspects:

1) first, of course, a basic GL transaction import including dep't and
project.

2) second, AP and AR-specific transaction import with relevant fields.

3) third, dupe-checking functionality, probably on date and amount,
and/or transaction code (in notes?), with override option.

then, scripts to convert specific output CSV's from various sources (ie.
paypal/civicrm/etc.) to canonical import CSV format.

thoughts?

thanks so much,
.b