• Computers & Technology
  • Home Computing
  • Business & Culture
  • Programming
  • Software
  • Web Development
  • Certification
  • Networking
  • Security & Encryption
  • Computer Science
  • Operating Systems
  • Microsoft
  • Hardware
  • Databases
  • Graphic Design
  • Apple
  • Digital Music
  • Digital Photography & Video
  • Games & Strategy Guides
  • Project Management
  • Mobile & Wireless Computing
• Education & Reference
  • Encyclopedias
  • Test Preparation
  • Studying & Workbooks
  • Schools & Teaching
  • Writing, Research & Publishing Guides
  • Foreign Language Study & Reference
  • Atlases & Maps
  • Dictionaries & Thesauruses
  • Words, Language & Grammar
  • College & University
  • Trivia & Fun Facts
  • Consumer Guides
• Business & Investing
  • Industries & Professions
  • Management & Leadership
  • Organizational Behavior
  • Personal Finance
  • Small Business & Entrepreneurship
  • Popular Economics
  • Marketing & Sales
  • Finance
  • Skills
  • Business Life
  • Economics
  • Job Hunting & Careers
  • Biography & History
  • Reference
  • International
  • Real Estate
  • Investing
  • Women & Business
• Science & Math
  • Mathematics
  • Technology
  • Reference
  • Earth Sciences
  • Physics
  • Biological Sciences
  • Behavioral Sciences
  • Nature & Ecology
  • Astronomy & Space Science
  • History & Philosophy
  • Experiments, Instruments & Measurement
  • Agricultural Sciences

FileMaker Pro 8. The Missing Manual Authors: Coffey G.,  Prosser S. Published year: 2004 Pages: 72-74/176

Chapter 9. Introduction to Calculations

Fields and layouts form the basis of every database: You can't have a FileMaker database without them. Using just what you know so far, you can build all kinds of useful systems that store data, from an address list to a catalog of products.

But if you want to do the really useful stuff with your datatotal an invoice, analyze trends, compare figuresyou need specific tools. For those tasks , FileMaker provides Calculation fields and a whole drawer full of functions that let you examine, compare, and combine field values. And calculations aren't just limited to number- crunching tasks. You can use them to find out about the computer your database is running on, track who's logged into the system, monitor their privileges, and then perform logical tests based on what you find. You'll start by learning how FileMaker handles calculations, and then you'll see how some common functions can take your database up to a new level of power.

9.1. 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?

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.

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.

FileMaker Pro 8. The Missing Manual Authors: Coffey G.,  Prosser S. Published year: 2004 Pages: 72-74/176

Similar books on Amazon

FileMaker Pro Design & Scripting For Dummies

FileMaker Pro 10: The Missing Manual

Special Edition Using FileMaker 8

FileMaker Pro 11: The Missing Manual