When you have a civicrm, everything looks like a nail (or a finger)

2012-02-11 02:46
Written by
xavier - member of the CiviCRM community - view blog guidelines


So as every consultant, there is a bit of new projects, maintenance, stuff you do for free for the community, new ideas, meetings, pre-sales, funky developments & the dreaded admin part (invoicing/timesheet).


As any consultants, we are trying to get an overview on what are the issues, where we spend the time, who's involved and what has to be invoiced. For what I've seen for the past 20 years, the choice seems to be between separate tools that work well but don't talk to each other and one big ERP that tries to do everything but does it badly and that no one uses without cursing.


As all our contacts are in civi and that it also can track activities, was wondering if some of you are already using it as part of that combination and hopefully not turned it into a clunky 'ERP'?


We have started with Andreas, Tamsin, Julian & Cristel with some civi/drupal tools and Civi, but are in the middle of the road and wanted to brainstorm with you, see how/if we can get something that make sense. I was discussing it with michal and some other consultants, thought might be useful to put it in one place.


Logging issues & discussions

For each project, they are tasks/issues and discussion about them. This is usually done by email (with an empty subject line and a body "The page on the site doesn't work.").

For each project, we have a organic group and with og_mailinglist we have an email "projectA@my.org". Each new email starts a new issue, each reply is a comment. It mostly work, beside the odd new task "So, what's going on about the things of last week".

The views are usually good enough to see what's going on for any project or our workload across them.


Activities & Cases

The Civi way would be to have a case per project and one activity by task. As it matches the project/issues, we have written a module that creates a case for each new project and a new activity (type task) for each issue. We don't create comments in civi, as they are mosly discussions are not useful to get the bigger pictures (knowing how many times you have been asked to increase the size of the logo is better kept in the comments ;)


It works, but doesn't bring a lot of new features to the chain by itself, beside linking it to the contacts (that might be useful when you have lots of people involved on a project), but that's the needed part to make civi more integrated and....



Logging time on a website has always been a weak point. The interface is not user friendly enough, needs to re-enter info about the clients & projects & tasks that are already elsewhere... and you might be working offline or with a flacky internet connection.


Lately, we have been using hamster, a timetracker that integrates well and is easy enough to use. It is able to understand some commands so "writing the blog about civiproject @civi" would create a new task (or update an existing one) in the project civi.


Andreas has coded a tool that takes the timelog (the complete db) that you can upload to the server and match to the tasks/issues.

It mostly works, but right now that's done by uploading the sqlite db hamster uses. To make it really practical, it would be needed to automatically create the projects & issues on hamster when they are added on the web.

(shouldn't be complicated, a local script using the api), and upload the timesheet using the API too.


Right now, we are storing it on separate tables, but if we were to use civi, each worked timeslot could stored as a sub activity of civi.


Reporting & Invoicing

In our cases, the reporting is either internal (to see where we are on a project) or for the customer (on maintenance/per hour). the reporting functions of hamster are good but the export isn't, and anyway you'd want to be able to easily mix hours spent by various people.  This part is still too manual and involves excel, csv, sql commands and in general is not a pleasant experience.

We have all/most of the data on our server, but we aren't sure if we should develop reportings outside of civi (using views) or try to integrate them into civi & use it for reporting.


What's missing

That's very likely that we won't all use the same tool to track time and that it should be eas(ish) to import from a lot of various formats (eg meetings from a calendar, logs from the PBX, timesheets generated from various providers...). Right now, matching the different ways people name the same task/project is painful, not quite sure how to create a common taxonomy or tools to easily convert my project "Civi" with the project "civicrm" or the project "civicrm 4.0".

The next step would be to be able to add categories to the tasks, and see how much of the project is spend on pre-sale, brainstorm, coding, debuging, specification, testing... and let my inner data nerd have fun with that and do nice data visualisation.

Personally, I'm very keen on pushing our civibot (miss moneypenny) -that allows me already to interract to civi via my chat client- a bit further so I can chat "punchin writing the blog about civiproject @civi" that would create the tasks directly, with the added benefit of having the bot keeping a timelog already of when I'm online/away. This is another discussion probably ;)


How are you using it?

Are you already using civi for your project? How do you track time and what are your tools of choice? Do you think it makes sense to use civi to manage a consultancy shop?









Great discussion starter Xavier, and thanks for sharing your setup.

Time Recording - Custom Joomla (1.0!) component

About 5 years ago we wrote a custom component in Joomla! for the time recording and basic case management piece, and then integrated this with CiviCase in a loose way a couple of years ago.  It was never ready for prime time, so unfortunately was never released.

