Brian Dunning's FileMaker Custom Functions

ValueExists ( Value ; ValueList )

A recursive function that returns a 1 if a value is found in the supplied list

  Average rating: 3.9 (39 votes) Log in to vote

Caleb Ruth   Caleb Ruth
Data Performance
http://www.datap.co

Share on Facebook Share on Twitter

  Sample input:
ValueExists ( "rabbit" ; "mouse¶rabbit¶lynx¶snake" )
  Sample output:
1

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

The same results can be had using the native FilterValues function, but I find this to be more logical. Recursion limits apply to the size of the list.

 

Comments

Dave Graham   Dave Graham, San Diego, CA
Jun 9, 2010
There's no need for recursion for this function. This does it in a single line of code:


not IsEmpty( FilterValues ( valueList ; filterBy) )
 
F. Osman Cabi   F. Osman Cabi, Turkey
Jun 9, 2010
Alternative single line code:

PatternCount ( "mouse¶rabbit¶lynx¶snake" ; "rabbit" )

Also "IF" statement can be added if searched value exist more than one time.
 
Bruce Robertson   Bruce Robertson
Jun 13, 2010
Dave Graham's code works correctly. Osman's code will fail for partial matches.

PatternCount ( "mouse¶rabbit¶lynx¶snake¶lucky rabbit's foot" ; "rabbit" )
 
JPBoudreau   JPBoudreau, Montreal, QC, Canada
May 28, 2013
It seems that this custom function is incomplete at this time (20130528) - it stops at let
 
Andy Frazier   Andy Frazier, Mx4Px
Sep 13, 2013
An even easier one, to get around the weird "not" behavior I've seen since 12 came out:

GetAsBoolean(FilterValues(List("mouse";"rabbit";"snake");"rabbit"))
 
John Davis   John Davis, Houston
Jun 27, 2015
Dave Graham's code still works correctly.

A fix for Osman's PatternCount code that a) will not be True for partial matches and b) forces a strictly boolean result in case of multiple PatternCount matches:

GetAsBoolean ( PatternCount ( ¶ & valueList & ¶ ; ¶ & filterBy & ¶ ) )
 
John Davis   John Davis, Houston
Jun 27, 2015
Andy Frazier's code does not work except when strictly using numbers and when the filterBy number is not a 0. GetAsBoolean always returns False on text results and only returns True on container fields with data or on non-zero numeric results.

For a no-recursion function, use only Dave Graham's code (show all comments to see above) or my modification of Osman's PatternCount code above.
 
John Davis   John Davis, Houston
Jun 27, 2015
FilterValues is slow. PatternCount is faster than FilterValues, but Position is even faster. Here is a sample speed test of functions on a 10K starting found set on a local file:

FilterValues: 21 seconds
PatternCount: 10 seconds
Position: 6 seconds

The Position function only searches for the first match in the list, not all matches like FilterValues and PatternCount.

So, here is a better non-recursive code optimized for speed (returns True/False):

GetAsBoolean (Position ( ¶ & valueList & ¶ ; ¶ & filterBy & ¶ ; 1 ; 1 ) )
 
John Davis   John Davis, Houston
Jun 27, 2015
More intuitive 2nd parameter name than above. ValueExists ( valueList ; value ) :

GetAsBoolean (Position ( ¶ & valueList & ¶ ; ¶ & value & ¶ ; 1 ; 1 ) )
 
Federico   Federico, Bellinzona
Feb 17, 2017
GetAsBoolean (Position ( ¶ & valueList & ¶ ; ¶ & value & ¶ ; 1 ; 1 ) )
returns 1 when valuelist is "123" and value is "2"
beware of that.
 
Federico   Federico, Bellinzona
Feb 17, 2017
Edit:
GetAsBoolean (Position (valueList; value ; 1 ; 1 ) )
returns 1 when valuelist is "123" and value is "2"
beware of that.
 
Tom Fitch   Tom Fitch
Mar 29, 2017
The pilcrows are there for exactly that reason, Federico.
 
Howard   Howard, Los Angeles
Feb 5, 2018
not isempty( filtervalues ( value; valueList) )
 

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.