1. FileMaker Overview
FileMaker and Its Marketplace
However you approach FileMaker, some core strengths of the platform are important for all types of users:
• Flexibility—Working with FileMaker is inherently open ended. Users can easily create ad hoc data queries, quickly manage data entry, add functionality to a live system, or deploy to the Web in minutes.
• Ease of use—The folks at FileMaker, Inc., have labored hard to make FileMaker as approachable as humanly possible. Day-to-day users can easily learn how to add fields to a database, create reports, add form layouts, and more. With FileMaker Pro, organizations can be less dependent on specialized software engineers.
• Mobility—With the companion apps, FileMaker Go for iPad and FileMaker Go for iPhone, you can access FileMaker databases wherever you are. FileMaker Pro is the tool you use to build and manage your databases, but with FileMaker Go you can access your databases directly from your iOS device or over a network. The layout tools in FileMaker Pro 12 provide an easy way to customize interfaces for multiple devices.
For more information, see Jesse Feiler’s companion book, Data-Driven iOS Apps Using FileMaker Pro, FileMaker Go, and Bento by FileMaker, as well as the rest of this book.
• Interoperability—FileMaker Pro supports many common, open standards for data exchange, including Structured Query Language (SQL), Open Database Connectivity (ODBC), Java Database Connectivity (JDBC), and Extensible Markup Language (XML), and allows users to connect their database solutions to the greater world of standards-based applications—both within their organizations and online on the Web using Hypertext Transfer Protocol (HTTP).
• Modern data architecture—FileMaker, despite being “just” a productivity application that lives on your computer along with Pages, Microsoft Word, and Solitaire, allows users to create fully relational data structures and to properly build architectures that correctly manage real-world data.
• Modern developer tools—With its recent versions, FileMaker has added modern programming and scripting features, including parameters and results for scripts, parameterized calculations, and script triggers that allow event-based scripting.
Ultimately, FileMaker exists between the world of desktop applications and high-end, enterprise-level server systems. It is a third option: a flexible, robust workgroup application that can quickly come together, evolve over time, and be dramatically cost effective.
Mobility
Today, mobility is not just about mobile devices—it is about the mobility of data and solutions across a variety of platforms. FileMaker has been evolving into a structure that supports structured solutions with the data and related features in one set of files and interfaces in another set of files. It is relatively easy to implement or change the interface for a single device or platform without affecting others.
TipThe evolving structure mentioned in this paragraph is supported and easy to implement, but it is not required.
On Filemaker.com, you will find a number of case studies showing how people have used FileMaker to power solutions that are accessed with mobile devices such as the iPad and iPhone.
FileMaker’s web publishing features, which have been present in this architecture for a long time, allow other mobile devices to access shared solutions on mobile and desktop devices by using the Web.
The features of iOS mobile devices are integrated into FileMaker Go, allowing mobile users to take photos, capture signatures, and automatically retrieve their location. All of these mobile features can be accessed through FileMaker Go, and the data they capture (photos, signatures, and location) can be automatically stored in FileMaker databases on the device, on a shared FileMaker network server, or over the Web.
Rapid Application Development
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 three 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 embark 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.
Low Total Cost of Ownership
FileMaker Pro is focused around offering a low total cost of ownership for organizations. In September 2008, The Industry Standard published “The 25 Best Business Software Tools and Services.” It included FileMaker Pro on the list, saying “This database application is more intuitive than Access, while offering high-end features like live SQL data source support and easy Web-publishing capabilities, so your whole team can access the database via a browser.”
FileMaker Is a Seasoned Platform
FileMaker Pro is now 25 years old. It was first developed in the mid-1980s by Nashoba Systems, which was purchased by Claris Corp., an Apple subsidiary. Originally for the Mac only, by FileMaker Pro 3.0 (1995) it was ported to Windows as well.
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, five major releases have continued the evolution of the product.
The addition of FileMaker Go for iOS devices (iPad, iPhone, and iPod touch) has built on the common architecture. FileMaker 12 and FileMaker Go, released in 2012, have expanded the multiplatform and multidevice implementation of the basic architecture.
You’re Not Alone
FileMaker, Inc., has sold more than 18 million units worldwide as of this writing. Users range from a single magician booking gigs in Denver, Colorado, to Fortune 500 companies such as Bristol-Meyers Squibb and Coca-Cola. 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 among these builders seems to be that they own computers and have information to store.
There are some trends: FileMaker Pro is widely used in the worlds of K–12 and higher education. The top 100 undergraduate/doctoral universities in the United States and many more around the world, along with the top 250 school districts in the United States, use FileMaker Pro. The nonprofit industry is also a key focal point for FileMaker, as is the creative-professionals industry.
The most recent trend, based on anecdotal reports, is that FileMaker may be the most effective way to deploy mobile solutions that scale from one-person, on-the-go businesses to enterprises that deploy many devices in the field.
Introduction to Database Software
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, and 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 the following tasks:
• Organizing your data into reports—Databases can organize information into reports sorted by city, last name, price, or any other criteria necessary.
• Finding one or several items in your collections—Visually scrolling through a document with flat data displayed soon becomes unwieldy—even if you use a search command to do the scrolling for you. Databases make it relatively simple to search for one record (or row) of data within potentially millions of others.
• Creating related associations among data—Rather than duplicating the name of a company for multiple people, for example, or perhaps having to reenter an address in a dozen places, users can utilize databases to create associations between data elements (using a form of addressing) and preserve the integrity of their information.
• Sharing data with other systems—Databases are often built to exchange information with other systems; many become one component in a multitiered technology solution for companies—even small businesses often exchange data between QuickBooks, for example, and FileMaker Pro.
• Describing the data—Databases contain metadata, which is data about the data values. Whereas a spreadsheet allows you to format data in various date formats, a database allows you to specify that a certain field actually is a date and that no other types of data are allowed in that field.
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 occurs when users are frustrated with not being able to find a given piece of information.
The rest of this book gets into the details of how to do everything just mentioned and much more as well. You 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.
Database Software
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 application productivity to a server-based architecture and database. It is as accessible as programs such as Microsoft Excel, Intuit’s QuickBooks, and Apple’s Numbers. It also enables 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 data 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 all manage collections of financial transactions. On the Web, Salesforce, Microsoft CRM, SAP, and NetSuite provide services in the same general category, but with a very different type of implementation, interface, and customization environment.
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 this information needing to be entered 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). In this way, FileMaker graduates from a single-user productivity tool to a fully realized database development platform.
Off-the-Shelf Software
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 organizations operate.
Custom Development Software
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 walls. Software development often is a complex layering of interdependent parts.
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. Almost 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.
NotePeople 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. The author 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 a web-based FileMaker solution accessed from your smartphone.
What Database Software Does
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. However, 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 list 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. Simply 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.”
As mentioned earlier, this type of database is called a flat file database because everything is in one table. Although having everything in one place is nice, 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, as shown in Table 1.2. 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 four 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 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.
Both Figure 1.1 and Figure 1.2 show Starter Solutions that ship with FileMaker Pro. In Figure 1.1, you see the Contact Management solution; it uses a flat file approach with separate fields for the various phone numbers, just as you saw in Table 1.2.
Figure 1.1. FileMaker can be used to construct simple flat-file databases.
Figure 1.2. FileMaker Pro can easily add related data.
In Figure 1.2, the People Management solution lets you track multiple goals for a person. The data for a goal is more complex than a simple phone number; in addition, the number of possible goals for a person can be quite large. A flat-file approach would not work. In the People Management solution, a single table contains the employee information, and a separate, related, table contains the goal information. Still a third table contains issue information for that person. Logically, there is no difference between relating phone numbers and relating goals to a person.
For a thorough introduction to database application development with FileMaker Pro, see Chapter 3, “Defining and Working with Fields and Tables,” and Chapter 4, “Working with Layouts.”
Overview of the FileMaker Product Line
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:
• FileMaker Pro—This is the regular desktop client version of FileMaker. It can be used to author new database systems, host systems for a limited number of guests (currently nine), or serve as a guest of a hosted system. It can also publish as many as ten database files to up to five users with Instant Web Publishing (IWP).
• FileMaker Pro Advanced—This version has all the capabilities of the regular version of FileMaker Pro; it also has additional functionality aimed at application developers. With FileMaker Pro Advanced, developers can create custom functions, add custom menu sets to a database, and create external function plug-ins (all these enhancements to the files can be used by both FileMaker Pro and FileMaker Advanced users). A Debug Scripts feature and Data Viewer allow developers to walk through scripts one step at a time and test calculations, watching the effect of each script step or process. The Database Design Report (DDR) enables developers to document and troubleshoot development issues from a systemwide perspective. The Advanced version also enables developers to create runtime versions of single-user solutions and enable kiosk mode.
The author strongly recommends developing with FileMaker Pro Advanced. The additional functionality in custom functions alone makes it well worthwhile, not to mention the added capability to control all menu selections in a solution. The debugging tools are invaluable, and the DDR is a great source for documentation and troubleshooting alike.
• FileMaker Server—This software hosts FileMaker files on a hardware server and offers support routines, evaluates server-based calculations, and provides for a larger user load: FileMaker Server can host a maximum of 125 database files and 250 FileMaker Pro or FileMaker Pro Advanced client connections. In addition, it can manage database backup schedules, log usage statistics, disconnect idle users, and manage FileMaker plug-in updates. It supports Custom Web Publishing XML or PHP (PHP: Hypertext Preprocessor) to up to 250 web sessions for users accessing the database with browsers. With FileMaker Server, scheduled scripts can import and export data; FileMaker Server itself can also send email at the request of clients.
For a complete discussion of Custom Web Publishing, including session handling and server capacity, see Chapter 25, “Custom Web Publishing with PHP and XML.”
• FileMaker Server Advanced—FileMaker Server Advanced has all the features of FileMaker Server and can also host ODBC/JDBC and provide Instant Web Publishing for up to 100 web sessions. In addition, the limit of 250 clients is removed: The limit is based solely on the capabilities of the hardware on which it is deployed.
For a complete discussion on ODBC, see Chapter 21, “Connecting to External SQL Data Sources”; for Instant Web Publishing, see Chapter 24, “Instant Web Publishing.”
• FileMaker Go—These apps for the iPhone and iPod touch, as well as the iPad, enable you to access FileMaker databases that are stored on the device or are available over a network. The layout and design features of FileMaker Pro are not available on FileMaker Go, but you can use the layouts and databases that you design with FileMaker Pro on your mobile devices.
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.
FileMaker, Inc., also provides a personal database app called Bento. Like FileMaker Pro, Bento comes with a number of built-in templates that you can use with or without modifications for your own purposes. Bento can be synchronized with up to five of your mobile iOS devices and your Mac. It can be an excellent tool for managing your life or small business; it can also be a wonderful tool for prototyping what will eventually become a FileMaker solution.
FileMaker Deployment Options
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 12 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.
Single User
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 additional functionality being provided (oftentimes, both happen).
Considerations to keep in mind with single-user hosting include the following:
• Only ten 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. If you’re the host of a file, you can’t close the file while other users are working with it.
• Performance might suffer for other users as the hosting user puts her computer through its daily paces.
Peer-to-Peer Hosting
The next stage in the evolution of a typical system 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.
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.
FileMaker Server Hosting
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 perform the following tasks:
• Remotely administer the server
• Set up groups of administrators with their own privileges and passwords
• Create schedules for automated database backups
• Set the server to encrypt the network traffic between the server and the clients
• Log server actions
FileMaker Server also provides Custom Web Publishing with XML and PHP.
For more information about hosting database files with FileMaker Server, see Chapter 27, “FileMaker Server and Server Advanced.”
FileMaker Server Advanced Hosting
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.
Kiosk Mode
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 Single-User Runtime
FileMaker Advanced also allows developers to bind files into a runtime application that allows 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 26, “Deploying and Extending FileMaker.”
Extending the Functionality of FileMaker Pro
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. Literally dozens (if not hundreds) of plug-ins are actively supported by the FileMaker industry at large.
To get more information about plug-ins, see Chapter 26, “Deploying and Extending FileMaker.”
Technical Specifications
FileMaker 7 represented a complete re-architecting 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.
Table 1.5 summarizes the capabilities of FileMaker Pro 12.
Table 1.5. FileMaker Pro 12 Capabilities
