Brian Dunning's FileMaker Custom Functions

PositionValue ( text ; searchValue ; start ; occurrence )

Same as the Position function but for Values

  Average rating: 4.3 (36 votes) Log in to vote

Geoff Wells   Geoff Wells
DataIsland Software LLC
http://www.dataisland.com

Share on Facebook Share on Twitter

  Sample input:
PositionValue ( MyList; "Seven"; 1; 1 )

MyList contains
One hundred
Two hundred
Thirty
Four thousand
Five hundred
Six hundred
Seven
Eighty eight
Ninety nine
One thousand
  Sample output:
7

  Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

In the same way that the Position function provides the number of a character position in a text string, PositionValue calculates the value number of a text string in a return delimited list.

 

Comments

DS   DS, NYC
Feb 24, 2009
This works!!! Hallelujah!
 
DJ   DJ, Santa Fe, NM
May 25, 2010
This will break if consecutive values are the same and you're trying to get the non-first one.

Dang Position() function.
 
Bruce Robertson   Bruce Robertson
May 25, 2010
Doubling up the ¶ seems to fix the position problem for identical consecutive values

/*
PositionValue( text; searchValue; start; occurrence)
*/

Let ([
cr = "¶" ;
adjustedText = Cr & Substitute(text; cr; cr & cr) & cr ;
target = cr & searchValue & cr ;
p = Position ( adjustedText ; target ; start ; occurrence );
chunk1 = Left ( adjustedtext ; P - 1 + Length ( searchValue ) ) ];

Case( p > 0; ValueCount ( Substitute( chunk1; "¶¶"; "¶") ) )

)
 
Cynthia   Cynthia, USA
Jun 5, 2010
When I tried to use the suggested solution for the problem with identical consecutive values, I got a value which was always 1 greater than it should have been. I also needed to be able to perform a backward search (negative value in occurrence), and I wanted to return a 0 if the searchValue was not found. Building on what came before (thank you Mr. Robertson!) here is what works for me:

/*
PositionValue (text; searchValue; start; occurrence)
If occurrence is negative, the search begins at the end of the list (backward search).
*/
Let(
[
cr = "¶";
adjustedText = cr & Substitute( text; cr; cr & cr ) & cr;
target = cr & _searchValue & cr;
// Added a case statement for start parm to allow for backward search
p = Position( adjustedText; target;
Case ( occurrence < 0 ; Length ( adjustedText ) ; start ); occurrence );
chunk1 = Left( adjustedtext; p - 1 + Length( searchValue ) )
];
// Subtracted 1 from ValueCount to account for cr added at beginning of adjustedText
// Added default case to return 0 if searchValue was not found
Case( p > 0; ValueCount( Substitute( chunk1; "¶¶"; "¶" ) ) - 1 ; 0 )
)
 
Cynthia   Cynthia, USA
Jun 5, 2010
Hmmm, also noticed that using searchValue in the calc for chunk1 truncates the last value because searchValue does not contain the leading/trailing cr. One solution is to change searchValue to target. The trailing cr in target does not need to be included in chunk1, so I ended up using the following calc for chunk1:

chunk1 = Left( adjustedtext; p + ( Length( target ) - 1 ) )

Also, a couple of examples using a backward search (negative occurrence):
PositionValue("A¶B¶C¶D¶B¶E" ; "B" ; 1; -1 ) = 5
and
PositionValue("A¶B¶C¶D¶B¶E" ; "B" ; 1; -2 ) = 2
 
Tom Elliott   Tom Elliott, Yorkshire, UK
Oct 18, 2010
Whoops, error check for empty search value missing in my previous comment;

The last Let variable should be: t = If ( sv ≠ "¶¶" ; Left ( l ; p ) )
 
Tom Elliott   Tom Elliott, Yorkshire, UK
Oct 18, 2010
Somehow my original comment never arrived. Corrected, it read:

Seems that start refers (intentionally?) to CHAR position not VALUE position

Here's what I use, start refers to value posn and may be outside range of the list (like Position fn), occurrence may be -ve, identical consecutive values are handled, SearchValue = ¶ finds empty values - assumes Char(1) is invalid in the list

// ListPosition ( theList ; SearchValue ; Start ; Occurrence )
Let ([
c = Char ( 1 ) ;
sv = ¶ & If ( SearchValue = ¶ ; c ; GetValue ( SearchValue ; 1 ) ) & ¶ ;
l = Substitute ( ¶ & theList ; [ "¶¶" ; ¶ & c & ¶ ] ; [ "¶¶" ; ¶ & c & ¶ ] ; [ ¶ ; "¶¶" ] ) ;
sp = Position ( ¶ & l ; "¶¶" ; 1 ; Min ( Start ; ValueCount (theList ) ) ) ; // char position of Start'th value in l
p = Position ( l & ¶ ; sv ; sp ; Occurrence ) ; // char position of required occurrence in l
t = If ( sv ≠ "¶¶" ; Left ( l ; p ) )
] ;
ValueCount ( t ) - PatternCount ( t ; "¶¶" )
)

Simpler version without SearchValue = ¶ capability:

Let ([
sv = ¶ & GetValue ( SearchValue ; 1 ) & ¶ ;
l = ¶ & Substitute ( TheList ; ¶ ; "¶¶" ) & ¶ ;
sp = Position ( ¶ & l ; "¶¶" ; 1 ; Min ( Start ; ValueCount (theList ) ) ) ; // char position of Start'th value in l
p = Position ( l & ¶ ; sv ; sp ; Occurrence ) ; // char position of required occurrence in l
t = If ( sv ≠ "¶¶" ; Left ( l ; p ) )
] ;
ValueCount ( t ) - PatternCount ( t ; "¶¶" )
)
 
Ken Nussear   Ken Nussear, Las Vegas NV
Apr 17, 2015
Looks like this was posted many years back, but seems have the functionality I need, but I can't seem to get it to work.

I'm using a list of dates produced by a Summary Calculation in FM 13. I'd like to return the order of the date in the list that matches the current record. No matter what I do I always get the answer back of 1. Even extracted out the txt into a simple txt field and I get the same. Any idea what I might be doing wrong?
 

Log in to post comments.

 

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

Under construction. Email me your wish list for improvements.