One Part Harmony – FileMaker Database Design, Development, and Maintenance

Custom Functions: Value Lists & Script Parameters
In_Named_Value_List

Determines if a specified value is present in a named value list defined in the current file, and if so, to return the index to the value in that list.

Format
In_Named_Value_List( Value_List_Name , Value )
Parameters
Value_List_Name - A string containing the name of a value list defined in the current file (database)

Value - The string containing the search value
Data Type Returned
number / boolean
Examples
Assume the variable $List_Name identifies a value llst in the current file that contains High¶Medium¶Low

Assume the variable
$Value contains Medium

Set Variable[$Index; Value:In_Named_Value_List($List_Name; $Value)]

Then
$Index would now contain 2 (which also evaluates to True)
Description
Returns -1 if the named value list does not exist in the current file (database).

Returns
0 (False) if the either the value list or the value is empty.

Return
0 (False) if the specified value does not exactly match any item in the value list. (Not case sensitive.)

Returns the index (a positive number indicating the sequence number of the value in the list ... that also evaluates to
True) if the value exactly matches an item in the value list.
FileMaker Versions Supported
7, 8, 8.5, 9, 10, 11, 12, 13, 14, 15, 16
Code
/*
Determines if a specified value is present in a return-separated list of values:

Returns -1 if the named value list does not exist in the current file (database).

Returns 0 if either the value list or the value is empty.

Return 0 (False) if the specified value does not exactly match any item in the value list.

Returns the index (a positive number indicating the position of the value in the list ... that also evaluates to True) if the value exactly matches an item in the value list.
*/

Let
(
[
Value = If( Right( Value ; 1 ) = "¶" ; Left( Value ; Length( Value ) - 1 ) ; Value ) ;

GoodValueListName = If( PatternCount( "¶" & ValueListNames( Get( FileName ) ) & "¶" ; "¶" & Value_List_Name & "¶" ) > 0 ; 1 ; 0 ) ;

ValueList = If( GoodValueListName ; ValueListItems( Get( FileName ) ; Value_List_Name ) ; "" ) ;

FirstCharPos = If( GoodValueListName ; Position( "¶" & ValueList & "¶" ; "¶" & Value & "¶" ; 1 ; 1 ) ; -1 )

] ;

Case
(
not GoodValueListName ; -1 ;
FirstCharPos < 1 ; 0;
PatternCount( Left( "¶" & ValueList ; FirstCharPos ) ; "¶" )
)

)