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

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.



Log in to post comments.


Note: these functions are not guaranteed or supported by 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: