Productivity solutions guide
http://www.macworld.co.uk/filemaker
A database is any organized collection of facts. Two great examples: the telephone directory and the card index at a local library. Both are collections of facts—the first tells you about people who have telephones, and the second about the books in the library. In both cases, the data is sorted into a particular order; by surname, or book title. And both have been around long before the arrival of the computer.
The library’s card index is an excellent analogy for understanding how a FileMaker database is put together, and it’s also a fantastic example of why it’s useful to store your database on a computer.
Suppose you’re looking for a book by Agatha Christie. You go to the card index, open the ‘C’ drawer, and rifle through until you get to CH. However, what if you can’t remember the author, but you know the book was called “Cider with Rosie”? Most libraries have two sets of index cards—books sorted by author, and books sorted by title. What if you want to find all the books to do with computing? Then you just have to hope that the library has yet another card index, with the cards sorted by Dewey Decimal number. This duplication of information poses a number of problems.
You need to fill out two (or more) copies of each index card and file one under author, and one by title. You might make a mistake on one of the cards, or when you come to sell old stock, you might only remember to remove the index card from one of the indicies.
Physical card indexes take up space, take time to edit, and are prone to what are called errors of integrity: one card index says the book is in the library, the other has no mention of it—which is telling the truth? With a computer-based database like FileMaker, you need only store the book’s details once, and the computer creates as many indexes as you need for you—which means you only have one record to edit or delete.
In the library, each book has its own index card. In a FileMaker database, the information about each book is stored as a record—one record for each book. To properly complete an index card, you need to fill in certain bits of information, usually by writing on a dotted line. Each dotted line has a heading identifying what exactly needs to be filled in, Author say, or Title, or Publisher. The answers taken together uniquely identify each book. (Where there’s more than one copy of a popular book, “Harry Potter and the Ring-binder of Destiny” for example, then each book might have its own serial number as well.) The same is true for a FileMaker record—each required piece of information is called a field. A book record might have fields for author, title, publisher, publication date, and whether or not it’s in the outsize section.
An important difference between a FileMaker database for, say, a library and a simple card index, is that if you designed a FileMaker database for a library, you might also use it to store information in it on library staff or users. You could go a step further, and perhaps store information about who has which book.
All records of a particular type (ones that share the same set of fields) are grouped in a Table. A library database would therefore need tables for books, staff, users and loans.
FileMaker Pro is a relational database—meaning that if you have lots of tables, it makes it easy to tie the records from appropriate tables together. What sort of index card would you need to create if you were to build a paper-based loan system? Each card would need to have details about the book being loaned, the person borrowing the book, and the date on which the book was due back.
By specifying how the various tables in a database relate to each other, FileMaker can do most of the work for you. Instead of copying out names and book titles in longhand, you specify who the loan is for (from the users table), which book is being borrowed (from the books table), and what the due date is. In other words, you only need three pieces of information: a book number, and user number, and a date. FileMaker can simplify things by providing a list of books to choose from, and a list of users as well. Using something called a calculation field, (which you specify with a formula similar to a spreadsheet), it can even fill in the due date for you by adding 14 days onto today’s date.
Perhaps you want to send each library user a reminder when their books are overdue by a month. With FileMaker, it’s easy to find those loan records that where due last month—and from there it’s easy to print out each related user’s details, formatted as an address label, and stick it onto a postcard. You can even automate the procedure using a script.
By storing information on a computer instead of paper, it’s easier to manipulate the data. And manipulating raw data is how you obtain information. Who wrote the most books on the library bookshelves? You could work it out, but it would take you a long time. FileMaker can print you a list of authors, sorted by the number of titles in the books table, in reverse order, highest to lowest.
With FileMaker Pro 7, you can store as many different tables as you need, all in the same database file. A database file will also contain other information such as layouts, scripts, passwords and access privileges, value lists and so on. But the important thing is that it will contain your data, stored in an orderly fashion, ready to work for you.
1. Each card index corresponds to a FileMaker Record.
2. Each box to be completed is the equivalent of a FileMaker field.
3. The entire collection of index cards is the equivalent to a FileMaker table.
Creating a database in FileMaker is easy. Give each new table a name. Specify the names and types of fields that you need for each table. Then use drag-&-drop to create relationships between your tables. Don’t worry if you change your mind: you can add, edit, or delete relationthips, fields, or even tables at any time.
Every database must include at least one table.
Each table is made up of a list of fields and the data that goes into those fields.
To model more complex things, you may need several tables that inter-relate.