Custom Functions

In addition to all the wonderful and powerful calculation functions built into FileMaker Pro 8, you also can create your own custom functions. To create custom functions, you need to have a copy of FileMaker Pro 8 Advanced. Any custom functions you create using FileMaker Pro Advanced remain in the file and are fully usable when it's subsequently used by the regular FileMaker Pro 8 client application. You just can't edit the formula of the custom function unless you have FileMaker Pro Advanced.

As with other objects, such as scripts, tables, and user account information, custom functions live in a particular file. There isunfortunatelyno easy way to move or import custom functions defined in one file into another one. The implications of this are obvious: If you have a solution that consists of multiple files, you need to define custom functions redundantly in all the files that need to access them, thus complicating maintenance and troubleshooting. This fact shouldn't scare you off from using custom functionsthey're really quite wonderfulbut it's certainly a constraint you need to be aware of.

Custom functions that have been created for a particular file show up with all the built-in functions in the list of functions within the calculation dialog. To see just the custom functions, you can choose Custom Functions from the filter above the function list. Custom functions are used in a formula just as any other function. The function name and the names of its parameters are defined by the person who writes the custom function.

Uses of Custom Functions

There are several reasons for using custom functions in a solution. Custom functions enable you to abstract snippets of calculation logic so that they become reusable. Abstracting out bits of logic also makes your code easier to read and eliminates redundancy.

Simplifying Complex Formulas

The best place to begin understanding the potential uses of custom functions is with a simple example. Imagine that for some reason you need to generate a random integer from 10 to 50. Knowing, as you do from reading Chapter 8, "Getting Started with Calculations," that the Random function returns a random number between 0 and 1, you eventually conclude that a formula that solves this particular problem is as follows:

Int(Random * 41) + 10

With the problem solved, you write your formula and go on your merry way. Now, imagine that the next day you come back and discover you need to write another function that requires a random integer from 1 to 6. After a bit more thinking, you come up with the following:

Int(Random * 6) + 1

About this time, you'd be wishing that the engineers at FileMaker, Inc., had thought to create a function that would return a random integer from x to y. Using FileMaker Pro 8 Advanced, you can in fact write your own custom functions for situations such as this. Rather than continuing to solve particular problems, you can solve the general case and never again need to divert your attention to the particular.

So, what would a generalized solution to the random-number problem look like? First, you'd need to have some way of abstractly representing the "from" and "to" numbers. Let's call these two numbers lowNumber and highNumber. Then the function that satisfies the general condition would be this:

Int (Random * (highNumber - lowNumber + 1)) + lowNumber

For any lowNumber and highNumber you feed this function, you get back an integer between the two. We'll look in a moment at how you would go about setting this up as a custom function, but for now, the important thing is the concept that custom functions, just like the built-in functions you use all the time, have inputs (which are called parameters) and an output. Let's say that you decide to call this function randomInRange. Now, to solve the first problem we looked at, finding a random integer from 10 to 50, you could just use the following function:

randomInRange (10; 50)

And to find a number from 1 to 6, you could use this one:

randomInRange (1; 6)

You've simplified your code by replacing a complex expression with a single function, thereby making it easier to read and maintain. You've abstracted that bit of logic out of whatever larger formula you were working on, leaving you with one fewer thing to think about.

Custom Functions as System Constants

There are a few different schools of thought about when you should write a custom function to abstract your programming logic, and when you should use existing tools to solve the problem. Some hold that you should always write custom functions. Even if you use a given custom function only a single time, you've made your code more modular, thus making it easier to track down and troubleshoot problems. Plus, if you do ever need that function again, it's there, ready and waiting.

Other developers find that they use custom functions more sparingly. Their attitude is this: If you find yourself solving a particular problem more than once, go ahead and write a custom function for it, and go back to change the original occurrence to reference the custom function instead. This process, often called refactoring as a general programming concept, has a certain pragmatism to it: Write a custom function as soon as it's more efficient to do so, but not sooner.

Whatever camp you find yourself falling into, you should be aware of two other common uses for custom functions. The first is for defining system constants. As an example, imagine that in your sales organization, the commission rate is 15%. In calculations in which you determine commission amounts, you might find yourself writing numerous formulas in which sales figures are multiplied by .15. If, heaven forbid, you ever need to change that figure to, say, .18, you'd need to sift through all your code to find all the instances where you had hard-coded the commission figure.

As an alternative, you might consider defining custom functions to represent systemwide constants such as these. In this example, you would simply have a custom function called CommissionRate that had no parameters and returned a value of .15. By abstracting out the hard-coded value, you're able to quickly and easily make global changes just by editing a single function. You should never refer directly to the magic number in a formula; use the custom function instead. Other examples of numbers and strings that should be abstracted out of your formulas include IP addresses, URLs, and colors.

