First appeared in The Apple
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
FILEMAKER PRO 3.0 GOES
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
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!
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
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!
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
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"
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
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
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
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
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
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
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.