Database Reference
In-Depth Information
WORKAROUND WORKSHOP: WHEN DATA DOESN'T COMPLY
Sometimes the text you need to break up doesn't come in pieces that FileMaker automatically re-
cognizes, like characters or words. For example, suppose you have a URL:
music.site.com/Fishbone/Truth_and_Soul/Deep_Inside.mp3
You need to get the name of the file (Deep_Inside.mp3) and its parent folder (Truth_and_Soul). Un-
fortunately, this text value isn't divided into characters, words, or values. It's divided into
path com-
ponents
, with a slash between each.
When you're faced with something like this, your best bet is to make it look like something
FileMaker
can
deal with. If you can turn every slash into a new line symbol (¶), then you can simply
use the
RightValues()
function to pull out the last value. In other words:
Substitute ( File Path ; "/" ; "¶" )
The result of this expression is the list of path components, each on its own line:
music.site.com
Fishbone
Truth_and_Soul
Deep_Inside.mp3
To get just the file name, you can nest the Substitute function inside a
RightValues()
function like
so:
RightValues ( Substitute ( File Path ; "/"
; "¶" ) ; 1 )
Unless your data already contains multiple lines, you can always use the substitute function to turn
any kind of delimited list into a list of values. Bear in mind, though, that the substitute function is
case-sensitive
. You can read more about case sensitivity in the box on
Matching Text Values
.
These functions become powerhouses in combination with the various left, right, and middle
functions. When the fields you're parsing contain varying amounts of text, you can have
FileMaker count each one so you don't have to. For example, you might have a Parts Num-
ber field that contains parts numbers of varying length. Always, though, the last character is
one you don't want. To return all but the last character in a field, use this calculation:
Left ( My Field ; Length ( My Field ) - 1 )
It uses the left function to grab characters from the field, and the length function (minus one)
to find out how many to get. Just change the number on the end to chop off any number of
junk characters from the end of a field. You're welcome.
