Way back in the baby days of this weblog I wrote:

FileMaker is a unique tool in my toolbox that I have no plans to give up and, as of yet, it doesn't talk to Drupal.

Well, those days are over. I just contributed my first Drupal module, which facilitates development of FileMaker web applications through a Drupal interface.

Please download the FileMaker module for Drupal.

Demo video of FileMaker module for Drupal

Some future improvements

• Value lists
• Drupal actions
• Adding and editing portal records
• FileMaker containter fields
• FileMaker user-based, and table-based authentication
• Replace of the FileMaker API with FX.php (which is GPL'd and available over the net)

Update: I added support for FileMaker value lists, displayed in Drupal through select fields, radio buttons, and checkbox sets.

Update 2: I added support for Drupal actions, a default FileMaker view for non-authenticated users, and required fields.

Update 3: I added support for tokens and created a sandbox project on Drupal.org while I wait for the project page approval.

 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.

United Palestinian Appeal (UPA), a client close to my heart, is a Washington, DC based non-profit dedicated to alleviating suffering in Palestine. The organization has been performing difficult, and often thankless, work for over 30 years. The amount of data they move around is enormous and as a good friend of mine who works at UPA put it: everyone says they want to help Palestine, but nobody wants to file. Recently, UPA hired a new executive director -- a retired college professor and former UPA volunteer -- Ghassan (GJ) Tarazi.

GJ has been a pleasure to work with and is traveling to the Gaza strip later this week to promote UPA's University Scholarship and Emergency Relief programs, in part by ensuring they have Internet access. In an initiative begun by their former director, Samer Badawi (now with the World Bank), I am pleased to announce that UPA will, for the first time in its history, accept scholarship applications and proposals for emergency relief funding over the Internet. (These are private websites so I can't provide links.)

This seemingly minuscule change will save literally hundreds of hours of tedious work a year. Now the staff will be able to spend time ensuring that money is well spent by reviewing applications instead of doing data entry with applications. Just as importantly, less money will be spent on data entry. Think about it: high school students in the devastated Gaza Strip applying for college scholarships online, instead of by paper, will actually create the funds to send more of them to college. That is the kind of result that makes me proud of Inner File and the work we do.

The technical details

UPA uses a FileMaker database, built by yours truly through my super cool company Inner File Software, and hosted by FM Gateway. FM Gateway is run by FM Web School, a company based in Florida and dedicated to hosting FileMaker databases and helping their clients get those databases on the web. I have used their FileMaker hosting for a couple years (it rocks) and read most of their books. I don't like their books, as they are poorly written and written for beginners, but they are the only books out there on FileMaker/web integration.

FM Web School offers a Dreamweaver plugin, FM Studio. At this year's DevCon (which, regrettably, I missed), FM Web School released the second generation of FM Studio: FM Studio Pro. I am in the middle of a number of FileMaker/web projects, so I decided to purchase the software and try it out. I will likely post a further review of FM Studio Pro (I need to quit blogging and finish up the testing before GJ heads to Gaza), but I will start by saying that, after maybe two dozen development hours, I have mixed feelings. I have a lot of experience with Dreamweaver plugins, having used MX Kollection, now the recently discontinued Adobe Dreamweaver Developer Toolbox, for years. I also currently use a fair number of WebAssist plugins. All of my experience with DW plugins made it easy to learn FM Studio, but also highlighted how unrefined this plugin is. The menus need a lot of work and consolidation, almost no PHP can be edited through the plugin once written, and the code itself could be much better organized. The MX Kollection, which Adobe bought and destroyed, was as streamlined as I have ever seen these plugins, and FM Web School could perhaps explore some of their features, such as the NexTensio database management tools and the integrated authentication tools.

An issue worth mentioning is that I had no access to most of my other plugins while working on any PHP/FileMaker website. This is a potential issue for anyone who relies on DW plugins. Most of my plugins assume a PHP/MySQL website (DW forces users to decide by website) and few of them worked. Even the javascript tools, which have nothing to do with the server side scripting language.

But as I said, my thoughts are mixed. I probably saved 50% in development time (not kidding), although I have no idea how that will actually pan out when it comes to maintaining the code over years. And let's hope this plugin doesn't get bought and destroyed by Adobe after hundreds of developers, and thousands of their clients, become dependent on the plugin (as has happened twice before with me and DW plugins I purchased). There are a few neat tools in FM Studio Pro, and I particularly dig the portal tool shown in this cheesy video:

And my conclusion

I already decided to move away from Dreamweaver plugins, in favor of open source web development software, Drupal in particular, before purchasing this plugin. FileMaker is a unique tool in my toolbox that I have no plans to give up and, as of yet, it doesn't talk to Drupal. Coding by hand does take a fair amount of time so I will use FM Studio Pro for the foreseeable future, at least for some of its tools. I will, however, carefully ensure that my code is easy to edit without FM Studio Pro in case I decide (or am forced) to choose another tool.

How to organize and edit code written by FM Studio is the likely topic of a future post. I don't mean for this posting to sound harsh in my review of FM Studio and comparing the affordable FM Studio to the most sophisticated (and expensive) PHP/MySQL plugins is probably unfair. All in all, FM Studio is a pretty good tool and most importantly, it does what it promises: quickly, and relatively easily, allows people to build FileMaker powered database web applications. And, much like their books, FM Web School is the only game in town for FM Dreamweaver plugins.