You probably have a preference for how you would like a telephone number to appear. And the appearance will probably be different if the number is a mobile (cell) or a free call number. In Australia, there are a range of different number types. My preference is for formatting like this:
Standard phone numbers - (08) 8981 1144 or 8981 1144
Mobile numbers - 0402 839 829
Freecall numbers - 1300 797 201 or 13 13 13 or 1800 123 456
Phone numbers in the United States might be formatted (408) 987-7000. And in the UK, they are formatted like this (01923) 242926.
The problem here is having users remember how to format different numbers for different countries and uses, and then having them do it consistently. What we need is a database that can take a number and automatically format it as it should appear. This takes the onus off the user to do it properly and results in more consistently entered data.
So how is it done? Over the years, many developers have proposed many quite complex calculations to use as either a separate calculated field or, more recently, as an auto-entered calculation that replaces the text. The business logic of the required format is built into the calculation.
What I will show you here is a different approach that uses a custom function to format the phone number. That custom function can then be used directly in an expression, or indirectly through another custom function.
So let's get straight to the custom function. Custom functions can be created and edited using FileMaker Pro Advanced. Once created in a file, they can be used by anyone who has access to the FileMaker Pro calculation engine.
This custom function is called phoneMask and requires two arguments - number and format. The number is the phone number that needs to be formatted. The format is the phone mask to be used, supplied as a text string and using the # character for a digit placeholder. Some examples of phone masks you might use are:
(08) 8981 1144 would use (##) #### ####
13 13 13 would use ## ## ##
1300-797-201 would use ####-###-###
(408) 987-7000 would use (###) ###-####
23. 456.1212 would use ##. ###.####
The idea is that you can add any other required characters to the mask. The custom function will use the mask and replace any # characters with digits.
So you want to see the custom function?
It is called phoneMask (number; format). Here it is:
Let ([
digits = Filter ( number ; "1234567890" );
left_digit = Left(digits; 1);
left_format = Left(format; 1)
];
If ( left_format = "#" and not IsEmpty(left_digit); left_digit; left_format )
&
If ( Length (format) > 1;
phoneMask (
If ( left_format = "#"; Right ( digits; Length (digits) - 1) ; digits ) ;
Right ( format; Length (format) - 1 )
)
)
) // end Let
The first thing the function does it to filter the number supplied down to just digits (0-9) stripping out all other characters.
Then, using this statement:
If ( left_format = "#" and not IsEmpty(left_digit); left_digit; left_format )
It looks at the first character of the supplied format (mask). If it is a digit placeholder (#) and there is a digit available, it returns the first digit of the number supplied. Otherwise, it returns the first character of the supplied format.
Then, if there are more format characters to process, it calls the phoneMask function again using a reduced form of the number and the format mask. The act of a function calling itself is called recursion and is a powerful feature of custom functions in FileMaker Pro. In this case, it calls itself until it runs out of format mask characters.
An intended side-effect of this recursive process of replacing the # placeholders with digits is that if the function runs out of digits, the placeholders are used. For example, if 088981114 was entered using a format mask of (##) #### ####, it would return (08) 8981 114#. This is feedback to the user that not enough digits were entered.
So now we have a custom function, how do we use it? We could use it in an auto-entered calculated replace. That is a field option that we could set for the telephone field. We make sure that we uncheck the Do not replace existing value (if any) checkbox so that any telephone number entered is updated with the correctly formatted version. The calculation we would use could be:
phoneMask ( telephone ; "(##) #### ####" )
This will enforce the same formatting on every telephone number entered. To be more flexible, we could build in conditional selection of a format mask based on data in the record (such as country) or within the telephone number entered (such as number of digits or starting digits). An example of this is:
Let ([
number = Filter ( telephone ; "1234567890" );
digit_count = Length ( number );
format = Case (
country = "USA"; "(###) ###-####";
country = "Netherlands"; "##. ###. ####";
country = "Aust" and digit_count=6; "## ## ##" ;
country = "Aust" and digit_count=8; "#### ####" ;
country = "Aust" and Left(number;2)="04"; "#### ### ###" ;
country = "Aust" and Left(number;4)="1800"; "#### ### ###" ;
country = "Aust" and Left(number;4)="1300"; "#### ### ###" ;
country = "Aust" ; "(##) #### ####" ;
"(##) #### ####" // this is the default format mask
);
format_digit_count = PatternCount ( format ; "#" );
red = RGB ( 200 ; 0 ; 0 )
];
phoneMask ( number ; format )
&
If ( format_digit_count < digit_count; TextColor ( Right ( number; digit_count - format_digit_count ) ; red ) )
)
In the above calculation, the last If statement will take any remaining digits after the placeholders have been exhausted and add them to the end of the formatted number in red. This is visual feedback for the user that too many digits have been entered.
Finally, it is also possible to create a second custom function that contains the logic above. This function can then be used more simply to format the phone number while hiding the choice of format masks.
There is a sample file to go with this article. It can be downloaded from this link: Telephone Formatting. It contains the custom functions and examples of their use.