We are looking to dump it soon, so that all time is recorded against a task in CiviCase, but to do that I think we really need to get CiviCase distinguishing between tasks and activities.

Task tracking - Jira

Currently we store all the tasks on big projects in Jira, so that we can break a project down into manageable work blocks and then estimate time against them.  Pulling this back to the high level then enables to estimate the time involved in the project.  This is something that lots of case workers would benefit from being able to do in CiviCase.

Clients generally only want to know about the tasks undertaken on their project, and not all the individual activities recorded for those tasks, so when you create an Activity in CiviCase you should be able to say what Case and Task you were working on, so that you can generate reports by Task, not just by Activity.

Jira is a brilliant issue management platform, and is free (as in free beer) and open source (in terms of the code being hackable), but it is neither Free Software nor Open Source Software - but it does have web service capabilities that enable it to push data through to CiviCRM and vice versa.  Consequently, even if CiviCase had a very basic Task/Activity framework, it could centralise the basic record of activities on tasks undertaken on small projects within CiviCRM and large projects within Jira, to enable centralised reporting for clients/management.  That would be great!

Correspondence and document management - Alfresco

When we send email, we put a unique "ref" field and Case ID in the footer, so that the CiviCRM Autofiler can add the emails to a Case - but the way CiviCase displays these activities is pretty clunky, so we are looking to integrate this piece with Alfresco - the Open Source document management system that we think is the best fit for organisations that have serious document management needs.

We are in the final stages of planning the creation of a CiviCase hook that will create a folder in Alfresco for each case, with custom rules based on the Case Type, and an Organic Group within Drupal for discussion.  This will enable CiviCase to not just create an appropriate set of folders for a "Housing Case" or "Divorce Case" but also trigger rules to generate appropriate documents, discussion/wiki spaces etc.  Very exciting... just wish there were more hours in the day!

Currently we have setup the Alfresco templates to trigger when a new client/case is created, we now need to write the CiviCase Hook and look at how to hook into the merge process to ensure that Contact and Case files don't get orphaned when Contacts are merged and cases are moved around (creating a new Case ID).

Because Alfresco provides WebDav over SSL in addition to the browser interface, it can provide a DropBox like experience as well as being able to display folders via IMAP!  You can activate version control on individual documents or those in specific folders, and have rules to apply when documents are added to those folders.

Alfresco is extremely powerful, and as such has a reasonably steep learning curve (including setting up a Tomcat server and using Java, rather than the LAMP stack that is more familiary to CiviCRMers).

Organisation directory / Mail services - Zimbra (using openldap)

To glue all of this together using a consistent username/password and authentication system, we are using the Open LDAP provided by the Open Source Zimbra Collaboration Suite.  This enables an organisation to create an email account for a new employee/volunteer/contractor/client and when they create their email account those new users will automatically be able to login to the Alfresco document management system to see just their own folder, or the folders that relate to their contacts/cases, using their email username/password.  We have not got the group/folder permissions piece completed yet, nor the hooks that would need to be added to CiviCase that would enable it to change group permissions in the LDAP Zimbra server when people were added/removed from a Case.

One thing that we are thinking would be useful, is to have a separate email address automatically created in Zimbra when you open a "support case", so that all emails to that account can be recorded as an Activity against an existing Task (if they contain the Task ID in the Subject) or will create a new Task and add itself as the first Activity.  This action could be triggered in Zimbra via a "Zimlet" or in CiviCRM via the email processor.

When an activity is assigned to a contact, it could also be added to that user's Zimbra calendar (there is currently no native CiviCRM per user activity feed - but ns web solutions has built a Drupal module to do this!).

Zimbra is also a Java based platform, like Alfresco, so Java together with OpenLDAP requires a fair bit of learning for those coming from a basic PHP/MySQL background.


Because Zimbra and Alfresco provide great web service interfaces, and are really solid enterprise solutions, I think they are brilliant candidates for Enterprise integrations that more than compete with the proprietary closed source alternatives.  When installed within Drupal in particular (due to its permissions structure, hooks and open modular architecture), CiviCRM could provide a fantastic hub for tying together these Enterprise solutions into an easy to use and familiar interface - without becoming a piece of ERP bloatware that makes it unusable to all the small non-profits with simple needs and small/non-existent budgets.

Fundamentally, however, we need to make some improvements to CiviCase so it can play this co-ordinating role. 

A subject for a great discussion at CiviCon 2012 (hope I can get there)!





The key thing is having Tasks separate from Activities.

This is relevant for ticket tracking too, because it enables you to have a new Task created with the first report of an issue, and then have that task id in future emails.

I think it is really important to not turn task management into a forced ticketting system, but for it to be flexible to support being used as a ticketting system.

