Book Review

FileMaker Pro 7 Bible

Steven A Schwartz and Dennis R Cohen Wiley Publishing Inc ISBN 0-7645-4347-4 901 pages + CD-ROM

FileMaker is, perhaps surprisingly, the world’s biggest selling database software but I have to confess that until I recently started using it with a client I had never heard of it.

The objective of this book is to enable its reader to be able to start using the FileMaker database software from scratch and by the end of it, be a competent user of it. It really works!

The back cover of the book also says that it is aimed at intermediate and advanced users: intermediate users I can agree with as they will probably want to use it for reference purposes if nothing else. Advanced users will probably not find a lot of use for this book, I think.

The book is arranged in seven parts:

1. The Fundamentals
2. Database Design Basics
3. Working with databases
4. Putting FileMaker Pro to work
5. Mastering FileMaker Pro
6. Developing Databases for others to use
7. Appendices

Whilst essentially this is a logically arranged book, I found that whilst the 53 page long chapter five, defining fields, is one of the most vital chapters in the book, it repeated aspects of chapters 1 and 2: what is a database? and FileMaker Pro basic operations respectively; and I could have done without that.

The basic style of the book is to introduce an idea or concept that the FileMaker has been produced to resolve and then demonstrate how FileMaker does what it does. I think they have done this very well and I worked through every example they presented. This meant that I had the computer switched on and running FileMaker version Pro 7 as I read the book.

I would say that the authors have been extremely thorough and I haven’t made any notes in the book suggesting errors or corrections required.

Having been faced with the task of designing and implementing a database driven system for a client, I needed to learn FileMaker very quickly. I found the book and within a week had read and worked through the first 600 pages and felt in command of the new database that I was already developing by the end of the week. I cherry picked the rest of the book as I needed to.

During that first week I had set up two separate databases: I could have developed them as two tables within one database but chose not to. Those tables talk to each other so that I have now got relational databases: tables that talk to each other.

I also built into my work a few calculations: formulae that look and behave remarkably like spreadsheet formulae. Given my spreadsheeting background, I was able to prepare and use a wide variety of formulae/calculations easily and quickly.

The two main databases I prepared were

• Client database: at the time of writing there are over 250 clients
• Customer database: at the time of writing there are over 350 clients and they are increasing at the rate of around 10 – 15 a day

Subsidiary databases or tables include

• Agents
• Invoices
• And one or two subsidiary tables

Based on the data, there are reports to produce, so far they include such things as:

• automatically generated advertisement for clients
• automatically generating codes for candidates
• automatically generated invoices

For anyone who is contemplating using databases, I believe that a knowledge of a spreadsheet will stand you in good stead. Databases are set out in tables in the same way that a spreadsheet is so knowing a spreadsheet really does give you a good start with a database. The value added by a database over a spreadsheet is that databases can be designed to be based on forms rather than large tables. This means that inputting data can be a real breeze with a database relative to a spreadsheet.

Reporting in databases can also be very convenient. For example, in one database I might gather, say, 50 or more variables and yet I might only want to report on three or four of them at any one time. The next report I generate might use 25 variables and a final report might use just 10 variables.

Setting out a report is also extremely simple using a database and they can be made to look really simple or nicely decorated.

Here’s a simplistic view of a table or report in a database: it looks just like its spreadsheet equivalent:

The power of the database over the spreadsheet starts to become apparent when we start to explore, or mine, through our data. The way we need to work now varies radically from database to spreadsheet: imagine that we want to look only at variables A, D and F. We might see a report like this:

Database version	Spreadsheet version

It’s difficult to spot the difference between the two except that I have exaggerated the view of our results. I have exaggerated for a specific reason, however.

With the database, I simply select variables A, D and F by means of a search or query that effectively hides all other data until I unhide it; and I can unhide it all just as easily as I hid it.

With a spreadsheet I have to do one of

• move the columns around and isolate the three variables I need
• copy the spreadsheet and delete the variables I don’t need, leaving the data that I do
• copy the data from variables A, D and F onto another sheet
• write a macro or routine that will highlight or select my chosen variables
• hide all variables except the ones that I need using the spreadsheet’s hide utility

I have illustrated the final of the options I mentioned. All of these options will give a result along the lines of the one I want and the end user may be perfectly happy with what they are given. The spreadsheet, however, is nowhere near as convenient to work with even with such a simple example as we are showing here.

With a database I can even change my report to demonstrate my chosen variables and, with little more effort, present them in a different style:

Again, it is possible to achieve the same aim with a spreadsheet: in Excel use the

• copy
• edit
  • paste special
  • transpose

series of commands to do that.

The relational aspect of databases is also a major feature of them. In one database, as I have already mentioned, I might have 50 bits of data relating to my clients. My customer database contains, say, 35 bits of information about them. Then there are overlaps between client and customer … I then link the two databases in a very simple and convenient way and lo and behold I set up sections within one of the tables to read from the other thus saving typing time, time in looking up data from one table and then entering it in the other table.

The relational aspects of a database are often best illustrated as follows, a graphic of the tables that are related or made to relate to each other. In this case, I have taken a screenshot from a simplified accounting database I put together:

Having got this far, I was haunted by the words of a computer geek from many years ago, “Why are you bothering with a spreadsheet? One day you’ll learn that databases are much better.”! He was right.

Here’s the input section of the customer database: this is just information gathering.

Those big boxes at the bottom are containers: they are fields that can contain photographs and files or links to files such as word processor, presentation and spreadsheet files. You can, of course, also link to ZIP files … any other file you like.

There are also calculation fields that give the age of the client given the date of birth. The right hand big box is a text box in which we can enter any other notes or ideas as relevant.

The client database is not so pretty but it’s neat and tidy and it looks like this:

The data gathered here includes name, address, telephone number, any visits to them. There are also calculation fields that give the age of the client given the date of birth. The big boxes here allow us to enter additional details and comments.

We are also linking this table to the customer database so that an entry made in the customer database can be made automatically in this database.

What else can an accountant use a database for? Well, the number of potential uses of a database is legion:

• Ledgers
• Accounts
• Ratio analysis
• Management accounting statements
• Stock control and valuation
• Asset register
• The list is almost endless

Just to see how easy or difficult it was, I set up a simple set of accounts in a simple database and it proved very effective. I did this with just one month’s database experience.

The CD-ROM that comes with the book contains the author created materials including examples to accompany exercises in various chapters. The CD also contains a wide range of applications, including software utilities of interest to database developers and users.

Compared to many book related CD-ROMs I have to say that I find this one rather disappointing. Remembering that the book itself is aimed at the novice and intermediate user alike, it is disappointing that there are so few examples and specimen databases on the CD. For example, there is an example address book database to accompany chapter four; and there is a summary field tester to accompany chapter five.

Why do I feel the need for more examples on the CD? Well, by seeing how other more experienced people put their databases together, we can see how they design them, what features they have used, any shortcuts and other tricks they have used. So, four specimen databases aren’t enough: it’s a 900 page and seven part book and the authors must have developed dozens of databases in the time that they took to write the book.

One other feature missing from this CD that other book related CD-ROMs often have is that the book itself is not on it. In my experience, it’s normal for the publisher to put the entire book as a PDF file on the CD in case the reader finds it useful. It’s not on this one.

One very reassuring aspect of the FileMaker software is that it is accompanied by this book and a relatively large number of online resources. Browsing various bookshops over the last few weeks, I have found at least half a dozen books aimed at the FileMaker range of software.

As far as online resources are concerned, they typically include free materials and commercial materials. Just do a search for FileMaker in your favourite search engine and you will find many such resources.

Do I recommend the book? Yes, in spite of the lack of files and example given on the CD-ROM, FileMaker is a popular database package and there are lots of resources to be found on line and in other books. For example, take a look at http://www.filemaker.com/ … here is a screenshot of their home page:

If you click through on the FileMaker Web View Gallery (just off the screenshot!) you will find examples of application of FileMaker in the real world:

Police Pro Maps

This example uses the address, city, and state data that is stored in the Police departments FileMaker Pro database. It gives the responding officer an overhead map of where the call is in relation to other streets.

Package Tracker

Get package tracking details from your favourite shipping Web sites using the FileMaker Web Viewer. Shipping details automatically display based on tracking numbers stored in your database!

The Wall of News

This example shows how you can view news stories from a variety of sources on one screen and keep notes in FileMaker regarding the content. It was developed for a TV production company which creates current affairs programming.

Dynamic Charts with ASP.NET

This example produces a JPG chart from FileMaker data.

Global Routing

This example displays routing and distance information using airport codes stored in your database linked to a Web site that displays distances between airports and other useful airport data.

This book is well written and comprehensive and it meets the objective of having been written with the beginner and intermediate reader in mind. In spite my reservations over chapter 5, this book can get you up and running within a week … it did it for me!

Duncan Williamson
21st September 2006