Understanding Calculations

A calculation is a mathematical formula: It shows how different things combine to come up with an answer. But even if you hated algebra class, don't close the book yet! Instead of the Xs, Ys, and Zs of the math world, FileMaker lets you use your own fields to supply your formula with dataa much more intuitive approach.

FileMaker calculations can also do more than just math. For starters, you can do calculations on time, date, timestamp, container, and text fields, too. (See the box on Section 9.2 for an example.) Way back in Chapter 3, you learned that FileMaker can create calculation fields (Section 3.2.6.5). Calculation fields work just like any other FileMaker field, except that you can't type data into them. Instead, they use a calculation to determine their own value.

You can use a calculation field just like any other field: Put it on a layout, use it in Find mode, and even use it in other calculation fields. But as a calculation field, its value always stays up-to-date automatically. If the price or quantity changes, the calculation updates the data in your field without your doing anything. Figure 9-1 shows where calculation fields could benefit your invoices layout.

Figure 9-1. When you fill out an invoice, you have to manually calculate the extended price for each line item by multiplying the quantity times the price. You also have to type in the invoice total. To make your job easier and minimize errors, you can use calculation fields to do both these jobs for you. You can even make the Due field a calculation, so it's always 30 days after the invoice date.

Furthermore, calculations aren't limited to working inside fields. Once you understand the basic concept of calculations, you gain all kinds of new FileMaker skills. For example, you can use calculations with the Records Replace Field Contents command (Section 2.3). Instead of replacing the data in every record with the 9.1.1. Creating a Calculation

A simple example speaks more loudly than several pages of explanation, so you start off by adding a calculation to an existing number field to the database you built in Chapters 7 and 8. As your database now stands, when you add line items to an invoice, you have to type the quantity, price for each item, and the extended price, as shown in Figure 9-1. But since the extended price is always the price times the quantity, why not let FileMaker handle the repetitive work while you do the creative stuff?

---

Note: To work through the examples in this chapter, you can download the Customers database from the "Missing CD" page at www.missingmanuals.com.

---

Here's how you add a calculation to a number field:

1. In the Customers database, choose File Define Database, and make sure youre on the Fields tab. From the Table pop-up menu, choose Line Items.

   That's the table with your Extended Price field in it.

2. In the field list, select the Extended Price field. From the Type pop-up menu, choose Calculation; then click Change.

   FileMaker warns you that when it converts the field, it changes (read: deletes) any information already in the field. See Figure 9-2.

   

   Figure 9-2. This warning is serious. Your new calculation overwrites existing data. But even if you write a calculation, there's a way out, so long as you haven't OK'd the Define Database dialog box. Just click its Cancel button and you'll get the chance to discard all the changes you've made in the dialog box.

    
3. Click OK.

   As shown in Figure 9-3, FileMaker asks you what calculation you have in mind.

4. In the Calculation Box, type Price Each * Quantity.

   The calculation box holds the calculation itself. In this case, you've just added a brand-new calculation that multiplies the contents of the Price Each by the contents of the Quantity field.

5. In the Define Database window, click OK, and then OK again.

   You're now back in your database. Switch to the Invoices layout and add a line item. You see that the Extended Price field updates automatically to reflect the proper price.

---

Tip: If FileMaker complains about not being able to find a field when you click OK, check the spelling of your field names.

---

Figure 9-3. Whenever you edit a calculation, you see a window just like this one. There's a lot going on here, including the fundamental step of typing your calculation in the box. You'll learn how to use all these options in the rest of this chapter.