The Structure of a Calculation

If you accidentally mistyped or misspelled a field name in the example above, you already know that FileMaker is a little picky about how you create a calculation. Clearly, syntax (the order of elements and the punctuation in your calculation) matters. Still, a calculation is really just text, arranged in a specific order. Here you learn the basic parts of a calculation through a couple of examples.

Tip: Calculations are often called calcs for short, or formulas. Although there are some slight differences between a calculation and a formula, people usually use the terms interchangeably. Note: Sometimes, a formula is so useful, or common, that FileMaker defines it as a reusable formula, also known as a function (Section 9.3.1.5).

A calculation can be short and simple:

Pi * Diameter

Or it can be more complicated:

Case ( Shape = "Circle" ; Pi * (Diameter/2) ^ 2 ; Shape = "Rectangle" ; ShapeLength * Width ; )

Note: FileMaker won't let you name a field the same thing as an existing function. The field containing the length of a rectangular object has the unwieldy name "ShapeLength" to avoid confusion with FileMaker's Length function. The field name "Width" is fine, because there's no Width function.

In fact, calculations can be really long and complicated if you need them to beup to 30,000 characters. Practically speaking, the only limit on the complexity of a calculation is your patience for creating it.

Regardless of its complexity, a calculation, or formula, is made up of three different elements: field references, constants, and operators. In the first example, "Pi" is a constant, "*" is an operator, and "Diameter" is the name of a field. The second example uses a function, called a case statement. In that example, "Shape" and "Diameter" are field references, "Circle" and "2" are constants, and "=," "*," and "^" are operators.

Field references have values that FileMaker replaces in the formula once it goes to work on your formula. First, all the values are replaced, then the operators tell FileMaker what to do to those values, and finally, FileMaker returns a result in your field.

You'll see these terms again in the next few chapters. Here are some helpful definitions:

• Field References are just what they sound like. They refer FileMaker to data that's in the field you're referring to. Since the data inside those fields may change on each record in your database, the values that get replaced in each record are different and may yield a different result.
• Constants stay the same each time FileMaker does the calculation. See Section 9.2.2 for details.
• Operators tell FileMaker how to treat the values in the calculation. See Section 9.2.2.2 for a listing of operators and what they do.
• FileMaker has more than 180 defined Functions that you can use as shortcuts when you create your formulas. You learn about some of the most common functions later in this chapter. Chapter 11 introduces you to more advanced functions, and in Chapter 12 you learn how FileMaker Advanced lets you create your own reusable functions, called Custom Functions.
• Each calculation has a Result. This result is, in a sense, the "answer" to the calculation. The result of the first calculation above is the radius of the circle. The second calculation is a little more complex: Its result is the area of a circle or a rectangle, depending on the value in the Shape field. (Don't worry if this calculation doesn't make sense to you. It will before too long.)

  The result of a calculation has a type (just like every field has a type). The type can be any of the standard field typesText, Number, Date, Time, Timestamp, or Containeror a type called Boolean. A Boolean value has only two possible results: yes or no. (Chapter 10 goes into more detail about calculations and data types.)

Note: Sometimes people call a Boolean value "True or False" or "One or Zero" instead. Which term you use doesn't matter much if you just remember that there is a yes-like value and a no-like value.

9.2.1. Fields

The most exciting thing about calculations is learning all the new things you can do with the fields that hold all your data. But the underlying principle is simple: When you reference a field in a calculation, FileMaker takes the data stored in the field and uses its value when it determines the calculation's result. For example, if a field has this calculation:

First Name & " " & Last Name

When FileMaker performs (or evaluates) the calculation, it replaces the First Name and Last Name field names with the person's first and last names from a given record. The field type determines the value's type: A number field has a number value; a text field has a text value; and so forth.

Note: FileMaker uses the value stored in (not displayed in) a field. So if you have a number field with 3.1415926 as the stored value, and you've formatted the field on the layout to display only two decimal places, FileMaker uses all seven digits of the stored value to do its math. If you don't want it to use all those digits, use the Round function (Section 9.3.1.1).

