Custom Functions has been around since versions 7 of FileMaker. For specific problems a custom function can be a real life saver. The most common area of use is string handling and manipulation. Before we hade GT, GTE, LT and LTE operators in relations one very common use was generating intervalls of dates or numbers for showing a range of records in portals or related valuelists.
Today, with current version FileMaker 12, the use of custom functions hade changed somewhat. I would say that the most common use today is data gathering or parsing using either ExecuteSQL or getNthRecord. The need to make subqueries for data without bothering about the current context seem bigger then ever.
In many cases one or more aspects of a task for a Custom Function is dynamic. For example a list of values can be sent as parameter and your functions task is to sort the values or perhaps filter unique values. In this case you more or less must apply recursion to your Custom Functions.
The method for recursion available to us is called tail recursion. As the name implies it means calling the function from within itself, usually in the end of the calculation (at its tail). It is not very hard to write a recursive custom function. The one important thing one must keep in mind is some sort of controller that keeps track of when the Custom Function is done executing. If we just keep calling the function from within itself it will continue to run forever, or rather until the callstack is full.
A controller can be anything from a numeric counter that should reach a predetermined value, to a list of values that should be parsed and one value removed until the list is empty. Anything we can catch using a If-statement can act as a controller.
The very simplest recursive custom function can look like this
There is one parameter to this function and that is the counter that keeps track of how many times the function shall call itself. In this case the If-statement will be true when the counter is greater then 10. When that happens it will return the value of the counter, else it will call itself with the value of counter plus 1 as parameter.
If you call this function in a calculation and set the counter parameter to 1 the result will be 11 because the function will call itself 10 times. 1 + 10 equals eleven.
Now to what I really wanted to blog about. The method above works just fine but there is, according to me, a better way to pass values between the recursive calls. Instead of using parameters I prefer to store my values in two-dollar-variables (persistant variables). That does not make things much more complex but it do require that we use the Let-function (or Let-statement if you prefer to call it that). The Let-statement allows us to set and unset variables with three types of scope.
- Local variable - Only accessible inside the current Let-statement
- One-dollar-variable - Accessible in the current script during runtime, not really useful in this case
- Two-dollar-variable - Accessible in the entire database until we unset it
The benefit of using two-dollar-variable in this case is that we only need to define parameters that the developer must worry about setting. If we want to pass more then one value between recursions the function is in risk of being difficult to use because some parameters are required and some are just data carriers between recursions. A good rule of thumb is to mimic FileMaker Incs way of naming functions and parameters. Make the developers that will use you function feel right at home.
FileMaker uses upper camel case for function names and lower camel case for parameters.
Example (notice the lower case in the first char in lower camel case)
- UpperCamelCase
- lowerCamelCase
FileMaker does not use underscore or other special characters so neither should you.
Of course you can argue that you can reuse a parameter by concatenating strings and then divide them again in the following function call but I would not recommend this since it tends to make your calculation harder to read and force it to do a lot of unnecessary work.
Now to the fun part, how to store the values in two-dollar-variables. We need two let-statements for this. One that contains the entire calculation and one that simply clears the two-dollar-variables when done. First, here is a screenshot of a recursive custom function that filters out unique values from a list of values.
Lets break that down into parts
// Initiate the Let statement
Let([
// Set a two-dollar-var that acts as value counter, when this
// is equal or greater to the amount of values in the list
// the function will no longer call itself but instead return
// a list of unique values. On the first call this value will
// not be set so we make sure it is set to 1
$$counter = If( $$counter < 1; 1; $$counter + 1 );
// Set a two-dollar-var that stores the current value that we
// are testning. We use the counter to get the next value from
// the list
$$currentValue = GetValue(ListOfValues; $$counter);
// Set a two-dollar-var that holds the list of values to
// return when everything is done. This list holds the
// unique values found and is added to when a new value is
// encountered. The If-statement checks if the value already
// exist in the returnstack
$$returnStack = $$returnStack &
If( PatternCount( "¶" & $$returnStack & "¶"; "¶" & $$currentValue & "¶" ) = 0 and Length( $$currentValue ) > 0;
// If true
$$currentValue & "¶";
// If false
""
);
// Set a local variable to hold the list of values to return
// in case the current recursion is the last. We do not need to
// worry about clearing this var, that is done automatic. We
// need to store this locally so that we can clear $$returnStack
// if needed
return = $$returnStack
// End the declaration part of the Let-statement
];
// Check if the value of counter is greater then the count
// of values passed to the function as parameter listOfvalues when
// calling in. If the value of counter is less we call the function
// again to make one more recursion. If the value of counter is
// greater then we are done looping and shall return values.
// We return values using a Let-statement to clear two-dollar-vars
If( ValueCount( ListOfValues ) > $$counter;
// If true
FilterUniqeValues( ListOfValues );
// If false
Let([
// Clear two-dollar-vars because we are done looping
$$counter = "";
$$currentValue = "";
$$returnStack = ""
];
// Return the list of unique values
Left( return; Length( return ) - 1 )))
)
Of course this is a bit excessive commenting even for me but I hope it clarifies what the calculation does. The point is that the function carries three values ($$counter, $$currentValue and $$returnstack) as two-dollar-variables between recursions and because the are two-dollar-variables we do not need to pass the values as function parameters between recursions. The syntax is very simple to read, executes quickly and the developer using the function only needs to see the one parameter that he actually is using.
When we return values the Let-statement on the end clears the two-dollar-variables and thus they are gone the second the function is done looping.
There is however one safety factor to think about. Since two-dollar-variables are scoped to the entire database is is wise to user fairly long variable names. This is to ensure that no existing two-dollar-variables gets replaced by the ones used by the function. As example the name $$counter is a very poor choice. It is likely that the very same name could be used in a script where this function is used and in that case the name would colide.
In my working version of the function above I use the following names to make sure that wont happen
- $$counter is called $$RQCF__FilterUniqeValues_Counter
- $$currentValue is called $$RQCF__FilterUniqeValues_CurrentValue
- $$returnStack is called $$RQCF__FilterUniqeValues_ReturnStack
making the complete function look like this
The longer names on the two-dollar-variables make the syntax a little bit harder to read but they also make sure we won´t run into name conflicts later on.
I hope this post helped you realize the benefits of storing values between recursions in two-dollar-variables instead of complex parameter handling. Have a nice day =)
