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

["Chris Travers" <..hidden..>]

On 10/31/07, brush <..hidden..> wrote:

> 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?

Good point.  There is a two-way tradeoff (easier checking for the
account_uses table) but since this is seldom updated, I think we
should be OK with an additional performance cost there.

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

My original reasoning had to do with referential integrity enforcement
between accounts and account uses.  Obviously headings are merely
reporting entities and cannot have uses.  However, merging these
tables (and possibly using a custom trigger to do the RI checking)
seems to be the most elegant solution.

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

Maybe we should make projects something which can be nested as well.
That would make a lot of other things easier as well.

>
> 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.

Ok, I can;t think of a way to deal with this outside of extending line
items to be multi-department.  Certainly we need to add that to the
feature request tracker so we don't lose sight of the need to do this.
I am also thinking that nested departments might not be a bad thing
either.

>
> 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.

Payment is less of an issue than accrued expenses, correct?  The issue
is that a single utility bill has to be apportioned.  My suggestion
would be that we should look at a specific design for handling utility
apportionment as automatically as possible.

If you can provide more information about how you decide how much to
apportion to different departments, that would be helpful.

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

Ok, I understand now.
>
> 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.

There is general support in the software for this.  It requires a
little bit of patching to do it per line item, but it could be done.
However, I think we need to differentiate between aspects of this
problem which are specific to NPO's vs aspects of the problem which
are generally applicable.  I suspect that being able to apportion
utilities between departments automatically would be something very
helpful to a larger set of businesses.

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

How about a company-wide setting which determines whether department
values are per-line-item?
>
>
> > > 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?

The major issue is that in 1.4, the table is going away, so your
changes are likely to be somewhat non-portable.

>
> > > > 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?

No, I was referring to the core team.  It will not be in 1.3, however
(though we very much would have liked it to be).


>
> 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.

Well, there are two approaches which could work here.

1)  You could do a basic COPY FROM STDIN in PostgreSQL (probably best
to do a temporary table).  Then create a INSERT INTO ... SELECT ...
query to populate the tables.

2)  Create a utility which creates an XML document out the CSV and
posts it to the web service.  The web service could then also be a
neutral form between EDI, CSV, and the like.

Long-run, I think the second is likely to be the most maintainable, as
our data schema is likely to change.

Best Wishes,
Chris Travers