9.2.2. Constants

As handy as it is to refer FileMaker to a field to find the values in your calculations, you don't want to have to store everything in fields just to use it in a calculation. When a value is going to be the same for every record, it's time to call in a constant. You simply include that value right in the calculation.

9.2.2.1. Number constants

Sales tax is one of the most common constants. If you need to add sales tax to your order, you can just type the percentage right in the calculation, since it's the same for everybody:

Order Total * 1.0625

You can enter numbers in any of the formats supported by number fields:

• 37
• .65
• 28.3
• 6.02E23

9.2.2.2. Text constants

You can also use a constant to have FileMaker plunk some text in with your results. If you want a text value instead of a number, put it in quotes:

Age & " years old"

Everything within the quote marks is a text constant (some people call it a string as in "string of characters"). Those quote marks are very important (see box "Quotes in Quotes"). Suppose you have a field called First Name, and a calculation like this:

"This is my First Name"

The quote marks enclose the text that is also a field name, so the result of this calculation is always (constantly) "This is my First Name." FileMaker makes no connection whatsoever between the First Name field and the words "First Name" in the text, because the text is in quote marks.

Forgetting quote marks around a text string, or putting them in the wrong place, can make FileMaker whiny. If you make the following calculation:

"This is" my First Name

FileMaker throws up a warning message that says, "The specified field could not be found," when you try to click OK to close the Define Database dialog box. The characters "my First Name" are highlighted in your calculation so you can tell exactly which part of the calculation confuses FileMaker. Move your quotes appropriately, and FileMaker stops telling you that it can't find your field reference. Here's the correct answer:

"This is my " & First Name

9.2.3. Operators

If a calculation could only contain one thing, it wouldn't be much good: How often do you want to calculate a field value, number, or text value you already know? The power of calculations comes from their ability to combine different values to come up with a new and interesting value. This situation is where operators come in. An operator takes the values on either side of it (the operands) and does something (operates) with them.

A special symbol or word stands for each operator. This calculation uses the + (addition) operator:

3 + 2

In this case, the + operator is given 3 and 2 as operands. When the calculation evaluates, the operator and its operands combine to produce a single value.

Operators come in three flavors:

• Mathematical and logical operators combine two values into one. The + operator is a good example; It takes two number values and adds them together. Its resulting value is the sum of the two numbers.
• Comparison operators compare two values. For example, the = operator tells you if two values are exactly the same. This kind of operator always produces a Boolean value.
• Parenthesis operators are used to group parts of a calculation together. Remember your 8th grade math teacher carrying on about the "order of operations"? FileMaker remembers too, and it uses those same rules to figure out how to evaluate calculations. If the rules don't work for you, parentheses let you take more control.

Note: Two buttons in the Operators section of the Specify Calculation window aren't really operators at all. The is a special character that tells FileMaker you want a new line in your calculation result and the quote marks are for entering text values, as explained in the box above.

9.2.3.1. Mathematical operators

The most obvious use for a calculation is to do a little math. Maybe your database of products includes fields for dimensions (Length, Width, and Height) and you want to know the volume. This calculation does the trick:

Length * Width * Height

It consists of three field values and two copies of the * (multiplication) operator. The result is just what you'd get if you used a calculator to multiply the three field values together.

FileMaker includes operators for basic math:

• + for addition
• - for subtraction
• * for multiplication
• / for division
• ^ for exponentiation ("to the power of" a value)

9.2.3.2. The concatenation operator

While the mathematical operators combine numbers, the & (concatenation) operator works with text. It hooks together two text values:

Length & " inches"

If the Length field contains 36, the result of this calculation is 36 inches.

Tip: FileMaker is such a good friend that it can mix numeric values and text together in the same calculation. If you use a number value or field in a place where FileMaker is expecting text, it just converts the number into text and gets on with its business.

9.2.3.3. Comparison operators

