The FileMaker Let And Filter Functions For The Uninitiated

From Dwayne Wright PMP
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Please Note: If you are viewing this page in a news feeder, the images may get munged up a bit or other formatting of the posting may fail. For the best experience, please visit the journal directly by clicking (here).



Last week, I needed to build and populate a quick staff table for a project at the day job. I had a recent ROTS list to work from and I  could get what I needed from a low level parsing session.  If you are not familiar with the term ROTS, it is a code name for a simple list of people usually found as a PDF, Word or Excel file. It is short for Reach Out and Touch Someone. When I first heard the term, I briefly considered ROTS and "Reach Out And Touch Someone" likely NSFW candidates. I mean really?

Anyhoo … when I was done with my parsed data table, I realized this may be a good example to introduce Let or Filter functions to those intermediate developers unfamiliar with them.

THE QUICK LOWDOWN ON THE LET FUNCTION
The FileMaker Let Function allows you to define variables within its syntax. It can be used to make complex calculations easier because you can reference just about anything as a variable. A Let function has 3 parameters which are the variable name(s), the expression that defines each variable and the ending expression that uses the variables, along with other traditional FileMaker calc elements, to return a result. It tends to look something like...

variable = this expression
variable1 = this other expression
variable2 = this yet another expression
calculated result is (variable + variable2 + variable3)

Here we use the Let function to parse out the contents between a pair of parenthesis.

Let([
start = Position ( text ; "("; 1; 1)+1;
end = Position ( text ; ")"; 1; 1);
difference = $end - $start];
 
Middle ( text; start; difference)

THE QUICK LOWDOWN ON THE FILTER FUNCTION
The Filter function allows you to define what data or data strings you want to pass through the function and nothing else gets through.

Filter(”abc123xyz”, “abcdefghijklmnopqrstuvwxyz”) would return abcxyz
Filter(”abc123xyz”, “123456789”) would return 123


PUTTING IT ALL TOGETHER
The format of the ROTS list was the persons name, phone extension, dept and so forth. Each columns data was tab separated and I really only wanted to capture all the names, compare them with my previous list and add any new ones. Looking at each line, you could see the phone extension column always started with a number value. So the routine would be to look at everything to the left of the first number in the first line, capture it, discard the remainder of the line and repeat until finished.

I wrote a script to capture the first line of a text field and put it into another field. Then I applied the following calculation to that line to give me just the person's name data string. I would write that to the bottom empty row of a portal designed to create new records on the fly. Here is the calculation ...

Let([
nums = Filter ( ROTS Converter::line1; "0,1,2,3,4,5,6,7,8,9"),
firstnum = Left(nums,1);
numspos = Position ( ROTS Converter::line1; firstnum; 1; 1);
beforenum = Trim(Left(ROTS Converter::line1, numspos -1 ));
 
bob = "robert"];
 
beforenum)

If the bob = "robert" is throwing you off, disregard it. I explain what that is about below. Going to the top of the calc, the nums variable filters out everything in the row but numbers. The firstnum variable tells me what that first number of the row is and that helps me with the following numspos variable. The numspos variable tells me where that specific number position is in the first row and the before number captures everything to the left of the first number. The trim function is used to remove the blank spaces between the persons name and phone extension.

Dwayne Wright    167

nums = 167
firstnum = 1
numpos = 15
beforenum = Dwayne Wright

YEAH, I KNOW THE CALCULATION ISN'T OPTIMIZED
My Let function, although performing admirably to its assigned task, isn't very clean and that isn't uncommon when I know that my work is only a temporary endeavor. I got in the habit of doing this when I write test calculations in the "Watch" area of the FileMaker Advanced Data Viewer window. In cases like this, it was more important to move fast than to write a pristinely formatted expression. You will notice my last variable is bob = robert. I do that as a placeholder for the end of the variable setting because I'm not sure how much I'm going to mod the calculation.

That is one of the many things that is so cool about the Let function. You can stair step your way through complex calculations. You can declare some of the variables, write an initial "what did I get so far" end calc and then move forward with another level of variable. My placeholder variable is there simply so I don't have to keep track of the last variable and makes mods much faster. As I do my stair stepping, I simply add another variable above "bob" and then tweak the final ending expression to match.

Another aspect that isn't very clean is that the ending calculation expression. In this rough cut, it is simply the last variable I defined. Again, this is for stair stepping and mind mapping what you want to do. In a clean function, you would have the actual calculation there, although performance is exactly the same either way. This method does allow a way make the Let based calculation more flexible to mod, in just a very slight way.

=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2011 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.