storeFile(); $cgi->store('-link', 'Home'); $userName = $cgi->get('userName'); $passWord = $cgi->get('passWord'); $fm = & new FileMaker(); $fm->setProperty('database', 'GUESTS'); $fm->setProperty('username', $userName); $fm->setProperty('password', $passWord); $fm->setProperty('hostspec', FM_HOST); ExitOnError($fm); $layoutList = $fm->listLayouts(); ExitOnError($layoutList); ?> Data Design Pros
get('-link'); if (is_null($storedLink)) $cgi->store('-link', 'Home'); include_once 'navigation.php' ?>

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

 

Editor's Note: Some things change, but Jonathan's enthusiasm never does. It has been about eight years since Jonathan and I watched Andy Frederick perform some computer miracles on FileMaker. I remember Jonathan's "Wows" of excitement then. Much of his early practice was on our own LAMALUG 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.--Carole Groves

 

FILEMAKER PRO 3.0 GOES RELATIONAL

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 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 old which way you want.

For example; 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 do 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, version 3.0 of FileMaker Pro 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 on a customer. The customer tells you he's moved. Now you have to change his address in the Ordering file and open the Customer file and change his address there, too--IF you remember. Maybe the customer is also a friend. Open and change his record in you Friends file.

Instead, define a 'relationship' to the Customer file and place the related fields in the Ordering file. Now when you make changes in the Ordering file, you also update your Customer file instantly and automatically (extremely important if you're on a network). The information is only stored in one place so you save file space, too.

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 Club's 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 looks into one or a number of 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 has a clue because he was a professional programmer at one time and still does some occasionally. He's also used ACTUS' 4D which has even more scripting capabilities than FMP3. But John thinks it is not as easy to work with 4D.

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

Complaints and Wishes
When I first started with this version of FMP, I kept wishing for things that turned out to be in there. Nevertheless, I do have a wish list.

I'd like to be able to print out a list of buttons and fields in any given layout. 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 (which already has all the same fields). 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 a good place for a little warning screen to pop up. I created a calculation field using fields in the file I was calculating. Later, I converted it to include a field from a related file. Without any warning, FMP3 also changed my calculation to an 'unstored' number. This meant as I worked in the file, my totals didn't change when I changed numbers that made up the total. It took me two hours to figure out what had happened.

You can't do 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 show up 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 background 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 color.

This be the one
I'm really not listing all the improvements in FMP3 over the last 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 breath taking depth to accomplish almost anything. And it allows for many possible solutions to a problem. If you can imagine some thing 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 pro gram to get the full use of it. All along the journey are great rewards. It allows you to be creative-and what you create is art. What you see on the screen can look completely different depending on who has set it up. There is no reason you have to be looking 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 pro gram. 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 freedom 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 basic program is only 800k! How great! FMP3 sells for $199 (or $99 for an upgrade).

Many companies upgrade their pro grams. 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 now!

I'm building FMP3 databases professionally. Many people think it's easy to build something in FMP3. Well, it is-and it isn't. It's easy if you'll be using it like a basic Rolodex. But 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!)

You can learn FMP3. But maybe you would be better off 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