tracking grant applications -- extend CiviGrant or custom data and activity?

[planigan]

Hi all,

I'm not sure where to post this, so I guess General Discussion is as good as any place!

The non-profit that I work with, like many, lives on grants. Right now our staff keeps track of grant applications using a speadsheet, which has become cumbersome. It would be great to use CiviCRM to track grant applications (deadlines, amounts, status etc), especially since a lot of the foundations and organizations that we receive grants from already exist in our contact database. This information could then be used to create reports for presenting to our board of directors (current the spreadsheet serves this purpose).

I've read up on the forums and elsewhere about this, but most of the posts that I found were pretty old. Here is what I have gathered so far, though:

1) CiviGrant was meant to track grants going in the opposite direction (from our organization to constituents). There was some talk of extending it to track grants in both directions, but it doesn't seem like anything materialized.
2) Another possibility is to use either a custom data tab or a custom activity to track grant applications associated with an organization (http://forum.civicrm.org/index.php/topic,1694.msg7338.html).

Would there be any interest in extending CiviGrant to track incoming grant? There isn't much documentation on CiviGrant, which gives me the impression that it doesn't receive a lot of attention as a component. I might be willing to take on this task if no one else actively maintains this component. I would like to become involved in contributing to the project, but this might be a rather daunting initial task. However, my PHP skills are pretty rusty and the CiviCRM codebase is honestly a little intimidating.

So, it might be more worthwhile to just follow option (2). Does anyone here have experience setting up a similar workflow? Right now I'm just trying to gauge the relative merits of both approaches. Any thoughts are much appreciated!

[Donald Lobo]

if you have the energy and the initiative, option 1 is definitely the way to go and will help push the project forward

there have been a few people who have requested it, but no one has stepped forward to do it.

we'd be happy to help get you started and going on IRC

lobo

[xavier]

Hi,

I never enabled civigrant, but for tracking complex projects like a grant with different contact involved and a lot of a activities, you might want to consider using civicase.

If you come up with a proper solution using case, would be great to include the config in the default config of civicrm like the housing and health, that's probably quite a common case.

X+

[planigan]

if you have the energy and the initiative, option 1 is definitely the way to go and will help push the project forward

there have been a few people who have requested it, but no one has stepped forward to do it.

we'd be happy to help get you started and going on IRC

lobo

Glad to hear that it sounds useful. I like option (1) as well.

Hi,

I never enabled civigrant, but for tracking complex projects like a grant with different contact involved and a lot of a activities, you might want to consider using civicase.

If you come up with a proper solution using case, would be great to include the config in the default config of civicrm like the housing and health, that's probably quite a common case.

X+

I never enabled CiviCase, but I just now gave it a quick look on your suggestion. It's really interesting in that it already follows the model that I was thinking of applying CiviGrant. Specifically, creating a new case takes a bare minimum of information regarding the case itself, and then interactions with the contact regarding the case are all recorded as activities.

My basic idea for CiviGrant would have been to make the grant entity generic with regard to incoming / outgoing grants and their static static characteristics. This would entail adding and removing some fields. Dynamic interactions with the contact associated with a grant would then be replaced by activities.

For example, I would remove the 'decision_date' field from the civicrm_grant table and replace it with a 'grant_decision' activity. This activity would be recorded when the final disposition of a grant is decided (approved / denied / tabled / etc). At this point the grant, like a case, would be considered closed.

However, there seem to be some important characteristics of grants are unique with regard to cases. For example, "amount requested" and "amount granted". You could maybe create an 'amount_requested' activity and record the amount in the activity details, but then it would be very difficult to analyze these amounts later. Running reports on the total amount requested / granted / denied would be messy.

So, I think that using CiviCase as a model to extend CiviGrant is the best way to go. I already have a rough outline of what the civicrm_grants table would look like and the corresponding activities. I'll put that in a followup post for feedback.

[planigan]

OK, here is a rough outline of how to extend CiviGrant.

New fields in civicrm_grant table:
1) name (might be useful to have a descriptive title for individual grants)
2) direction (incoming / outgoing)
3) grant_application_due (could be rolling deadline specified by NULL value)

Modified fields in civicrm_grant table:

• grant_due_date -> grant_report_due (just make the name more consistent with the semantics)

Removed fields in civicrm_grant table:

• decision_date -> replace by 'grant decision' activity
• applicaton_received_date -> replace by 'grant application received' activity
• money_transferred_date -> replace by 'grant money transferred' activity
• grant_report_received -> replace by 'grant report received' activity

For migrating existing grants from older versions, the removed fields can be automatically replaced by activities so that the information is not lost.

After these modifications, each of the fields in the civicrm_grant table will be applicable to a grant regardless of its direction. Moreover, they have a consistent semantics regardless of direction or disposition.

New activity types

• 'grant inquiry' -> an inquiry was made regarding the status / eligibility of the grant (both)
• 'grant application submitted' -> a grant application was submitted (outgoing only)
• 'grant application received' -> a grant application was received (incoming only)
• 'grant decision' -> a grant decision was made (both)
• 'grant money transferred' -> grant money was transferred (both)
• 'grant report received' -> grant report was received (incoming only)
• 'grant report submitted' -> grant report was submitted (outgoing only)

Notice that each of the activity types are applicable to incoming grants, outgoing grants, or both. My reasoning for not simply making these fields in the civicrm_grant table varies. For example, you could just store a date in a 'grant_inquiry' field. However, each grant might have multiple inquiries associated with it. These inquiries might take different forms -- email, phone, postal mail. In this light, a new activity type might not be needed, we could just use an existing type.