Note

There's a subtle pitfall here. Note that stored values that reference custom functions will not automatically update when a custom function definition changes. For example, if you have a system constant called commissionRate implemented as a custom function, and you then go on to create one or more stored calculations that reference commissionRate, the values in those calculations will not update if you later redefine commissionRate to be 18%. The same would hold true of data that's auto-entered into a field. If you wanted these stored values to take account of the new commission rate, you'd need to force the fields to explicitly refresh their contents somehow.

Creating Recursive Functions

The final common situation in which custom functions are used is for making recursive functions. One of the limitations often lamented by developers over the years has been the fact that you can't create looping constructs within calculation formulas. That is, you can't instruct a regular calculation formula to keep doing something until some condition holds. Custom functions, on the other hand, can contain recursive logic, which mimics the effects of a looping control structure. This means that a class of problems can be solved only by the creation of custom functions. This stands in stark contrast to the "custom functions as vehicles for abstraction" idea discussed previously. As an abstraction tool, custom functions can always be replaced in a formula by the logic they've abstracted. No such substitution can be made when dealing with recursive functions. In those cases, using custom functions is not a convenience; it's a necessity. In the section that follows, we develop and discuss several recursive functions.

Creating Custom Functions

Now that you understand what custom functions are and why you might want to use them, it's time to turn to the mundane subject of how to actually create them. First, recall that custom functions can be created and edited only with FileMaker Pro 8 Advanced, and that custom functions live in a specific file. To see a list of custom functions that have been defined in a particular file, and to define new ones, choose File, Define, Custom Functions. The resulting Define Custom Functions dialog is shown in Figure 14.21.

Figure 14.21. With FileMaker Pro 8 Advanced, you have access to a Define Custom Functions dialog.

Buttons from this dialog enable you to create, edit, and delete a custom function. You also see the names of the parameters that have been defined for each function, as well as whether a function is available to all accounts or just those with the Full Access privilege set. When you go to create or edit a custom function, you're taken to the Edit Custom Function dialog, shown in Figure 14.22.

Figure 14.22. The parameters and formula for a custom function are defined in the Edit Custom Function dialog.

This dialog is similar in many ways to the standard calculation formula dialogs, so it shouldn't seem terribly unfamiliar. The main difference is the upper-left portion of the dialog, where instead of seeing a list of fields, you can instead name your function and its parameters. The restrictions for function and parameter names are the same as the those for field names: They can't contain any mathematics symbols (such as + - * / ^ = ); they can't contain the words AND, OR, XOR, or NOT; they can't begin with a digit or period; and they can't have the same name as an existing function or keyword.

Tip

When naming your custom functions and parameters, we think it's best to follow the same naming conventions used in the built-in functions. The initial letter of each word in a function name should be capitalized, and the name should contain no spaces or other punctuation. Parameters should be in camel case, with the first letter in lowercase and the first letter of subsequent words capitalized (for example, numberOfCharacters, textString1). Some developers prefer that the function name itself should be in camel case as well.

There is no practical limit to the number of parameters you can define for a function, but most functions require anywhere from zero to a handful. The order of the parameters is important: When you use a function and specify the input parameters, they are interpreted as being in the order in which they are listed in the Edit Custom Function dialog.

Note

If you find yourself writing a function that requires more than four or five parameters, that's a pretty good signal that you should break the function down into two or more smaller functions.

The other significantly new and different portion of this dialog is the Availability section at the bottom. By default, a function is available to all user accounts. Anytime a user or developer has access to a calculation dialog, he or she will see and be able to use all the unrestricted custom functions. The other option available to you is to restrict the use of the function to only those users who have been assigned the Full Access privilege set. The latter can be referred to as private functions, and the former can be thought of as public functions. We find it helpful to place an underscore at the beginning of the name of private functions so that they can be quickly and obviously identified. If access to a function has been restricted, users who don't have full access will not ever see or have access to use that function. If these users ever view a calculation dialog that references a private function (say, in a script), the name of the function is replaced with in the calculation dialog. Declaring a function as private has no impact on what data is displayed or accessible to a user. The functions still do their jobs and work properly. It's just the functions themselves that can't be viewed or used.

You might want to restrict access to a function for several reasons. As you will see in some of the examples in the section that follows, often when you define recursive functions, you need to define two functions to accomplish one goal. In these cases, the first function is often a public function, whereas the other is restricted, thereby keeping users from accidentally calling it directly. Another reason to define a function as private is simply to keep from confusing novice developers. Your function may not be documented anywhere, and it might not contain adequate error trapping to handle improper parameter values. By making it private, you reduce the risk that the function will be used improperly.

Examples of Custom Functions

