In this chapter
Introduction to Database Software
Overview of the FileMaker 9 Product Line
However you approach FileMaker Pro, some core strengths of the platform are important for all types of users:
Ultimately, FileMaker exists between the world of desktop applications and high-end, enterprise-level server systems. It is the third option: a flexible, robust workgroup application that can quickly come together, evolve over time, and be dramatically cost-effective.
In the world of software development, flexibility and speed are critical. We live in the world of Internet time, and usually businesses embark on a development project only when they need something yesterday.
The practices and experiences of the past two decades have proven software development to be a risky, unpredictable business. New job functions have developed in software quality assurance and project management. Certification programs exist to sift the wheat from the chaff.
FileMaker Pro exists in many respects to help organizations take on less risk and navigate the waters of software development without having to take on massive engineering efforts when they aren’t warranted. Because this is a rapid application development platform, it is possible to build a system in FileMaker Pro in a fraction of the time it takes to build the same system in more classic, compiled software languages or by using enterprise-level systems.
FileMaker Pro is focused around offering a low total cost of ownership for organizations. In October 2001, the Aberdeen Group, an independent research firm in Boston, found that “under conservative assumptions, FileMaker Pro was superior, with an average ratio of 5:1 in [cost of ownership] over the industry average database” (quote taken from the Aberdeen Group Executive White Paper “FileMaker Low-IT Database Cost-of-Ownership Study,” October 2001). Both the cost of the software itself and the rapidity with which systems can be built mean that IT organizations have a viable alternative to the massive enterprise-level systems of the past.
FileMaker Pro is now 20 years old. In the mid-1980s, Nashoba Systems created an initial version that was acquired and published by Forethought, Inc., in April of 1985. Nashoba then reacquired the rights to the software and published FileMaker Plus in 1986 and FileMaker 4 in 1988.
Claris Corp., which was then being formed by Apple and was to become FileMaker’s guiding parent, purchased Nashoba and published FileMaker II in 1988 and 1989. Finally in October 1990, FileMaker Pro 1.0 made its debut and set the product line on the course it has largely followed to this day. In December of 1995, Claris shipped FileMaker Pro 3.0, which saw the introduction of relational data modeling to the platform and, even more important, a completely seamless cross-platform application that’s virtually identical between the Mac OS and Microsoft Windows. Today a majority of FileMaker’s audience lives on the Windows side.
In 1998, at the time of version 4.1, Claris Corp. rechristened itself FileMaker, Inc. and focused all its energy around its flagship product. FileMaker has been profitable every quarter since (an extraordinary feat considering the climate in Silicon Valley for the previous few years) and continues to enjoy the backing (as a subsidiary) of a cash-flush Apple, Inc.
Other major innovations have occurred along the way, but nearly everyone in the community recognizes that it was the watershed version 3.0 that broke open the gates for FileMaker. Version 4.0 introduced web publishing to the platform, and version 6.0 offered significant support for XML-based data interchange.
In 2004, FileMaker Pro 7.0 was released. This major release featured a reengineered architecture from the ground up, a new model for working with relationships, modern security capabilities, and the capability to hold multiple data tables within a single file. Since then, FileMaker Pro 8, a major interim release of FileMaker Pro 8.5, and now FileMaker Pro 9, have continued the evolution of the product.
FileMaker, Inc., has sold more than 10 million units worldwide as of this writing. Users range from a single magician booking gigs in Denver, Colorado to Fortune 500 companies such as Citibank and Genentech. Just like any tool, FileMaker is noteworthy only when it has been employed to build something—and its builders come in all shapes and sizes. The only true common element seems to be that they own computers and have information to store.
There are some trends: FileMaker Pro is widely used in the world of both K–12 and higher education. All 50 of the top universities in the United States use FileMaker Pro. The nonprofit industry is also a key focal point for FileMaker, as is the creative-professionals industry.
At its heart, FileMaker Pro is database software; databases are useful for keeping track of contacts and their addresses and phone numbers, the students in a school, the sales and inventory in a store, or the results of experimental trials. Although this sort of information can be kept in spreadsheets and word processor documents, a database makes it much easier to take on these tasks:
There are other advantages to using database software, not the least of which is the capability in FileMaker Pro to construct a user interface that can map to an organization’s workflow. The members of an organization often outgrow the documents of desktop applications when they need to support multiple authors, track data in structured, interrelated ways, or manipulate data sets based on differing criteria. Often the first herald of the need for a database is when users are frustrated with not being able to find a given piece of information.
The rest of this book gets into detail on how to do everything just mentioned and much more as well. You’ll get a more detailed look at what a database is and how it works, how to build databases, and so on. But before we dive into the mechanics of databases, it’s important to understand how they—and FileMaker—fit into the overall software computing world.
A huge variety of software is on the market today. FileMaker generally falls into the category of business productivity software; however, it really is a hybrid application that marries desktop application productivity to a server-based architecture and database. It is as accessible as programs like Microsoft Excel and Intuit’s QuickBooks, yet it also allows developers to create complex workgroup databases that deploy in the same manner as other IT server-based applications.
The idea of managing a collection of structured information is what database software is all about. Some database products on the market manage specialized collections such as business contacts. Products such as Act and Goldmine are good examples of those. Quicken, QuickBooks, and Microsoft Money manage collections of financial transactions.
FileMaker and other nonspecialized database products such as Microsoft Access are used to create database systems just as word processing software is used to create specific documents and Microsoft Excel is used to create spreadsheets. In fact, Microsoft Excel is often used as a database because it has several strong list-management features. It works well for managing simple databases, but it doesn’t work well in managing multiple lists that are related to each other.
Often, simple grids of columns and rows of information (such as spreadsheets) are called flat file or list databases. Simple databases like these are generally self-contained; they usually don’t relate to each other, so keeping information up-to-date across many such databases can become unwieldy or impossible. In such cases a relational database is called for. FileMaker is a fully relational database system and allows developers to associate a row (or record) in one area of the database (a customer list, for example) with records in another area of the database (a list of purchase orders, for example). To take another example, users of a relational database system can tie a single company entry to multiple contact people or even associate a single person with multiple company entries. Rather than entering this information in a dozen different places, relational databases, using a form of internal addressing, simply associate one item with another (customers with their orders, companies with their contacts). It is in this way that FileMaker gradates from a single-user productivity tool to a fully realized database development platform.
There are many relational database products on the market: Specialized products such as Act and Quicken are also relational database products, but the difference is that those products are finished systems, offering a specific set of functionality, whereas products such as FileMaker are tools, used to create custom systems tailored to the individual needs of an organization or a person.
It is certainly possible to re-create the functionality of Act or Quicken by using FileMaker, and some organizations choose to do so when faced with the fact that such specialized products are relatively inflexible. If an organization has nonstandard ways of doing things, its members might find it difficult to work with specialized products. Although FileMaker Pro comes with several database templates that might be perfectly suitable for an organization to use right away, most users instead turn to FileMaker to create custom database systems that exactly match how their organization operates.
With a database development tool such as FileMaker Pro, a person can build a system to be exactly what is needed. It’s the difference between buying a house that is a pretty good match and building a custom home that has exactly the features one wants (or at least can afford).
Home construction is actually a great analogy for building a database because both follow similar trajectories. A home has to be designed by an architect before it can be built. An owner has to wait for the home to be built before he can move in, and questions or issues often arise during the construction process. After the home is built, the owner’s needs might change and he might have an addition built onto the house to accommodate changed circumstances.
Building a custom home often follows a similar path: The foundation needs to be laid and the walls and plumbing need to be stubbed in before the final coat of paint can be applied to the drywall. Software development often is a complex layering of interdependent parts, and we in the software business don’t have the good fortune to be able to run to Home Depot for standardized parts at a moment’s notice! Often we need to build our own tools as well.
Finally, imagine that a home’s construction is well under way and the owner decides to move the living room wall six feet. Although that is always possible, the impact of that change will vary a great deal depending on the stage at which the crew is working.
This last point is an important one, and it is also where we diverge from the home construction analogy because real-world environments always change. This is especially true for today’s email-driven, connected-network world. One of the key advantages to developing database systems in FileMaker Pro is that these systems can be rapidly redesigned, even while the system is in use by other users. Any aspect of a FileMaker system can be changed while it’s live, if need be, although doing so might not always be advisable. FileMaker’s greatest strength is its inherent flexibility.
People frequently wonder when to turn to custom development, when to use built-in FileMaker Starter Solutions, and when to start their own development from scratch. All the authors have worked on a variety of FileMaker projects—from-scratch custom development, rehabs and modifications of old systems that no one quite understands, many training sessions, and the like. When it comes to custom development, each has his own point of view. Jesse Feiler provides his own simple rule of thumb: The closer any operation is to the core of an organization, the more it is a candidate for custom development. If your organization prides itself on customer relations, a customized contact management system might be for you, whereas inventory control can chug along quite happily with an off-the-shelf product or a FileMaker Starter Solution (perhaps with a few tweaks).
On the other hand, if your organization really shines at managing its complex inventory process, that might be where your customized software should be focused, and your contact management software may be a Starter Solution, an off-the-shelf product, or even your cell phone.
FileMaker is database software. The thing that makes it unique in the market is the ease and means by which it allows developers to present information, but it’s important to grasp the fundamentals of how all database software—including FileMaker—works. The simplest kind of database is a list. It could be a list of employees or products or soccer teams. Consider an employee example. The information a Human Resources department might want to keep track of could look like the information shown in Table 1.1.
In database parlance, a list like this is called a table. Crudely put, a table is a collection of like things—in this case, people. After a table for people is established, one might extend it to include other attributes (or columns) for, say, phone numbers. Table 1.2 shows the result.
Table 1.2. The Growing Phone Directory
For a thorough understanding of data modeling and the definition of tables, see Chapter 5, “Relational Database Design,” p. 163.
As mentioned earlier, this type of database is called a flat file database because everything is in one table. Although it’s nice to have everything in one place, this kind of structure has shortcomings. In this case, every time someone thinks up a new type of phone number to track, another column has to be added to the table. This is likely fine for phone numbers—in the real world people usually have only a handful—but imagine what would happen if the example were tracking people’s previous job titles? The spreadsheet or list would have a potentially unlimited number of columns, and there would be no logical correspondence between one person’s “job #1” column and another’s.
Furthermore, if someone doesn’t have a particular type of phone number, that cell is left blank, resulting in a “Swiss cheese” look to the table. Unused cells take up space in the database and can slow things down for larger data sets.
In a relational structure, only the first three columns would be in the employee table itself. The last three columns, which all represent phone numbers of some kind, would be moved to their own table. A label field could be added to identify each type of phone number, with the resulting two tables looking something like those shown in Tables 1.3 and 1.4.
Table 1.3. The Revised Employee Table
Table 1.4. The New Phone Table
Note that a field has been added: Emp ID. Think of this field as an internal address within a table. It is used to match employees with their phone numbers. In relational database terminology, this column is called a key field. The FileMaker Pro help system refers to it as a match field, but they are one and the same. Key fields are used to identify specific records. Although FileMaker Pro can be used to build simple flat file database systems (see Figure 1.1), it really shines at creating relational database systems (see Figure 1.2).
Figure 1.1. FileMaker can be used to construct simple flat file databases.
Figure 1.2. The two-table Employee/Phone example can look something like this when implemented in FileMaker.
For a thorough introduction to database application development with FileMaker Pro, see Chapter 3, “Defining and Working with Fields and Tables,” p. 87, and Chapter 4, “Working with Layouts,” p. 119.
FileMaker Pro is just one product in a broader product line. It’s worth noting the differences among the products and how they work together:
For a complete discussion on ODBC, see Chapter 21, “Connecting to External SQL Data Sources,” p. 603; for Instant Web Publishing, see Chapter 25, “Instant Web Publishing,” p. 689.
Consult the FileMaker website for more information on the products and special offers that might be available. Also use the website to locate consultants who might have additional information on pricing and bundles (http://www.filemaker.com/solutions/find/consultants.html).
After a database application has been developed in FileMaker Pro or FileMaker Pro Advanced, it can be deployed in various ways and on various operating systems. FileMaker Pro 9 runs on Mac OS X and Microsoft Windows. The following sections describe different ways to deploy a FileMaker database system.
For detailed technical specs, hardware and software requirements, and other issues, see the FileMaker website at http://www.filemaker.com.
Many people get their start in FileMaker development by building a small application for their personal use. Although FileMaker Pro is inherently a networkable application, there’s nothing wrong with a single user working with a system on his computer. These solutions often grow over time—sometimes by being networked to other computers, and other times by adding additional functionality (oftentimes, both happen).
The next stage in a typical system evolution is that other members of an organization notice the system that a single person made and want to use it also. It’s a simple matter to enable FileMaker Network Sharing on a file—after that’s done, other FileMaker users can become guests of one user’s shared file. This kind of FileMaker hosting is called peer-to-peer because the database host and the database clients all use the same application: desktop versions of FileMaker Pro or FileMaker Advanced.
You should keep some considerations in mind with this type of hosting. Only 10 files at a time can be hosted on a single machine this way. Up to nine users can be guests of a file hosted in this fashion. Another consideration is that if you’re the host of a file, you can’t close the file while other users are working with it, and performance might suffer for other users as the hosting user puts her computer through its daily paces.
Only one user can open a FileMaker database file at a time. When you are using peer-to-peer hosting (or FileMaker Server hosting, as described next), one user or FileMaker Server opens the database. The other users connect over the network to the copy of FileMaker that opened the database; networking transactions between that application and the “client” users provide access to the database through the first copy of FileMaker or through FileMaker Server.
Also note that because the file format for FileMaker 7, 8, and 9 is the same, you can mix and match these versions in shared hosting environments. A very few features (such as the web viewer) have been introduced that will not display properly in FileMaker 7 or FileMaker 8, but rest assured that the software will not break.
FileMaker Server is optimized for sharing FileMaker databases, and it can host (share) more files (125) for more users (250) than FileMaker Pro peer-to-peer can. Administrators can remotely administer the server, create schedules for automated database backups, set the server to encrypt the network traffic between the server and the clients, and log server actions. It also provides Custom Web Publishing with XML/XSLT and PHP.
For more information about hosting database files with FileMaker Server, see Chapter 29, “FileMaker Server and Server Advanced,” p. 785.
FileMaker Server Advanced can host files for FileMaker users just as FileMaker Server can, but it can also allow ODBC/JDBC clients to access hosted files and provide service as a web host, allowing up to an additional 100 user connections for Instant Web Publishing clients.
Using FileMaker Pro Advanced, you can configure FileMaker databases to run without the menu bar or operating-system controls, effectively making a solution take over the entire computer screen. Developers will need to build whatever user interface controls users might need, given that menus are no longer available.
FileMaker Advanced also allows developers to bind files into a runtime application that will allow a single user to work with a FileMaker solution without needing a copy of FileMaker. No authoring capabilities exist (a user cannot access layout mode or make schema changes via the runtime engine), nor can the application serve as a host (peer-to-peer or server-based); in addition, the PDF output and External SQL Data Sources are not provided in runtime versions. However, this is a great option for creating a commercial application without requiring that customers purchase copies of FileMaker Pro.
To learn more about Kiosk mode or the Runtime Engine, see Chapter 28, “Deploying and Extending FileMaker,” p. 767.
FileMaker solutions can be enhanced by incorporating plug-ins that extend the functionality of FileMaker Pro. The functionality that plug-ins offer varies widely and is determined by the third-party developers who write and market plug-ins. Some plug-ins provide advanced math capabilities, some generate charts from FileMaker data, some manipulate image files, and others provide security or user-interface enhancements. There are literally dozens if not hundreds of plug-ins actively supported by the FileMaker industry at large.
To get more information about plug-ins, see Chapter 28, p. 767.
FileMaker, the database product, has been around since 1985, and has evolved a great deal since its inception. It is fully relational, offers both development- and user-level support in a single application, is completely cross-platform compatible, and takes advantage of a modern security architecture.
The following is a brief list of the major changes and new features in FileMaker 9. There are many, many more, but this is a list of some of the most popular ones. Some of them, such as the support for external SQL databases, have major impacts throughout FileMaker. Thus, to find out more information, check out the index because there you will find the various references for these topics that might be covered in multiple chapters.
If there is a major theme to FileMaker 9, it is the integration of FileMaker with other databases, particularly in large environments such as organizations that use SQL databases. An additional area of integration has been the further development of web features.
Version 7 was a major architectural shift for FileMaker, and many of the changes from prior releases of the platform represent critical issues that all current developers must know. To present a complete picture of FileMaker, the following section reviews, and compares to prior versions, the new architectural structure introduced in FileMaker 7.
The file format for FileMaker 7 is radically different from that of earlier versions of FileMaker Pro. Earlier versions of the product were limited to one table per file, whereas FileMaker 7 and 8 allow one million tables per file. Not only is the file format different, but the network protocol that FileMaker uses to communicate between hosts and guests is different as well. That means that if you need to, you can run both FileMaker 6 and FileMaker 8 on the same network and the two versions won’t “see” each other on the network or conflict in any way. After you convert FileMaker 6 files into FileMaker 8 files, the FileMaker 8 versions will no longer be readable by FileMaker 6. Conversion in this sense is actually a misnomer because when FileMaker 8 converts a FileMaker 6 file, the original file is left untouched. Instead, FileMaker 8 uses the original to create a new version of the file.
Along with the FileMaker 7 file format come some dramatic benefits. One favorite is the significantly improved stability of files, leading to far less corruption when systems suffer crashes. In addition, developers can now make changes to everything—field definitions, table definitions, access privileges—while the database is being hosted with guests logged on. This extends the flexibility of the platform even further.
The Relationships Graph is a visual representation of the relationships between table occurrences. The term table occurrences is used because there can be only a single relationship between two table occurrences. If a developer needs to create multiple relationships between two tables, those tables will be displayed multiple times on the Relationships Graph—hence the term table occurrence as shown in Figure 1.3.
Figure 1.3. This Relationships Graph shows multiple occurrences of the same table.
Figure 1.3 shows the Contact Management Starter Solution. The basic table is replicated several times, each with different relationships as described in the following section.
In versions of FileMaker Pro before version 7, relationships could be constructed only on the basis of a key field (match field) in one file being equal to a key field in another file. These relationships, or joins, are known as equijoins in relational database terminology. FileMaker Pro 9 supports multiple join types. Now relationships can be constructed in which one value is less than, greater than, or not equal to the other value.
In addition to the multiple join types, FileMaker 9 also supports complex, multiple-predicate joins, or joins with more than one criteria. In Figure 1.4, the Edit Relationship dialog shows the different join types.
Figure 1.4. This relationship shows a single relationship.
The Relationships Graph shown in this section is part of the Contact Management Starter Solution. Each table is an occurrence of the same base table. The various relationships allow you to access a record from Contact Management of a contact with the same company name or the same last name. This is a sophisticated use of FileMaker’s relationships, and is described further in Chapter 6.
In FileMaker Pro 9, a user can have more than one window open per file and, more important, more than one window open per table (because it is possible to have multiple tables per file). It is possible to view two separate found sets of data at once, perform a search in one window while maintaining the state of another, or develop a multipaned application.
FileMaker 9 supports a centralized, modern security architecture in which accounts and privileges can be changed while a system is live. System behaviors are grouped into privilege sets; each account is associated with a single privilege set, ensuring that all users have their own accounts and passwords while sharing access levels with their peers.
Extended privileges serve as an additional feature that allows developers to define custom privilege settings. In scripts and calculations, a developer can test for the existence of an extended privilege and modify the behavior of a user’s experience with a given solution accordingly.
Security capabilities also include the capability to integrate with an external authentication server (Open Directory or Active Directory) where an IT organization may maintain passwords on a central server for all the various applications in an organization, including FileMaker. Although technically not really a security feature, it is possible to tie a FileMaker Server into an LDAP (Lightweight Directory Access Protocol) infrastructure so that users see only host servers applicable to their department or division, and so on.
For a complete overview of FileMaker’s security features, see Chapter 12, “Implementing Security,” p. 371.
FileMaker 7 represented a complete rearchitecting of FileMaker’s file format and dramatically extended the platform’s capabilities. The transition from the file format supported by FileMaker 3.0 through FileMaker 6.0 is still going on for some users; for others, it is more or less complete as they move to the latest versions of FileMaker Pro.
It should be noted that the automatic conversion of pre–FileMaker Pro 7 databases lets them run in the new architecture; however, to take full advantage of the new features, you need to do some additional work as described in Chapter 20, “Converting Systems From Previous Versions of FileMaker Pro,” p. 581.
A comparison between the two architectures demonstrates the dramatic growth the platform has undergone (see Table 1.5).
3.16.69.199