You often need to compare two values to learn about them. For example, you may need to add an additional shipping charge if the total weight of an order is more than 20 pounds. All comparison operators result in a Boolean value (Section 6.6.1.1).

FileMaker can compare things in several ways:

• = tells you if two values are the same.
• or <> tells you if the two values are different.
• < tells you if the second value is bigger than the first.
• or >= tells you if the first value is bigger than or equal to the second.
• images/U2264.jpg border=0> or <= tells you if the second value is bigger than or equal to the first.

9.2.3.4. Logical operators

The logical operators evaluate values and come up with a Boolean (Yes/No) result (Section 6.6.1.1). Unlike the other operators, most of them are recognizable words.

• The and operator tells you if both values are Yes. The calculation below uses the and operator. It evaluates to Yes if the length is more than 3 and the height is more than 5.

  Length > 3 and Height > 5

• The or operator tells you if either value is Yes. The or calculation below evaluates to Yes if the length is more than 3 or the height is more than 5.

  Length > 3 or Height > 5

• The xor operator's function is as offbeat as its name. It stands for exclusive or. The xor operator tells you when only one of your two choices is Yes. Put another way, if you find yourself thinking, "I want one of two things to be true, but not both of them," then xor saves the day. For instance, you may want to track whether you've billed a customer or you've marked her character rating below 3. That formula would look like this:

  Invoice Sent = Yes xor Character Rating < 3  

  ---

  Note: If you can't think of a use for xor, don't worry. Most of the time when you need an "or" calculation, you can handle it with plain old or and not exclusive or.

  ---

• The last logical operator, not, stands alone: It only works on one value, not two like every other operator. It simply reverses the Boolean value that comes after it. So the calculation below would evaluate to Yes if the length is not more than 3.

  Not Length > 3

Note: The comparison and logical operators are usually used with the logical functions. Those are covered in Chapter 10.

9.2.3.5. The ^ Operator

The lastand probably least usedoperator is the exponentiation, or ^ operator. This lets you calculate exponents:

Pi * Radius ^ 2

This calculation uses the exponentiation operator and squares the value in the Radius field.

9.2.3.6. Parentheses

FileMaker uses standard mathematical rules to decide in what order to evaluate things. The order of evaluation is exponentiation, then multiplication/division, then lastly addition/subtraction. If you need FileMaker to do part of your calculation first, before moving onto any other operators, put it in parentheses. The parentheses tell FileMaker to treat everything between them as a single unit.

In the calculation below, FileMaker multiplies 3 and 2 before adding 4, and gives you a result of 10.

4 + 3 * 2

Even though the + operator comes first in the calculation, FileMaker follows the order of calculation. If you want to add 4 and 3 before multiplying, you need to use parenthesis:

(4 + 3) * 2

Thus, it sees that it needs to multiply 2 by the value of (4+3). You can see the value of parentheses in calculations like the one below, which calculates the interest on the sum of the balance and service charge. Without the parentheses, FileMaker would calculate the interest on only the service charge and then add that to the balance due, with an entirely different result:

(Balance Due + Service Charge) * Interest Rate

Note: If you have trouble remembering (nay, understanding) the order of calculation, just use parentheses when in doubt. It certainly doesn't hurt to be too explicit.

9.2.4. Functions

Values, fields, and operators alone make for a pretty powerful combination. But if you stop there, you've only scratched the surface. The meat of calculations is found in the Function list (which you saw back in Figure 9-3). A function is simply a predefined formula, and FileMaker's list covers most common calculation purposes. If you find a function that already does what you want to dolike average all invoices over a specific time perioduse it. When you add these tried-and-true formulas to your calculations, you save time and even help prevent errors.

For example, if you didn't know about functions, you could find your average with a series of fields. First, you'd need to define a calculation field to total all the invoices in your found set. Then you'd need another field to count the invoices in the set and a third one that divides the first field by the second. It would work, but it'd be clumsy and inefficient, since you've created at least two fields that you didn't really need.

Because you often need to find averages, FileMaker gives you a function that handles the math in a given field. All you have to do is tell FileMaker which field you want to average. The function takes care of figuring out the total of the found set and how many records there are. It looks like this:

