Operators, along with fields, constants, and functions, are one of the basic building blocks of a calculation. FileMaker has five operators that specifically apply to numerical calculations, the same five that you probably already know:

Section 9.2.2.2 explains how to use these operators in detail. In your day-to-day work with FileMaker, you usually use these operators to calculate the numerical values in your fields.

Say you’re an event planner and you always plan for 10 percent above the expected number of attendees. Use this calculation to find your fudge factor:

	Sum ( Attendees::Confirmed ) * 1.1

FileMaker also gives you dozens of functions that do special things to numbers. Because there are so many number functions, the function list breaks them up into smaller groups. The groups have descriptive names, so you can drive right to the group you need (or skim by them without looking, if you might be traumatized if you accidentally saw a sine or cosine function). The functions you use with your numeric data are:

As you saw on Section 9.2.4, every number function expects one or more parameters, each a number value, and all the number functions return a number result. Here’s how to put them to work:

Some calculations demand a high degree of precision, like those that track radioactive isotopes or other scientific data with lots of places following the decimal point. FileMaker comes prepared with the SetPrecision function. This function extends FileMaker’s default precision of 16 decimal places, up to a maximum of 400. If you need more precision, you add a SetPrecision function to the calculation that produces the value that requires precision. You can use SetPrecision with all other numeric functions, except trigonometric functions, which don’t accept this extended precision. See Figure 10-1.

Figure 10-1. The Default Result field shows the result of dividing one by three. In the field called Precise Result, the calculation is changed to use the SetPrecision function (as you can see in the background window). This version gives a more precise answer.

The SetPrecision function requires two parameters. The first parameter is a number, or any expression that results in a number. The second is the number of decimal places you want to see.

This calculation would return Pi with 3 decimal places:

	SetPrecicion( Pi ; 3 )

This version would show 100 decimal places:

	SetPrecision( Pi ; 100 )

You’ve decided to start reselling personal security products to your customers. To help you keep track of things, you need to add a Products table to your database. The table should have these fields:

Once you’ve defined this new table, FileMaker automatically creates a layout—Products. Switch to that layout now and create a few product records. To help drive sales to your larger clients, you want to implement a volume discount scheme—they should get a 5 percent discount if they buy enough. But some of the products you sell don’t have enough mark-up to justify these discounts. You want to be sure the discount never reduces your mark-up below 20 percent.

First, you add a line to your marketing materials: “Volume discounts not available for all products.” You can make this line as small as humanly possible and hide it way down in the corner. Next, you need to fix your database so it tells you the discount price for each product.

To implement this discount scheme, take what you need to know and translate it into calculation terms. You need three fields to carry out your sales promotion logic.

Using the Max function, the previous calculation results in either the discounted price, or the minimum price, whichever is greater (see the box on Section 10.2.3). That result’s almost perfect. But suppose you have a product whose normal price is less than 20 percent above cost (hey, it’s a competitive market). If you use the Max calculation as it is now, the new discounted price is more than the normal price. You need to go back and add to your calculation so that it takes the regular price into account, and uses that price if it’s lower than the calculated discount. Read on to learn how to think through this calculation quandary.

	Max ( 10 ; 3 ; 72 ; 19 ; 1 )

	Min ( Distances )

	Max ( Line Items::Price )

When calculations start to get complicated like this discount price example, imagine that you have a field that contains the value you already want. You can use this pretend field in your new calculation, and then, when you’re all finished, put the old calculation in place of the pretend field. In this case, just pretend you have a field called Calculated Discount that holds the discount price. With that imaginary field in mind, you can fix your problem with this calculation:

	Min ( Calculated Discount ; Price Each )

The result of this calculation is either the calculated discount or the regular price, if it’s lower. Now, just put the old calculation in place of the words “Calculated Discount” (since the old calculation results in the calculated discount):

	Min ( Max ( Price Each * .95 ; Cost * 1.2 ) ; Price Each )

The entire Max function, complete with its two parameters, is now the Min function’s first parameter. You might think it looks a little confusing at first, but with practice you become accustomed to looking at functions-inside-functions like this.

If it helps, you can add white space and comments to clarify the calculation, as Figure 10-2 shows.

Figure 10-2. You may find it hard to write nested calculations, and hard to read them if you need to come back later and tweak them. In this case, though, a savvy person formatted the calculation with copious white space and plenty of comments. Chances are you’ll understand this version better than the one shown on Section 10.2.3.

To use the calculation, just create a new calculation field (call it Discounted Price) with the calculation above. It automatically adjusts and shows you an appropriate discounted price.