Our approach is to try and push the data that you need to be centralised into CiviCRM, but to keep the specialised tools doing what specialised tools do best.

Making CiviCase have a Task/Activity model rather than just an Activity model would help enable:

  • case workers to record their time as activities against tasks, with just the tasks displaying and their status, rather than get overwhelmed by a huge long list of activities for daily time recording
  • the provision of an API which Redmine (or Jira) or another ticketing/task tracking system can use to keep a record of the tasks within Civi, together with the time recorded against each Activity for that task, so that you aren't trying to reinvent the wheel by building a ticketting system into CiviCRM when it just needs to co-ordinate the data from an existing platform (Jira enables work on issues to be pushed out via an API, so developers can use a tool that works well for them, while having their time records pushed into a central place that the time of other non-developers is also stored for billing purposes)
  • the ability to set a default hourly rate for a person and override it for a particular case - ideally this could be set by an admin and may only be viewable by the person with permission to generate the report, so that the personnel cost to a non-profit of running a case can be monitored, and/or a client/govt agency charged for the work on a legal aid case by a community legal centre (for example)
  • the ability to sync contacts/cases and organic groups - our preferred model is for each client to have an organic group and each case having a sub-group below it.  We've demonstrated this approach to people via Drupal Commons, and it really appeals to people.  Sarah has mentioned above that she uses an organic group per client. We are looking to setup a hook so that when you create a case for a contact it would:
  1. create an organic group for that client if one didn't already exist, an LDAP group for that client and an Alfresco space that the LDAP group can access;
  2. for the case, create a sub-group (Drupal), an LDAP group, and a sub-space in (Alfresco) permissioned for that case's group; and
  3. when a person is added to an appropriate role to the case their Drupal contact is added to the appropriate group and their LDAP identity is added to the LDAP case sub-group.

Ideally the Drupal organic group access rights would also be driven by LDAP, but we haven't looked into that.  This would enable all the "heavy lifting" to be done behind the scenes by the tool designed to do that job, while CiviCRM sticks at what it does best and uses hooks to provide the glue to keep everything together.

This way small NFP organisations could use CiviCase to record time by recording activities against tasks, while larger organisations could use other enterprise tools with good APIs to do specialised tasks (eg Jira for development) and have that data pushed into the central "task and activity" repository for the organisation - CiviCase!

YJ (not verified)
2012-02-12 - 00:41

I think is a good discussion, i.e. talking about one's own itches that need scratching, and if we can gather enough momentum to develop something akin to a Drupal distribution that would help us as implementers achieve higher efficiencies during our day-to-day work of promoting and servicing the CiviCRM user community of clients, then that's a win-win for the community at large.

We've looked into using CiviCRM as the unified system for client interactions in the past, but we've never pulled the trigger due to lack of integration to a good issue tracker and a good time tracker, both of which are critical in getting the full use of the system in our opinion. We use a combination of Kanbanpad, an android time tracking app, and LaTeX to get from issue to resolution to invoice. Having said that we will however be using CiviCRM starting this month to keep track of sales leads and proposals. In our analysis we really would like the ability to generate invoices straight from within the system while also giving our clients a peek at their own project status as a dashboard to cut down on unnecessary email exchanges.

I know some consultants are using redmine as a pure ticket and time tracking system, but it's not integrated into CiviCRM.

There is a actively developed support ticket system built on Drupal sponsored by the folks at Tag 1 consulting http://drupal.org/project/support that is worth mentioning here which does allow for time tracking, and it might be useful to layer this into a possible Drupal/CiviCRM distribution.


What is missing in your mind for having a good ticket system?


In my experience, stuff like redmine is too complex for clients, and the best I could find it a pure email solution, where they get notifications and reply to them, and a dashboard of issues we look at in meetings and that's about it.


We looked at support, don't recall why we prefered using og_mailing list at the end, will try to remember.



I think what's needed for good ticketing are in order of importance:

  • creating tickets by email (both from client side and staff side)
  • responding to a ticket by email while keeping a copy in the ticket system
  • closing tickets by email
  • tracking time spent on a ticket
  • support staff comments for internal use
  • allow clients to comment on an existing ticket without opening a new ticket
  • ability to paste in stock responses and being able to draw from knowledge base of existing answers

