Future Tense: Filemaking For Pros

Posted 06/29/2011 at 5:22pm | by David Gerrold

2

Comments

Print

James Burke, who made the marvelous TV shows Connections and The Day The Universe Changed (worth buying or renting!) once demonstrated that one of the most important inventions in the history of information technology was the vertical array of storage shelves—the filing cabinet. Why? Because it allowed for a visual system of organization. It was the first database. It made it possible to access information a lot more quickly than spelunking through a stack of scrolls or books.

The computer, of course, makes it possible to have far more complex databases than will fit on a single wall, and provides near-instantaneous information retrieval. One of the first and most important (and possibly the most overlooked or taken for granted) uses for personal computers—after word processors and spreadsheets—was database handling.

At the very beginning, there was dBase II—it was both a database system and a programming language. It was so versatile that it wasn’t just the industry leader in database software, it was the industry. dBase II owned the database market for more than a decade, because users needed to build their own specific applications for handling customer lists, billing, record collections, whatever. And unlike WordStar and Lotus 1-2-3, dBase is still around today. The .dbf format remains an industry standard. But most users don’t buy database software anymore.

What has happened is that database functionality has been subsumed into a host of other products. You don’t need to do it yourself anymore. Outlook and Gmail handle your address book. Windows Media Player and VLC and Media Monkey manage your music and video collections. Calibre manages your ebook library. PaperPort keeps track of all your scanned files and .pdf documents and everything else it can index. InfoSelect and OneNote are free form managers of all kinds of things, odd little paragraphs, quotes you want to remember, great rants, limericks, websites, even pictures and sound clips.

So the average user doesn’t need to create his own databases anymore. Somebody has already written the software for managing a comic book collection or cataloging a home inventory or tracking investments, or keeping recipes. There’s a lot of software available, even a lot of freeware. But what do you do if you have a need so specialized there is no software for it? What do you do when you need to track a specific set of data.

Most people default to a spreadsheet. Excel is very good for a quick and dirty database. I’ve used it that way myself. And you can import many database formats into Excel and back out again. So you can fiddle and diddle and massage your information that way. But Excel has its limits too. What do you do if you need to go beyond a simple Excel table? What if you need relational functions, or you need to include pictures or sound clips or videos?

In 1991, a marvelous Windows-based relational database program called Approach hit the market. The Approach database won over 30 awards the first year, including “best of show” at Comdex. Where dBase was primarily a programming language for accessing and manipulating data files, Approach was a database engine with a graphic user interface. It was easy to use. You could declare a few data fields, drag and drop elements to create personalized layouts, and go to work immediately, adding and adjusting as necessary.

Unfortunately, Approach was sold to Lotus in 1994 and became part of the Lotus SmartSuite. And then IBM bought Lotus and promptly neglected it, allowing the whole package to sink into obscurity almost immediately. Approach isn’t officially abandonware but it might as well be. There hasn’t been an upgrade in more than a decade.

Fortunately, there is a much more powerful alternative, Filemaker Pro. It began as Nutshell, a DOS-based program, evolved to a GUI-based program for the Macintosh, and eventually ended up as Claris, a subsidiary of Apple.

The Windows version was first published in 1992 and in 1995 Claris changed its name to Filemaker, Inc. specifically to focus on their best-selling product. Since then, they have released increasingly powerful upgrades approximately every two years. Filemaker is now up to Filemaker Pro 11. There are Advanced and Server editions too.

To the best of my knowledge, there isn’t anything else on the market as convenient as Filemaker Pro for quickly creating a flat-file or relational database. And despite the inevitable accretion of new features over the past 15 years, the program is still easy for a beginner to get into. You declare the fields in your database, drag and drop them onto the layout, resize fields and fonts to your looking, assign colors, specify behaviors, add buttons or triggers to run scripts, create checkboxes or radio buttons or drop-down calendars, create container fields for pictures, and so on. And with the Advanced edition, you can even compile your database into a standalone application for distribution.

Data types include text, number, date, time, timestamp, container, calculated, and summary. (There is no Boolean data type, but you can fake it well enough with a text field and radio buttons that access a “yes/no” category list.) Individual fields can hold up to 2 gigabytes of data, so you can store a whole novel in a text field, if you wanted to. But like Notepad, text fields do not maintain formatting. (My request for a future release is an enhanced text field that stores text in .rtf or .doc format.)

A calculated field can contain dozens of nested “if-then” statements as well as all kinds of math functions and text concatenations. Likewise, scripts can be nested to accomplish almost any task, no matter how complicated. Fields can be self-validating, and can show up as edit boxes, pop-up menus, drop-down lists, checkboxes, radio buttons, calendars, and more. Conditional formatting is also available. Filemaker Pro 11 can access databases up to 8 terabytes in size. (Of course, first you’ll need an 8 terabyte drive....in 2013, or thereabouts.)

Filemaker Pro has event-driven scripts, built out of many small functions. It’s kind of like assembling Lego bricks. Each little brick is simple in itself, but you can assemble a multitude of them to produce marvelous structures. Once you get the hang of it, thinking that way, it’s fairly easy to create a very powerful interface, data structure, and program behaviors.
Scripts can respond to a variety of different events. To make Filemaker Pro do something interestingly arcane (like having a button change color depending on the specific state of a record) sometimes requires a little thought—Where do you find the state of the record? Where do you store it? What triggers the script?—but with a little jiggling, a little juggling, you can make Filemaker Pro 11 do just about anything you want.

The advantage of being able to create your own database is that you can make it work the way you want, and adjust it as your needs change. You get to determine your own interface, any special fields you need, what your reports should include, and how you want them to look.

Not everybody needs to create their own databases, but for many people Filemaker can be an essential tool. I use it to catalog my own ever-growing bibliography (including book covers), to create a much more powerful to-do list manager than anything else I’ve found, to catalog my CD collection (including album covers), to compile quotes for a quotebook, for tracking and generating passwords, for managing auctions, and most spectacularly (when the Assistant Director flaked out) I used it to create a detailed shooting schedule for a feature length script in less than two days. And once, just for the fun of it, I even used it to create a political babble-generator.

Filemaker Pro is a powerful database environment, rationally priced and easy to use. You’ll likely end up finding new applications for it, far beyond your original needs.