Custom Functions
Often, you need to use a particular calculation more than once. Perhaps you have several tables that hold information about people and you want to write that "Full Address" calculation only once, then use it in each table. Or you might find yourself wondering why FileMaker doesn't have a function to do some incredibly useful thing. Maybe you frequently need to calculate your custom sprockets' weight, and FileMaker (for some reason) doesn't have a MySprocketWeight function. In all these cases, you can define your own functions to get the job done.
12.1.1. Defining a Custom Function
|
To get started, you add a new function that can calculate the radius of a circle. You may not actually need to calculate radii in your own databases much, but this example illustrates important concepts common to creating any custom function. Note: If you don't have the FileMaker Advanced edition, you'll have to sit these next few exercises out, as explained in the previous Note.
|
||
|
- Click the New button.
The Edit Custom Function window appears. You can see it in Figure 12-2.
- In the Function Name box, type Circumference.
You've just given your function a name, which you'll call upon later when you want to use this function in calculations.
- In the Function Parameters box, type diameter.
Most functions need parameters (Section 9.2.4), and this box is where you tell FileMaker which parameters your function needs. If you have more than one parameter, you must enter them one at a time.
- Click the Add Parameter button (see Figure 12-2).
FileMaker moves "diameter" into the parameter list. This action means "diameter" is now officially a parameter of the Circumference function.
Figure 12-2. The Edit Custom Fuction window is where you actually define the function. It looks a lot like the Specify Calculation dialog box you've seen so much of lately. But instead of fields, it has a list of Function Parameters (it's empty right now). Aside from this distinction, it also works a lot like the Specify Calculation dialog box.
Note: When you add more than one parameter to the parameter list, you can use the arrow icon by each item to move it up or down in the list. The order here is important: It determines the order in which you want the parameters to pass to the function (Section 9.3.1.1).
- From the View pop-up menu (above the function list), choose "Trigonometric functions."
The function list changes to show just the relevant options.
- In the function list, double-click the Pi function, then click the * operator button (or type *).
The function calculation area now reads: Pi *.
- Finally, in the parameter list, double-click diameter.
FileMaker adds the word diameter to the end of the calculation: Pi * diameter.
When you're done, click OK, and then OK again to close the Edit Custom Function and Define Functions dialog boxes. The database has a brand new function called Circumference. You can see your handiwork in the Specify Calculation dialog box's Functions list.
12.1.2. Editing Custom Functions
You probably feel like a pro at the Define Custom Functions window already, since it works a lot like other FileMaker dialog boxes. But even you could end up with a custom function that needs adjustment or repair. To edit an existing custom function, either double-click its name, or select it from the list and click Edit. In the Edit Custom Function window (Figure 12-2), you can modify the definition of a function as follows:
- To change its name, just type a new name in the Function Name box.
- You can also add new parameters, just like you added the "diameter" parameter in the Circumference function. If you no longer need a function parameter, select it and click the Delete Parameter button.
- To change a parameter's name, you must first select it in the parameter list. When you do, FileMaker puts the parameter in the Parameter Name box, where you can edit it. When you're done, click the Edit Parameter button to apply your change to the one in the list.
- You can also reorder parameters by dragging them up or down in the list.
If you click Duplicate, FileMaker makes an exact copy of the selected function. Finally, if you don't need a function anymore, select it and click Delete.
Warning: Be careful adding, reordering, or deleting parameters to an existing function. If the function is being used in a calculation somewhere, that calculation breaks because it no longer passes the right parameters back to the function. On the other hand, it's safe to rename a function or its parameters. FileMaker fixes any existing calculations when you do.
12.1.3. Recursion
As described in the box on Section 12.1.3.1, you can create custom functions that call other custom functions, creating whole strings of mathematical wizardry that perform to your exact specifications. Even more interesting, a custom function can use itself, a technique known as recursion. With recursion, you can create calculations that repeat a process over and over again until they reach a resultcalled iterative calculations.
Tip: Recursion is a notoriously complicated topic, and many very capable FileMaker developers are stymied by it. Fortunately, recursion is rarely the only solution to a given problem, so you can certainly get by without it. (For example, consider using a script instead.)
| UP TO SPEED Using Custom Functions |
|
For the most part, you can use custom functions just like any of the FileMaker's built-in functions. When you're in the Specify Calculation dialog box, choose Custom Functions from the View pop-up menu to access the functions you've made. (When you define a custom function, you're adding it to the FileMaker file you're working in. It isn't available in other files unless you add it to them as well.) Just like other functions, custom functions can also use other custom functions to do their job. For example, if you want to add a new function that calculates the surface area of a cylinder, it can take two parameters (diameter and height) and it can use your custom Circumference function, like so: Circumference(diameter) * Height With this in mind, you can define functions that build upon one anotherto keep each one simple, or to provide different but related capabilities. |
Imagine you need a function that removes duplicate lines from a list. For example, if a field contains a list of colors, you want a new list with each unique color name, even if it appears in the original list several times. You can't do that with a normal calculation, because you just don't know how many words you need to pull out. A recursive function solves the problem by repeating its work until it takes care of all items (colors).
While the concept of a recursive function is simple, creating one can be tricky. To implement a recursive function, you're best off tackling the calculation in three distinct steps. First, solve the initial problem; second, call that first formula over and over again (that's the recursive part); and third, tell the formula how to stop.
Tip: If you're having trouble getting through the following recursion example on your own, you can download a sample database from the 'Missing CD" page at www.missingmanuals.com.
12.1.3.1. Step 1: solve the first case
Rather than think about how to solve the entire problem, just figure out how to deal with the first line in the list. If you have a field called List, for example, and you want to make sure the first line appears only once in the list, you can use this calculation:
LeftValues ( List ; 1 ) & Substitute ( List ; LeftValues(List; 1) ; "" )
Suppose List contains:
Red
Green
Orange
Red
Orange
Yellow
The Substitute part of this expression does the lion's share of the work, so start with that to figure out how the formula works. The Substitute function sees that "Red" is the first item in List and takes it out of the field everywhere it occurs. If Substitute were the whole shooting match, "Red" would disappear entirely from List. But the "LeftValues ( List; 1) &" piece of the expression also notices that "Red" is the first item in List and it puts "Red" back at the top of the list, then repeats all the other items in List. Here's the result you'd see if you made a calculation field with the formula above:
Red
Green
Orange
Orange
Yellow
Now you're ready to move on to the rest of the function, where you call the same action over and over again, and things start to get interesting.
12.1.3.2. Step 2: assume your function already works, and use it
You're ready to take the recursion leap of faith. A recursive function, by definition, calls itself. So at some point, it depends on its own resources to work. But when you're writing the recursive custom function, it obviously doesn't work yet. You'll be at a total impasse if you don't assume it already works and just get on with writing.
So since you're writing a new custom function called RemoveDuplicates, write its syntax as if you already have a function called RemoveDuplicates that does what you want. If such a function did exist, you could use it in the above calculation like this:
LeftValues ( List ; 1 ) & RemoveDuplicates ( Substitute(List; LeftValues(List; 1); "") )
This new version removes the first item from the list, then sticks it in front of the result of the RemoveDuplicates function, and then works its way through the list. Think of it this way: You removed duplicates of the first item from the list using the Substitute function, and RemoveDuplicates removed the rest.
So far, so good. Keep going to make sure RemoveDuplicates work for the rest of the calculation.
12.1.3.3. Step 3: find a stopping point
You now have two of the three critical components of a recursive function: You're manually doing the first part of the job, and you're telling recursion to do the rest. If you leave the function like this, though, you're in trouble. If RemoveDuplicates calls RemoveDuplicates, which in turn calls RemoveDuplicates (ad infinitum), you have a problem. This function just keeps going forever.
Note: When you work on recursive functions, you inevitably create such loops accidentally. When you do, you see FileMaker think for several seconds, then give up and return invalid (a question mark). If FileMaker seems to be hung, give it some time. It gives up eventually.
To avoid ending up in a loop, you need to figure out when to stop calling RemoveDuplicates. Think about what happens after this function calls itself several times. Each time it's called with a slightly smaller list than the time before (because the first itemalong with any copies of ithave been removed). Eventually it's going to get called with just one item (or zero items). When that happens, you no longer need the services of RemoveDuplicates. Instead, you can just return that last word by itself since it obviously has no duplicates. You use an If function to help the recursion figure out when to stop. The final function looks like this (with comments added):
// Start the result with the first item in the list LeftValues ( List ; 1 ) & // If there are more items in List… If ( ValueCount(List) > 1; // …then remove duplicates from the remaining items RemoveDuplicates ( Substitute(List; LeftValues(List; 1); "") ); // …otherwise we're done "" )
Now, all you have to do is create the RemoveDuplicates custom function shown above. RemoveDuplicates needs one parameter defined, which is a reference to the field from which you're sifting duplicates. A descriptive name, like "theField," helps you remember what this parameter does. Finally, create a calculation field using your new custom function and create a reference to the field containing the list of duplicated values.
Note: RemoveDuplicates works great for finding unique values in a list, so long as the last item in the list has a
Figure 12-3 illustrates an example of a recursive calculation calling the RemoveDuplicates custom function to remove all duplicate colors it finds on the list. (It takes four iterations to remove all the duplicates and return a unique instance of each item in the list, in the order in which they occur.)
|