Q: I've recently discovered the power we have in defining calculation fields. It knocks my socks off, what I can do with all the built-in functions, but I get easily confused when I try to use multiple functions in a single calculation. Sometimes, just to keep things straight, I resort to defining parts of the formula in two or three fields, then combining them in yet another "final result" calculation field. I know this isn't the most elegant approach, but it's the best way I've found to keep all my formulas straight. Can you recommend another approach?
Figure 1: Make a formula more readable by adding spaces and blank lines -- FileMaker Pro ignores whitespace and will interpret the calculation identically either way. So, if it helps you read the formula, go ahead and take up some space!
Figure 2: Helpful comments -- Leave yourself notes where you can best use them: in the calculation itself. FileMaker Pro supports two styles of embedded comments in calculations.
A: Welcome to the world I call "back in 7th grade algebra." I don't know about you, but many years have passed since I first learned (and subsequently forgot most of) algebra. What does this have to do with FileMaker Pro? Two things: managing parentheses and using variables. I'm going to suggest these techniques as two ways to minimize calculation confusion.
One thing I remember from algebra is that in any formula or expression, parentheses must come in balanced pairs: one left and one right. Combine a few functions in FileMaker Pro and those parentheses start getting hard to track. My first suggestion is that you give yourself some room and visually open up your formula. Make the specify calculation dialog larger by dragging the lower right corner down and use the correspondingly larger formula box to your benefit. For this tip, height is usually more important than width.
FileMaker Pro's specify calculation is blind to whitespace, meaning it ignores spaces and blanks lines. So the key is to put spaces and blank lines (using the return key) into your formula so it's more easily readable for you. For demonstration, I'll use a common calculation that returns an age in years by using the field Birthdate and some of FileMaker Pro's built-in functions. This method of determining age subtracts the current year from the birth year, and subtracts 1 if the birthday hasn't passed yet this year. In this formula you must determine whether the birthday has already occurred this year. So you have to derive this year's birthday by creating a date using the function Date(month;day;year), taking the month and day from Birthdate but obtaining the current year from the Get(CurrentDate) function. Look at figure 1 to see the improvement in legibility by laying it out in an exploded view. The formula is the same -- I've just spaced it out so the different parts are visually separated into logical sections.
Here's a bonus tip: Comment your calculation as well. FileMaker Pro lets you embed comment text in your calculation, providing two styles for you to choose from. One is known as C++ style commenting (because this is how the C++ programming language does commenting), where you start the comment with two forward slashes (//). The calculation ignores everything from // to the end of that line (designated by a carriage return). The other style is C style commenting, where you begin the comment with the characters /* and end it with */. The calculation ignores everything between and including those characters. This style is useful when your comment comprises multiple lines or when you want to insert a comment into the middle of something on one line. Figure 2 shows examples of both styles. Don't worry about taking too much space … a formula in FileMaker Pro can be up to a whopping 30,000 characters long!