Creating a Calculation Field
The invoice line items now calculate their extended prices automatically, because you created a calculation at the beginning of the chapter to handle that. But you still have to add up the extended price of each line item and enter the total amount due on the invoice itself. Another calculation solves this problem.
- In the Customers database, choose File
Define images/U2192.jpg border=0> Databases and go to the Fields tab. From the Table pop-up menu, choose Invoices. Select the Total Due field in the list.
The Field Name, Type, and Comment at the bottom of the window update to show information about this field.
- From the Type pop-up menu, choose Calculation, and then click the now highlighted Change button. When FileMaker asks if you're sure you want to make this change, click OK.
The Specify Calculation dialog box pops up. (See Section 9.2 for details on its many features.)
- From the "Evaluate this calculation from the context of" pop-up menu, choose Invoices.
The Invoices table has multiple occurrences on the graph, but Invoices is the one that models your ER diagram. And it's the one that's directly attached to the line items you're totaling up. (See Section 8.3.2 to learn about a context.)
- From the View pop-up menu (above the function list), choose Aggregate Functions.
The function list now shows just the functions FileMaker uses to calculate various kinds of totals and averages. You're looking for the Sum function.
- Double-click the "Sum ( field {; field…} )" function in the list.
FileMaker copies the full function example into the calculation box. To save you an extra step, it even selects everything between the parentheses. The next thing you type or click becomes the first parameter to the function.
Tip: Anything within curly braces in a function is optional. In the Sum function above, you could reference several fields that all get summed up into one glorious total. But that doesn't make sense when you're trying to summarize line items on an invoice, so you're just replacing all the highlighted material with a single field reference.
- From the pop-up menu above the field list, choose Line items. Then, in the list of line item fields, double-click the Extended Price field.
FileMaker adds this field to the calculation, placing it between the parentheses that surround the parameters to the Sum function. Your calculation should now read:
Sum ( Line Items :: Extended Price )
Tip: If you prefer the keyboard, you can use the Tab key to move from the calculation box to the field list, then the operator list, then the function list. Once you're in one of these lists, use the up and down arrow keys to select an item (or type the first few letters of the item's name). Finally, press the Space bar to add the selected item to the calculation box.
- From the "Calculation result is" pop-up menu, choose Number.
This calculation is based on number fields and produces a number too.
- Click OK.
If you've done everything right, the Specify Calculation window disappears.
Your Total Due field should now work perfectly. Since you modified an existing field that's already on your layout, you don't need to do anything else. Every layout that shows the Line Items::Extended Price now shows the new calculated value.
Switch to the Invoices layout to try for yourself. As you change the Price Each or Quantity fields on a line item, the Extended Price field changes automatically to reflect the correct total.
9.3.1. The Specify Calculation Dialog Box
As you saw in the steps on the previous pages, whenever you create a new calculation field, FileMaker shows the Specify Calculation window (Figure 9-4). This window is where you tell it what calculation to use. This window is loaded with options, making it seem a bit dauntingbut all those buttons are there to help you. FileMaker shows you the table occurrences, fields, operators, and functions, and all you have to do is point and click to build any calculation you have in mind.
Once you learn how this box works, you can write calculations like a pro without memorizing complicated functions and or typing out long field names. The following pages give you a guided tour of each element in the window.
|
9.3.1.1. Table occurrence context
This pop-up menu lists every occurrence of the current tablethe one you're adding a field toon the relationship graph. In the last chapter, you learned that FileMaker sees your entire database from the perspective of one table occurrence at a time. This list is where you tell FileMaker how to view the database when it evaluates this calculation. (If the calculation doesn't reference any related data, you can skip this menu.)
9.3.1.2. Field list
Since most calculations include fields, and field names are often long and hard to remember, FileMaker lets you pick field names from a list. The Table pop-up menu shows every table occurrence in the graph. The list below it shows the fields in the selected table occurrence. A calculation can refer to any field in the database: FileMaker follows the appropriate relationships if you use related fields.
| TROUBLESHOOTING MOMENT Think Like a Machine |
|
If you've jumped right in and started making perfect calculations every time, you can skip this bit of arcana. But if FileMaker throws up a warning dialog box every time you try to make a halfway complex calculation, or if the syntax seems fine, but you just aren't getting the math to work out right, you might have to try thinking like FileMaker thinks. To understand how fields, constants, functions and operators come together to produce a single result, you have to think very logically and in a straight line that inexorably leads to the end of a problem. When FileMaker evaluates a calculation, it looks for something it can do to simplify it-fetch a field value, perform a function, or evaluate an operator. The calculation shown here has a function (Average), several operators (* and &), a constant ("cubic inches") and six fields (L1, W1, H1, L2, W2, H2). You might think the Average function is the right place to start, because it comes first. But you quickly realize that you can't compute the average until you figure out what its parameters are by performing the multiplication. The * operators multiply values on either side to produce a new value-but FileMaker needs to replace these fields with their values before it can do anything else. In Step #1, FileMaker identifies six fields. Step #2 shows how the calculation looks once FileMaker replaces them with values. Now the * operators are all surrounded by values, and FileMaker is ready to do some multiplication (Step #3). Step #4 shows the calculation once all the multiplication is finished. At last the Average function has two parameters (Step #5), which is just what it needs, so FileMaker performs this function and the new calculation looks like Step #6. There are no more fields to replace and no more functions to perform, but there's one last operator. The & operator takes two text values and puts them together, but this & operator has a number on one side. FileMaker notices this fact in Step #7 and fixes it in Step #8. Finally, the & operator is evaluated and Step #9 shows the calculation result. If you apply the concepts outlined here to your problem calculations-find the answer to each step, then plod along to the next one-you can always figure out where your calculation has gone astray. |
If you want to put a field in the calculation itself, just double-click its name in the list, and FileMaker does the typing for you. See step 7 on Section 9.3.1 for a prime example of when not to type a long field name.
Note: When you double-click a field from the list of table occurrences, you create what FileMaker calls a "fully-qualified field reference," which contains the Table name, two colons, and the Field name (Invoices::InvoiceID). Because you might have similar field names in several tables, a fully-qualified name makes sure you reference the right one.
9.3.1.3. Operators
To help you remember all those operators, FileMaker shows them in the Operators area. Eight buttons represent the most common operatorsjust click one to insert it. Other operators appear in a scrolling list, which requires a double-click.
9.3.1.4. Function list
Some functions are so short and sweet that it's faster to type them than to hunt through this very lengthy list. Or you may come to memorize the functions you use most often. But you can't beat the Function list for convenience. It shows every function FileMaker understands and all the parameters each function expects, in the right order. (See the box on Section 9.3.1.1 for more detail.)
As usual, double-click a function to add it to the calculation. If you don't fancy an alphabetical list of every function, you can narrow down your choices using the View pop-up menu. You can pick a specific function type and see a list of just those functions. The pop-up menu also includes three special categories. The first, "all functions by type" reorganizes the functions in the list. You can see the effect in Figure 9-5.
|
Tip: You can always switch back to "all functions by name" to see every function in one alphabetical list. This choice comes in handy when you know a function's name, but not its category.
| UP TO SPEED The Function List |
|
The function list doesn't show just a list of meaningless namesit also shows an example of how to use the function. The example includes everything you need to call the function in a calculation: name, the necessary parentheses, and a placeholder for each parameter. You just need to replace the placeholders with fields, constants, functions, or expressions. Most functions are simple, and have a simple example to match: Date ( month ; day ; year ) This function, called Date, expects three parameters, a month, day, and year. (If you're curious, it returns a date value based on the three numbers passed to it. See Chapter 10 for more details. Many functions aren't quite so simple. Some functions don't have a predetermined number of parameters. The Average function needs at least one parameter, but you can pass as many as you want. It looks like this in the Function list: Average ( field {; field…} ) The first "field" parameter shows that you must specify at least one value. The second one is inside curly braces, meaning it's optional. And it's followed by "…" meaning you can add more copies if you want. The Case function shows up like this: Case ( test1 ; result1 {; test2 ; result2 ; … ; defaultResult} ) This shows that you can add additional test and result parameters, and you can put a final defaultResult parameter on the end if you want. Finally, a few functions actually accept more than one value for a single parameter. The Evaluate function is an example: Evaluate ( expression {; [field1 ; field2 ;…]} ) It always expects one parameter, called an expression (see Section 9.2.4.15). You can also specify a field to go with it. The brackets around the field show you that it can take two parameters, but the second can be a bracketed list of multiple values. In other words, you can call this function in three ways: Evaluate ( "" ) Evaluate ( '" ; A Field ) Evaluate ( "" ; [Field 1 ; Field 2 ; Field 3] ) In the first case, it receives only one parameter. In both the second and third cases, you're passing two parameters. In the third case only, the second parameter is actually a list of values. Functions like this are rare, but a few exist. |
9.3.1.5. Result type
Just as you specify field types when you define fields (Section 3.2.6, you also specify result types for your calculations. You use the Result Type pop-up menu to tell FileMaker what kind of data this field holds. You can easily figure out result type. If you're using a text calculation, your result is probably going to be text. If it's a number calculation, the result type is number. Surprisingly though, you can also make calculations that are container types (Section 10.5).
9.3.1.6. Calculation box
Your calculation itself goes in the Calculation box in the middle of the window (it has the field name above it as a label). You can type right into the calculation box if you're a codehead, but mere mortals usually use the field list, operators, and function list and let FileMaker assemble their calculations for them. When you're getting started, you probably mostly point and click, but as you get more familiar with formulas and functions, you start typing more often. Most people end up using a hybrid of typing and clicking to create their calculations.
Tip: You can also copy and paste into the Calculation box. If you have a calculation in another table file that's the same or similar, you can paste and then update it for its new home, saving yourself some typing.
| FREQUENTLY ASKED QUESTION Result Type |
|
Why do I have to tell FileMaker my calculation has a number result? I'm multiplying two numbers together, so isn't it obvious? You're right; FileMaker can figure that out for itself. In fact, in a calculation where you're performing simple math, the field always has a number result. But the ability to set the result type for a field gives you a good measure of control. For one thing, you and FileMaker may have different ideas about what type a result should be. Take this calculation, for example: 1 & 1 * 3 Because you're mixing concatenation (&) and math (*) operators, it's not terribly obvious what that calculation will producea number? Or just a numerical text value? So FileMaker lets you say what you want it to produce. If it doesn't do what you expect, you can easily fix the calculation, but at least you don't have to wonder what type of field you have. Furthermore, setting the type explicitly prevents FileMaker from changing it later. Imagine if a simple change to your calculation accidentally changed the result type from number to text. If you tried to reference this field in a calculation or relationship, you'd get strange results. And it might take you a while to figure out that the problem is due to FileMaker calculating a text value rather than a number, rather than a mistake in your calculation. If you've set a result type and your calculation doesn't naturally produce the correct type, FileMaker converts it for you before it stores the final result. Thus, you can always tell exactly what type the field is just by looking at Result Type pop-up menu. |
9.3.1.7. Repetitions
Like any field, a calculation field can be a repeating field (Section 3.3.4.2). FileMaker provides this option for the rare occasion when you need to calculate repeating fields. Suppose you have a repeating field that holds five quantities, and another with five prices. You can write a calculation that multiplies the two fields, and turn on this box. FileMaker takes care to match all the repetition numbers for you, so the third repetition of the calculation multiplies the third price and the third quantity, for instance. (You're more likely to have related tables, and use a standard single calculation to do the totals, but the option to calculate on repetitions is there if you need it.)
| POWER USERS' CLINIC Do Not Evaluate if All Referenced Fields are Empty |
|
When you define calculation fields (like the Extended Price and Total Due fields in this chapter), you may notice a longwinded checkbox labeled "Do not evaluate if all referenced fields are empty." FileMaker always turns this option on when you first specify a calculation. Here's what it does: Say you create a new invoice record. It's completely empty, no line items yet. If the Total Due field's "Do not evaluate when all referenced fields are empty" checkbox is turned on, that field is also completely blank, as shown here. After all, there are no referenced fields (line items), so FileMaker skips the calculation, as the checkbox says. To be perfectly accurate, however, the value of total due isn't a blank oblivion, it's zero dollars and zero cents, or $0.00. And indeed, if you turn the "Do not evaluate" option off, FileMaker goes through the calculation even when the invoice's fields are empty. For a newly created invoice, FileMaker shows the correctly formatted Total Due result$0.00. You're free to choose whichever result you prefer. There are other types of calculations where you'd want FileMaker to keep its paws off empty fields, though. Suppose you had a calculation like this: Height & " inches" If FileMaker evaluates that calculation and the Height field is empty, the result is " inches," which is pretty meaningless. In cases where something is worse than nothing, the "Do not evaluate if all referenced fields are empty" option saves you from worry. |