Essential Functions

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.

Note

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 Operations

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.

Note

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:

• Length ( text ) The Length function takes a single argument and simply returns the number of characters in the string. Remember that spaces and return characters are considered characters. So Length ( fullName ) would return 15.
• PatternCount ( text; searchString ) The PatternCount function tells you the number of times a search string occurs within some string. As an example, PatternCount ( someString; "the" ) would return 2. Note that this function is not case sensitive. If the search string is not found, the function returns 0. Although the function returns an integer, it's often used as a true/false test when you just want to know whether something is contained in a string. That is, you don't care where or how many times the string is foundyou just care that it's there somewhere. Recall that any nonzero value represents "true" when being used as a Boolean value.
• Position ( text; searchString; start; occurrence ) You've already looked in depth at the Position function. To recap, it returns an integer that specifies the place where one string is found in another. The start argument specifies where to begin the search; the occurrence argument specifies whether you want the first occurrence, the second, and so on. Much of the time, you'll simply use 1 for both the start and the occurrence parameters.
• WordCount ( string ) WordCount is similar to the Length function, except that instead of counting every character, it counts every word. So WordCount ( someString ) would return 9, because there are 9 words in the phrase. Be careful if you use WordCount that you have a good understanding of what characters FileMaker considers as being word delimiters.

  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.

• Exact ( originalText, comparisonText ) The Exact function takes two strings as its inputs, and it compares them to see whether they are exactly the same string. It returns a 1 if they are, a 0 if not. By "exactly," we mean exactly; this function is case sensitive. The order of the two input arguments is irrelevant.

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.

Trim()

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.

Substitute()

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".

Case-Altering Functions

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".

Text-Parsing Functions

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 ).

Nested Functions

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; "¶" )))

 

Number Functions

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:

• Int ( number ) The Int() function returns the integer portion of the number that it's fedthat is, anything before the decimal point. Int ( 4.5 ) returns 4. Int ( -2.1 ) returns -2.
• Floor ( number ) Floor() is similar to Int(), except that it returns the next lower integer of the number it's fed (unless that number is an integer itself, of course, in which case Floor() just returns that integer). For any positive number, Floor ( number ) and Int ( number ) return the same value. For negative numbers, though, Floor ( number ) and Int ( number ) don't return the same value unless number is an integer. Floor ( -2.1 ) returns -3, whereas Int ( -2.1 ) returns -2.
• Ceiling ( number ) The Ceiling() function is complimentary to the Floor() function: It returns the next higher integer from the number it's fed (unless, again, that number is already an integer). For example, Ceiling ( 5.3 ) returns 6 and Ceiling ( -8.2 ) returns -8.
• Round ( number; precision ) Round() takes a number and rounds it to the number of decimal points specified by the precision parameter. At the significant digit, numbers up to 4 are rounded down; numbers 5 and above are rounded up. So Round ( 3.6234; 3 ) returns 3.623, whereas Round ( 3.6238; 3 ) returns 3.624. Using a precision of 0 rounds to the nearest whole number. Interestingly, you can use a negative precision. A precision of -1 rounds a number to the nearest 10; -2 rounds to the nearest 100, and so on.
• TRuncate ( number, precision ) TRuncate() is similar to Round(), but truncate() simply takes the first n digits after the decimal point, leaving the last one unaffected regardless of whether the subsequent number is 5 or higher. TRuncate ( 3.6238; 3 ) returns 3.623. For any number, truncate ( number; 0 ) and Int ( number ) return the same value. Just as Round() can take a negative precision, so can truncate(). For example, truncate ( 258; -2 ) returns 200.

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:

• Abs ( number ) The Abs() function returns the absolute value of the number it's fed. There's nothing tricky to understanding the function itself, but there are a few handy uses you might not think of. One is to toggle a flag field between 0 and 1. The formula Abs ( Flag-1 ) always "flips" the flag. If Flag is 0, Flag-1 is -1, and Abs (-1) is 1. If Flag is 1, then Flag-1 is 0, and Abs (0) is 0.
• Mod ( number; divisor ) The Mod() function returns the remainder when a number is divided by a divisor. For instance, Mod ( 13; 5 ) returns 3 because 13 divided by 5 is 2, remainder 3.
• Div ( number; divisor ) The Div() function is complimentary to the Mod() function. It returns the whole-number result of dividing a number by a divisor. For instance, Div ( 13; 5 ) would return 2. In all cases, Div ( number; divisor ) and Floor ( number/divisor ) return exactly the same value; it's a matter of personal preference or context which you should use.
• Random() The Random() function returns a random decimal number between 0 and 1. Usually, you'll use the Random() function when you want to return a random number in some other range, so you'll need to multiply the result of the function by the span of the desired range. For instance, to simulate the roll of a six-sided die, you'd use the formula Ceiling ( Random * 6 ). To return a random integer between, say, 21 and 50 (inclusive), the method would be similar: First you'd generate a random number between 1 and 30, and then you'd add 20 the result to translate it into the desired range. The formula would end up as Ceiling ( Random * 30 ) + 20.

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.