ValuePosition: The Function FileMaker Forgot

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.

But what if we want to go the other way? Can we perform inverse operations for the above examples? In other words, if we know the string we’re looking for, can we find its address?

For the first example, the answer is yes. We can use the Position function, like so:

Position ( "Literature" ; "era" ; 1 ; 1 ) = 4

But what about the inverse of the second example? Can we throw “Summer” at “Winter¶Spring¶Summer¶Fall” and get back a 3? Suprisingly, FileMaker does not provide us with a simple, built-in function to accomplish this. However, I didn’t come here today to whine, ladies and gentlemen, but to suggest a work around in the form of a custom function; and I’m not the first to do so.

Geoff Wells and Bruce Robertson have both posted custom functions on Brian Dunning’s web site, called PositionValue and PositionValues respectively. Theirs are more powerful than mine is… Geoff’s emulates the Position function and allows you to specify the “start” and “occurrence”; Bruce’s returns multiple positions if the value appears in the list more than once.

I have never needed either of those capabilities, so decided to keep mine as simple as possible… it assumes that the value only occurs once (it won’t break if that’s not the case; it simply returns the position of the first occurrence). Also, I decided to name my custom function ValuePosition, because that makes more sense to me. And without further ado, here is the definition:

ValuePosition ( theList ; theValue )

Let( [
pLp = ¶ & theList & ¶ ;
pVp = ¶ & theValue & ¶ ;
thePos = Position ( pLp ; pVp ; 1 ; 1 ) ;
theSegment = Left ( pLp ; thePos ) ;
theValPos = PatternCount ( theSegment ; ¶ )
] ;

theValPos

)

So now I can answer the question I raised above.

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

In case you were wondering, this isn’t just an academic exercise. I use this CF on a daily basis, and will give what I hope is a convincing example in an upcoming posting.

10 thoughts on “ValuePosition: The Function FileMaker Forgot

    1. Kevin Frank Post author

      It’s an incredibly useful tool to have in your tool kit, isn’t it? I came to rely on it so much, that a few years ago I forgot it was a CF. At one point, I was helping someone with their FileMaker files, and I couldn’t figure out why that function wasn’t available. “I know there’s a function called ValuePosition — I use it all the time! It must be in here somewhere….” Finally the light bulb went off — “I’ll be darned, it’s a CF!”

      Reply
    2. Kevin Frank Post author

      Hi Tom,

      A belated thanks for your article. I have found a couple good applications for your technique recently, when I needed the sort criteria to live in the parent rather than the child.

      Regards,
      Kevin

      Reply
  1. Bruce Robertson

    Note that theValue can be a value sequence, not just a single value.
    Result is 6 for the following:
    Let([
    theList = “Winter¶Spring¶Summer¶Fall¶2001¶spring¶Summer¶fall¶2002” ;
    theValue = “Spring¶Summer¶Fall¶2002”;
    R1 = ValuePosition( theList; theValue)];
    R1
    )

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s