Brian Dunning's FileMaker Custom Functions

GetValueIndexByPosition ( valueList ; valueString ; matchCase ; occurrence ; positionLCR )

Retrieve the index of the matching valueString in valueList by comparing valueString against the left, center (middle), or right of each value in valueList.

  Average rating: 4.2 (29 votes) Log in to vote

Lewis Lorenz   Lewis Lorenz
Lorenz Companies
http://www.LorenzCom.com

Share on Facebook Share on Twitter

  Sample input:
GetValueIndexByPosition ( "Alpha¶Bravo¶Charlie¶Echo" ; "charl" ; False ; 1 ; "L" )
GetValueIndexByPosition ( "Alpha¶Bravo¶Charlie¶Echo" ; "charlie" ; True ; 1 ; "L" )
GetValueIndexByPosition ( "Daniel¶Danny¶Bill¶daniel¶Frank¶daniel¶Ed" ; "aniel" ; True ; -2 ; "R" )
GetValueIndexByPosition ( "Daniel¶Danny¶Bill¶daniel¶Frank¶daniel¶Ed" ; "an" ; False ; -2 ; "C" )
  Sample output:
3
0
4
5

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

Retrieve the index of the matching valueString in valueList by comparing valueString against the left, center (middle), or right of each value in valueList.
Returns 0 if not found.
Toggle case-sensitive searching by passing a boolean through matchCase.
Search for specific valueString occurrence. A negative occurrence number causes backward search from the end of the valueList.
Specify whether to match the valueString from the Left, Center (middle), or Right within each value.


NOTICE: While this function is optimized to be single-pass when possible, this is a recursive function; so, if you change the function name remember to also change it in the function code.

Examples:
GetValueIndexByPosition ( "Alpha¶Bravo¶Charlie¶Echo" ; "charl" ; False ; 1 ; "L" ) = 3
GetValueIndexByPosition ( "Alpha¶Bravo¶Charlie¶Echo" ; "charlie" ; True ; 1 ; "L" ) = 0
GetValueIndexByPosition ( "Daniel¶Danny¶Bill¶daniel¶Frank¶daniel¶Ed" ; "aniel" ; True ; -2 ; "R" ) = 4
GetValueIndexByPosition ( "Daniel¶Danny¶Bill¶daniel¶Frank¶daniel¶Ed" ; "an" ; False ; -2 ; "C" ) = 5

 

Comments

John Davis   John Davis, Houston
Jun 30, 2015
This function fails with the "double sequential entry" bug, like so many other list custom functions. With the following valueList this custom function will fail in some instances:

using valueList: "Alpha¶Bravo¶Charlie¶Charlie¶Charlie¶Charlie¶Echo"

To obtain the index of "Charlie", the valueStrings used for LEFT = "charl", RIGHT = "arlie", CENTER = "arl".
For 1-4 occurrences, the correct results are: 3, 4, 5, 6
For negative occurrences, the correct results are: 6, 5, 4, 3

- LEFT and CENTER returns WRONG results: 3, 5, 7, 9
- RIGHT returns CORRECT results.
- LEFT, RIGHT, and CENTER using NEGATIVE occurrences returns CORRECT results.


To obtain the index of "Echo", the valueStrings used for LEFT = "ech", RIGHT = "ho", and for CENTER (with True matchCase) = "ch"
The correct result is: 7

- LEFT and RIGHT returns THE CORRECT result.
- CENTER returns the WRONG result: 11
- LEFT, RIGHT, CENTER using NEGATIVE occurrences returns CORRECT result.

I was able to alter the code to give correct results in almost all instances by modifying index2 with a Case statement. I'll give the code in my next comment...
 
John Davis   John Davis, Houston
Jun 30, 2015
To continue my comment from above, I altered the code for index2 with a Case statement:

