"No Holding Back FileMaker Blogging"


Support this site by clicking on a sponsor below or becoming a patron!

Create a Complete Contact Manager

Become a patron of this FREE web site!

Recent Blogs:

Calendar Conundrum
Calendar Conundrum

Themes Rule
Themes Rule

Weight Lifting
Weight Lifting

Form Letters in Browse
Form Letters in Browse

Context and Relationships
Context and Relationships

FREE FileMaker 17 Videos

Quick Tip:

No Relationship Needed
Global fields can display in any table within the same file without using a relationship. The same is true between two files in the same solution as long as there is a reference setup between the two files (External Data Source). What a lot of developers do is create a table called "Globals" to house all their global fields and unclutter other Manage Database tables.

Document Management Videos

Fun Stuff:

Dull Boy
FileMaker files were stored in clear text prior to FileMaker 7 so, if you opened a FileMaker file in a text editor, you could see the phrase "All work and no play makes Jack a dull boy" in the header repeated over and over. No, it's not a virus! My understanding is the developers just needed to take up some space in the header and that's what they came up with. Today, modern FileMaker files are stored in Unicode so I'm not sure if the phrase is still there.

Tips & Tricks Videos

RSS Feed
Phone Filter Part Two
Level: Advanced
Version: FileMaker 15
Category: Calculations
Tuesday, March 14, 2017
This is part 2 of the phone filtering solution. If you haven't read part 1 then it's a good idea to start there. Even if you are familiar with filtering phone numbers already and just want to read about the recursive version, it's still best to read part 1 to familiarize yourself with the approach. And, you never know, you might find a nugget of truth!

Phone Filter Part Two

Basic Recursion
Recursive custom functions are not a simple matter so let's start with a primer to get your juices flowing. All recursion is based on the simple premise of a conditional statement determining whether the calculation formula should continue call itself or looping. Once you learn a simple example of recursion, you can use it as a starting point for all other recursion, no matter how complex. That's literally how I start every custom recursive function. So, I just basically memorized a simple example and start there.

The basic technique I teach students is how to repeat the contents of a text field. It really doesn't accomplish anything worthwhile but teaches the foundation of recursion in an easily digestible form. Let's say you have "John" in a text field. If you feed the name of the text field and the number "3" into the completed custom function like so:

RepeatText(name_first; 3)

Then, the result is as follows:


Creating and editing a custom function requires FileMaker Advanced but the final formula will calculate in any version of FileMaker and can be called using the function name and parameter(s) assigned in FileMaker Advanced. The custom function dialog looks similar to the standard calculation dialog but with a few changes.

Phone Filter Part Two

The formula for the RepeatText function requires a function name and two parameters (Field and Repeat) to be defined with the following recursive formula:


Repeat > 0;

Field & RepeatText(Field; Repeat - 1)


Recursion is the repeated application of a recursive procedure or definition. What repeats the formula is a call back to the custom function at the end of line three. What stops the recursion from endlessly looping is the Case statement. Each time the function calls itself, it subtracts "1" from the Repeat parameter. Once the Repeat parameter reaches "0", the Case statement does not call the custom function, effectively exiting the recursive loop, and the concatenated result of the multiple function calls is returned.

Let's play around with the function and place a space between each iteration of the text field. The new formula looks like the following:


Repeat > 0;

Field & " " & RepeatText(Field; Repeat - 1)


The result of the formula now looks like the result below:

John John John

If you are looking carefully, there is actually a space after the last "John". While this may be disregarded by some developers, I find it unacceptable. If you are trying to parse the result with another set of functions, that extra space could throw off the formulation. Better to be precise than pay for the oversight later, especially if you happen to be creating return-separated lists which may be more sensitive to an extra carriage return. Besides, it's easy to fix anyhow.


Repeat > 1;

Field & " " & RepeatText(Field; Repeat - 1);



The changes made were increasing the "0" to a "1" in the test portion of the Case statement and adding a default false result. What happens is the formula recurses one less time and at the end, the default adds the text from the specified field minus the space. This trick will help tremendously with more complicated recursion so remember it well.

SIDE TIP: Recursive Custom Functions are great for building return-separated lists, which are often used in multi-key relationships.

Complex Recursion
Using the basic recursion, from above, as a template, let's create a recursive custom function that formats a phone number. The idea is to loop through each value in a format string and feed the phone number into the correct places. For example, the function call for the finished custom function looks like the following:

PhoneFormat(Self; "(###) ###-####")

Each pound sign (#) represents a number to be substituted. The rest of the values in the format string remain untouched. Starting from the left side of the format string, a number from the phone number field is plugged into the formatting whenever a pound character is encountered. If formatting is encountered, it is simply added as is to the result stack. In either scenario, the custom function makes a call back to itself to get the next formatting or phone number digit.

Phone Filter Part Two

This recursive function actually has two calls back to itself. If the value in the formatting is a pound sign (#), it uses the following call back to itself:

PhoneFormat(Right(@NumbersOnly; Length(@NumbersOnly) - 1); Right(Format; Length(Format) - 1))

If the value in the format string is a formatting character, it uses the following code:

PhoneFormat(@NumbersOnly; Right(Format; Length(Format) - 1))

The difference between the calls is one removes a character from both the format string and the phone number while the other only removes a character from the format string. There is no need to remove a digit from the phone number if the value in the format string is not a pound character.

The removal is done by determining the length of the phone number and/or formatting string, subtracting "1" and grabbing the calculated number of characters starting from the right. Some aspiring developers might think to approach the problem by removing the first character but it's easier to retain than remove.

Other than that, the rest of the recursion is pretty much the same as the basic recursion. There is a Let declaration for the phone number so the filtering of the user entered phone number only happens once but that's standard calculation efficiency. The Case statement controlling the recursion also has two tests. The format string is tested for empty since that controls how many times the formula recurses (the format string is used because it is longer than the phone number). The phone number is also tested for empty so it doesn't enter formatting when the number is deleted in the actual field.

In part 1 of this article, the auto-enter worked with a 7 or 10 digit phone number. Anything else was left unformatted so the validation could function properly. The recursive version of this formula has no limits as far as phone length, making it great for phone numbers around the globe. The only limit is the length of the formatting provided. While this makes the formula very flexible, it still needs to know when a phone number length doesn't match the formatting.

Phone Filter Part Two

Validation is a simple comparison of the length of the phone number to the length of the pounds in the formatting. Those two values must equal each other to have a valid phone number. The phone field is also checked for emptiness so no validation occurs when the phone number is removed.

John Mark Osborne

Example File (available to patrons at $5.00 per month): Download here if you are a patron

This blog is completely free. Please support it by clicking on one of the advertisers at the left side of the window or becoming a patron. Thanks so much!


Frank 03/27/2017
  Did I miss it, or is there no sample file for this article?

Response by:   John Mark Osborne 03/27/2017
That is correct. There is no sample file. It should be pretty easy to recreate as there are only two calculations. One for the filter and one for the validation custom function. Just copy and paste directly from the article. Happy FileMaking!
ERTER 03/24/2017

Add Comment:

First Name: *
Last Name:
Email: *
Web Site:
Comment: *
 Email Addresses will not be shared on the web site!