SOA Watch, an organization working to shut down the "School of the Americas" and change the oppressive US foriegn policy the school represents, recently "upgraded" from ebase to Democracy In Action (DIA). My company, Inner File Software, did the data conversion and import.

The move from ebase to Democracy in Action

SOA Watch had two databases -- ebase for donations and mailings; DIA for email blasts. Duplicative databases are devastating, unsustainable and needed to go. When deciding which to use, the choice seemed easy: their ebase database ran in FileMaker v4.1 which is amazingly old. So old, in fact, that I have never worked with a .fp3 file before and I have been working with FileMaker for over half a decade.

My job was to (a) clean up the ebase data, (b) prepare the data for DIA, and (c) import the data while (d) avoiding duplications if a person existed in both DIA and ebase.

There were about 50,000 supporter records in ebase, most also in DIA, and about 40,000 donation records. There were a little over 100,000 supporter records in DIA. DIA didn't have any donations records. All other ebase tables were being abandoned.

Open Source Rocks

ebase is free and open source, although it requires FileMaker, a closed source application to edit the code or network for multi-user. This isn't a problem for me, as I regularly work with FileMaker. The relevant point here is that I had complete access to the data. I exported the data from ebase as two .fp3 files (again, wow) and built a simple deduplication utility  (as most orgs with a lot of volunteers tend to, they had accumulated a few dupes over the years) that moved donations when de-duping. I wrote the application in FileMaker Advanced 10. With an SOA Watch staff member in control of the tools I built, the duplicate supporter records were eliminated in a few hours.

Proprietary Sucks

Then I turned my attention to DIA, which is when things got hairy. First I "created" custom fields for the data ebase collected and DIA did not. By created fields, I mean created labels for a whole bunch of empty fields that already existed at the bottom of the supporters table. I know, right? Then I prepped the data for import (again, in FileMaker) using their "API documentation," which was really just a collection of SQL descriptions of the tables pasted into a web template that doesn't fit them. Not kidding. And they shouldn't have shown the database details to me because, as database developer, it made me sad. The field names lacked naming conventions, seemed to be added randomly, were nowhere close to normalized, were in no order, and were not very efficient. But at least I had access to their table descriptions and could write and test an SQL import script to handle the dups, right?

Wrong. I explored their documentation and found a little bit of information about importing supporters and donations, but all of it involved their import utility. No way to import SQL, despite it being a MySQL database. Very little of it explained what was actually going on with their import utility. I explored the import tool and couldn't find a way to compare updated dates or any other way to elegantly deal with duplicates. And it seemed that all imports had to be done through their (poorly documented) import utility. I called up their tech support and was informed that there were "security concerns" with giving me access to their database directly. Fair enough, I guess. But then I need tools. I didn't want to leave SOA Watch with 30,000 duplicate supporters.

No options, little safety net

DIA's tech support (smart and friendly, despite mostly just telling me no, by the way) insisted that I trust their import utility to handle the duplicates. Well, I had no choice but to trust it. At least I learned that the import utility always assumes the imported data is newer and that I could get a complete export of the supporters before attempting my import. I could use that information.

I exported the DIA data -- again using their tools. The export was quick, but there wasn't an option for SQL. I created a new table in my growing FileMaker file and imported the DIA data. Now I had access to modification dates, could count empty fields, etc.. I updated the ebase data as best I could and got ready for the import. While I had a DIA backup, there was no easy way to roll back DIA if something went wrong. And I had no idea what was about to happen.

Friday evening, after everyone left the office at SOA Watch, I nervously imported the supporters. Incidentally, everyone also left the office at DIA tech support. I was somewhat impressed with their importation utility while matching up fields, but then after the import the numbers came out wrong. Not bad wrong, but wrong. An extra 20 records updated here or an extra 11 inserted there. The numbers simply didn't match what I was importing. And there was no way for me to drill down, or roll back. I did some investigation on individual records, and everything I checked looked OK. I decided to ignore the extra records and move onto donations because, well, what else could I do? And that was where things went bad wrong.

First attempt at importing the donations created 40,000 empty supporter records, one for each donation record. Plus the extra dozen or so I was starting to expect. 40,000 empty records? WTF? The donations had the supporter's ID and tech support just assured me the import utility would recognize that.

Fast forward to my Monday morning tech support call. Tech support didn't give up on me, and the problem proved to be a setting on the import utility. I’ll spare the details, just keep de-duplication on even if you are importing donations to an empty donations table.

I said I would spare the details, but this one is important: when the tech support person learned that there were empty supporter records and I had a backup of supporters, her first instruction was to delete all the supporters and re-import them. I asked her if that would delete their their entire history of communications and click-throughs. She said oops.

Wow. We easily got rid of the empty supporters through their tools. But that could have been a devistating error.

Other than that, things went smoothly with tech support on the phone. They deleted the donations, which I couldn't have through the tools provided. I re-imported the donations with the correct settings. Aside from the extra few records, which I have come to view as sort of a DIA success message, the donations matched up with their supporters. At least the random sample set I tested through their interface.

I am not a fan of Democracy In Action's software

Problem solved, I hope, but that sucked. And DIA is expensive. Really expensive. They do have decent tech support but the software sure leaves a lot to be desired. It is a black box, except for the few places you can see in, and it looks horrible in there from those vantage points.

The answer to DIA?

The answer to DIA?

The question is worth exploring, as a lot of organizations pay DIA a lot of money for their mediocre software. I will save that for another blog post, as this one is a touch long already. For now, the conclusion I would leave you with is this: don't move to DIA (or any other contact relationship management platform that puts a wall between you and your data) if you aren't there already. But if you have two databases, consolidate. Carefully.