ListPosition ( ListValues ; SearchValue )
Finds a value in a list and returns the position.
Average rating: 4.5 (32 votes) Log in to vote
|
Joseph Arzate IT Professional Consultants http://www.itprofessionalconsultants.net |
5
Function definition: (Copy & paste into FileMaker's Edit Custom Function window)
/* INSTRUCTIONS / COMMENT / EXAMPLES - BEGIN
______________________________________________________________________
IT Professional Consultants Inc.
www.itprofessionalconsultants.net
Author: Joseph Arzate (9.5.13)
----------------------------------
Function: ListPosition ( ListValues, SearchValue)
[ListValues] - Expecting a "¶" delimited list of values. List submitted should not begin with MORE than 1 ¶ or the function will be inaccurate.
[SearchValue] - The value being searched upon in the List list provided. Any ¶ found in the SearchValue are removed.
-----------------------------------
Function Result: Returns a number of the position of the "SearchValue" in the list provided. If the "SearchValue" is found more than 1 time returns a ¶ delimited list of the position in the order it is found in the list. Returns 0 when "SearchValue" not found. This is a recursive function and will stop once the "SearchValue" no longer appears on the list in the order it is submitted.
Any ¶ characters submitted in the 'SearchValue' are removed and a provision has been added to remove any leading and trailing ¶ from the 'ListValues' if it exists. However, if more than 1 ¶ is submitted at the START of the 'ListValues' the CF will be inaccurate. Example: ListPosition( "¶¶Apple¶Grape¶Orange¶Cherry" ; "Orange" ) will result in "4" rather than "3".
Why this CF Created? Needed an easy way to search and analyze a value in a ¶ delimited list. Can be used with the GetValue function to grab the value from a list. Can also be used to count the number of times a value exists in a list by pairing with FM ValueCount function.
This function is more accurate than simply using 'PatternCount' due to the way PatternCount works in a value list.
Example: PatternCount( "123¶1234" , "123")
FM Result: 2
Using the CF with the same above example: ValueCount( ListPosition("123¶1234" , "123") )
Result: 1
-----------------------------------
Example
-----------------------------------
Example A - Simple Result
Syntax: ListPosition ( "Apple¶Grape¶Orange¶Cherry" , "Orange")
Result: "3"
Example A.2 - Multiple Result
Syntax: ListPosition ( "Apple¶Grape¶Orange¶Cherry¶Orange" , "Orange")
Result: "3¶5"
Example B - Count. If you want to know how many times a value exists in the list; wrap function with FM ValueCount function.
Syntax: ValueCount( ListPosition ( "Apple¶Orange¶Cherry¶Orange" , "Orange") )
Result: "2"
INSTRUCTIONS / COMMENT / EXAMPLES - END
______________________________________________________________________
CODE BEGIN - Function: ListPosition ( ListValues, SearchValue)
*/
Let ([
ListValues = Trim( Case( Left(ListValues ; 1 ) = ¶ ; Right ( ListValues; Length (ListValues)- 1 ) ; ListValues ) ) ; //House Cleaning. Remove any leading and trailing ¶ from the list submitted.
SearchValue =Trim( Substitute ( SearchValue ; ¶ ; "" ) ) ; //House Cleaning. Remove any ¶ from the Search Value.
EXISTS = not IsEmpty( FilterValues ( SearchValue; ListValues ) ) ; //Check to see if the value exists.
ListCount=ValueCount( ListValues ) ; //Count the List provided
POS= Position ( ¶ & ListValues & ¶ ; ¶ & SearchValue & ¶ ; 1 ; 1 ) ; //Position of the first occurence of the search value. IMPORTANT to pad with ¶ to ensure unique value is found.
LineNo = PatternCount( Left( ListValues ; POS ) ; ¶ ) + 1 ; //Line Position where the value is found.
FIRST= Case( LineNo = 1; 1 ;0) ; //Boolean result to see if the value found is in the first position of the list.
LAST= Case( LineNo = ListCount ; 1 ;0) ; //Boolean result to see if the value is the last position.
RemovedValue="x." & SearchValue & ".x" ; //On each iteration the found value is replaced with this value to prevent it from being processed again.
NewList= //This variable will produce a new List replacing the 'SearchValue' with 'RemovedValue' to ensure it moves to the next iteration of the 'SearchValue'
Case( FIRST =1 ; RemovedValue & ¶ & RightValues(ListValues ; ListCount - 1) ;
LAST <> 1; LeftValues( ListValues ; (LineNo - 1 ) ) & RemovedValue & ¶ & RightValues(ListValues ; ListCount - LineNo ) ;
LAST = 1 ; "" ; //Value found is the last item on list. No need to create a NewList, setting NewList to NULL causes function to exit.
) ;
FOUND = PatternCount( ¶ & NewList & ¶ ; ¶ & SearchValue & ¶ ) ] ; //Checks to see if the SearchValue exists in the newly constructed list. Used to determine if the function should exit or continue to process.
Case( exists = 0 ; 0; //Value not Found Exit.
LineNo & Case( FOUND > 0 ; ¶ & ListPosition( NewList; SearchValue ) ) //Value found and 'SearchValue' still on the list. Continue to evaluate list. Recursion will only occur if the value exists in the newly created list.
)
)
/* CODE - END: Function: ListPosition ( ListValues, SearchValue) */
Comments
 |
Bart Bartholomay, South Florida Sep 6, 2013 |
|
| in the example you cite in the commented portion of the function: "Example: ListPosition( "¶¶Apple¶Grape¶Orange¶Cherry" ; "Orange" ) will result in "4" rather than "3". " I believe the correct answer should be "5" not "4" owing to the fact that you didn't seem to account for the first value, which contains a NULL just like the second value. |
||
|
Joseph Arzate, Los Angeles, CA Sep 6, 2013 |
|
| Thanks for the comment Bart, however, the function will remove the first ¶ return from the ValueList. The second ¶ return is considered as a valid position resulting in 4. The Line Position is evaluated from counting the number of ¶ to the 'Target' SearchValue. | ||
 |
Patrick, JB Formation Mar 24, 2015 |
|
| I tried to use this function but it wouldn't let me because it uses itself. "LineNo & Case( FOUND > 0 ; ¶ & ListPosition( NewList; SearchValue )) | ||
|
Joseph Arzate, Los Angeles, CA Mar 24, 2015 |
|
| Hi Patrick, This is a recursive function, so it will continue to call itself until all the items on the list have been processed. The CF will exit once it finishes all the items submitted to it. This line of code: "LineNo & Case( FOUND > 0 ; ¶ & ListPosition( NewList; SearchValue )) Will only be true when it finds an item on the list. |
||
|
Patrick, JB Formation Mar 25, 2015 |
|
| Maybe I didn't make it clear enough. When I'm creating the custom function, It stops and says this function could not be found and highlights what I just showed. | ||
|
Joseph Arzate, Los Angeles, CA Mar 26, 2015 |
|
| Got it... I think your problem is that you have not renamed the custom function to "ListPosition" - by default FM will name the new function as "New Function". | ||
|
John Davis, Houston Jun 30, 2015 |
|
| Kudos for your well coded function. Most custom functions like this (finding values in a list) fail because of the "double sequential entry" bug, when more than one of the same value is in the list sequentially. For example, using functions like Position, PatternCount, etc. to search for ( ¶ & "Charlie" & ¶ ) in the value list "Alpha¶Bravo¶Charlie¶Charlie¶Charlie¶Charlie's Angels¶Echo" will give incorrect results, because for example, the ¶ after the first "Charlie" has already been evaluated, so that the 2nd "Charlie" ( ¶ & "Charlie" & ¶ ) is not found. I've always modified the value list by substituting "¶¶" for every ¶, which will then give accurate results. But I like how you replace each found result with a RemovedValue before searching for the next result. |
||
|
John Davis, Houston Jun 30, 2015 |
|
| Made some speed optimizations to the code. According to this article - http://filemakerhacks.com/2014/12/08/fm-13-anti-deduping-part-2/ - FilterValues is slow, PatternCount is faster, but Position is the fastest, since it doesn't have to evaluate the entire list, as it only finds the first match. His test of the three functions on a 10K found set in a local file was: FilterValues: 21 seconds PatternCount: 10 seconds Position: 6 seconds Now considering this custom function, the FilterValues function in the EXISTS variable is really unnecessary, since there is already a POS variable which finds the position of the first occurrence anyway. No reason to re-evaluate the list with FilterValues at every recursion! The PatternAccount function in the FOUND variable is also unnecessary, as all we need to know is if at least one occurrence of the SearchValue exists, so the Position function will do fine here, too. So, here are the changes to make to this function to optimize it for speed: 1) Move the POS variable line directly above the EXISTS variable line. 2) Change the code for the EXISTS variable to: EXISTS = GetAsBoolean ( POS ) ; //Check to see if the search value exists. 3) Change the code for the FOUND variable to: FOUND = Position ( ¶ & NewList & ¶ ; ¶ & SearchValue & ¶ ; 1 ; 1 ) ] ; //Checks to see if the SearchValue exists in the newly constructed list. Used to determine if the function should exit or continue to process. |
||
|
Edgar Ramirez, Veramar Nov 2, 2016 |
|
| This test below returns the wrong results ListPosition ( "¶¶a¶b¶¶c¶d¶e¶¶f¶d¶" ; "d") returns "6¶9" when the expected result is "7¶11" |
||
 |
Eric, SJSU Sep 13, 2018 |
|
| There are enough situations where I want the value after the one I'm searching for (and I'm too lazy to post process the returned value numbers), that I added a "valueShift" as a parameter. Thus: Function: ListPosition ( ListValues; SearchValue; valueShift) [ListValues] - Expecting a "¶" delimited list of values. List submitted should not begin with MORE than 1 ¶ or the function will be inaccurate. [SearchValue] - The value being searched upon in the List list provided. Any ¶ found in the SearchValue are removed. [valueShift] - Shift the value positions returned by the number valueShift. —eric ... Case ( exists = 0 ; 0; //Value not Found Exit. LineNo + valueShift & Case( FOUND > 0 ; ¶ & ListPosition( NewList; SearchValue; valueShift ) ) //Value found and 'SearchValue' still on the list. Continue to evaluate list. Recursion will only occur if the value exists in the newly created list. ) ... |
||
Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.