Ideas

Grant tracking with CiviCRM and custom fields

As part of an ongoing project with Environmental Grantmakers Assocation (EGA), which involves moving them off of a series of disparate databases to Drupal/CiviCRM, we developed a solution that allows them to track grants from a 3rd party perspective.

One of the many valuable services that EGA provides is "Tracking the Field." TTF tracks tens of thousands of EGA members' grants, by geography, program area, interests, dollar amount, recipient, etc. It's a "who gave what to whom, and when" database that provides powerful search functions and reporting. It provides a picture of the giving landscape and view trends in giving history.

Early in the project, we prototyped using multi-value custom fields, in this case, associated with contacts. This worked fine except editing multi-value fields can be unwieldy when there are 20-30 fields and 20-30 multi-value entries. In addition, the "count" doesn't show up correctly when looking at the grantee, since the custom field values are associated with the single contact, the grantor.

We explored CiviGrant as well as CiviCase, but TTF doesn't need to support grant status or workflows so both approaches seemed like overkill, despite the chance of being able to leverage existing CiviGrant reports. Using CiviCRM relationships was another option, but there's a limitation on multiple same relationships (i.e. multiple "Grantor to/Grantee of").

We went back to our original prototype and extended it by writing a module and some PHP and template overrides to provide the functionality we needed, namely:

• Showing the correct count in the Grants tab for grantors and grantees
• Per row view/edit/delete for a multi-value custom field group.
• A few little touches, like switching labels "grantor" or "grantee" depending on perspective.

Using custom fields seemed like the best choice, especially since we didn't anticipate having to alter how the data is stored, EGA can add or alter custom fields on their own, and custom fields are well integrated into other aspects of CiviCRM including Drupal Views. Even without our modifications, their data is intact and usable despite drastically reduced functionality and usability.

All of the TTF code is in our repository. Improvements, custom reports and documentation will continue to be checked in over the coming weeks. The code is a bit messy still and is not a drop-in replacement for multi-value custom fields without changing some of the hard-coded values in the PHP classes. Classes and templates that implement this UI for any multi-value custom field group is an exercise left up to the reader. :P

svn export https://svn.dharmatech.org/svn/custom/ttf

Going back to our requirements listed above, we have a few screenshots to demonstrate how TTF works with our code. If you're playing along at home, trying creating a multi-value custom field group called Grants, that has a few custom fields like date, amount and contact reference to compare the UI differences.

The contact summary screen of a grantor.

The Grants tab counter is implemented in the module and is accurate whether we're viewing a grantee or grantor. Custom values are associated with a single contact (entity_id) but using the grantee custom field, we can also query for the number of grants received by an organization.

View/edit/delete is implemented in the template and a few PHP classes. We were able to look at how relationships, contributions, and activities are displayed in contact summary and use that as a starting point.

Viewing the details of the grant, including any and all custom fields.

CiviCRM provides a great platform for managing contacts, memberships, contributions, events and relationships. However, it doesn't (and shouldn't!) meet the needs of everyone or every condition. Where CiviCRM falls short, it makes up for by allowing developers to extend the functionality by way of hooks, APIs, template/class overrides and extensions.

Refer to the developer documentation for the specifics. If you get stuck or need to bounce ideas around, many CiviCRM developers hang out in IRC #civicrm.