The first New England GiveCamp was this weekend at Microsoft’s Northeast Research and Development building in Cambridge, MA, and it was, by far, one of the most rewarding experiences I’ve had in the 15 years I have been professionally coding.
About 100 technical and non-technical volunteers spent the weekend of June 11-13 writing code for charities. Most projects were Web site upgrades — either installing a content management system, or extending that system to do something it didn’t do before, such as collecting very specific data, integrating with a customer relationship management tool, etc.
Other projects were more complex. For example, my project was data normalization and version control.
I was assigned to the Goshen Land Trust, a charity that protects open and green space in Goshen, CT. My team members were Kriss Aho and Pat Tormey, both from the Boston area; and Chris Craig, the president of GLT.
Prior to last weekend, GLT tracked all its customer relationships in Excel spreadsheets. They do their accounting in Quickbooks.
If someone was a volunteer, his name went into the volunteer spreadsheet. If he owned land, his name was in the landowner spreadsheet. If he was a land or money donor, his name went into another spreadsheet. And so on, and so on; this story has been told a thousand times before, we all know it by heart.
And, of course, there were several versions of each of these spreadsheets out there: They were exchanged back and forth via e-mail, meaning no two copies of the same spreadsheet were alike. Again, stop me if you’ve heard this one before.
Finally, donor payments are managed entirely separate from the spreadsheets, via entries into Quickbooks. So there’s a completely different store of around 800 mostly duplicate names in Quickbooks, too, which isn’t easily compared to a spreadsheet of about 2,000 names.
So we had to figure out a way to impose some version control on these sheets; we had to create a master data store, so we could have an authoritative source of customer relationship information; and we had to sync customer information in Quickbooks to match the master data store.
Sounds like fun, I know. It actually was, after it stopped being awful.
If It’s Half-Broke, Fix The Half That’s Broke
We wound up doing the real scoping for this project on Friday night, where the initial project parameters changed from a simple training exercise to the data normalization project we wound up doing.
My immediate thought, as we were discussing how to tame these spreadsheets, turned to CiviCRM. It’s an open-source, Web-based customer relationship management tool that is designed specifically for nonprofits, and that would have fit Goshen Land Trust’s needs nicely.
But that idea suffered a few significant flaws. The biggest problem was, none of the end users have any experience with CRM software in the first place, nonetheless CiviCRM, and we didn’t want to force a solution on to people who hadn’t bought in to the solution beforehand.
In other words, if Excel was getting the job done, except for the fact that none of the data jived among the sheets, then let’s stick with Excel, or at least spreadsheets, since everyone is comfortable with that.
We would simply combine all the current sheets into one “master” spreadsheet. Every person would be a row; every conceivable relationship and bit of information about that person would be a column.
Was this the cleanest, most elegant solution to the problem? No. But it’s one that meets the client’s needs and expectations, and it’s something we could do over the weekend. Or, to be more specific, that Kriss could do over the weekend. I got a different assignment; more on that in a moment.
In a perfect world, we would have discussed the project sooner and given the Goshen Land Trust a chance to check out CiviCRM. But again, Chris said he was OK with sticking with Excel, so long as the versioning issue was fixed; and Kriss could combine the sheets with minimal data mangling and need for cleanup by hand.
So the master data store problem was fixed. Now we needed to address version control. The answer to that was Google Docs.
Since the spreadsheet in question is “flat” — that is, one sheet, with many columns reflecting every relationship — we didn’t have to worry about macros, VBA scripts or other Excel-only attributes.
We also didn’t need complex user permissions: One guy owns the sheet, a few people need to be able to edit it and a few more need to be able to look at it. Google Docs has that built-in.
So we could just pop our master sheet into the cloud, set up some Google accounts and everyone would have access to one copy of one thing. Problem No. 2 solved.
Quickbooks, How Do I Hate Thee? Let Me Count The Ways …
Problem No. 3 fell to me: How do we synchronize the contact information being kept in the spreadsheet with the customer data being stored in Quickbooks?
My kingdom for a unique key, is the answer.
Because syncing records in two data stores was never a consideration before, GLT’s roughly 800 Quickbooks customers were recorded in ways that generally weren’t easy to match against the spreadsheets they maintained. I tried a mapped Excel import into Quickbooks of data in the spreadsheets, and came up with what was at least a 70 percent match failure rate.
It quickly became apparent I was going to need a unique key. The question was, how could I get a key other than Customer Name out of Quickbooks, since customer name is the only unique key exposed by built-in import / export functions, and clearly that wasn’t going to work?
The answer was the Quickbooks SDK, one of the sorriest, worst-documented excuses for a programming tool I’ve ever encountered. The only way to get access to Quickbooks’ internal unique identifier for customer objects is via the SDK.
My suggestion: If you ever need to do a Quickbooks SDK project in a weekend, and you haven’t used the Quickbooks SDK before, don’t bother.
The Quickbooks SDK provides, at the desktop level, two ways to get data.
The first, oldest and the one I chose was qbXML, which is basically SOAP: You present an XML request, you get an XML response. I have experience working with XML-RPC, Web services and XML in general, so this seemed the smart way to go.
The other is QBFC, which is more like writing a traditional object-based program using classes, methods and properties. This is the way I would have preferred to go, had the Quickbooks SDK’s documentation made that an option.
When I call the Quickbooks SDK “poorly documented,” what I mean is that it completely lacks object or entity references. As in, don’t bother trying to look up what classes, methods, properties or XML markup you should use, or what arguments any of them take. It isn’t written down anywhere. Not in the SDK documentation, not on Intuit’s Web site, not on the Interweb. Apparently, the Illuminati are keeping it a secret.
To figure out what you should do for a Quickbooks SDK project, you need to reverse-engineer a bunch of sample projects that come in the SDK. There is a “jump start” PDF that explains the basics of making requests and handling responses, but they’re written in Visual Basic 6, apparently by some junior-college student who’s being way too verbose in order to show his work to the professor.
(And yes, you guessed it: The Quickbooks SDK is straight-up COM+. You must install at least one DLL on each machine that wants to run an SDK application.)
Thankfully, there are plenty of XML, XSLT and similar sample files in the SDK for both qbXML and QBFC, covering virtually anything you could want or do; so one can hammer out a solution that’s a mashup of a provided sample.
Unless time and experience with the SDK aren’t luxuries you enjoy. Which was certainly the case for me this weekend.
My original goal was to create, in Quickbooks itself, a macro that would directly grab the online spreadsheet (via JSON requests to the Google Spreadsheets Data API), match up my records, correct any customer contact info that is wrong in Quickbooks with information from the online spreadsheet, and report results.
It was everything I could do over the weekend to get Quickbooks to give me the customer list as an XML file. And that XML file has multiple object levels, but no schema definition or namespace declaration available anyplace, either. Again: Worst. SDK. Ever.
So now I need to apply XSL to flatten it out, for purposes of comparing it to the online spreadsheet.
Needless to say, I’m still working on the project. My new plan:
- Create my record-flattening XSL.
- Have my program run standalone.
- Compare whatever is in the Quickbooks Customer List against the online spreadsheet.
- Any unique ID that is in the Quickbooks Customer List, but not in the online spreadsheet, is listed in a new Excel sheet, for manual updating.
- Any unique ID that is in Quickbooks, but has address / contact information that doesn’t match the online spreadsheet, is reported to a new Excel sheet, for manual updating.
I figure I’m about six hours out from being done. And I will deliver this. Because now, it’s personal. And when I’m done, I’m going to blog about it, so the next poor soul has a head start.
The Experience Was Amazing
OK, I admit I’m being overly dramatic about the Quickbooks SDK. But it was such an intense experience, being part of New England GiveCamp, that you will have to grant me some poetic license.
Cambridge, MA, the MIT campus and the NERD building are completely awesome.
The entire city is a visual feast: architecture, art, people, happenings; you name it, there’s something interesting to look at anywhere you go, at all hours.
The Charles River has become quite a centerpiece, too. On Friday and Saturday nights I opted to walk from NERD to the Hyatt Regency Cambridge hotel, where I was staying, which are about two miles apart on Memorial Drive.
That walk could not have been more pleasant. The Boston skyline at night is beautiful, and the buildings I passed on the way were all alive with the sounds of music and conversation and celebration.
Every morning, I took a cab in to prevent getting sweaty; on Saturday morning, I passed Harvard crew practice. (Memo to them: Time for a Web site redesign.)
I know, I should have taken pictures. The entire weekend, I kept saying, “next time.” Until I ran out of next times. So, next time.
The Hyatt is a very nice hotel, too. The glass elevators are kind of cool; the bed was completely awesome, and the room was more than sufficient in size for one person.
I did give the restaurant / lounge a try on Saturday night; they charged me $17 for 32 oz. of beer, served in a mimosa glass and milk pitcher, which I thought was pretty silly. They also messed up my hamburger order, but gave it to me for free as a result.
I made the mistake of reserving a room at the last minute — priceline.com completely came up short on reasonable, affordable alternatives — so it was hard to get the Microsoft corporate rate, but after a series of phone and e-mail exchanges, I managed to get in at the last minute.
So I would happily recommend the Hyatt Regency if you’re staying in the Boston area. They were very accommodating, no pun intended.
Such Interesting People And Causes, Too
The people at GiveCamp were pretty interesting, too: Both the developers and the charities.
I reconnected with Greg Howe, who I went to college with and who co-manages the Bangor Area .NET Development group. Greg is exceptionally well-versed in Web security and gave a talk during the event.
I met Alexander Lee, whose charity, Project Laundry List, advocates line-drying clothes as a green technology; it sounds silly to a country boy like me, but in many places in the world, a clothes line is a practical, or political, impossibility.
I wanted to meet, but couldn’t manage it, Spare Change News, a newspaper produced and distributed by the homeless, which I thought was probably one of the most interesting ideas I’ve heard in a while; rather than trying to get the homeless to not be homeless, it aims to provide the homeless with the information and financial support to meet their needs.
The event was full of incredible people doing incredible things. You couldn’t help but be inspired and driven to give your best.
I figured that at worst, the GiveCamp experience would be an opportunity to help out some people who needed it and to jabber with some like-minded souls. Turns out it was that and more.
It plain feels good to have made a difference to someone else. It plain feels good to have been in the company of unselfish people for a weekend. It plain feels good to have been in a beautiful place for a weekend. And it plain feels good to have been thanked by people doing largely thankless work.
Count on me for New England GiveCamp 2011 and beyond.
All links in this post on delicious: http://delicious.com/dougvdotcom/new-england-givecamp-2010-what-a-great-experience