Indexing FileMaker Pro indexes every word in a text field so that finds can be performed quickly. Think of the analogy of a book. Rather than trying to locate something by thumbing through each page, you look in the index and find all the pages numbers at once. FileMaker works in the same way. In order to make finds as fast as possible, the first 100 characters of every word is indexed. If you click into a field and type Command-I (Macintosh) or Ctrl-I (Windows), you can view the index of a field. Most characters are indexed but you may be surprised what characters designate a new word other than a space. The index does take up space, so if a field is not going to be searched on or used as a match field in a relationship, set the Storage Options in Manage Database to never index that field.
Level: Advanced 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:
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.
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.
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:
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.
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!