Average ( Invoice Total )

The word "Average" is the function's name. "Invoice Total" is a reference to a field. This field reference is called a parameter. Parameters tell the function how to perform its specific calculation. The Average function has only a single parameter, but many functions have two or more.

Parameters are always enclosed in parentheses. (A few functionsmost notably, Randomdon't need any parameters, so you leave the parentheses off all together.) When there's more than one parameter, they're separated by a semicolon, as in the Date function below:

Date ( Month ; Day ; Year )

FileMaker has more than 180 functions, divided into 16 groups, as described below. Later in this chapter, you learn how to use some of the more common functions. (Functions come into play in Chapters 10 and 12, as well.)

Note: FileMaker has a lengthy help file (Ctrl-? Or -?) that lists each function and some sample uses. If you want to explore a function that isn't covered here, open Help, then type in the function's name.

9.2.4.1. Text functions

Dozens of text functions let you work with text values. You can compare them; convert them into other types (like numbers); split them up in various ways; count the number of letters, words, or lines; change case; and replace parts of them with new text values. If you're trying to slice, mix, or examine words, look here first.

9.2.4.2. Text formatting functions

Text formatting functions let you adjust the font, size, style, and color of all or part of a text value. For instance, you could make the account balance for a customer turn red if it's over $100.

9.2.4.3. Number functions

Number functions do everything with numbersfrom the mundane (rounding) to the esoteric (combinatorics). In between, you can get rid of the decimal part of a number, calculate logarithms and square roots, convert signs, generate random numbers, and perform modulo arithmetic.

9.2.4.4. Date functions

Date functions make working with dates a breeze. You can safely create date values without worrying about the computer's date settings. You can also pick date values apart (for example, get just the month from a date), convert day and month numbers into proper names, and work with weeks and fiscal years.

9.2.4.5. Time functions

Time functions are few: They create time values from hours, minutes, and seconds, and split times up into the same parts. You use these values most frequently when you're trying to find out how long something took. For instance, if you bill your services hourly, you can create Start Time and Finish Time fields. Then, in a Duration field, you can subtract Finish Time from Start Time to find out how long you worked on a project.

9.2.4.6. Timestamp functions

There's only one Timestamp function: It lets you build a timestamp value from a separate date and a time. If you're creating your own data, you already know that FileMaker needs both a date and a time for a valid Timestamp field and you've planned accordingly. But you may receive data from an outside source in which the date and time aren't already in a single field. No problem, just use the Timestamp function.

9.2.4.7. Aggregate functions

Aggregate functions calculate statistics such as average, variance, and standard deviation. They can also count things, sum things, and find minimums and maximums. By definition, aggregate functions gather up multiple values and find results based on the group as a whole. (See the box below for more detail.)

9.2.4.8. Summary functions

There's only one Summary functionGetSummary. Its sole purpose is to let you use the value of a summary field (Section 6.9) in your calculations. In the olden days, before FileMaker was the robust relational database it is now, the GetSummary function was the best way to sort and summarize certain kinds of data. Now that FileMaker is relational, you use calculations through table occurrences to do that work, as you saw in the last chapter.

Note: GetSummary is included in FileMaker 8 so that files created in older versions still work. Nowadays, though, GetSummary serves primarily as a way for one FileMaker developer to ridicule another as in, "Get a load of that guy. He thinks he's a hotshot, but his database is spilling over with GetSummary functions."

9.2.4.9. Repeating functions

Repeating functions work with repeating fields, and some of them work with related fields as well (see Part 4). You can make non-repeating fields and repeating fields work together properly in calculations, access specific repeating values, or get the last non-empty value. Since repeating fields have limited uses in these days of related tables within files, so do these functions. However, there are a few valid uses, as you'll learn in Chapter 11.

9.2.4.10. Financial functions

Financial functions make the MBAs in the audience feel right at home. Calculate present value, future value, net present value, and payments. Non-MBAs could calculate the cost of competing loans with these functions.

9.2.4.11. Trigonometric functions

Trigonometric functions, on the other hand, bring back terrible memories from high school math. If you're making a business-related database, don't worry; you don't even have to look at these functions. But engineers and scientists will know what to do with this bunch: sine, cosine, and tangent. They can also convert between radians and degrees. And because everybody has trouble remembering it, you get Pi out to 400 decimal places.

9.2.4.12. Logical functions

Logical functions are a powerful grouping. These functions can make decisions based on calculated values (if the due date is more than six months ago, double the balance due); functions to evaluate other calculations inside your calculations; functions to figure out if fields are empty or contain invalid data; performance enhancing functions to define and use variables (Section 11.3); and functions to perform lookups inside calculations (Section 11.2.1.3). Chapter 11 is where you learn when and how to use these big dogs of the function world.

9.2.4.13. Get functions

Get functions pull up information about the computer, user, database, or FileMaker Pro itself. They make up the largest group (70 in all). You can, for example, find out the computer's screen resolution, the current layout's name, the computer's network address, the current user's name, or the size of any database window. This list just scratches the surface, though. If you're looking for information about the current state of FileMaker, the computer, or the user, you can probably find it with a Get function.

9.2.4.14. Design functions

Design functions tell you about your database's structure. You can get a list of tables, fields, layouts, or value lists; or details about any of these items. You won't need most of these functions until you become an advanced database designer indeed. But one notable exception is ValueListItems, which gives you a list of the values in a value list, separated by paragraph breaks.

9.2.4.15. Custom functions

If you have FileMaker Advanced, you can create your very own custom functions and have them show up on the list. Once you have them, you (or anyone you let define fields in your database) can choose them just like the built-in functions. (See Chapter 12 for details on creating and using custom functions.)

9.2.4.16. External functions

If you're not using plug-ins or FileMaker Server, your External functions category is empty. If you've installed any plug-ins ("mini-programs" that add extra features to FileMaker), they probably brought along some functions for their own use. FileMaker stores them in this category. FileMaker Server also uses plug-ins, ironically to help you update your third-party plug-ins. (External plug-ins are covered on Section 12.2.)

9.2.5. Expressions

Expression is a fancy name for a subsection of a calculationone or more fields, functions, or constants, each connected with operators. When you made the first calculation in this chapter (Section 9.1.1), you multiplied the contents of the field called Price Each by the contents of the field called Quantity. That's a calculation, but it's also an example of an expression.

An expression always reduces to a single value when you combine its individual values according to the operators. If you can't boil it down to a value, it's not an expression. That's an important point, because it means you can use expressions as function parameters (Section 9.3.1.3) just like any individual valuesfields and constants. When used in a function, these expressions are called sub-expressions.

Here are some examples of expressions:

The following is a simple expression, which reduces to the value 6.

3 + 3

Below is a more complex expression. It might turn into something like "Miller, Thomas J."

Last Name & ", " & First Name & " " & Middle Initial & "."

The following calculation is a function and it's an expression, because it reduces down to a single value.

Average(L1 * W1 * H1 ; L2 * W2 * H2 ; L3 * W3 * H3)

But if you look at just the stuff in parentheses, you have this:

L1 * W1 * H1 ; L2 * W2 * H2 ; L3 * W3 * H3

That's not an expression because it doesn't reduce down to one value. It has three expressions in all, each separated by a semicolon. Each expression reduces to a single valuethree values in allwhich become parameters passed to the Average function.

You can put any valid expression in place of a parameter in a function. In the trade, that's called nesting expressions. For example, the expression 3 + 3 above could be rewritten like this:

(1 + 1 + 1) + 3

In this case, the sub-expression (1 + 1 + 1) has replaced the original value 3. The whole thing is a new expression, and it contains one sub-expression. This idea comes in particularly handy when you work with functions. Instead of using individual fields or constants in a function, you can pass along whole expressions. You can even nest functions within other functions (Section 11.3.2.2).