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.