Introduction: Welcome to FileMaker 12

Best of Three Worlds

Welcome to the world of FileMaker. From the start, one of the most important things to understand is that FileMaker is far more than just a database application.

FileMaker is nearly unique in the world of software. It is a powerful database system that can manage and store a wide range of information, it’s an application for end users (like Microsoft Excel or Intuit’s Quicken), and it’s also a robust rapid application software development platform.

When you hear people speak about FileMaker, keep in mind they might be viewing it from any one of these different perspectives. An IT professional likely sees FileMaker as a database engine that fits into a larger security and network infrastructure. An end user is probably thinking about a specific solution built with FileMaker Pro and how it helps make her work more efficient. A software developer might see FileMaker as one of many tools he employs in building a wide range of applications.

This book was written with an eye toward the FileMaker developer community. If you’re mostly interested in learning how to use the essential features of the FileMaker application, though, this book might not be for you. Although some introductory chapters are included to be as comprehensive as possible, the focus is on an audience that is largely familiar with the essential operations of FileMaker already and is interested mostly in topics for the beginning to advanced developer.

A key component of the FileMaker world today is FileMaker Go for iPad and iPhone (separate apps). It enables users to access databases built with FileMaker Pro and hosted by FileMaker Pro (for up to 10 users) or by FileMaker Server (for larger configurations). With FileMaker 12, the prices of these apps changed: they are both free. This means that with your purchase of FileMaker Pro, an idea for a mobile solutions (and, we hope, this book), you’re ready to deploy a world-class mobile solution.

In today’s world, FileMaker developers are creating solutions for a range of technologies. The databases you create with FileMaker Pro can be accessed through FileMaker Pro and FileMaker Pro Advanced. They can be deployed with FileMaker Server and FileMaker Server Advanced. In addition, they can be accessed from iPhone, iPad, and iPod touch using FileMaker Go. And, in case that isn’t enough for you, you can use Instant Web Publishing and Custom Web Publishing to deploy your FileMaker solution on any browser.

For more on FileMaker Go, see Data-Driven iOS Apps for iPad and iPhone with FileMaker Pro, Bento by FileMaker, and FileMaker Go, by Jesse Feiler.

How This Book Is Organized

FileMaker 12 In Depth is divided into five parts, organized into something like a tree. Part I, “Getting Started with FileMaker,” and Part II, “Developing Solutions with FileMaker,” constitute the “trunk” of the tree; they cover fundamental material that we recommend everyone read.

Subsequent parts branch out from this base. Part III, “Developer Techniques,” focuses on using FileMaker’s features to develop complete, robust database applications. Part IV, “Data Integration and Publishing,” covers getting data into and out of FileMaker. And Part V, “Deploying a FileMaker Solution,” covers options for making a FileMaker solution accessible to others.

The following sections describe the five parts of FileMaker Pro In Depth and the topics they cover.

Part I: Getting Started with FileMaker 12

The chapters in Part I introduce you to FileMaker and its uses and features, and they get you started with the basics of defining databases.

• Chapter 1, “FileMaker Overview,” situates FileMaker Pro within the wider world of database and productivity software. This chapter is appropriate both for those who are new to FileMaker Pro and for those who have used previous versions and want a quick tour of the major innovations.

• Chapter 2, “Using FileMaker Pro,” is intended as an introduction to the software from the perspective of a database user rather than a database developer. You’ll see the major components and functions of the FileMaker interface, such as the Status toolbar, layouts, FileMaker’s modes, and the basics of record creation, editing, and deletion.

• Chapter 3, “Defining and Working with Fields and Tables,” provides a thorough overview of all of FileMaker’s field types and field options, including lookups, validation, storage types, and indexing. This chapter is intended to help lay the groundwork for talking about database development and to serve as a thorough reference on FileMaker field types and options.

• Chapter 4, “Working with Layouts,” covers all of FileMaker’s layout-building options in detail. We cover all aspects of layout building and offer guidelines for quicker and more efficient layout work.

Part II: Developing Solutions with FileMaker

Part II is intended to introduce you to the fundamental techniques of database application development using FileMaker Pro and FileMaker Pro Advanced. Chapters 5 through 7 cover the theory and practice of designing and building database systems with multiple data tables. Chapters 8 through 10 introduce you to foundational concepts in application and reporting logic.

• Chapter 5, “Relational Database Design,” introduces you to relational database design concepts. We proceed by working on paper, without specific reference to FileMaker, and introduce you to the fundamental vocabulary and techniques of relational database design (keys and relationships).

• Chapter 6, “Working with Multiple Tables,” begins the task of translating the generic database design concepts of Chapter 5 into specific FileMaker techniques. We show how to translate a paper diagram into an actual FileMaker table structure. We show how to model different relationship types in FileMaker using multiple data tables and how to create fields that function effectively as relational keys.

