We use dotproject to help us manage our consulting projects. It’s an open source project management/time tracking system written in PHP/MySQL, that allows you to create projects with tasks for a customer, and then log time against those tasks. It works for us–there are a lot of things we wish were different. We’re not just griping–we gotten our hands dirty with lots of modifications, including major changes to a module called TimeCard to make entering time tracking data as pain free as possible. Overall, it’s a very good project, and I want to give props to the developers–we all use it every day.
In an act of faith, we’ve been diligently creating projects and entering our time against them for 2 years. I call it an act of faith because while we get benefits from using dotproject, we’ve got 2 years of project data that is basically invisible to analysis. We can’t get the data out easily, in a repeatable, customizable way. Because the thing with dotproject, as is the thing with a lot of complex database applictions, is that reporting is “suboptimal” (to quote my cousin Flora).
Reporting engines are hard to do well, so this is no surprise–I want to reiterate my props to the dotproject folks becasuse it’s been getting steadily better. But it’s not there yet.
I was getting pinged every week by our consulting lead to create this report, then that report, then another. It involved writing MySQL queries, creating PHP pages, etc. Not rocket science, but I’ve been the “report guy” before, and I didn’t want that role again!
So I decided to take a different approach. One weekend in March I spent about 12 hours on a proof of concept–could I bring our project data into Salesforce.com so that I could just point my consulting manager to the reporting engine and, even cooler, dashboards? Here are the steps I undertook:
- Modeled a dotproject project as a custom object in Salesforce.com
- Installed latest version of PHP on my desktop
- Installed the salesforce.com API PHP toolkit on my desktop
- Gave myself permissions to query the dotproject MySQL database
- Took a hello world PHP/Salesforce.com app and the Project view PHP page in dotproject and started to merge them together
- Hackety, hack, hack, hack.
That proof of concept is now a PHP script I run on a schedule that finds all the projects in dotproject and brings them in to Salesforce.com as read-only objects. The script does a few helpful things:
- If a project already exists, the data is updated rather than having a new object created (using upsert method)
- If the project is for a Customer in dotproject that doesn’t exist in Salesforce.com, an Account is created
- Projects are related to Accounts, and our project lead (a Contact record)
My consulting lead and my executive director nealy wept when they saw the first Salesforce.com reports on project actual and budgeted amounts, which projects are over, which projects are at risk of going over, the project pipeline, etc. dotproject could show us some of this, but Salesforce.com aggregates data much better, and my consulting lead is off and running with it.
Two-way data syncing is cool, but don’t forget the power you can get from getting read-only visibility to data important to the mission of your organization. We’ve seen this with our dotproject integration, which is by all accounts an ugly hack that at the same time will significanly impact the way we work.
