Now that you know your way around the Specify Calculation dialog itself, it's time to start learning more about particular calculation functions. Here we'll present an in-depth tutorial on what we feel are the most essential functions and techniques. These will form a solid base for your own work and for assembling complex formulas. As a reminder, Chapter 14 covers advanced calculation formulas and techniques, and there's a complete function reference in our companion book, FileMaker 8 Functions and Scripts Desk Reference.
The Parts of a Function
Let's begin with a general discussion about what functions do and how to learn about them. Their sole mission in life is to act on some set of inputs and produce an output. The inputs are usually referred to as parameters; the function's syntax specifies the number of parameters it expects to be fed, and provides a clue about what the nature of each of those parameters is.
An example will help clarify this point. Look at the syntax of the Position function as it's taken directly from the function list in the calculation dialog:
Position ( text ; searchString ; start ; occurrence )
A function's parameters are always placed in parentheses directly after the name of the function itself. They are separated from one another by semicolons.
In English versions prior to FileMaker 7, the parameter separator was a comma. In fact, if you use commas now, they are transformed into semicolons for you.
You can see that the Position function has four parameters. Any function reference will tell you that the first parameter should be a text string in which you want to search, and the second should be a text string you want to find within it. The third parameter is a number that specifies the character number at which to begin searching. The final parameter is also a number; it specifies which occurrence of the search string to find.
Besides knowing what to feed a function (here, two text strings and two numbers), you also need to know what type of output the function produces. Again, you first learn this by consulting some reference source or the help system. There, you'd learn that the Position function returns a numbernot just any number, of course, but the character number where the search string was found within the initial text string. If the string was not found at all, it returns a 0. So, for example, if you had the function
Position ( "Mary had a little lamb"; "a"; 1; 1 )
the function would return 2 because the first occurrence of the letter a is at character 2 of the input string. If you change the function slightly, to
Position ( "Mary had a little lamb"; "a"; 1; 2 )
you'd now expect a value of 7 because that's the position of the second occurrence of the letter a.
In these examples, all the parameters were hard-coded with constant values. More typically, the parameters that you feed a function will be either fields or the outputs of other functions. For instance, if you have a field called PoemText and another called SearchCharacter, you might end up using the Position function as shown here:
Position ( PoemText; SearchCharacter; 1; 1 )
Now, each record in your database will contain a different result, dependent on the contents of those two fields.
Using functions as parameters of other functions is called nesting. In those cases, the inner functions evaluate first, and their results are used as the inputs for the outer functions. For instance, you might have the following function:
Position ( PoemText; SearchCharacter; Length( PoemText ) - 5; 1 )
Notice that the third parameter of the Position function here is the expression Length( PoemText ) - 5. The Length function (which we'll discuss in more detail shortly) takes a single parameter, a text string, and returns the number of characters in the string. So in the preceding function, the length of the PoemText field will be determined, and that value less 5 will be used as the third parameter of the Position function. There is no practical limit on the number of layers you can use to nest functions within one another. Just remember that readability becomes very important as your calculations become more complex.
At this point, you know quite a bit about the Position function. You know about its inputs and outputs; you've worked with a few examples. Eventually, you'll likely want to memorize the inputs and outputs of a core set of functions. For lesser-used functions, you can look up the parameters and usage on an as-needed basis. There's still a difference between proficiency with a function and a complete understanding of it. For instance, to truly master the Position function, you'd need to know such things as whether it's case sensitive (it's not), and what happens if you supply a negative number for the occurrence (it searches backward from the specified start character). Over time and with use, you'll learn about the subtle and esoteric usage of various functions, thereby moving from mere proficiency to mastery.
Let's turn now to a close look at those functions and techniques that should form the core of your calculation knowledge.
Text functions enable you to interrogate and manipulate text strings. If you haven't done much programming before, the concept of a string may need some explanation. Essentially, a string is a series of characters. Think about threading characters on a string like you do popcorn to make holiday decorations, and you'll have a good mental image of a text string. The characters can be anything from letters and numbers to spaces and punctuation marks.
In versions of FileMaker prior to 7, the size limit for text strings was 64,000 characters. In FileMaker 7 and 8, it's been expanded to a whopping 2GB. This fact alone has driven many organizations to upgrade.
Typically in FileMaker, text strings are found in text fields, but be aware that you can treat any numeric, date, and time data as a text string as well. When you do that, it's called coercing the data. FileMaker automatically coerces data into the type expected for a given operation. If you ever need to override the automatic coercion for any reason, you can use the GetAs functions. These include GetAsDate(), GetAsNumber(), GetAsTime(), and GetAsText().
The simplest text operation you can perform is concatenation. Concatenation means taking two or more text strings and placing them beside each other to form a new, longer text string. As an example, consider the following formula:
FirstName & " " & LastName
Here, we're taking three strings, two of which happen to be field data, and we're concatenating them into a full name format.
Let's look next at several functions that can be used to interrogate text strings. By interrogate, we mean that we're interested in answering a specific question about the contents of a text string. For the examples in this section, assume that you have a field called fullName with the string "Fred Flintstone" and the field someString which contains "The quick brown fox jumped over the lazy dog". The following is a list of some of the core calculation functions with examples that apply to the fullName and someString fields:
What s in a Word?
Several FileMaker functions, such as WordCount(), LeftWords(), RightWords(), and MiddleWords(), TReat text strings as collections of words rather than as collections of characters. But how does FileMaker determine what constitutes a word? It's actually quite simple. There are a handful of characters that FileMaker recognizes as word separators. Spaces and carriage returns are both word separators, as you'd probably expect. Additionally, just about every punctuation symbol or other special character is considered a word separator. The two exceptions are worth knowing: Neither a period (.) nor an apostrophe (') is a word separator. Also, in versions of FileMaker prior to 7, hyphens (-) were not considered word separators, but they are in FileMaker Pro 7 and 8. If you have multiple word separators right next to each other, they're considered together as a single delimiter. For instance, the string " hello ,-, world " is considered to have two words, even though there are a total of nine word separators in the string.
The other broad category of text operators consists of those functions that enable you to manipulate a string. Whereas the interrogatory functions returned a number, these functions all return a string. You feed them a string; they do something with it and spit back another string. The text operators that fall into this category are explained in the following sections.
The simplest of these functions is the trim ( text ) function. trim() takes a string and removes any leading or trailing spaces from it. Spaces between words are not affected; no other leading or trailing characters other than a space (that is, return characters at the end of a field) are removed.
There are two common uses of trim(). The first is to identify data entry problems. Imagine you have a field called FirstName, and that some users have been accidentally typing spaces after the first name. You might want to display a message on such records, alerting users to that error. You'd define a new calculation field, called something like SpaceCheck. Its formula could be one of the following:
Case ( FirstName images/U2260.jpg border=0> Trim ( FirstName ), "Extra Space!" ) Case ( not Exact( FirstName, Trim ( FirstName )), "Extra Space!" ) Case ( Length( FirstName ) > Length( Trim( FirstName )), "Extra Space!" )
To review the use and syntax of the Case() function, see "Using Conditional Functions," p. 238.
The other common usage of trim() is in a calculated replace to clean up fixed-length data that's been imported from another application. Fixed-length means that the contents of a field are padded with leading or trailing spaces so that the entries are all the same length. After importing such data, you'd simply replace the contents of each field with a trimmed version of itself.
The next text manipulation function we'll explore is the Substitute() function. Substitute ( string; searchString; replacementString ) is used to replace every occurrence of some substring with some other substring. So Substitute( fullName; "Fred"; "Wilma") would return the string "Wilma Flintstone". If the initial substring were not found, the Substitute function would simply return the original string. You should be aware that the Substitute() function is case sensitive.
One common use of Substitute() is to remove all occurrences of some character from a string. You just substitute in an empty string for that character. For instance, to remove all occurrences of a carriage return from a field, you could use Substitute ( myString; "¶"; ""). If there are multiple substitutions you want to make to a string, you simply list them all as bracketed pairs in the order in which they should be performed. Let's say you have a PhoneNumber field from which you want to strip out any parentheses, dashes, or spaces that users might have entered. One way to do this would be to use the following formula:
Substitute (PhoneNumber; ["("; ""] ; [")"; ""] ; ["-"; ""] ; [" ", ""])
Be aware when performing multiple substitutions like this that the substitutions happen in the order in which they are listed, and that each subsequent substitution happens on an altered version of the string rather than on the original string. Say you had the string "xxyz" and you wanted to put z's where there are x's, and x's where there are z's. The formula Substitute ("xxyz"; ["x"; "z"]; ["z"; "x"]) incorrectly returns "xxyx". First, the two leading x's are turned to z's, yielding "zzyz"; then all three z's are turned into x's. If you ever want to swap two characters like this, you need to temporarily turn the first character into something you know won't be found in your string. So to fix this example, we could use the formula Substitute("xxyz"; ["x"; "**TEMP**"]; ["z"; "x"]; ["**TEMP**", "z"]). That would correctly yield "zzyx".
There are a few text functions you can use to alter a string's case. These are Lower ( text ), Upper ( text ), and Proper ( text ). It's quite intuitive how these act. Lower ("Fred") returns "fred"; Upper ("Fred") returns "FRED". Using Proper() returns a string in which the first letter of each word is capitalized. For instance, Proper ("my NAME is fred") returns "My Name Is Fred".
The final category of text operators we'll look at here is text-parsing functions. Text-parsing functions enable you to extract a substring from a string. The six text-parsing functions are Left(), Middle(), Right(), LeftWords(), MiddleWords(), and RightWords(). The first three operate at the character level; the other three operate at the word level.
The Left() function extracts a substring of length N from the beginning of some string. For example, Left ( "Hello"; 2 ) returns the string "He"; it simply grabs the first two characters of the string. If the number of characters you ask for is greater than the length of the string, the function simply returns the entire string. A negative or zero number of characters results in an empty string being returned.
The Right() function is similar, except that it grabs characters from the end of the specified string. Right ( "Hello"; 2 ) would return "lo". Middle(), as you might expect, is used to extract a substring from the middle of a string. Unlike the Left() and Right() functions, which require only a string and a length as parameters, the Middle function requires a starting position. The syntax is Middle ( text; startCharacter; numberOfCharacters ). For example, Middle ( "Hello"; 2; 3 ) yields "ell".
The LeftWords(), MiddleWords(), and RightWords() functions all operate exactly as Left(), Middle(), and Right() functions, except that they operate at the word level. One typical use of these functions is to extract names or addresses you've imported as a lump of data from some other application. Say that your import resulted in contact names coming in as full names. You might want to create a LastName calculation field so that you could sort the records. If you knew that the last name was always the last word of the FullName field, you could use the formula RightWords ( FullName; 1 ).
The text operators we have discussed often appear nested within each other in formulas.
Writing nested formulas can be tricky sometimes. One thing that helps is to think of a particular example rather than trying to deal with it abstractly. For instance, let's say that you have a big text field, and you need a formula that extracts just its first linethat is, everything up until the first carriage return. So imagine that you had the following text:
The quick brown fox jumped over the lazy dog
Think first: What text-parsing formulas would potentially yield "The quick" from this text? Well, there are several of them:
Left (myText; 9) LeftWords (myText; 2) Middle (myText; 1; 9)
Of course, at this point these formulas apply only to this particular example. Think next: Could one of these be extended easily to any multiline text field? If there were a constant number of words per line, the LeftWords() formula would work. And if not? What do the text interrogation formulas tell us about this field? Length ( myText ) is 44. Not particularly helpful. PatternCount ( myText; "¶" ) is 3. This indicates that there are four lines total. Interesting, but not obviously helpful for extracting the first line. WordCount ( myText ) is 9. It's just coincidence that this is the number of characters in the first line; be careful not to be misled. Position ( myText; "¶"; 1; 1 ) is 10. Finally, something interesting. In this example, the length of the first line is one less than the position of the first carriage return. Is that true in all cases? At this point, if you write out a few more examples, you'll see that indeed it is. Therefore, a general formula for extracting the first line of text is
Left ( myText; Position( myText, "¶"; 1; 1 ) - 1 )
How about extracting the last line from any multiline text field? You should approach this problem the same way, working from a specific example. Counting characters by hand, assemble a list of options:
Middle ( myText; 36, 8 ) Right ( myText; 8 ) RightWords ( myText; 2 )
What clues do the interrogatory functions yield? If you spend a few minutes thinking about it, you'll realize that 36 is the position of the last return character. You can derive that by using the number of returns as the occurrence parameter in a Position() function, like this:
Position ( myText; "¶"; 1; PatternCount( myText; "¶" ))
After you have the 36 figured out, recall that the length of the string is 44 characters, and notice that 44 36 = 8. Given these discoveries, you'll soon see that a simple and elegant generalized formula for grabbing the last line of a text field is
Right (myText; Length ( myText ) - Position( myText; "¶"; 1; PatternCount( myText; "¶" )))
In general, most people find working with math functions simpler and more intuitive than working with string functions. Perhaps this is because they remind us of various high-school math courses. Or it could be they typically have fewer parameters. Regardless, you'll find yourself using number functions on a regular basis. This chapter focuses not so much on what these functions do, but rather on some interesting applications for them.
The first set of functions we'll look at includes Int(), Floor(), Ceiling(), Round(), and Truncate(). Each of these can be thought of as performing some sort of rounding, making it sometimes difficult to know which one you should use. You can look up these functions in the help system for complete syntax and examples, but it's helpful to consider the similarities and differences of these functions as a set. Here's a rundown:
Which function you use for any given circumstance depends on your needs. If you're working with currency and want to add an 8.25% shipping charge to an order, you'd probably end up with a formula like Round ( OrderTotal * 1.0825 ; 2 ). Using TRuncate() might cheat you out of a penny here or there.
Floor(), Ceiling(), and Int() have some interesting uses in situations in which you want to group numeric data into sets. For instance, imagine you have a list report that prints 10 records per page, and that you have a found set of 57 records to print. If you wanted, for whatever reason, to know how many pages your printed report would be, you could use Ceiling ( Get( FoundCount )/10 ). Similarly, if you wanted to know what page any given record would print on, you would use the formula Floor ( (Get( RecordNumber )-1 )/10 ) + 1. The Int() function would yield the same result in this case.
Another common use of these functions is to round a number up or down to the multiple of some number. As an example, say you had the number 18, and you wanted to know the multiples of 7 that bounded it (...14 and 21). To get the lower bound, you can use the formula Floor ( 18/7 )* 7; the upper bound is Ceiling ( 18/7 )* 7. These generalize as
Lower bound: Floor ( myNum / span ) * span Upper bound: Ceiling ( myNum / span ) * span
The span can be any number, including a decimal number, which comes in handy for rounding currency amounts, say, to the next higher or lower quarter.
You should know a few other number functions as well:
Working with Dates and Times
Just as there are functions for working with text and numbers, FileMaker Pro provides functions that enable you to manipulate date and time fields. This section introduces you to the most common and discusses some real-world applications you'll be likely to need in your solutions.
The most important thing to understand at the outset is how FileMaker itself stores dates, times, and timestamps. Each is actually stored as an integer number. For dates, this integer represents a serialized number beginning with January 1, 0001. January 1, 0001, is 1; January 2, 0001, is 2; and so on. As an example, October 19, 2003, would be stored by FileMaker as 731507. FileMaker understands dates from January 1, 0001, until December 31, 4000.
Times are stored as the number of seconds since midnight. Midnight itself is 0. Therefore, times are typically in the range of 0 to 83999. It's worth knowing that time fields can contain not only absolute times, but also elapsed times. That is, you can type 46:18:19 into a time field, and it will be stored as 166699 seconds. Negative values can be placed in time fields as well. FileMaker doesn't have the capability to deal with microseconds; however, it can manage fractional elements: 10:15:45.99 is a valid time within FileMaker and 10:15:45.99 - 10:15:44 = 00:00:01.99. Note that this is not hundredths of a second, but rather simply a case of using a decimal instead of an integer.
Timestamps contain both a date and time. For example, "10/19/2003 8:55:03 AM" is a timestamp. Internally, timestamps are converted to the number of seconds since midnight on January 1, 0001. You could derive this number from date and time fields with the formula (( myDate - 1 ) * 86400 ) + myTime.
The easiest way to begin learning date, time, and timestamp functions is to split them into two categories: those that you feed a date or time and that return a "bit" of information back, and those that are constructors, in which you feed the function bits and you get back a date, time, or timestamp. These aren't formal terms that you'll find used elsewhere, but they're nonetheless useful for learning date and time functions.
The "bit" functions are fed dates and times, and they return numbers or text. For instance, say that you have a field myDate that contains the value 10/19/2003. Here's a list of the most common "bit" functions and what they'd return:
Month ( myDate ) = 10 MonthName ( myDate ) = October Day ( myDate ) = 19 DayName ( myDate ) = Sunday DayOfWeek ( myDate ) = 1 Year ( myDate ) = 2003
Similarly, a field called myTime with a value of 9:23:10 AM could be split into its bits with the following functions:
Hour ( myTime ) = 9 Minute ( myTime ) = 23 Seconds ( myTime ) = 10
You need to know only three constructor functions. Each is fed bits of data and returns, respectively, a date, time, or timestamp:
Date ( month; day; year ) Time ( hours; minutes; seconds ) TimeStamp ( date; time )
For example, Date ( 10; 20; 2003 ) returns 10/20/2003. TimeStamp ( myDate; myTime ) might return 10/19/2003 9:23:10 AM. When using these formulas in calculation fields, be sure to check that you've set the calculation result to the proper data type.
One very interesting and useful thing to know about these constructor functions is that you can "overfeed" them. For example, if you ask for Date ( 13; 5; 2003 ), the result will be 1/5/2004. If the bits you provide are out of range, FileMaker automatically adjusts accordingly. Even zero and negative values are interpreted correctly. Date ( 10; 0; 2003 ) returns 9/30/2003 because that's one day before 10/1/2003.
There are many practical uses of the date and time functions. For instance, the "bit" functions are often used to generate a break field that can be used in subsummary reports. Say that you have a table of invoice data, and you want a report that shows totals by month and year. You would define a field called InvoiceMonth with the formula Month ( InvoiceDate ) and another called InvoiceYear with a formula of Year ( InvoiceDate ).
A common use of the constructor functions is to derive a date from the bits of a user-entered date. Say, for example, that a user entered 10/19/2003 into a field called myDate, and you wanted a calculation formula that would return the first of the next month, or 11/1/2003. Your formula would be Date ( Month( myDate ) +1; 1; Year( myDate )).
If you're importing dates from other systems, you may need to use text manipulation functions in conjunction with the constructor functions to turn the dates into something FileMaker can understand. Student information systems, for example, often store students' birth dates in an eight-digit format of MMDDYYYY. To import and clean this data, you'd first bring the raw data into a text field. Then, using either a calculated replace or a looping script, you would set the contents of a date field to the result of the formula:
Date ( Left ( ImportedDate; 2 ); Middle( ImportedDate; 3; 2 ); Right( ImportedDate; 4 ) )
Timestamps are quite useful for logging activities, but sometimes you'll find that you want to extract either just the date or just the time portion of the timestamp. The easiest way to do this is via the GetAsDate() and GetAsTime() functions. When you feed either of these a timestamp, it returns just the date or time portion of that timestamp. Similarly, if you have a formula that generates a timestamp, you can set the return data type of the calculation result to date or time to return just the date or just the time.
Part I: Getting Started with FileMaker 8
Using FileMaker Pro
Defining and Working with Fields
Working with Layouts
Part II: Developing Solutions with FileMaker
Relational Database Design
Working with Multiple Tables
Working with Relationships
Getting Started with Calculations
Getting Started with Scripting
Getting Started with Reporting
Part III: Developer Techniques
Developing for Multiuser Deployment
Advanced Interface Techniques
Advanced Calculation Techniques
Advanced Scripting Techniques
Advanced Portal Techniques
Debugging and Troubleshooting
Converting Systems from Previous Versions of FileMaker Pro
Part IV: Data Integration and Publishing
Importing Data into FileMaker Pro
Exporting Data from FileMaker
Instant Web Publishing
FileMaker and Web Services
Custom Web Publishing
Part V: Deploying a FileMaker Solution
Deploying and Extending FileMaker
FileMaker Server and Server Advanced
Documenting Your FileMaker Solutions