Introduction to FileMaker Pro

by Alan Graham
02/14/2003

We've been discussing for some time the possibility of starting a new section on the O'Reilly Network to cover the popular database application FileMaker Pro (FMP). The time has finally come.

Whether you are an existing FMP developer or interested in getting your feet wet, you'll find this section informative, helpful, and compelling. We'll be covering just about every aspect of FMP and expanding into a few directions that haven't been effectively explored.

About FMP

FileMaker Pro is a relational database application that has been around in some incarnation since the 80's. Although it is often recognized as a friend to Mac users, many people aren't aware that FMP solutions run on multiple platforms including Red Hat Linux, OS 9 and X, and Windows. With millions of registered users, including NASA, Lockheed Martin, JC Penny, and United Airlines, FMP solutions are powerful, yet simple to develop. In fact, every purchase you make in any Apple store is entered into a FMP database.

What is a relational database?

Think of a relational database as a collection of files (other individual databases) that, although separate, work together to share data. They do this by using a relational link (relationships) between the files.

Imagine you have a database solution that consists of a Contact Manager, Invoices, and Inventory. Each customer is automatically assigned a Customer ID number when entered. Now when you create a new invoice for a client, you want a number of things to happen automatically.

• You want the contact information to be filled into the invoice automatically.

• When you enter the product/item ID number into the invoice, you want the description of the item and the price to be entered automatically.

• When you complete the invoice you want the Inventory database to reflect the sale and automatically deduct the items sold from the inventory.

• You want to record who placed the order, when, and even where (if you have multiple locations).

• You want to automate the calculation of shipping and handling costs as well as tax.

• You want printed copies of invoices and receipts generated automatically.

• You want shipping departments to be notified of orders and be able to locate the items quickly.

• Once the item is shipped you want to notify the customer via Email that it has gone out the door and provide them with the tracking information.

• You want to be able to recall all this data in moments if the customer calls with questions.

• And finally you want to be able to create and print reports at the end of the day/week/month/quarter/year that contain all this information in a format that makes sense.

How does the database know how to do this? You create a common relationship between the different files and then build scripts to handle the automation. In relationships, when common data matches between these files, you can automate the sharing/entry of any information between them.

For example, by defining a relationship between the Customer ID field in the Contact Manager and the Customer ID field in the Invoice, when the two numbers match, the customer's info is pulled through to the Invoice. When the Inventory ID on the Invoice matches the ID in the Inventory database, the pricing and product description is pulled through.

When a new invoice is created from the Contact Manager, the Customer ID number allows the remaining contact data to be pulled into the Invoice database. When you locate the item requested in the Inventory database, the Inventory ID number is brought into the Invoice database, allowing the relevant product data to be pulled into the Invoice itself. When the quantity ordered (not shown) is entered into the customers invoice, the Inventory database will subtract those items from the stock records.

Once you become accustomed to how data can be moved and manipulated across multiple files, you begin to see creative solutions for practically any type of problem.

Advanced Automation

Creating a FMP database is building an application within an application. It can behave just like any other stand alone application, such as Word or Excel, however it doesn't require programming thousands of line of code.

At the core of FMP you'll find a diverse scripting language that allows you to create complex automated task with relative ease. These scripts give you the power to mimic the performance of a stand-alone application within a stable architecture that allows you to build solutions in record time.

Scenario

Let's say that it's the end of the financial quarter and each department must supply their records to multiple people in different departments and locations. With FileMaker, you could easily script your solutions to automate this process.

• Department heads can pull the data that is critical to them without having to track down and tie up staff from other departments.

• Each department can have reports designed to work to their exact specifications.

• Documentation templates and be built to reflect the corporate identity.

• Reports can be automated to trigger when specific criteria are met.

• Email notifications and reminders can be sent to anyone in the database.

• Reports can be automated to print to multiple locations, multiple printers, multiple media formats, regardless of location.

Copies are automatically printed in other locations.

When the VP runs the report, each person specified to get a copy has one printed in their location and on their specific media formats.

When data matches certain criteria, a FMP plug-in can trigger multiple scripts to perform a variety of automated tasks. In this diagram, when certain data meets specified criteria in multiple files, items are automatically printed (reports, letters, envelopes), and emails are generated and sent.

Why FileMaker?

For 10 years I've been developing advanced database applications for just about every type of business. From Internet service firms to mail order shipping systems to architecture and project planning, I've seen just about every type of problem. To date, I've yet to find a database application that can match FMP for price/performance. I'm able to build complex solutions in a fraction of the time and cost it would take to roll out a comparable solution on a competing platform.

In addition, FMP has the tools that make IT departments happy. With support for data standards like XML, ODBC and JDBC, I know that my clients can connect to corporate data stores and perform SQL queries. FMP also supports barcoding and works with both the Pocket PC and Palm products.

FMP is barcode-compatible and works with Pocket PC and Palm OS.

If that weren't enough, according to FileMaker, sixty of the Fortune 100 firms have volume license relationships with FileMaker, not to mention 47 of the top 50 universities in the U.S. So without sounding too much like a cheerleader for the company, I wouldn't have stuck with the program all these years if it didn't deliver the goods.

More to come...

In the future we're going to cover a wide variety of topics from starting from scratch, solving complex problems, creative solutions, UI design tips, web solutions, Java, and much more.

Alan Graham is the creator of the Best of Blogs book series and is a frequent writer on the O'Reilly Network.

---

Return to the Mac DevCenter.