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 - Show more from this author
Data Performance http://www.datap.co |
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, 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, 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 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, Montreal, QC, Canada May 28, 2013 |
|
| It seems that this custom function is incomplete at this time (20130528) - it stops at let | ||
 |
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, 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, 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, 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, Houston Jun 27, 2015 |
|
| More intuitive 2nd parameter name than above. ValueExists ( valueList ; value ) : GetAsBoolean (Position ( ¶ & valueList & ¶ ; ¶ & value & ¶ ; 1 ; 1 ) ) |
||
|
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, 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 Mar 29, 2017 |
|
| The pilcrows are there for exactly that reason, Federico. | ||
|
Howard, Los Angeles Feb 5, 2018 |
|
| not isempty( filtervalues ( value; valueList) ) | ||
Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.