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

PositionValue ( text; searchValue; start; occurrence )

Rate this function:  

RatingRatingRatingRatingRating
  Average rating: 4.3  (37 votes)
  Discuss this Custom Function

Geoff Wells, DataIsland Software LLC
http://www.dataisland.com

Same as the Position function but for Values

Sample Input:
PositionValue ( MyList; "Seven"; 1; 1 )

MyList contains
One hundred
Two hundred
Thirty
Four thousand
Five hundred
Six hundred
Seven
Eighty eight
Ninety nine
One thousand
Sample Output:
7


 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:

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.

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 8 comments

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
June 05, 2010 9:18am

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

Cynthia, USA
June 05, 2010 5:54pm

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
October 18, 2010 5:34pm

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 ; "¶¶" )
)

Tom Elliott, Yorkshire, UK
October 18, 2010 5:52pm

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?

Ken Nussear, Las Vegas NV
April 17, 2015 4:49pm

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 1 + 3 =
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