Brian Dunning's FileMaker Custom Functions

ComparativeValuePosition ( VList ; Comparator ; Value )

Quickly search for a comparative (>, <) number in an ascending number value list. Returns the value closest to the boundary that meets the criterea

  Be the first to rate this function Log in to vote

Bill Thurmes   Bill Thurmes - Show more from this author
MDCA
http://www.miyotadca.com

Share on Facebook Share on Twitter

  Sample input:
ComparativeValuePosition ( "5¶6¶7¶8¶9¶10¶11¶12¶" ; ">=" ; 5.27 )
ComparativeValuePosition ( "5¶6¶7¶8¶9¶10¶11¶12¶" ; "<=" ; 75 )
ComparativeValuePosition ( "5¶6¶7¶8¶9¶10¶11¶12¶" ; ">" ; 75 )
  Sample output:
2
8
0

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

# Quickly search for a number in a ascending-sorted list of number values, to get the position of the value in VList
# For instance, looking in VList for the first value >= 79
# Result is the position of, for >, the first value larger than the Value; for <, the last value less than the Value
# So, it finds the values just above or just below (or, for =, at) the Value boundary
# This is most useful when VList has >100 values, particularly when they're roughly linear
# This assumes that the values ARE linear, and computes where the boundary should be, and jumps to that position
# If it hits it, it gives that as an answer; otherwise it throws out the extra values from the side of the boundary it is on, and tries again
# The 'throwing out' is what makes this work quickly, and this alternates throwing out early or late values
#
# Parameters
# VList must be an ascending sorted ¶-delimited list of numbers. It must have no empty values and no leading or trailing ¶s,
# except for the one trailing ¶ often used in FM value lists
# Value is the target number
# Comparator can be >, >=, ≥, <, <=, ≤
# If VList contains no values that meet the criteria (all outside the range), the result is 0
#
# Example: ComparativeValuePosition ( "5¶6¶7¶8¶9¶10¶11¶12¶" ; ">=" ; 5.27 ) => Result 2 (because 6, in position 2, is the first value higher than 5.27)
# ComparativeValuePosition ( "5¶6¶7¶8¶9¶10¶11¶12¶" ; "<=" ; 75 ) => Result 8 (because the highest number less than 75 is 12, position 8)
# ComparativeValuePosition ( "5¶6¶7¶8¶9¶10¶11¶12¶" ; ">" ; 75 ) => Result 0 (because no numbers in list are > 75)
#
# Comparisons: for a 3648-item value list, choosing two values that were inexact matches and evaluated as items 791 and 2915,
# ValuesLessThanOrEqual gave wrong answers, took 2600ms; ValuePlaceInSList took 1611 ms; this CF took 13 ms
#
# Idea comes from ValuePlaceInSList (Steven Zeidel), which steps through one by one;
# I wanted a faster method for large value lists that were roughly linear, such as wavelengths from a spectrometer

 

Comments

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.

Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 16 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth: