The introduction of custom functions in FileMaker Pro Developer 7 brought a power that most of us FileMaker Pro users have only just begun to unleash. Without custom functions, doing a global search and replace would have been done using a clunky and slow script.
The function I’m demonstrating was originally created when a client of mine needed to be able to define custom templates on the fly 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 and updated composite value without having to run a script.
If you are simply interested in acquiring the functionality of this feature, you can copy and paste the code into Body Function. 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 including the URL for this article.
If you want to understand how these features work, read the full article. Described in detail
Recurrence in FileMaker Pro custom functions
Without true looping in FileMaker Pro calculations, it is recursion capability that explains much of the power of custom functions. Any looping functionality can be replicated using recursion.
For those of you unfamiliar with recursive functions or interested in a review, here is a simple definition:
FOR recursive function it is a function that calls itself. There are two main characteristics that are necessary for an efficient recursive function:
- FOR basic condition which returns a simple value and does not call itself;
- Defining condition which breaks down the complex problem into simpler problems, then calls itself with simpler values that bring it closer to the base condition.
The defining condition must always eventually lead to the base condition to avoid infinite recursion. If this is still not clear to you, it will make more sense after reviewing the global search and replace feature below.
Unique search and replace function
Before adding the complexity of recursion, here’s a look at a simple find and replace function that simply replaces the first instance of the found instance. This function will be used by the function that performs global search and replace.
Function name: Chain replacement
Parameters:
Needle – This parameter represents the substring that we want to find.
Replacement – Contains the string that will replace the substring in Needle.
Alpaca – This parameter contains the entire string to search for: the proverbial Alpaca in which to find and replace the Needle, If you like.
Body Function:
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 Yes function could be used here as well), we tested for the existence of Needle within Alpaca with the built-in PatternCount function:
PatternCount ( Haystack ; Needle )>0
For him certain case, we print the results of the function Replace (text; start; number of characters; replacement text) function.
Replace ( Haystack ; Position ( Haystack ; Needle ; 1 ; 1 ) ; Length ( Needle ) ; Replacement )
The replacement within Alpaca begins at the position of the first instance of Needle and expands the number of characters in Needle and is replaced by Replacement.
For him fake gold default case, we just print Alpaca since there is not Needle to replace.
Global find and replace function
Function name: Chain replacement
Parameters:
Needle – This parameter represents the substring that we want to find.
Replacement – Contains the string that will replace the substring in Needle.
Alpaca – This parameter contains the entire string to search for: the proverbial Alpaca in which to find and replace the Needle, If you like.
Body Function:
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 Alpaca with 0 instances of Needle. Here we simply take out the Alpaca.
For a defining condition, when Alpaca has more than 0 Needle instances (you can never have a negative number of instances, of course), we replace the first instance and call this function recursively with the rest Alpaca. With each call there will be one less Needle at Alpaca, until the base condition is reached where 0 Needles exist in the Alpaca.
Using the built-in Yes function, we test for a non-base condition that is a haystack with 1 or more Needle instances.
PatternCount( Haystack ; Needle ) > 0
The reason we don’t directly test the base condition (i.e. PatternCount (Haystack; Needle) = 0), is that in some circumstances, like when Alpaca is empty, the PatternCount The function can 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 is Needles in the Alpaca, then we set these variables:
-
pos = Position( Haystack ; Needle ; 1 ; 1 ) + Length( Needle )
– The position of the character inside Alpaca immediately after the first instance of Needle.
-
HaystackBegin = Case( pos > 0 ; Left( Haystack; pos - 1 ) ; "" )
– The content of the Alpaca chain to position pos. This string will contain exactly one instance of Needle
-
HaystackEnd = Middle( Haystack ; pos ; Length(Haystack) - pos + 1 )
– The remaining content of Alpaca containing one less instance of Needle.
Then we print the result of Haystack with your only instance of Needle replaced and concatenatedAND) with the recursive call to Chain Replace All with the rest Alpaca.
String Replace( Needle ; Replacement ; HaystackBegin ) & String Replace All( Needle ; Replacement ; HaystackEnd )
Finally, when the PatternCount condition is false, we just print Haystack.