Understanding How and Where Calculations Are Used

 <  Day Day Up  >  

Calculation functions are among the most important and powerful tools at your disposal in the development of FileMaker Pro applications. Some find learning calculations to be an easy task. Others find writing complex calculations to be a terrifying experience. Whichever camp you fall into, becoming a master of calculation formulas is a worthy goal to have, and we'll get you started on the path .

This chapter focuses on basic calculation functions and techniques for using them well. Chapter 14, "Specialized Calculation Functions," looks at more advanced calculation formulas and 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 info that will make it worth your while.) We've also included a complete function reference in Appendix B, "Calculation Function Reference." That's intended to be a reference when you need specific or comprehensive information. Here, we take more of a tutorial approach.

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 calculation formulas are most frequently encountered in the construction of calculation fields, but try not to let that fact confuse you.

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 calculation dialog.
graphics/08fig01.jpg

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 truthfulness of an equation or statement. The "value" returned by these formulas is either a 1, indicating 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.

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.

Figure 8.2. Calculation formulas are often used to determine the truthfulness of an equation or statement.
graphics/08fig02.jpg

For certain uses, then, the purpose of a calculation formula is to return a value; for other uses it's simply to evaluate the truthfulness of an equation or statement. In situations where FileMaker is expecting a formula that returns a true/false result, you see the words "Calculation results 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 fancy term for a simple concept: The formula you provide evaluates as being either true or false. Any value returned other than 0 or a null value (for example, an empty string) is considered true.

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 a number of other places where calculation formulas are used.

Briefly, these include:

  • Script Steps ” Calculation formulas come into play in many script steps. The If and Set Field script steps are notable examples. Many other script steps allow you to use a calculation formula to act as a parameter. These include Go to Layout , Go to Field , Go to Record , Pause/Resume Script , and Omit Multiple . Additionally, calculation formulas can be used to define script parameters.

  • Field Validation ” One of the options available to you for validating data entry is validating by calculation. This, in effect, lets you define your own rules for validation. 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 call-back 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.

  • Record-level Security ” When you define privilege sets, you have the option of limiting a user's access to view, edit, and delete records based on a calculation formula you provide. If the equation you provide evaluates as true, then the user can perform the action; if not, the action is prohibited . For instance, you might want to prevent users from inadvertently modifying an invoice that has already been posted. So, you'd set up limited access for editing records based on the formula Invoice_Status <> "Posted" . Only records where that is a true statement would be editable.

  • Auto-Entry Options ” When defining text, number, date, time, and timestamp fields, several auto-entry options are available for specifying default field values. One of these options is to auto-enter the result of a calculation formula. For instance, in a contact management database, you might want a default call-back date set for all new contact records. The formula you'd use for this might be something like Get(CurrentDate) + 14 , if you wanted a callback date two weeks in the future.

  • Calculated Replace ” A calculated replace is a way of changing the contents of a field in all the records in the current found set. It's particularly useful for cleaning up messy data. Say, for example, that your users had sometimes entered spaces at the end of a name field as they were doing data entry. You could clean up this data by performing a calculated replace with the formula Trim (First Name) .

 <  Day Day Up  >  


QUE CORPORATION - Using Filemaker pro X
QUE CORPORATION - Using Filemaker pro X
ISBN: N/A
EAN: N/A
Year: 2003
Pages: 494

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net