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
JUNE 1992

 

DIRTYWORK WITH DATABASES

When I first got my Mac back in 1986, I had a very specific use in mind - making music. I had heard that you could also use computers for business and that sounded OK, but I didn't know how. Then a friend told me about databases. I want to be that "friend" to you and introduce you to the concept. If you are already a database user, I may have some new ideas for you.

My pet program is FileMaker. The same concepts will work with most databases. But FileMaker is designed to be the easiest to use. Don't let ease of use make you think it lacks power though. It is outstanding in power and flexibility. Anyway, I just want to give you an idea of what databases are and what they might do for you.

[This next part is a little technical, but it'll only hurt for a little while. For a simpler comparison of a database to a Rolodex file go to the article FileMaker Goes Relational.] To say it very basically, a database is a program that allows you to save and find information. You can then group and analyze your information and get summaries (especially of numbers).

The information is organized in 4 ways;

1) Files: A file is similar to a file folder. It's a place to keep related information.

2) Records: A record is all the information you keep on a person, item or transaction. A file would consist of a number of records - like the pages in a file folder.

3) Fields: A field is the place you keep specific parts of a record. Name, address, and phone number are examples of three often used fields.

4) Layouts: A layout is a way of viewing the information you've collected, both on screen and on paper. [Now that the icky stuff is over, let's get down to what they can do for you.]

I made a file I called "Phonebook" to keep track of the people I come in contact with. Aside from the usual fields consisting for Name, Address, City, State & Zip code, I've included some other interesting fields. I have a "Notes" field where I keep general info on the person. You're not limited to just a few words, you can type huge amounts of information here. In my "Letter" field, I can type the person (guess what) a letter - without having to quit and go into a word processing program. Of course you can cut and paste just like any other program. So you can take part of the letter to one person and include it in a letter to another person. A "Codes" field has just numbers in it. The numbers refer to specific topics of interest. For example a "1" might mean the person is a musician and a "2" might mean they are an artist. And, of course, I can create mailing labels in this file.

In FileMaker, fields can contain text, numbers, dates, pictures, calculations, and summaries. I used the pictures format for a field and created a file called 'Pics'. In this file I keep the same kinds of pictures you might keep in your scrapbook file. But I can view more than one clipping at a time. I also use codes to identify similar pictures, and there is a field that describes the pictures. It's really easy for me to find all pictures that deal with stars, for example.

Once I got the hang of calculations, I started using them a lot. For example, in my customers file I can figure out what a club is likely to earn when I do a show for them. I just type in their seating capacity, ticket price, dinner charge, and any free drinks. That way I can determine if I should charge them more or less than my usual fee. Before MacInTax, I made my own file to figure my taxes for me. I even made one that helps me figure postage - the fewest number of stamps of which denominations.

I'll be working for a bunch of doctors in Reno at Christmas time. I need some doctor jokes. I'll just open up my "Comedy" file and perform a find for the words 'doctor', 'nurse', and 'hospital' to find the jokes I need.

Where did I put that old LAMALUG membership file? I look in my "Disks" file. Since I keep all my disks in numerical order, I can find the disk.

I keep a mailing list of people who want to be notified of my upcoming shows. I improved that one recently. I have it look up the city and state (from another file) when I type in the zip code. And I have it find duplicate entries as well as sort and count by zip so I can take advantage of bulk mailing rates.

I save magazine articles I find interesting, but I could never find them when I needed them. So I created a "Magazine" file. I cut the articles out and put them in a 3-ring binder & use my own page numbers. Now I can find any article and I often find quite a few related articles. Someday I hope to scan all the material into the computer and do away with the 3-ringers.

Did you ever put something away really well? So well you couldn't find it? Now I code my 10 favorite locations and keep track of what goes into them. All I have to do now is remember to update the file when I move stuff. So far I haven't done too well with that.

A large insurance company located here in town had trouble keeping track of their case folders as they moved around the building. Some files would be missing for up to a month! They came up with a system of attaching bar code strips to folders and locations such as employee's desks. Two times a day all folders are scanned along with their location. That info is transferred to their database. That way most any folder can be located within 1 day. (Update: These days what used to be paper documents are on a central computer server and available to everyone in the company simultaneously. Approval signatures are handled electronically.)

One of our members has a company that sells food and medication for exotic birds to pet stores nation wide. They asked me to create a system for taking orders by phone from their customers and generate the billings and mailing labels. It had to include automatic lookups of prices, shipping rates, discounts for large orders, previously recorded customer information and to generate quarterly reports for tax purposes. I think it works pretty well, but you'd have to ask them how they like it.

I use FileMaker and my databases every day. And I really enjoy creating new files. These are my computer games. See what a database can do for you.  

© 1992 Jonathan Stars