Something that is missing is being able to close per email. How do you do that? with a command like subject (re: ticket title [close]")?



I'm not so sure this is a good idea. I've implemented something similar myself in Drupal, pulling time data in from Freckle, and sending out to the invoicing system (Freeagent). It worked as far as it went, but then I tried to add a reporting facility for clients. I found Drupal too rigid to output the data in the way that I wanted (this is coming from someone who's worked almost exclusively with Drupal since 5.0), and was running into bugs in coreish modules (Views and Token).

So I rewrote using express.js (rails would have been an easier choice but I know node better than I know rails) and to me it feels like much less friction to develop.

Drupal/Civi are very optimised for what they do, and are good at it. Imo as soon as you start trying to use them for something more app-like you're going to be swimming upstream.

Plus as others have mentioned there are some great tools that solve all these problems already, and do it much better than a home-rolled solution could. If you're building you're business infrastructure around a Drupal/Civi solution you're kind of locked in to whatever the community provides.



I've been using node.js on some project, and like it very much, but not sure it'd help custom dev. As for being locked by whatever the community provides, part of the goal would be to improve civi. Things like timetracking has been mentionned by different use cases than for providers.


This being said, not sure that'd be the best solution indeed ;)

Sarah Gladstone (not verified)
2012-02-12 - 18:54

I am using CiviCRM + Drupal + Organic Groups to manage clients, projects, and tickets. Timetracking/Invoicing is done in QuickBooks Online.

Its been a bit of a journey to get to this point. Originally used SugarCRM to track clients, but it had to many limitations so I switched to CiviCRM to track clients. Tried briefly using CiviCase to manage client projects, but it did not work out as the client could not see their project status, nor share files, comment on what was going on, etc. So we switched to Organic Groups for managing projects. There is one group per client. At one time I used osTicket for ticketing, but could not easily associate a ticket with a client. (This became esp. painful when multiple staff at a single client opened tickets without coordinating first). I am no longer using osTicket, and instead created a CCK type called "support ticket" which is required to be posted to an organic group.

I am also using Views to allow a dashboard of all open tickets, plus a block of open tickets that is displayed in each group. This way a client can access their group and see any communication about their project, upload/download files, plus see a block listing their open tickets.

The main pain points is that I have an organic group for each client organization, and I also have a CiviCRM contact record for each client organization, yet there is no mapping/linkage between the 2. It would be nice to pull up an organic group, and see the main phone number and related contacts for that client(as set up in the CRM record). It would also be nice if when looking at the contact record, to see the status of their project and any open tickets. There is also redundancy about maintaining relationships between the client contact record and all the employees/volunteers, and also have to manage the group membership.

We seem to have the same drupal setup.


With the sync, can see the activities/tasks on the civicrm profile. Having an overview in the contact summary (both org & persons) might be useful indeed)

Currently I have one organic group per client. This works out because at any given point in time there is only one main project with each client. There is one client where we have lots of mini-projects,but its still just one organic group with lots of "support tickets/tasks".

Some very interesting ideas here. 

Not advocating this as a solution but more in terms of exposing our inner workings.

We really only use civicrm to record hosting arrangements (as a Membership) but should be pushing a lot more data about the servers etc in as custom data and exposing as Views.

While we used OpenAtrium (Drupal) for a while for ticket management we shifted over to Redmine and never looked back. Ability to commit code as part of the issue tracking process was a definite advance for us.

For timetracking we use a proprietary NZ solution called MinuteDock largely as this integrates with Xero - an proprietary accounting package - another NZ-solution that is taking the world by storm (well they say so ;-) )

To top it off we use Flexitime - yes another proprietary NZ solution that also integrates with Xero - for TimeSheets and PayRoll stuff.


I've been gradually increasing my own dog food consumption over my last 7 years as an independent drupal/civicrm consultant.


I've recently been converted to using OpenAtrium for ticket tracking, and I've been using a fairly simple views/cck type solution for time tracking, which I haven't yet, but plan on integrating into OpenAtrium soonish.


I wouldn't bother posting this except that as far as time-tracking goes, i wanted to say that i've always hated systems that force you into an unrealistic model of doing one thing at a time. So for a long time, i was stuck using an excel spreadsheet and doing a lot of manual calculations and maintenance because I didn't want to post one task at a time and then have to keep re-editing them as I was pulled off and on a variety of tasks over the course of the day.


Several years ago, I wrote a Drupal 5 custom module to do a kind of batch input system for a local movie theatre, and realised it would work for me as a timetracking input tool, and used that for several years. But it was a bit of a hack, so i was hemming and hawing about trying to upgrade it when i discovered ...




which is a way of doing a kind of smart spread sheet style input screen using views, just as you might expect and hope for.


To me - combining this into the existing openatrium case tracker feature seems like a nice way forward, and being able to glue civicrm constituent records to my client content type would be as much integration with CiviCRM as I'd need or want.


I haven't explored civicase, but integrates enough with views to allow editview to work, then i'd consider it.