If you want to test a complicated calculation, spot-check a few records where you know the result. Sometimes the math is so complex that you just have to work it out on paper and enter dummy records to check the calculation. Usually, if a calculation isn’t working, you can figure out how to fix it when you compare your math to the value in the field.

In contrast to the wide variety of mathematical operators for working with numbers, there’s only one that pertains specifically to text—the concatenation operator. Represented by the & sign (ampersand), it strings bits of text together. (When you need to chop and divide your text in order to parse it, you use a function instead of an operator, as described on Section 10.3.2.)

To use it, put it between units of text, as in the expression below:

	"This is a " & "test"

The result of this calculation is This is a test.

The concatenation operator lets you combine text from two different fields and make them work better together. For example, when you set up the Jobs layout in your database in Chapter 8, you had to settle for a compromise. When creating the value list for the Customer pop-up menu (Section 8.1), you could pick only one field to show along with the ID value. That example used the First Name field, but the full name would make the menu more useful. With a calculation, you can do just that.

Create a new field that shows what you want in your value list. Add a new calculation field to the Customers table, called Full Name. Use this calculation:

	Last Name & ", " & First Name "

Some results might be “Gribble, Dale” or “Hill, Henry.” Note that the calculation includes a comma and the appropriate spaces for separating data between your fields. Now you can modify the All Customers value list to take advantage of the new field. Just change it to use the new Full Name field instead of the First Name field. Figure 10-3 shows the result.

Figure 10-3. The background window shows how the pop-up menu looked before the calculation field. In the front, you can see how it looks now. People often use calculations to combine multiple field values like this for display purposes.

FileMaker’s text-handling functions come in two flavors: Text functions and Text formatting functions. Text functions handle tasks like the parsing mentioned above, or finding whether a particular string of characters occurs in a field. You can change all instances of specific characters within a field, or count text length, with text functions.

Text formatting functions change the way your text looks, like making a part of the text in a field bold and red. These functions are a lot more flexible than just making a field bold and red on your layout, because you can tell the calculation to search inside the field, find just the characters “Propane Sale!” and make them red, while it leaves all the surrounding text untouched.

Many of the text functions exist to help you parse text—split it apart in useful ways. Sometimes one text value contains multiple useful pieces of information, and you need to look at them individually. If you’re lucky, the information comes in a form that FileMaker already understands. If you’re not so lucky, you have to do some extra work to tell FileMaker exactly how to divide the text value.

FileMaker can automatically break text up three ways: by characters, words, or values. When it does the dividing, it gives you three ways to decide which parts you want: Left, Middle, or Right.

	"http://www.mapquest.com/maps/map.
	adp?country=US&addtohistory=&address=" &
		Customers::Street Address &
	"&city=" &
		Customers::City &
	"&state=" &
		Customers::State &
	"&zipcode=" &
		Customers::Zip Code &
	"&homesubmit=Get+Map"

	Left ( Model Number ; 3 )

	Middle ( Model Number ; 1 ; 3 )

	Middle ( Product Number ; 4 ; 3 )

	Right ( Left ( Product Number ; 7 ) ; 3 )

	LeftValues ( Colors ; 2 )

	Get Value ( Email ; 2 )

	RightValues (Premiums ; 1 )

	LeftValues ( Premiums ; 2 )

	RightValues ( Premiums ; 2 )

	Left ( My Field ; Length ( My Field ) - 1 )

Normally when you see data in a calculation field, it’s displayed in the format (font, size, style, color, and so on) you applied in Layout mode. Every character in the field shares the same format, unless you want to manually search through all your records selecting the words “Limited Time Only” in your Promotion Notes field, so you can make that bold and red every time it appears. Not only does that method waste your precious time (especially if you’re on salary), it also plays havoc with your design when you try to print the field.

FileMaker’s Text Formatting functions let you specify exactly what bit of text you want in 18-point, boldfaced, red Verdana. And you don’t have to visit a single record in person. You just write a calculation and FileMaker does the drudgework for you, without tampering with the real data.

FileMaker has six text formatting functions, as described below.

	TextColor ( First Name ; RGB ( 255 ; 0 ; 0 ) )
		& " " &
	TextColor ( Last Name ; RGB ( 0 ; 0 ; 255 ) )

	RGB (255 ; 255 ; 0)

	TextFont ( "Dewey Defeats Truman!" ; "Times New Roman" )

	"Do it with " & TextStyleAdd ( "style" ; Italic )

	TextStyleRemove ( My Text Field ; Italic )

Before you starting writing date and time calculations, you need to know how FileMaker actually keeps track of dates and times. FileMaker internally stores any date or time value as a single number that makes sense to it. Then, when it needs to display a date or time, it converts the number to a value people recognize as a date or time, like “11/7/2005” or “10:23 AM.” As with other numbers that it stores one way and displays another, FileMaker does the math on the stored value, then converts it for your convenience. Here’s how FileMaker keeps track of time:

This secret to date and time storage isn’t just a technicality. It actually tells you a lot about how you can use dates and times in calculations. In the next few sections, you’ll see how you can use simple math to do temporal magic.

Because FileMaker looks at dates and times as numbers, you’re free to use them right along with other numbers and operators in all kinds of mathematical functions. By adding, subtracting, multiplying, and dividing dates, times, timestamps, and numbers, you can come up with meaningful results.

Just as you can parse out bits of text from text fields, FileMaker lets you pull out parts of a date or time value. For example, you might keep track of all your employee’s birthdays in a normal date field, but you’re trying to get statistical data from the year they were born, so you’re not concerned about the month or date part of that value. You have six functions at your disposal to pick those individual components from a date, time, or timestamp value. They are:

With a date value, you can use Year, Month, and Day. If you have a time, Hours, Minutes, and Seconds apply. You can use all six functions with a timestamp value.

These functions all have the same form. Each takes a single parameter—the value—and returns a numerical result. For example, the Day function returns the day portion of a date. This calculation returns 27:

	Day ( GetAsDate ( "7/27/2006" ) )

	MonthName ( Invoice Date )

Almost every database in existence has fields that create date and time values. Otherwise, folks would still sit around pecking out business forms with a typewriter, an adding machine, and a wall calendar. It sounds quaint, but there was one advantage—the human brain. Without even being aware of it, people do incredibly complex math every time they glance at a paper calendar or analog clock. When the boss said, “I want these invoices to go out two days after the end of next month,” a human clerk knew exactly what to do.

When you work with dates and times in FileMaker, sometimes you can get away with just simple math as in the previous section, plugging date and time values into basic calculations. But how do you tell a computer to put “two days after the end of next month” in the Invoice Date field? FileMaker provides three functions to assist the translation:

	GetAsDate ( "1/10/2006" )

	Date ( 5 ; 0 ; 2006 )

	Date ( 15 ; 11 ; 2006 )

	Month ( Order Date ) + 1

	Date ( Month(Order Date) + 1 ; 1 ; Year(Order Date) )

	Date (
		 Month(Order Date) + 1;	// the _next_ month
		 1;				  //the _first_ day
		 Year(Order Date)	   // the same year
	)- 1				 // subtract 1 to get the day before

When you create a calculation field (Section 9.1.1), you can set its result type to Container. You can’t create container data in a calculation, but you can refer to other container fields. When you do, the picture, sound, movie, or file in the referenced container field shows in the new calculation field.

You can, for example, make a calculation field that shows the contents of one container field when you’re in Browse mode and another in Preview mode. This field lets you use low-resolution images when you view on the screen, and higher resolutions when you print. (You’ll learn to do that in the next chapter, when you learn about calculations with that kind of decision-making ability.)

You might also want to use container fields in a calculated replace. Suppose you have a found set of 30 records that don’t have a low-resolution image. You have to have something in that field, so you decide to take the hi-res image for those few records and plunk them down in the low-resolution image field. Choose Records → Replace Field Contents and perform this calculated replace:

	Graphics::High Resolution Image

The entire calculation consists of a reference to a field of that name in the Graphics table. The calculation does the grunt work of copying the high-resolution image into the low-resolution field in each record.

If a container field holds a reference to a picture, movie, sound, or file, instead of the object itself (Section 2.7.4), you can do even more. When you treat such a field as text, FileMaker gives you some information about the referenced file.

If you have a field called Product Shot (see Figure 10-4) that holds a reference to a photograph file, you can use this calculation:

	GetAsText ( Product Shot )

Figure 10-4. FileMaker also includes two paths identifying the file’s location. One’s a relative path, starting from the location of the database file. The second’s an absolute path, meaning it starts from the top level of your computer’s system of files and folders.

The result of this calculation looks like this:

	size:266,309
	image:../../../../../quilt database/sale quilt pix/batik squares.jpg
	imagemac:/babycakes/current work/quilt database/sale quilt pix/batik squares.
	jpg

As shown in Figure 10-4, FileMaker tells you the size (width and height in pixels) and location of the file (if this weren’t a picture, you wouldn’t see the “size:” line).

You can use this calculation to help you keep track of a set of images that the whole company needs to use. You really need two container fields for this purpose. One holds the graphic itself, or a low-resolution copy, if you don’t want the file size to balloon. Then, when you place the graphic as a reference in the second container field, the calculation stores the graphic’s original location. The calculation’s not dynamic, however, so the path serves as a reference of where the file should be, not where it really is. Company policy about putting things back where you found them has to reinforce your good data practices.