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 - Show more from this author
Lorenz Companies
http://www.LorenzCom.com

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)

Let ( [ vtext = Substitute ( valueString ; "¶" ; "" ) ;

//Set from where in each value to search ( the justification of the valueString in each valueList value ) - L[eft], C[enter], R[ight].
just = If ( IsEmpty ( positionLCR ) ; "L" ; Left ( positionLCR ; 1 ) ) ; 

//Set vlist and vtext ( the search string ) based on justification: L, R, C.
vlist = Case ( just = "L" ; "¶" & valueList ; just = "R" ; valueList & "¶" ; valueList ) ;
vtext = Case ( just = "L" ; "¶" & vtext ; just = "R" ; vtext & "¶" ; vtext ) ;

//Get position of vtext in vlist. Continue searching until pos is 0.
pos = If ( occurrence = 0 ; 0 ; Position ( vlist ; vtext ; If ( occurrence < 0 ; Length ( vlist ) ; 1 ) ; If ( occurrence < 0 ; -1 ; 1 ) ) ) ;

//Get index of found valueString in valueList.
index = If ( pos ; PatternCount ( Left ( vlist ; pos - 1 ) ; "¶" ) ; 0 ) ;

//Always test if search string is exact match to the text found at pos, for use in occurrance adjustment statement.
match = Exact ( Middle ( vlist ; pos ; Length ( vtext ) ) ; vtext ) ;

//Adjust occurrence toward 0. If pos = 0 then set to 0. If matchCase then adjust only if matched, else adjust all. 
occurrence = Case ( pos = 0 ; 0 ; matchCase ; If ( match ; occurrence + If ( occurrence > 0 ; -1 ; 1 ) ; occurrence ) ; occurrence + If ( occurrence > 0 ; -1 ; 1 ) ) ;

//Prepare for recursive call. Get remaining valueList to search.
vlist = Case ( occurrence < 0 ; Left ( valueList ; pos - 1 ) ; occurrence > 0 ; Right ( valueList ; Length ( valueList ) - ( ( pos - 1 ) + Length ( valueString ) ) ) ; "" ) ;

//Recursive call only if not done. Store returned index.
index2 = If ( occurrence ≠ 0 ; GetValueIndexByPosition ( vlist ; valueString ; matchCase ; occurrence ; just ) ; 0 ) ;

//Set value to return. Add to previous index or send most recent index.
index = Case ( occurrence < 0 ; index2 ; occurrence > 0 ; If ( index2 = 0 ; 0 ; index + index2 ) ; index ) + If ( occurrence = 0 and ( index > 0 or ( index = 0 and pos > 0 ) ) ; 1 ; 0 ) ] ;

index

)

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

by Lewis C. Lorenz.
11/24/2016: UPDATED. 

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
*/

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, 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, 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, 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, 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, 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, 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, Lorenz Companies Nov 24, 2016
Fixed. Function returns 1 instead zero for matching first value.

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

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

Comments

Support this website.

Support this website.