• Chapter 7, “Working with Relationships,” builds on the concepts of Chapter 6. Rather than focusing on FileMaker’s relationships from the standpoint of database design, we focus on their practical implementation in FileMaker programming. We look in detail at the relational capabilities of FileMaker and discuss nonequality join conditions, file references, and some strategies for organizing a multitable system.

• Chapter 8, “Getting Started with Calculations,” introduces FileMaker’s calculation engine. The chapter delves into the major types of FileMaker calculations. We cover a number of the most important functions and discuss general strategies and techniques for writing calculations.

• Chapter 9, “Getting Started with Scripting,” introduces FileMaker’s scripting engine. Like the preceding chapter, this one covers the fundamentals of an important skill for FileMaker developers. We cover some common scripting techniques and show how to use event-driven scripts to add interactivity to a user interface.

• Chapter 10, “Getting Started with Reporting and Charting,” illustrates the fundamental techniques of FileMaker Pro reporting (such as list views and subsummary reports), some more advanced subsummary techniques, and some design techniques for improving the look and usability of your reporting layouts. This chapter also explores the charting features of FileMaker Pro.

Part III: Developer Techniques

The chapters in Part III delve deeper into individual topics in advanced FileMaker application development. We build on earlier chapters by exploring more complex uses of portals, calculations, and scripts. We also offer chapters that help you ready your FileMaker solutions for multiuser deployment, and we examine the still-important issue of conversion from previous versions.

• Chapter 11, “Developing for Multiuser Deployment,” explores the issues and challenges of designing FileMaker systems that will be used by several people at once. We discuss how FileMaker handles concurrent access to data and discuss the concept of user sessions.

• Chapter 12, “Implementing Security,” is a thorough overview of the FileMaker Pro security model. We cover the role-based accounts feature, extended privileges, and many of the complexities of server-based external authentication against Windows or OS X user directories, for example.

• Chapter 13, “Using the Web Viewer,” explores one of the interesting recent features of FileMaker Pro. You can incorporate live web pages into your FileMaker layouts, and you can use data from the FileMaker database to construct the URLs that are displayed.

• Chapter 14, “Advanced Interface Techniques,” provides detailed explanations of a number of more complex, applied techniques for working with layouts and data presentation in a FileMaker application. You will see how to use FileMaker themes to make your solutions look great and work elegantly on the various devices that may be used to access them. This chapter also focuses on techniques for implementing FileMaker Go interfaces for iOS mobile devices.

• Chapter 15, “Advanced Calculation Techniques,” looks closely at some of the more advanced or specialized types of FileMaker calculations, as well as the functions for text formatting and list manipulation.

• Chapter 16, “Advanced Scripting Techniques,” like the preceding chapter, is full of information specific to features of FileMaker scripting. Here, we cover programming with script parameters, the significant feature of script variables, programming in a multiwindow system, and the complexities of scripted navigation among multiple tables and recordsets.

• Chapter 17, “Working with FileMaker Triggers,” examines one of the most important features of FileMaker. Triggers let you set up automatic behaviors that occur whenever certain events happen. They let you exercise more control over the user interface with less programming in many cases. They can also improve the user experience by automatically performing scripts based on user actions so that you can eliminate buttons that require additional user actions and that use up precious space on layouts.

• Chapter 18, “Advanced FileMaker Solution Architecture,” is the last of the chapters in the Advanced series. It presents a variety of features and solutions that integrate and expand some of the techniques in the previous chapters. You will find information on window management, multiwindow interfaces, and selection portals, among other topics.

• Chapter 19, “Debugging and Troubleshooting,” is a broad look at how to find, diagnose, and cure trouble in FileMaker systems—but also how to prevent it. We look at some software engineering principles that can help make systems more robust, and can reduce the incidence and severity of errors. The chapter also includes detailed discussions of how to troubleshoot difficulties in various areas—from multiuser record lock issues to performance difficulties over large networks.

• Chapter 20, “Converting Systems from Previous Versions of FileMaker Pro,” explores the complex issues involved in moving to FileMaker 12 from previous. We then discuss the mechanics of conversion in detail, and discuss some of the more significant pitfalls to be aware of.

Part IV: Data Integration and Publishing

Part IV covers technologies and capabilities that allow FileMaker to share data, either by exchanging data with other applications or by exporting and publishing data, for example, via ODBC, JDBC, and the Web.

• Chapter 21, “Connecting to External SQL Data Sources,” explores FileMaker’s ODBC/JDBC interface as well as the exciting features that let you add SQL tables to your Relationships Graph. This means that you can now use SQL tables very much as if they were native FileMaker tables. You can use them in layouts along with FileMaker tables, you can use them in reports, and you can even expand them by adding your own variables to the FileMaker database that are merged with the external SQL data as you use it.

