February FileMaker Functions - Left, Middle, Right
This article covers nine functions that can all be understood as a group in three subgroups:
Left (text; numberOfCharacters)
LeftWords (text; numberOfWords)
LeftValues (text; numberOfValues)
Middle (text; start; numberOfCharacters)
MiddleWords (text; startingWord; numberOfWords)
MiddleValues (text; startingValue; numberOfValues)
Right (text; numberOfCharacters)
RightWords (text; numberOfWords)
RightValues (text; numberOfValues)
So each subgroup takes characters, words or values from the left, middle or right of the text. The middle functions require a starting value - which character, word or value to start from.
These functions are used extensively when parsing text - extracting parts of text defined by the starting position and number of characters from that point. There are countless examples of use of these functions. Here are some selected examples.
Examples of Use
1. Getting an Initial
If you know a person's first name (David), then you also know their initial (D). This is a simple calculation that uses the Left function to return the first letter from the left of the text:
Left ( firstName; 1 )
2. The Last Word
Sometimes you may be provided with data that has a person's full name in a single field. So you would like to extract the last word as the last name. You could use this expression:
RightWords ( fullName; 1 )
That is simple but you need to understand what FileMaker considers to be a word break. Obviously, a space is a word break. But a dash is also a word break. This means that a double-barrelled name like Fotherington-Smythe is actually two words. Using the above expression will return only Smythe from the full name.
One solution is to substitute out the dash with something that is not a word break character, extract the last word and then resubstitute the dash. So what is not a word break character? Any letter or number. We can't use letters because they may already exist in the last name so when substituting the dash back in we may end up with more dashes than expected. So let's use the number 1.
Let ([
name = Substitute ( fullName; "-"; "1" );
lastname = RightWords ( name; 1 );
lastname = Substitute ( lastname; "1"; "-" )
];
lastname
)
3. The Domain Name from a URL
We may want to extract the domain name from a URL such as:
http://www.ulearnit.com.au/course_book.php
The logic here is that we want everything from the first character after the http:// to that before the next /. In fact, if we assume that every URL starts with http://, the first character we want will always be the eighth. So then we need to calculate the position of the third / to get the end point.
Let ([
start = 8;
end = Position ( URL ; "/" ; 1 ; 3 )
];
Middle ( URL ; start ; end - start )
)
The end is calculated as the position in the URL field of the / symbol starting from the first character and being the third occurrence. The domain name is then extracted with the Middle function starting from the eighth character and being "end - start" characters long. So if the end character is at position 18, the domain is 18 - 8 characters long.
The above assumes that that URL starts with http://. What if it starts with https:// or ftp://? Or what if it just starts with www? To account for all these possibilities, we would need to be more specific about the calculation of the start and end variables. However, the principle remains.
3. Reducing Value Lists
When we considered the GetValue function, there was an example of a recursive custom function that processed the first value in a list and then passed the list back to itself minus the first value. It kept processing until there were no values left.
Our custom function, ListLastWord ( thelist ), was this:
If ( ValueCount ( thelist ) > 0;
RightWords ( GetValue ( thelist ; 1 ); 1 ) & ΒΆ &
ListLastWord ( MiddleValues ( thelist ; 2 ; ValueCount ( thelist ) - 1 ) )
)
In the context of this article, notice also that the custom function uses the RightWord function to extract the last word from the first value in the list.
The reduced value list is calculated using:
MiddleValues ( thelist ; 2 ; ValueCount (thelist) - 1 )
We can now look at this specifically and see how it works. It returns values from a variable called thelist. It returns values starting from value number 2. And the number of values it returns is the number of values in the list minus one. Thus, it is returning thelist without the first value.
You could also use this expression:
RightValues ( thelist ; ValueCount (thelist) - 1 )
