First appeared in The Apple COREspondent
Newsletter of LAMALUG, Mac User Group of Lansing, MI


Editor's Note: Some things change, but Jonathan's enthusiasm never does. It's been about eight years since he and I watched Andy Frederick perform some computer miracles with FileMaker. I remember Jonathan's "Wows" of excitement then. Much of his early practice was on our own LAMALUG (Mac User Group) database which we worked on together to enhance its capability for tracking our membership--he, as treasurer and, me, as membership chair. A mystery, a puzzle, a challenge... Jonathan doesn't need computer games because he has FileMaker.

Thanks to both Jonathan for his thorough presentation, and to Deborah, for the baked goods following the October meeting. The pictures of cakes made us wish for samples and Deborah's Bake and Cakes provided them.




OK, I've gotta be fair with you. I may not be the best judge of this program--because I'm addicted! I got FileMaker Pro 3.0 in May and haven't been able to stop long enough to write this article until now.

About the time I got the program, an opportunity came my way that would really put FileMaker Pro 3.0 (FMP3) thru it's paces. My wife's bakery had grown so much that they needed a better way to keep track of cake orders. Once they saw what the computer could do, they decided they wanted to track inventory, do billings, and have their employees punch a time clock on the computer too. The report is; this software is awesome!

What's a database
For the completely uninitiated, let's back up for a second. A database is sort of a glorified Rolodex. In a Rolodex you have a line for the name, one for the address, one for the phone number(s), and room for notes. In a database, you have 'fields' instead of those lines. [ For another discussion of databases go to the article
Dirtywork with Databases.]

So what's the advantage of a database? Well, for one thing you can have as many fields as you can dream up. Fields can be as large as you like. (I use some fields that are the complete text of a letter, and this article started in a 'Notes" field). You don't run out at the bottom of the little card. Another advantage is that you can find information any which way you want.

For example, let's you're trying to get in touch with a salesman about a product. You remember his first name is George, but you forgot his last name. With a Rolodex, you're stuck. You have to look at every card. In a database you can perform a 'find' for the name George in the first name 'field' and just page through the few that come up. Heck, you might have forgotten his name completely but you remember something about the product he carried that you'd typed in the 'Notes' field. Bingo!

