"No Holding Back FileMaker Blogging"
|
Navigation:
|
Support this site by clicking on a sponsor below or becoming a patron!
FileMaker 20 Video Tutorials
Become a patron of this FREE web site!
|
|
Recent Blogs:
|
Window Locking
 |
Everything Changes
 |
Subsummary Unique Count
 |
Scripted Change Log
 |
Abstracted Log
 |
|
Meta-Consulting
Meta-consulting is a service Database Pros offers to help other developers
over programming hurdles via GoToMeeting. Email John Mark Osborne or call John Mark at (909) 393-4664 to find out more about this service.
|
The Philosophy of FileMaker recommends PCI!
|
Quick Tip:
|
Enter Text Format Quicker
I've never seen this documented, but just today, while in Layout mode, I double-clicked a field while pressing the Option key on my Mac and, suddenly, I was in the Text Format dialog. Iíd always hoped that there was a shortcut to get there, but I kept expecting to see a key-combination. Who knew? You can do the same under Windows by holding down the Alt key. Compliments of Reeva Golub. |
|
|
Create a FileMaker Calendar
|
Fun Stuff:
|
FileMaker Developer Worthiness
Top 10 ways you know your developer is NOT worthy:
- Spells "FileMaker" with a lowercase "m"
- Uses inches or centimeters instead of points in Inspector
- Recommends WebDirect deployment over FileMaker Pro
- Creates a solution with one file for every table
- Doesn't know Claris and FileMaker, Inc. are the same company
- Isn't certified for latest version of FileMaker
- Isn't an FBA member
- Charges less than $100.00 per hour
- Uses a rainbow of colors on his interface
- Provides a quote without a requirements document
|
|
|
Create a Password Management solution!
|
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!
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:
JohnJohnJohn
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.
The formula for the RepeatText function requires a function name and two parameters (Field and Repeat) to be defined with the following recursive formula:
Case(
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:
Case(
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.
Case(
Repeat > 1;
Field & " " & RepeatText(Field; Repeat - 1);
Field
)
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.
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.
Validation
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.
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.
Author:
John Mark Osborne
jmo@filemakerpros.com
www.databasepros.com
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!
|
|
Comments:
|
|
|
Frank
|
03/27/2017
|
|
|
Did I miss it, or is there no sample file for this article?
Thanks!
|
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!
|
|
|
|
|
