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

Custom Functions: Value Lists & Script Parameters
In_Value_List_Key

Determines if a specified value is present as the first field (the “key”) of a multi-field value in a specified list of values, and if so, to return the index to the value in that list.

For this function to perform as expected, the individual fields that are concatenated to form a single value in the value list must be separated by the pipe character (“|”) as in this example of a value list with 3 values -- each value containing 3 “fields”:

001|Bob Smith|Client¶002|Sue Doe|Vendor¶003|Ray Jones|Client¶

Format
In_Value_List_Key( Value_List , Value )
Parameters
Value_List - A string containing the return-separated list of values to be searched.

Value - The string containing the search value.
Data Type Returned
number / boolean
Examples
Assume the variable $List contains
001|Bob Smith|Client¶005|Sue Doe|Vendor¶007|Ray Jones|Client¶” (not including the quotes, of course).

Assume the variable $Value contains “005” (without the quotes).

Set Variable[$Index; Value:In_Value_List_Key($List; $Value)]


Then
$Index would now contain 2 (which also evaluates to True)
Description
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 (“key”) 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 (“key”) 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 as the first field (the key) of a multi-field value in a return-separated list of values.

For this function to perform as expected, the individual fields that are concatenated to form a single value in the value list must be separated by the pipe character ("|" ) as in this example of a value list with 3 values -- each value containing 3 "fields":

001|Bob Smith|Client¶005|Sue Doe|Vendor¶007|Ray Jones|Client¶

Returns 0 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.

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 ) ;

FirstCharPos = If( not IsEmpty( Value_List and not IsEmpty( Value ) ) ; Position( "¶" & Value_List & "¶" ; "¶" & Value & "|" ; 1 ; 1 ) ; 0 )

] ;

Case
(
FirstCharPos < 1 ; 0 ;
PatternCount( Left( "¶" & Value_List ; FirstCharPos ) ; "¶" )
)

)