• Chapter 22, “Importing Data into FileMaker Pro,” looks at almost all the means by which you can import data into FileMaker. It covers how to import data from flat files, how to batch imports of images and text, and how to import images from a digital camera. (XML importing is covered in Chapter 24.) It also shows you how to import data from Bento on OS X.

• Chapter 23, “Exporting Data from FileMaker,” is in some respects the inverse of Chapter 22. It covers almost all the ways by which you can extract or publish data from FileMaker.

• Chapter 24, “Instant Web Publishing,” looks at the features of the FileMaker Instant Web Publishing model. Anyone interested in making FileMaker data available over the Web should begin with this chapter.

• Chapter 25, “Custom Web Publishing with PHP and XML,” shows you how to use FileMaker’s newest web publishing tools to build a PHP-based site. In addition, you will see how to export data using XML.

Part V: Deploying a FileMaker Solution

Part V delves into the choices you have for how to deploy a FileMaker database, including deployment via FileMaker Server and via kiosk or runtime mode using FileMaker Developer.

• Chapter 26, “Deploying and Extending FileMaker,” provides an overview of the ways you can deploy a FileMaker database to one or more users, reviews plug-ins, and explores the means of distributing standalone databases. Read this chapter for a quick orientation toward your different deployment choices.

• Chapter 27, “FileMaker Server and Server Advanced,” explores in depth setting up and working with FileMaker Server and FileMaker Server Advanced. The chapter covers setting up, configuring, and tuning FileMaker Server, as well as managing server-side plug-ins and authentication. The new Server Admin Console is described in detail here.

Special Features

This book includes the following special features:

• Troubleshooting—Many chapters in the book have a section dedicated to troubleshooting specific problems related to the chapter’s topic. Cross-references to the solutions to these problems are placed in the context of the relevant text in the chapter as “Troubleshooting Notes” to make them easy to locate.

• FileMaker Extra—Many chapters end with a section containing extra information that will help you make the most of FileMaker Pro. In some cases, we offer expanded, fully worked examples of tricky database design problems. In others, we offer shortcuts and maintenance techniques gleaned from our collective experience with developing production FileMaker systems (creating custom function libraries or getting the most out of the team development). And in still others, we delve all the way to the bottom of tricky but vital FileMaker features such as the process of importing records.

• Notes—Notes provide additional commentary or explanation that doesn’t fit neatly into the surrounding text. You will find detailed explanations of how something works, alternative ways of performing a task, and other tidbits to get you on your way.

• Tips—This feature identifies some tips and tricks we’ve learned over the years.

• Cautions—Here, we let you know when there are potential pitfalls to avoid.

• Cross-references—Many topics are connected to other topics in various ways. Cross-references help you link related information together, no matter where that information appears in the book. When another section is related to the one you are reading, a cross-reference directs you to a specific page in the book where you can find the related information.

• FileMaker scripts—Numerous examples of scripting are provided in the book. Because you can create long lines of code, they are sometimes split in order to be printed on the page. The character indicates the continuation of the previous line of code.

Downloadable Files

Most of the examples in this book are based on the FileMaker Starter Solutions that are installed automatically for you when you install FileMaker. Thus, you already have most of the files. In some cases, additional files or additional code has been added to the Starter Solutions as described in this book. These files can be downloaded from the author’s website at northcountryconsulting.com. You can also download them from the publisher’s website at www.informit.com/title/9780789748461.

FileMaker References

Other files and further information are available at filemaker.com. You can join TechNet at www.filemaker.com/technet/. It provides downloads, tech briefs, and members-only webinars on FileMaker topics. TechNet itself is free; you can add a FileMaker Developer subscription for $99/year. Although the features may change over time, at this writing that subscription includes a FileMaker Server Advanced development license, a download of the official FileMaker Training Series, and, as circumstances permit, early views of unreleased FileMaker software.

If you are converting from a previous version of FileMaker, you can find conversion references at www.filemaker.com/r/conver.

Who Should Use This Book

Like FileMaker itself, this book has several audiences. If you work with structured data a lot (Excel spreadsheets, for example) but are new to databases, this book will provide you with a solid foundation in the world of databases, in the basics of database theory, and in the practical skills you need to become a productive database user or developer. The book’s more introductory chapters tell you what you need to know to get started building basic databases for your own use. Later chapters introduce you to the world of multiuser database design and to some of FileMaker’s more advanced application design features.

If you’ve worked with other database systems—either server-side relational database engines based on SQL or desktop development environments such as Access—this book will help you see how FileMaker Pro fits into the universe of database software. Refer to the “How This Book Is Organized” section earlier in this Introduction to get a sense of which chapters will get you started quickly with FileMaker.

And in case you’re an old hand with FileMaker, we’ve provided a good bit of in-depth discussion of advanced techniques.