We think the best way to learn how to write your own custom functions is to study examples so that you can get ideas about uses in your own solutions. Some of the sample functions that follow might have intrinsic value to you, but more important than the specific formulas are the ideas and techniques. To that end, following each of the examples presented in this section, we provide commentary about the syntax and/or use of the function.

Hypotenuse (leg1Length ; leg2Length) = Let ( [ a2 = leg1Length * leg1Length; b2 = leg2Length * leg2Length; c2 = a2 + b2] ; Sqrt (c2) )

Although FileMaker Pro provides built-in functions for many common mathematical formulas and operations, a number of common equations are missing. The preceding Hypotenuse function uses the Pythagorean Theorem (a2 + b2 = c2) to find the length of the hypotenuse of a right triangle given the lengths of the two legs.

Examples:

Hypotenuse (3 ; 4) = 5 Hypotenuse (5 ; 12) = 13 NthRoot (number ; root) = Exp (Ln (number) / root )

This is another example of creating a custom function to provide an abstraction for a mathematical formula: There is a built-in function that returns the square root of a number, but no function that returns the nth root of a number. The NthRoot function uses logarithms to find this number.

Examples:

NthRoot (8 ; 3) = 2 NthRoot (64; 4) = 4 Quarter (myDate) = Ceiling ( Month (myDate) / 3)

This function returns the calendar quarter (14) of myDate. This function exemplifies the idea of custom functions being used to substitute for code chunks, making your code easier to read and maintain. The Month function returns a number from 1 to 12, so taking the ceiling of that number divided by 3 yields an integer from 1 to 4.

Examples:

Quarter ("12/11/03") = 4 Quarter ("4/1/04") = 2 WeekEndingFriday (myDate) = myDate + Mod (6 - DayOfWeek(mydate); 7)

Given a date, this function returns the date of the following Friday. This sort of functionality is often necessary in time-tracking systems so that you can summarize records by week. It would be easy to alter or extend this function to be referenced to some day other than Friday. To extend it, you would just specify a second parameter in the function and replace the hard-coded 6 (which is the DayOfWeek of any Friday) with a value derived from the parameter.

Examples:

WeekEndingFriday ("12/11/2005") = "12/12/2004" // the 11th was a Thursday WeekEndingFriday ("1/9/2006") = "1/9/2006" // the 9th was a Friday RepeatText (text ; numberOfRepetitions ) = text & Case (numberOfRepetitions>1; RepeatText (text; numberOfRepetitions - 1))

This is the first example of a recursive function. The RepeatText function returns n repetitions of the text string passed in the first parameter. For instance, RepeatText ("t"; 3) returns the string ttt. If the concept of recursive functions isn't clear to you, this is a good place to begin experimenting. Figure 14.23 traces through exactly what the function is asked to do when it evaluates this simple example. RepeatText ("t"; 3) is first evaluated as t and the result of RepeatText ("t"; 2). Of course, the latter is then evaluated as t and the result of RepeatText ("t" ; 1), which is simply t. The iteration stops at this point because numberOfRepetitions is not greater than 1. This is known as the function's exit condition; without one, you have endless recursion (picture a dog chasing its tail endlessly), which fortunately FileMaker Pro is smart enough to recover from after some large number of iterations.

Figure 14.23. This diagram shows how the recursive custom function RepeatText ("t" ; 3) is evaluated.

Caution

Be sure that any recursive function you write has some exit condition that is guaranteed to be reached.

Possible uses of the RepeatText function include creating progress bars or bar graphs. If you ever tried to do this sort of thing in previous versions of FileMaker, you know what a kludgy workaround was required to get a repeating string of characters. Another use is for padding out spaces when generating fixed-length data formats. Say you need to pad out a FirstName field to 15 characters by adding spaces at the end. In previous versions of FileMaker, you would have used this formula:

Left (FirstName & " " ; 15)

Using RepeatText, you could simply use this:

FirstName & RepeatText (" " ; 15 - Length(FirstName))

Of course, if you have a lot of padding to do, you might decide to abstract this one more layer and build the PadCharacters function shown next.

Examples:

RepeatText ("|" ; 10) = "||||||||||" RepeatText ("hello"; 3) = "hellohellohello" PadCharacters (text ; padLength; characterToPad; side) = Let ( [ padString = RepeatText (characterToPad; padLength - Length(text)); ] ; Case ( Length (text) > padLength ; Left (text; padLength); side = "start"; padString & text; side = "end"; text & padString ) )

Building on the preceding example, the PadCharacters function pads either leading or trailing characters onto a string. We've used four parameters here to gain flexibility. The third and fourth parameters specify the pad character and whether the padding should be at the start or end of the string. If you knew you always wanted to pad leading zeros, you could define this function with just two parameters and then hard-code the location and character within the formula.

