FileMaker Extra: Tips for Becoming a Calculation Master

FileMaker Extra Tips for Becoming a Calculation Master

As we mentioned at the outset of this chapter, it takes time to master the use of calculation formulas. We thought it would be helpful to compile a list of tips to help you get started on the path:

• Begin with a core Don't try to memorize everything at once; chances are you'll end up frustrated. Instead, concentrate on building a small core of functions that you know inside and out and can use without having to look up the syntax or copy from examples. Then, gradually expand the core over time. As you have a need to use a new function, spend a few minutes reading about it or testing how it behaves in various conditions.
• Work it out on paper first Before writing a complex formula, work through the logic with pencil and paper. This way you can separate the logic from the syntax. You'll also know what to test against and what to expect as output.
• Search for alternative methods of doing the same thing It's uncommon to have only one way to approach a problem or only one formula that will suit a given need. As you write a formula, ask yourself how else you might be able to approach the problem, and what the pros and cons of each method would be. Try to avoid the "if your only tool is a hammer, all your problems look like nails" situation. For instance, if you always use If() statements for conditional tests, be adventurous and see whether you could use a Case() statement instead.
• Strive for simplicity, elegance, and extensibility As you expand your skills, you'll find that it becomes easy to come up with multiple approaches to a given problem. So how do you choose which to use? We suggest that simplicity, elegance, and extensibility are the criteria to judge by. All other things being equal, choose the formula that uses the fewest functions, has tightly reasoned logic, or can be extended to handle other scenarios or future needs most easily. This doesn't mean that the shortest formula is the best. The opposite of simplicity and elegance is what's often referred to as the brute force approach. There are certainly situations in which that's the best approach, and you shouldn't hesitate to use such an approach when necessary. But if you want to become a calculation master, you'll need to have the ability to go beyond brute-force approaches as well.
• Use comments and spacing Part of what makes a formula elegant is that it's written in a way that's logical and transparent to other developers. There may come a time when someone else needs to take over development of one of your projects, or when you'll need to review a complex formula that you wrote years before. By commenting your formulas and adding whitespace within your formulas, you make it easier to expand on and troubleshoot problems in the future.
• Be inquisitive and know where to get the answer As you write formulas, take time to digress and test hunches and learn new things. Whip up little sample files to see how something behaves in various conditions. Also, know what resources are available to you to get more information when you get stuck or need help. The Help system; our companion book, FileMaker 8 Functions and Scripts Desk Reference; and online discussion groups are all examples of resources you should take advantage of.
• Use your keyboard Entering a less-than character followed by a greater-than character (<>) equates to the "not equal to" operator () within an expression. The following expressions are functionally identical:

  2

• and images/U2264.jpg border=0>, respectively.
• Use tabs to improve clarity To enter a tab character into an expression (either as literal text or simply to help with formatting), use (Option-Tab) [Ctrl+Tab].
• Learn the exceptions FileMaker allows for a shorthand approach to entering conditional Boolean tests for non-null, nonzero field contents. The following two expressions are functionally identical:

  Case ( fieldOne; "true"; "false" ) Case ( (IsEmpty (text) or text = 0); "false"; "true")

• Note that the authors do not recommend this shortcut as a best practice. We tend to believe you should write explicit (and, yes, more verbose) code, leaving no room for ambiguity, but if you ever inherit a system from another developer who has used this approach, you'll need to be able to grasp it.
• Use defaults with conditionals FileMaker allows for optional negative or default values in both the Case() and If() conditional functions. The following expressions are both syntactically valid:

  Case ( fieldOne = 1; "one"; fieldOne = 2; "two" ) Case ( fieldOne = 1; "one"; fieldOne = 2; "two"; "default" )

  We strongly recommend you always provide a default condition at the end of your Case statements, even if that condition should "never" occur. The next time your field shows a value of "never happens," you'll be glad you did.

• Remember that Case short-circuiting can simplify logic The Case() function features a "short-circuiting" functionality whereby it evaluates conditional tests only until it reaches the first true test. In the following example, the third test will never be evaluated, thus improving system performance:

  Case ( 1 = 2; "one is false"; 1 = 1; "one is true"; 2 = 2; "two is true" )

• Repeating Value Syntax Note that fields with repeating values can be accessed either using the GeTRepetition () function or via a shorthand of placing an integer value between two brackets. The following are functionally identical:

  Quantity[2] GetRepetition ( Quantity; 2 )