Brian Dunning's FileMaker Custom Functions

ValuePlaceInSList ( SList ; Value ; Offset )

Returns the value line number (plus offset), in an ascending list of numbers, of the search value or the next highest value

  Average rating: 4.7 (22 votes) Log in to vote

Steven Zeidel   Steven Zeidel
Zeiteq
notarealwebsite.com

Share on Facebook Share on Twitter

  Sample input:
ValuePlaceInSList ( "2¶4¶20" ; 5 ; 0 ) //not found in list

ValuePlaceInSList ( "2¶4¶20" ; 4 ; 0 ) //found in list
  Sample output:
3 // matches next highest number, 20

2 // matches number, 4

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

Takes a sorted ascending list of numbers delimited by the ¶ character (SList) and searches for the number Value in the list. Returns the first line number that matches Value. If Value is not found in SList then the function instead returns the line number of the next highest value in the list.

The value of Offset is added to the returned value.

If the search value is empty, returns 0 (plus Offset)
If the sorted list is empty, the search value is treated as the next highest value and returns 1 (plus Offset).

Assumes the SList is well formed without preceding or trailing return characters.

If SList = "2¶4¶20" and Offset = 0:
then if the Value is empty or equals 0 or 1 or 2, the function returns 1.
If the Value is 3 or 4 the function returns 2
If the Value is between 5 and 20 (inclusive) the function returns 3.
If the Value is larger than 20 then the function returns 4.


 

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.

Under construction. Email me your wish list for improvements.