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

[brush <..hidden..>]

dear chris,

On Tue, Oct 30, 2007 at 10:41:12AM -0700, Chris Travers wrote:
> I would subscribe to the devel list, search the archives for the CoA
> proposal, and offer your thoughts on our direction.  Our current
> approach is a flat CoA attached to hierarchical headers.  Mostly we
> are looking at data representation at the moment.  The interface is
> likely to be ostly PL/PGSQL, and a little Perl.
> 
> This means that accounts aren't really "nested" in the proper sense,
> but rather subtotals can be.
> 
> I.e. if you have:
> inventory
> ---->  cereal grains
>         ----> oats
>         ----> barley
>         ----> wheat
> 
> you can attach a transaction to oats, wheat, or barley, but not to
> inventory or cereal grains.  Hence the hierarchical structure is
> purely a reporting convenience and the CoA is still flat for
> transactional purposes.

understood.  i looked at the data structure you're working with, and my
immediate question is what you gain by breaking the headings out into a
separate table?  ie. couldn't you just extend the existing "header"
account flag to be deeper (ie. not just boolean but an integer), and
then do the same manipulations to get the reports to summarize and hide
subaccounts as appropriate?  is it just easier to work with?

i'm sure there's a good reason, just trying to clarify my understanding!

> > 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.
> 
> What abut projects?  Projects are per-line-item.

hmmm.  to explain, perhaps it would be useful to restate my original use
case, which is extremely common (at least for NPOs):

" 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) "            

so: a program or department is an organizationally distinct entity, with
its own budget, that lasts year after year.  its budget must be disjoint
with the other department's budgets, and they must add up to the whole
budget of the organization.  for 501c3's in the US, at least, it's
especially important to distinguish between program, admin, and
fundraising departments, and to apportion expenses among them
accurately.

projects are something different, both substantively and in accounting.
they are generally shorter, may or may not be fully budgeted, but
most importantly, they can span multiple departments, which means that
even if you were willing to use the same field for both, you can't.

additionally, 501c3's (and others, i'm sure) have to keep track of
restricted funds.  again, such funds may be spent in multiple
departments, over multiple projects.  and again, a single payment may be
apportioned over multiple restricted or unrestricted funds.

so, it is impossible to use the existing available fields (dept and
project) for all these needs.

now, as i mentioned, one approach would be the multiple field CoA, but
i'm currently leaning against that.

the most scalable approach, to me, seems to be to have user definable
fields that can be attached to transactions line-by-line.  user defines
whether there's one, two, three, etc. of these available, and what the
name of the field is.  functionally, this would be equivalent to
allowing the user to add additional "project"-like categories to the
data structure.

one way to do this (top of head) would be to add a new table,
"categories", which would link an id with a category name (ie. program,
project, fund, in our case).  then add a category_id field to the
current projects table.  and in the UI add as many fields as there are
categories, and record the category_id along with the existing 
project_id (or whatever it's actually called).


> > 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.
> 
> You would need to add a department_id field to acc_trans I believe.

yeah, that's what i feared.  does this have negative consequences, other
than it's annoying to add bulk to your core table?

> > > 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?
> 
> We are working on a RESTful web service API.  Similar to SOAP but uses
> basic HTTP as the RPC framework, and the XML document is entirely
> self-contained as a data resource.  It can be accessed from any
> language bu basically getting, putting, or posting XML documents.

cool!  when you say "we", does that include the civicrm folks?

> > 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.
> >
> Although I am generally the first person to object to pushing XML into
> everything (I personally think that XML as a format sucks for
> everything except serializing objects so that they can be transformed
> by another program into different objects), this seems to be a perfect
> case where XML./XSLT would be a lot easier than CSV-based
> manipulations.

yes, that's true, but ifyou're still getting CSV's from some source, and
importing CSV's into lsmb, then in between is still just a script; and
if you eliminate the import-by-CSV piece, then you risk losing less
technical folks that arent's getting their data from a source that's
been coded for.

thanks so much, chris!
.b