Notice that this function makes a call to the RepeatText function to generate the padString. We could have included the formula for RepeatText, but by abstracting it out, we centralize the code for RepeatText (making it easier to troubleshoot) while also making the formula easier to read.

Examples:

PadCharacters ("foo"; 8 ; "x"; "end") = "fooxxxxx" PadCharacters ("123"; 10; "0"; "start") = "0000000123" TrimChar (text; removeCharacter; location) = // valid locations are "start", "end", "all" Let ( [ leftChar = Left (text; 1); rightChar = Right (text; 1); remainderLength = Length(text) -1 ] ; Case ( (location = "start" or location = "all") and leftChar = removeCharacter; TrimChar (Right(text; remainderLength) ; removeCharacter; location) ; (location = "end" or location = "all") and rightChar = removeCharacter; TrimChar (Left(text; remainderLength) ; removeCharacter; location) ; text ) )

FileMaker Pro's built-in Trim function removes any leading and trailing spaces from a text string. There are times, however, when you need a more generalized way of removing a specific leading or trailing character from a string. The trimChar function does just this. The first parameter is the string you want trimmed; the second is the character you want removed. The third parameter, location, is used to specify whether you want the character removed from the start or the end of the string, or from both. Valid inputs are start, end, and all.

This function works by checking whether the first or last character in the string needs to be lopped off. If so, the remainder of the string is fed back recursively to itself. Each iteration removes at most a single character; the "loop" continues until no more characters need to be removed, at which point the shortened text string is simply returned.

Examples:

TrimChar ("xxThis is a testxxx", "x", "all") = "This is a test" TrimChar ("Another test¶¶¶", "¶", "end") = "Another test" CrossProduct (array1; array2) = _CrossProductGenerator (array1; array2; 1)

This, the final custom function example, looks at a more complex recursive function. In the recursive examples shown previously, the exit condition for the recursion was based either on an explicitly passed parameter reaching a certain value (RepeatChar), or on a condition no longer being true (TRimChar). There are other situations in which you want to be able to increment a counter with every iteration, and have the exit condition for the loop be based on that counter reaching some threshold. The interesting part is that because the counter needs to be passed along from iteration to iteration, it must be defined as a parameter. This means, however, that anyone using the function must initialize the counter for you, most likely setting it simply to 1.

The other solution is that you have a private function with a counter parameter that's called by a public function without one. In this case, the public function CrossProduct takes only two parameters, which are both expected to be return-delimited arrays. The function is defined merely to call another function, _CrossProductGenerator, which has three parameters. The first two inputs to _CrossProductGenerator are simply passed along based on the user's input. The third, however, is hard-coded to 1, hence initializing a counter used there.

The syntax for the private function is as follows:

_CrossProductGenerator (array1; array2; counter)

It has the following formula:

Let ( [ array1count = ValueCount (array1); array2count = ValueCount (array2); limit = array1count * array2count; pos1 = Ceiling (counter / array2count) ; pos2 = Mod (counter - 1; array2count ) + 1; item1 = TrimChar (MiddleValues (array1; pos1; 1); "¶" ; "end"); item2 = TrimChar (MiddleValues (array2; pos2; 1); "¶" ; "end") ] ; Case ( counter <= limit ; item1 & item2 & "¶" & _CrossProductGenerator (array1; array2; counter + 1)) )

The cross product of two sets is a set containing all the two-element sets that can be created by taking one element of each set. For example, if Set1 contained {A, B} and Set2 contained {P, Q, R, S}, their cross product would consist of {AP, AQ, AR, AS, BP, BQ, BR, BS}. The number of elements in the cross product is the product of the number of elements in each of the two sets.

The _CrossProductGenerator function "loops," incrementing a counter as it goes, until the counter is no longer less than the number of elements expected in the result set. Each time it iterates, it figures out what element number to grab from each list. With Set1 and Set2 of the example, the function would iterate eight times. If you were on iteration 5, the function would realize that it needed to grab the second item from the first list (because Ceiling (5 / 4) = 2), which is B, and the first item from the second list (because Mod (4; 4) + 1 = 1), which is P. That's how BP becomes the fifth element of the result set.

Notice also that this function, besides recursively calling itself, also calls the trimChar function created earlier in this section. From the section on working with arrays, you'll remember that the LeftValues, MiddleValues, and RightValues functions all return a trailing return after the item list; that trailing return needs to be removed before the item is processed.

Examples:

CrossProduct ("A¶B¶C" ; "1¶2¶3¶4") = "A1¶A2¶A3¶A4¶B1¶B2¶B3¶B4¶C1¶C2¶C3¶C4¶" CrossProduct ("Red¶Yellow¶Blue" ; "-fish") = "Red-fish¶Yellow-fish¶Blue-fish¶"