Read an Excerpt
Excerpt Chapter
12: Real-World Calculations-Part 1
- FileMaker Pro leaves a Rolodex in the dust once you get started using the operators and functions. This is where you turn your data into useful information. This is also where you check and correct data. You can take large amounts of data from other sources and whip it into shape, making all of it fit the proper format.
Rather than describing each of the functions, I'll show you some basic concrete examples and tell you about several hidden specifics. Then I'll show you tricks for combining functions into more complex calculations. That way you'll have a far better idea of how to make your own. The functions are listed in the manual and in the FileMaker Pro Help files.
FileMaker's Help Files
- Starting with this chapter, knowing how to use the FileMaker Pro Help files will be extremely valuable. The Help files cover each function in detail and give you comprehensive alternative examples.
To use the Help files, you need to have installed them previously. If you did a full install of the FileMaker Pro application, the Help files should already be available. Choose Help, Contents and Index. (If that choice is not available, you'll need to get out your CD and follow the instructions for a custom install. Then install only the Help files.) When you choose the Index or Find tabs in the Help files, you can type directly in a text area to find more information on your topic. Otherwise, you can go to the Contents tab and click on the list. Each click takes you deeper, outline-style, until you find the topic for which you're searching.
The Help files have become increasingly useful in the past few years, providing better examples and some extremely useful solutions.
What are Functions?
- Put simply, a function is a formula that crunches data. You choose the function, give it the data to operate on, and it gives you the result. The functions are available from the Specify Calculation dialog box, wherever you may find it. That includes when you're defining a Calculation field, when using the Replace menu choice, when the "Validated by Calculation" check box is selected in Field Definitions, and from within the Set Field, Insert Calculated Result, and the Replace script steps.
Some of the functions are fairly easy to understand, and some are quite deep. Not only that, but you can combine even the easiest functions into complex expressions. You don't need to know them all in order to do well with FileMaker. But you should know which functions are available and where to find out how to make them work for you when the time comes.
The first group of functions operate on specific types of data: text, number, date, and time.
TIP When you're working out the specifics of a calculation in a large file, it's often better to test it in a script rather than creating a field definition. When you exit Define Fields, it can take quite awhile to recalculate every single record only to find that it's not quite right. Using the same calculation in a script can tell you whether it's working in much less time. You can have it drop the result into an empty field (maybe in one or more temporary test records) to test it out. Once you have it fine tuned, copy and paste it into the field definition formula box.
Text Functions
- Text functions operate on, guess what, text. You can use them to pull apart data and to build new groups of text. The calculations can include constants, data from other fields, the expressions we worked with in the last chapter, and even other functions. When we created the AddressCombo field in our Contacts file, we were using text functions.
- "Text" is usually a field, but it can be an expression.
- "Search string" is a Text field or expression. It is the specific text for which you're searching.
- "Start" is a Number field or expression and it is determined by counting from the left.
- "Occurrence" is a Number field or expression that specifies the repetition for which you're looking. Position is often used with other functions to extract data starting with the character found by the Position function.
Go into the Contacts file now, start a new Calculation field called FunctionTest, and click Create. Click on the View pop-up in the upper-right corner, and pull down to Text functions. Double-click on the first function, Exact.
When a function moves into the Formula box, the parameters or arguments you need to fill in are highlighted between the parentheses. This is called the parameter template. In the U.S. English version of FileMaker, commas separate the parameters that you need to replace. In other localized versions, other symbols may be used, such as semicolons. Regardless, the functions in the list (and when double-clicked) will have the right types of separators in them.
TIP Unless you know specifically what you want to replace the parameters with, it's usually best to click further down in the Formula box to deselect them. That gives you some time to find their replacements and work on them one by one.
Left
Highlight and delete the Exact function, then double-click the Left function. The Left function looks like this:Left (text, number)
Starting from the left, it plucks the number of characters from the "text" parameter that you choose with the "number" parameter. You can put the number in directly, or have it come from a Number field, or it can even come from other functions. Choose "Calculation result is Text," and try this:
Left(FirstName, 4)
Go to Browse mode, place a copy of the field on the layout if one isn't already there, and look at the results of the calculation in a few of the records. Redefine the formula by putting quotes around FirstName, which turns it into a constant. Go back to Browse mode and take a look. Same result for each record!
A Calculation within a Calculation
This next section is a little trickier. We're going to use another function to replace one of the parameters. Remove the quotes from around FirstName, highlight the 4, and double-click the Left function in the function list:Left(FirstName, Left(text, number))
Now change the new text and number parameters so the formula reads like this:
Left(FirstName, Left(Address, 1))
I'm asking FileMaker to grab letters from the FirstName field based on what the first character is in the Address field (assuming it's a numeral). Go to Browse mode and try playing with the data in the Address field to see how FunctionTest recalculates. This is an example of a nested function. Nested functions are the earmark of complex calculations. They are complex, but you can keep them manageable by using the techniques described in the section titled "Building Complex Calculations" in this chapter.
The term nested function refers to replacing a parameter in a function with another function. You can only nest 125 If functions. Other than that and the 64,000-character limit of the Formula box, there is no limit to the number of nested functions you can use in a calculation.
LeftWords
The LeftWords function looks like this:LeftWords (text, number of words)
It works just like the Left function, except this does the counting based on spaces and other non-text characters (such as the underscore) between words. Choose "Calculation result is Text."
Remember back when we had our contact's whole name in a Name field? You would have been able to extract the first name from the field this way. It works great for entries with just a first and last name. But you would still have had to figure out what to do with entries that included middle names, initials, and various other combinations.
Length
Length counts the number of characters in a field. It includes spaces, numbers, and special characters as well as text. The function looks like this:Length (text)
Choose "Calculation result is Number" and substitute FirstName for text: Length (FirstName) By combining it with one of the logical functions, you can use Length to check that phone numbers, Social Security numbers, and credit card numbers are the correct length. This isn't exactly that test, but you might try it for fun:
If (Length(FirstName) = 5, "Five", "Not Five")
Sometimes I use a field like this that shows large red letters on the layout when the validation fails, for example, if a needed field were left empty. (This type of "validation" is not to be confused with Validate by Calculation.) To use it that way, remove the word Five from the second parameter and let the quotes sit next to each other. Then nothing shows if the validation is OK. You could also use a calculation like this in the Validate by Calculation section of afield definition. You wouldn't get any large red letters that way, but once again it shows you how you can solve problems in FileMaker from different directions. Since Validate by Calculation uses Boolean logic, you could use the simpler calculation, Length(FirstName) = 5.
Position
The Position function looks like this:Position (text, search string, start, occurrence)
This returns a number representing the number of characters from the start point, where the first character of the specified string begins. A zero (0) means that that particular occurrence of the "substring" wasn't found in the segment of text you asked it to search.
Building Complex Calculations
- Trying to create complicated calculations to get the result you want can require so many nested functions that your eyes bug out. I've met a few people who can see these things in their heads. I can't do that. So here's a technique I've developed that might work for you.
I've included the answers to the questions that apply to our example calculation. Then I describe the process, including techniques for making it go more smoothly. If you work along on your computer, you may be able to remember some of the methods when it comes time for you to figure out your own calculations.
This example is one I was asked to do for a client who had a number of phone directories stored in a word processing program. The Street Name needed to be extracted from the whole address string.
The Steps
1. Ask yourself what you want, and describe it in plain English.A: I want the street name without the number.
2. What will be the main function?
A: The Right function ...