index2 = Case (

( just = "L" or just = "C" ) and ( Sign ( occurrence ) = 1 );

// THEN
If ( occurrence ≠ 0; GetValueIndexByPosition ( vlist ; valueString ; matchCase ; occurrence; just ) - 1; 0 );

// ELSE
If ( occurrence ≠ 0; GetValueIndexByPosition ( vlist ; valueString ; matchCase ; occurrence; just ); 0 )

);


Using the same valueList and search parameters in my above comment, the results I tested were all correct, except when trying to obtain the index of "Echo", when searching for any occurrences greater than the actual number of occurrences:

- LEFT incorrectly returns 11
- CENTER incorrectly returns 2
- the rest for "Echo", including negative occurrences, all return correct results
- and all results for "Charlie" are now correct


Giving an example with another valueList: "Alpha¶Bravo¶Charlie¶Charlie¶Charlie¶Bravo¶Charlie¶Echo"

when searching for either "Bravo", "Charlie", or "Echo", using LEFT, the results are correct, except for any occurrences outside of the number of actual occurrences.

Can anyone figure out how to force the function to always return 0 if there are no more occurrences?
 
John Davis   John Davis, Houston
Jun 30, 2015
FIXED THE CODE ==========

No more errors with sequential duplicate values, and no errors when calling an occurrence that doesn't exist. Modify the custom function this way:

1) Under the variable line for "vtext", add the following "exist" variable:

// Determine if requested occurrence even exists in list.
exist = GetAsBoolean ( Position ( vlist ; vtext ; 1 ; Abs ( occurrence ) ) );


2) Then replace the "index2" variable with the following Case statement:

index2 = Case (

// IF
exist = 0;

// THEN
0;

// IF
( just = "L" or just = "C" ) and ( Sign ( occurrence ) = 1 );

// THEN
If ( occurrence ≠ 0; GetValueIndexByPosition ( vlist ; valueString ; matchCase ; occurrence; just ) - 1; 0 );

// ELSE
If ( occurrence ≠ 0; GetValueIndexByPosition ( vlist ; valueString ; matchCase ; occurrence; just ); 0 )

);
 
John Davis   John Davis, Houston
Jul 1, 2015
Sorry, I'm still finding errors with my patchwork code above. The Custom Function needs to be rewritten. The original code fails in certain situations where there are double entries in succession. My code above fixes some of that but then breaks other searches/combinations.
 
Lewis Lorenz   Lewis Lorenz, Lorenz Companies
Aug 19, 2016
Fixed. Sorry for the delay.

For those with original version and the following changes were made to the code.

Changed:
//index = If ( pos ; ValueCount ( Left ( vlist ; pos ) ) ; 0 ) ;
To:
index = If ( pos ; PatternCount ( Left ( vlist ; pos - 1 ) , "¶" ) ; 0 ) ;

Changed:
//vlist = Case ( occurrence < 0 ; Left ( valueList ; pos - 1 ) ; occurrence > 0 ; Right ( valueList ; Length ( valueList ) - ( pos + Length ( valueString ) ) ) ; "" ) ;
To:
vlist = Case ( occurrence < 0 ; Left ( valueList ; pos - 1 ) ; occurrence > 0 ; Right ( valueList ; Length ( valueList ) - ( ( pos - 1 ) + Length ( valueString ) ) ) ; "" ) ;

Changed:
//index = Case ( occurrence < 0 ; index2 ; occurrence > 0 ; If ( index2 = 0 ; 0 ; index + index2 ) ; index ) ] ;
To:
index = Case ( occurrence < 0 ; index2 ; occurrence > 0 ; If ( index2 = 0 ; 0 ; index + index2 ) ; index ) + If ( occurrence = 0 and index > 0 ; 1 ; 0 ) ] ;

Thanks.
 
Joshua Willing Halpern   Joshua Willing Halpern, Los Angeles
Oct 31, 2016
Hey, when the matching value is first in the value list, this CF is returning 0. I think it should be returning 1.
 
Lewis Lorenz   Lewis Lorenz, Lorenz Companies
Nov 24, 2016
Fixed. Function returns 1 instead zero for matching first value.
 

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.