Calculation functions are among the most important and powerful tools at your disposal in the development of FileMaker Pro solutions. Some people find learning calculations to be an easy task, whereas others can find writing complex calculations to be daunting. Whichever camp you fall into, calculations will enable you to unlock much of the advanced power within FileMakerwe encourage you to stick with it. Our hope is that this chapter and its companion, Chapter 14, "Advanced Calculation Techniques," will provide you a solid grounding.
This chapter focuses on basic calculation functions and techniques for using them well. Chapter 14 looks at more advanced calculation formulas and specific techniques. If you're new to FileMaker, you should start here. Those who have been using FileMaker for years may want to just skim this chapter. (There are probably a few nuggets of information that will make it worth your while.) We've also included a complete function reference in our companion book, FileMaker 8 Functions and Scripts Desk Reference. That book is intended to serve as a reference when you need to look up specific information. Here, we take more of a tutorial approach and explore how and why one employs calculations.
From the outset, it's important to understand the difference between calculation fields and calculation formulas. The term calculation is often used ambiguously to denote both concepts ("That table has more than 100 calculations!" or "What's the calculation used to determine access to this record?"). Calculation fields are a particular type of field whose value is determined through the evaluation of a calculation formula. Calculation formula is a broader concept that refers to any use of a formula to determine an output. When you learn "calculations," you're really learning calculation formulas. It so happens that you'll use calculation formulas to construct calculation fields, but the formulas are applied widely throughout FileMaker solutions.
Writing Calculation Formulas
Essentially, the purpose of a calculation formula is to evaluate an expression and return a value. In Figure 8.1, for example, you can see the field definition for a calculation field called Mileage Calc. The value of this field is defined to be the result of multiplying the contents of the Mileage field by .37, which is a typical mileage reimbursement rate.
Figure 8.1. When defining calculation fields, you specify an expression to evaluate in the Specify Calculation dialog.
Most of the expressions you use in calculation formulas are intended to return a value, and that value might be a number, a text string, a date or time, or even a reference to a file to place in a container field. Another class of formulas, however, is intended to evaluate the veracity of an equation or statement. The value returned by these formulas is either a 1, indicating that the equation or statement is true, or 0, indicating that the equation or statement is false. Typically, calculations are used in this manner in If script steps, in calculated validations, and for defining field access restrictions.
To learn more about field validation, see Chapter 3, "Defining and Working with Fields," p. 67.
In Figure 8.2, for instance, you can see a calculation dialog that specifies the condition for an If script step. When the script executes, FileMaker evaluates whether the number of hours is in fact greater than 8 (based on the current record's data). Depending on the value of the Hours field for any particular record, the statement may be either true or false and the script will presumably react accordingly.
Figure 8.2. Calculation formulas are often used to determine the truthfulness of an equation or a statement.
For certain uses, then, the purpose of a calculation formula is to return a value; for other uses it's simply to evaluate the veracity of an equation or statement. In situations in which FileMaker is expecting a formula that returns a true/false result, you see the words Calculation result must be Boolean near the bottom of the calculation dialog. The If script step shown earlier is a typical example of this situation. Boolean is a software programming term for a value with one of two states: true or false. Any value returned other than 0 or a null value (for example, an empty string) is considered true.
Note that Boolean rules apply for text values, dates, negative numbers, and so on. "Hello" is true (not zero and not null), a single space character (" ") is true, and -1 is true. Note also that the results of a formula are evaluated in the same way: (0 * 100) is false. (0 + 100) is true. Last, also note that you can use comparative operators: 1 and 1 is true (where each clause on both sides of the and operator evaluate to true), 1 or 1 is true, 1 xor 1 is false, and so on. You'll learn about operators later in the chapter.
The GetAsBoolean() function treats all data as numeric, such that, for example, "hello" evaluates as false and "hello999" evaluates as true. This is an inconsistency with the way in which other Boolean logic operates, so be sure to take note of it.
Uses for Calculation Formulas
This chapter focuses on the use of calculation formulas in field definitions, but it's important that you understand that there are other places where calculation formulas are used as well. Briefly, these include the following:
The equation you provide is evaluated every time a user modifies the field. If it evaluates as true, the user's entry is committed. If it doesn't, the user is presented with an error message. For instance, if a user is supposed to enter a callback date on a contact record, you might want to validate that the entry is a future date. To do this, you might use the formula Call_Back_Date > Get ( CurrentDate ) as the validation for the Call_Back_Date field.
Exploring the Calculation Dialog Box
Part I: Getting Started with FileMaker 8
Using FileMaker Pro
Defining and Working with Fields
Working with Layouts
Part II: Developing Solutions with FileMaker
Relational Database Design
Working with Multiple Tables
Working with Relationships
Getting Started with Calculations
Getting Started with Scripting
Getting Started with Reporting
Part III: Developer Techniques
Developing for Multiuser Deployment
Advanced Interface Techniques
Advanced Calculation Techniques
Advanced Scripting Techniques
Advanced Portal Techniques
Debugging and Troubleshooting
Converting Systems from Previous Versions of FileMaker Pro
Part IV: Data Integration and Publishing
Importing Data into FileMaker Pro
Exporting Data from FileMaker
Instant Web Publishing
FileMaker and Web Services
Custom Web Publishing
Part V: Deploying a FileMaker Solution
Deploying and Extending FileMaker
FileMaker Server and Server Advanced
Documenting Your FileMaker Solutions