FileMaker Inspirations

This article, reprinted from one I submitted to Ezine Articles, discusses the creation of a Search and Replace function similar to the built-in Substitute function. Though it may not be practical to duplicate the functionality of this function, the article serves as a learning tool for creating recursive custom functions in FileMaker Pro Advanced.

The introduction of custom functions in FileMaker Pro Developer 7 delivered power that most of us FileMaker Pro users have just begun to unleash. Without custom functions, performing a global search and replace would have been done using a clumsy, time-consuming script.

The function I am demonstrating was originally created when a client of mine needed to be able to define custom, on-the-fly templates with placeholders for field values without having to create a new layout for each template. When included in a calculated field definition, the calculated field will always contain the live, up-to-date composite value without having to run a script.

If you are simply interested in acquiring the functionality of this function, you can copy and paste the code in Function Body. Be sure to define the function name and parameters as described. I would also appreciate a comment attributing this work to me, Danny Kohn of Inspirations Software Design, and please include the URL of this article.

If you want to understand how these functions work, read the whole article. They are described in detail

Recursion in FileMaker Pro Custom Functions

With no true looping ability in FileMaker Pro calculations, it is the recursion capability that accounts for much of the power of custom functions. Any looping functionality can be replicated using recursion.

For those of you not familiar with recursive functions or who are interested in a review, here is a simple definition:

A recursive function is a function that calls itself. There are two main features that are necessary for an effective recursive function:

1. A base condition that returns a simple value and does not call itself;
2. Defining condition that breaks down the complex problem into simpler problems, then calls itself with simpler values leading it closer to the base condition.

The defining condition must always lead to the base condition eventually in order to avoid infinite recursion. If this is not clear to you yet, it will make more sense after reviewing the global search and replace function below.Single Search and Replace Function

Before adding the complexity of recursion, here is a look at a simple search and replace function that simply replaces the first instance of the found instance. This function will be used by the function that performs the global search and replace.

Function Name: String Replace

Parameters:

Needle – This parameter represents the substring for which we wish to search.

Replacement – This holds the string that will replace the substring in Needle.

Haystack – This parameter holds the entire string to search: the proverbial Haystack in which to find and replace the Needle, if you will.

Function Body:

Case( PatternCount( Haystack ; Needle ) > 0
; Replace(
    Haystack ;
    Position( Haystack ; Needle ; 1 ; 1 ) ;
    Length( Needle ) ; Replacement )
; Haystack

)

Using the built-in Case function (since there are only two cases, the If function could be used here as well), we test for the existence of Needle within Haystack with the built-in PatternCount function:

PatternCount ( Haystack ; Needle )>0

For the true case, we print the results of the built-in Replace(text;start;numberOfCharacters;replacementText) function.

Replace ( Haystack ; Position ( Haystack ; Needle ; 1 ; 1 ) ; Length ( Needle ) ; Replacement )

The replacement within Haystack starts at the position of the first instance of Needle and extends the number of characters of Needle and is replaced by Replacement.For the false or default case, we simply print Haystack since there is no Needle to replace.

Global Search and Replace Function

Function Name: String Replace

Parameters:

Needle – This parameter represents the substring for which we wish to search.

Replacement – This holds the string that will replace the substring in Needle.

Haystack – This parameter holds the entire string to search: the proverbial Haystack in which to find and replace the Needle, if you will.

Function Body:

If( PatternCount( Haystack ; Needle ) > 0 ;
Let( pos = Position( Haystack ; Needle ; 1 ; 1 ) + Length( Needle ) ;

Let(

[  HaystackBegin = Case( pos > 0 ; Left( Haystack ; pos - 1 ) ; "" ) ;

HaystackEnd = Middle ( Haystack ; pos ; Length( Haystack ) - pos + 1 )

];

String Replace( Needle ; Replacement ; HaystackBegin ) & String Replace All( Needle ; Replacement ; HaystackEnd )

)

)

; Haystack

)

Our base condition here is a Haystack with 0 instances of Needle. Here we simply output the Haystack.For a defining condition, when Haystack has more than 0 instances of Needle (it can never have a negative number of instances, of course), we replace the first instance and call this function recursively with the remaining Haystack. With each call, there will be one less Needle in the Haystack, until the base condition is reached where 0 Needles exist in the Haystack.

Using the built-in If function, we do a test for a non-base condition which is a Haystack with 1 or more instance of Needle.

PatternCount( Haystack ; Needle ) > 0

The reason we don’t test directly for the base condition (i.e.PatternCount ( Haystack ; Needle ) = 0), is that in some circumstances, such as when Haystack is empty, the PatternCount function may return an undefined value, which is neither 0 nor greater than 0, but should result in a base condition return.If the condition is true, there are Needles in the Haystack, so we set these variables:

Then, we print the result of HaystackBegin with its single instance of Needle replaced and concatenated(&) with the recursive call to String Replace All with the remaining Haystack.

String Replace( Needle ; Replacement ; HaystackBegin ) & String Replace All( Needle ; Replacement ; HaystackEnd )

Finally, when the PatternCount condition is false, we simply print Haystack.