Software Venture Consulting

FileMaker Pro downloads & Resources
FileMaker Custom Functions
FileMaker Web Viewer Examples
FileMaker Pro & Lasso Consulting
Training
FileMaker Books
FileMaker Articles
FileMaker Error Reference

Free Web Tools
Free FileMaker Tools

Personal Pages
Videos
Adventures
Links

Shopping Cart
Shopping Cart

Search:

Free Newsletter
Signup


Contact


Privacy Policy



FileMaker is a registered trademark of FileMaker, Inc. in the U.S. and other countries.

 

 FileMaker Pro Custom Functions

List  |  Show Random  |  Upload  |  Add This to Your Site

ListPosition ( ListValues ; SearchValue )

Rate this function:  

RatingRatingRatingRatingRating
  Average rating: 4.5  (31 votes)
  Discuss this Custom Function

Joseph Arzate, IT Professional Consultants
http://www.itprofessionalconsultants.net

Finds a value in a list and returns the position.

Sample Input:
ListPosition ( "Apple¶Grape¶Orange¶Cherry¶Orange" , "Orange")
Sample Output:
3
5


 Then copy & paste into FileMaker Advanced's Edit Custom Function window.

Click here to copy To Clip Manager if you have myFMbutler's Clip Manager installed

Description:

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

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

This is my Custom Function and I want to edit it

Discuss:

5 most recent comments | Show all 9 comments

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.

Patrick, Utah
March 25, 2015 8:46am

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

Joseph Arzate, Los Angeles, CA
March 26, 2015 9:25am

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
June 30, 2015 2:10pm

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.

John Davis, Houston
June 30, 2015 3:59pm

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"

Edgar Ramirez, Veramar
November 02, 2016 11:20am

Make a comment about this Custom Function (please try to keep it brief & to the point). Anyone can post:

Your Name:
City/Location:
Comment:
characters left. If you paste in more than 1500 characters, it will be truncated. Discuss the function - advertisements and other useless posts will be deleted.
Answer 2 + 9 =
Search for Custom Functions:

Custom Functions Widget
Download the Custom Function Dashboard Widget for OS X
Keep all the latest Custom Functions right at your fingertips!

Newest Custom Functions:

1. decFromHex ( hexValue )
  (Mon, Oct 16, 12:34pm)
2. MonthYearList ( startmonth ; startyear ; numbermonth ; short )
  (Mon, Oct 16, 3:38am)
3. ErrorDescription ( errorNumber )
  (Wed, Sep 27, 2:51am)
4. decodeEntities (text)
  (Wed, Sep 27, 1:22am)
5. HexidecimalToNumber ( HexidecimalValue ; counter )
  (Mon, Sep 25, 12:39pm)
6. FixedFieldConverter ( Fieldname ; f1 ; f2 ; f3 ; f4 ; f5 ; f6 ; f7 ; f8 ; f9 ; f10 ; f11 ; f12 ; f13 ; f14 ; f15 ; f16 ; f17 ; f18 ; f1
  (Fri, Sep 15, 12:34pm)
7. ShannonEntropy ( text )
  (Thu, Sep 07, 5:59am)
8. LetterCount ( text ; summary )
  (Thu, Sep 07, 5:53am)

RSS Feed of Custom Functions