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 DataIsland Software LLC http://www.dataisland.com

  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, NYC Feb 24, 2009
This works!!! Hallelujah!

		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 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, 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, 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, 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, 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, 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.

Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 20 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth: