FileMaker Pro Custom Function – Global Search and Replace

[ad_1]

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:

  • pos = Position( Haystack ; Needle ; 1 ; 1 ) + Length( Needle )

    – The character position within Haystack immediately following the first instance of Needle.

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

    – The content of the Haystack string up to position pos. This string will contain exactly one instance of Needle

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

    – The remaining content of Haystack containing one less instance of Needle.

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.

[ad_2]

Source by Danny Kohn

Popular Posts