Tag: Middle

Level: Intermediate, Version: FM 8 or later

ValuePosition: The Function FileMaker Forgot

Kevin Frank

25 Feb 2012: Custom function syntax corrected to fix a minor bug.

There are various FileMaker functions that we can use to extract one or more characters from a text string (or any data string for that matter), provided we supply the proper numeric coordinates. For example,

Middle ( "Literature" ; 4 ; 3 ) = "era"

or

GetValue ( "Winter¶Spring¶Summer¶Fall" ; 3 ) = "Summer"

The key is knowing the numeric address of the data we wish to extract. In some cases we know it in advance (e.g., the first item of a return-delimited list); in other cases we calculate it on the fly. Continue reading “ValuePosition: The Function FileMaker Forgot” →

Level: Beginner, Version: FM 8 or later

Dude, that code is sooooo FM3

Kevin Frank

Recently I saw some code that brought nostalgic tears to my eyes. The goal was to parse the file name from a reference in a container field called, appropriately enough, photo_ref. Here’s an example of what the data viewer showed when pointed at that field:

image:/C:/Client/XYZ Corp/photos/andrew wigan.jpeg

And this is the code the developer had written:

Middle (
   Photos::photo_ref ;
   Position ( Photos::photo_ref ; "/" ; 1 ;
      PatternCount ( Photos::photo_ref ; "/" ) ) + 1 ;
   99999
)

In a nutshell: count the slashes, and then retrieve everything to the right of the final slash. Here in a FileMaker 11 solution was code that could have been written in 1995.

To his credit, the code correctly returned “andrew wigan.jpeg”, but I had to wonder whether the developer was aware that there were several things he could have done to make his life easier (and his code more readable).

First, he could have simplified the code by using Let() to eliminate the multiple references to “Photos::photo_ref”.

Let ( a = Photos::photo_ref ;
Middle (
   a ;
   Position ( a ; "/" ; 1 ; PatternCount ( a ; "/" ) ) + 1 ;
   99999
)
)   //   end let

He could also have moved a few more things up into the Let portion of the calc.

Let ( [
a = Photos::photo_ref ;
b = PatternCount ( a ; "/" ) ;
c = Position ( a ; "/" ; 1 ; b ) + 1
] ;
   Middle ( a ; c ; 99999 )
)   //   end let

I find that to be a heck of a lot more readable than the code we started with. However, there’s a different approach that could be used to solve this problem, which strikes me as being both easier to understand and more elegant.

Let ( [
a = Photos::photo_ref ;
b = Substitute ( a ; "/" ; ¶ ) ;
c = ValueCount ( b )
] ;
   GetValue ( b ; c )
)   //   end let

In other words, convert the reference to a list, by transforming the slashes into hard returns, and then grab the bottom value from that list. Once you get comfortable with this technique, you will find many situations where it comes in handy.

For example, if you use the GetFieldName() function, you know that it returns both the table occurrence name as well as the field name itself, separated by “::” like so:

Invoice::id_customer

What if you just want to extract just the field name? You can use a simplified version of the technique we just finished discussing:

Let ( [
a = GetFieldName ( Invoice::id_customer ) ;
b = Substitute ( a ; "::" ; ¶ )
] ;
   GetValue ( b ; 2 )
) // end let

…and the result is “id_customer”.