FileMaker Pro Custom Functions

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; "¶¶"; "¶") ) )

)

Bruce Robertson, Redmond
May 25, 2010 8:36pm

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, USA
June 05, 2010 9:18am

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

Cynthia, USA
June 05, 2010 5:54pm

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, Yorkshire, UK
October 18, 2010 5:34pm

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 ; "¶¶" )
)

Tom Elliott, Yorkshire, UK
October 18, 2010 5:52pm