It's Relational!
Along with many other improvements, FMP3 includes the long-awaited addition of relational capabilities. Well, long-awaited by some people. But I couldn't imagine what I'd want a relational database for [This is a hint for the Claris advertising crew. I have yet to read anything that made me think I needed the relational part. Now that I've worked with it, I don't know how I lived without it!

Older versions of FileMaker allowed you to automatically look up information in other files. Lookups are still available (and important for certain work), but lookups have their limitations. For example, let's say your Ordering file looks up information from the Customer file. The customer calls to tell you s/he's moved. Now you have to change the address in the Ordering file and open the Customer file and change the address there, too--IF you remember. Maybe the customer is also a friend. Open and change the record in the Friends file.

Now instead, you can define a 'relationship' to the Customer file and drop the related fields into the Ordering file. Now when you make changes in the Ordering file, you also update your Customer file instantly and automatically. The information is only stored in one place.

There are some other clever tricks you can perform using relationships. At the bakery we use it to see old orders by our customers. We can tell how often they order, and if we've made mistakes on their orders, or if they're a problem customer. We can even track how long it takes to place the order. (Is the customer fussy or do we need to work with our employee to show how to help a customer make a decision more quickly?)

In my entertainment business, I had a problem with my Clubs file. I used a repeating field to track people with whom I'd talked in the past. When the repeating fields were full, I had to cram the information into the last repeating field where it didn't show up when I needed it. Now I can have an unlimited number of people show up in a scrolling 'portal.' (A portal is like a little window that displays records in another file.)

Using the same tool, you can make a file have a relationship with itself (thereby uncomplicating its social life ; ). By placing a scrolling portal in the bakery's ordering form, we can see if the customer is supposed to be picking up more than one cake on a given date. What happens is, the person who orders may send someone else to pick up the cakes. If we don't know how many cakes were ordered, sometimes they end up making two trips. Not good! Doesn't happen any more!

More Power
Although I've been focusing on some of the benefits of its relational feature, FMP3 has a number of other new features. Among my favorites is its tremendously improved scripting power. A 'script' is simply a series of steps you want FMP3 to perform. A script may be as simple as "Go to Layout 'Labels"' Or you can really have fun; "Copy the phone number, go to HyperCard, dial the number, come back to the address file, go to the Notes field, put in the date and time, and wait for me to start taking notes on the call." This is really programming, but it's easy to learn.

John Rummel from the Library of Michigan Office of Public Information (and former President of LAMALUG) said that FMP3 scripting offers people with minimal programming experience the ability to manipulate data in a more powerful, intuitive, and user-friendly environment than anything else out there. John knows what he's talking about, because he was a professional programmer at one time and still programs occasionally. He's also used ACTUS' 4D which has even more scripting capabilities than FMP3. But John thinks 4D is not as easy to work with.

You can change the order of layouts and make some layouts inaccessible from the pop-up menu when in Browse mode. And, you can do some great tricks, and with passwords, you can provide safety nets against curious employees.

Complaints and Wishes

Of course I can make "screenshots," but I'd like to be able to print out a list of buttons and fields in any given layout from within the program. I'd also like to be able to print out the relationships.

I'd love to be able to copy an entire script with all the steps into another file. And while I'm wishing, I'd like to be able to copy the information about the script steps into a database so I can check which scripts I need to change that refer to fields I may be deleting from another file.

You can print out your scripts, but they don't indent the same way they do when you look at them on screen. That makes it difficult to follow "If' and "Loop" statements.

Any time a file is duplicated or copied from one disk to another (without the "Save As" command), FMP3 says the "file was not closed properly" and performs a "consistency check." Now that I'm used to it, the message is only irritating. But the first couple of times I saw those words, it was downright scary. If FMP3 wants to perform a consistency check, that's one thing. But it seems to me that a copied file is "properly closed."

I found one place where a warning screen would be helpful. I created a calculation field using fields in the current file. Later, I converted the calc to include a field from a related file. Without any warning, FMP3 changed my calculation to an 'unstored' calculation. This meant as I made changes to some of the numbers, my totals were no longer correct. It took me two hours to figure out what had happened.

You can't perform a sort in a portal. [This has been changed in FMP4.0] That means that as you add items to an external file that show up in your current file, you can't make them appear alphabetically. Oh, you can create a 'clone' of the external file, export the list and re-import it. But that seems like an unnecessary pain.

I'd like the Pop-up List to be sizable so you can choose the number of repetitions the way you can on portals.

Right now, you can make a field have a see through background. But it has to be the same color as the layout background (screen gray) I'd like to be able to have the choice to make the fields and graphic elements see-through or opaque.

This be the one
So far I haven't listed all the improvements in FMP3 over the previous version. That's really not fair to Claris, because my wish list above could mislead you into thinking this isn't such a great program. That's really not the case.

For the novice user, FMP3 is simple to start up. But it has the breathtaking depth to accomplish almost anything--except make ice cream. And it allows for many possible solutions to a problem. If you can imagine something you want FMP3 to do, you can probably do it.

As with any tool this flexible, you'll have to spend some time with the program to get the full use of it. All along the journey are great rewards. It allows you to be creative--and what you create can be beautiful. What you see on the screen may look completely different depending on who has set it up. There is no reason you have to look at a page full of gray boxes.

The manual will get you started, but that's it. The examples given don't come near the depth the available in the program. On the other hand, I've always been one to use tools in unorthodox ways. (Darn! The hammer is upstairs. Humm... The side of that wrench might be able to drive that nail.) You'll have to rely on your own creativity and maybe some other books to get beyond the basics.

Some explanations were hard to find in the manual (and also on the CD-ROM version which allows a little more flexibility with it's index). I simply had to figure out many things by myself. Maybe that's the nature of the creative process when you go beyond the basics.

You get 3 disks (or a CD-ROM that includes a tutorial for the basics and some example files). As amazing as it may seem, the program is only 800k! How great! FMP3 sells for $199 (or $99 for an upgrade).

Many companies upgrade their programs. But not many upgrades have this much impact on the product, MacUser gave FMP3 4.5 mice. I'd go along with that. If the rating had been 5 mice, would have probably moved my bed next to my computer, and you wouldn't be reading this review!

Although creating a simple file in FMP is fairly easy, things can get complicated, depending on your use. If you want to use it to keep track of orders, you might be better off hiring somebody who has put in thousands of hours learning how to avoid mistakes that can cost you. (Pick me! I'm building FMP3 databases professionally.)

Yes, you can learn FMP3. But maybe your time would be better spent finishing some other items on that endless 'To-Do' list that small business owners seem to accumulate. Call me at 694-8372.

© 1996 Jonathan Stars

< Back to In the News


Email us here.