Also take 'grant application received[/submitted]'. You could have a field called grant_application_date, and its semantics would change based on the direction of the grant. This information really describes an interaction with a contact though, which I believe is precisely what an activity is meant to capture.

Activities (5) - (7) all describe interactions that depend on the final disposition of a grant (i.e., grants that are not approved will obviously never have a money transfer associated).

Obviously, these mappings would require new joining tables similar to CiviCase.

As far as source / assignee / target of an activity, I think the following makes sense.

• The source is always the person logging the activity.
• The target is always the external contact (grantor or grantee).
• The assignee is the internal contact (i.e., staff) responsible for the activity.

It would also be nice to have a mechanism for ranking grants within a category. For example, you could rate the strength of application for incoming grants, or the likelihood of success for outgoing grants. This would be useful for organizations that are soliciting many applications for a single pool of money or program. I would see this functionality taking advantage of the current tagging facility in CiviCRM. However, I would wait to work on adding this feature after the general work above has been completed.

OK, that's all for now. Please let me know if you have any feedback or questions. I will try to catch up in IRC soon to get started.

[Dave Greenberg]

Patrick,
First, I definitely think that extending CiviGrant for tracking incoming grants is useful and will be helpful for a significant group of CiviCRM users.

Your rough outline makes sense to me, AND it also has me wondering ...
* Since the structure is so similar to CiviCase, would it be better to explore using case a bit more first and look at the obstacles (e.g. reporting).
* Current users MAY not want to give up the simple "one screen" Add / Edit Grant interface. AND we have had feedback that the CiviCase user interface is too complex for some use cases. (http://civicrm.org/blogs/colemanw/pushing-envelope-civicase).

I think it would be helpful to expose this discussion at a higher level and get some inputs from both current / potential users, and from Lobo and other folks on core team and in the developer community. How about writing up and posting a blog post on CiviCRM.org - combining your posts below. (I've added blogging rights to your CiviCRM.org user account - planigan).
 

[xavier]

However, there seem to be some important characteristics of grants are unique with regard to cases. For example, "amount requested" and "amount granted". You could maybe create an 'amount_requested' activity and record the amount in the activity details, but then it would be very difficult to analyze these amounts later. Running reports on the total amount requested / granted / denied would be messy.

So, I think that using CiviCase as a model to extend CiviGrant is the best way to go. I already have a rough outline of what the civicrm_grants table would look like and the corresponding activities. I'll put that in a followup post for feedback.

I would think that extending Case with custom fields would cover that nicely.

Adding a new case type already involve creating activities type and relationship, wouldn't be too shocked to have to create some custom fields. Ideally, adding features to the API so you can create these custom fields would have a simple script to add all the stuff needed by you grant case.

Definitely some custom reporting, but not super complicated either, and extending the extension system to cover all that is probably not going to take you more time to clone all the features you like from case and add them to grant.

In general, I'm a big fan of one tool that is extensible and allows you to cover your need that a multitude of small ones that cover part only.

Dave's suggestion is good, I'll try to come up with good suggestions to extend Case and cover your need.

X+

[planigan]

First of all, great discussion so far. I'm glad to see that there is interest in this.

I have a paper deadline today so I can't spend a lot of time replying here, but I did want to briefly touch on a couple of things.

* Current users MAY not want to give up the simple "one screen" Add / Edit Grant interface. AND we have had feedback that the CiviCase user interface is too complex for some use cases. (http://civicrm.org/blogs/colemanw/pushing-envelope-civicase).

I DEFINITELY don't want to give up the simple one screen add / edit interface. After playing around with CiviCase some more last night, I can definitely see its complexity being an obstacle. My goal would be to keep the CiviGrant interface as simple as possible.

I would think that extending Case with custom fields would cover that nicely.

Adding a new case type already involve creating activities type and relationship, wouldn't be too shocked to have to create some custom fields. Ideally, adding features to the API so you can create these custom fields would have a simple script to add all the stuff needed by you grant case.

Definitely some custom reporting, but not super complicated either, and extending the extension system to cover all that is probably not going to take you more time to clone all the features you like from case and add them to grant.

In general, I'm a big fan of one tool that is extensible and allows you to cover your need that a multitude of small ones that cover part only.

Dave's suggestion is good, I'll try to come up with good suggestions to extend Case and cover your need.

X+

After playing around with CiviCase some more last night, I definitely see how it could be extended to cover this. I'm also more hesitant to do so. It is very complex and kinda seems like overkill for this use case. Also, then you end up with two components that perform the same function, but in different ways. That leads to a disconnect between grants tracked with the CiviCase workflow and grants tracked with the CiviGrant workflow. I think two-way grant tracking is a general enough function for most non-profits that it deserves it's own component.

It's not that I want to duplicate the functionality of CiviCase into CiviGrant, but that I want to use a consistent paradigm with existing CiviCRM building blocks to implement CiviGrant functionality.

That said, I think it is definitely worthwhile to at least explore creating a Grants case type within CiviCase.

I'll probably have time this weekend to write up a blog post about all of this. I would love to get feedback from the wider community.

[Donald Lobo]

Note that if we can implement civigrant via civicase and also adapt the workflows to make it simple for the "simple" case, we will be open to obsoleting civigrant

lobo

[planigan]

The blog posting is now up (http://civicrm.org/blogs/planigan/enhanced-grant-tracking-civigrant-vs-civicase)

Sorry for the delay!

[brizone]

Wow, this is great, would be very useful to many organizations. I'll pop out to the blog for the rest.