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

Custom Functions: Value Lists & Script Parameters
Table_Lookup

Given a search value, finds an associated value by searching a “paired” value list.

Each “pair” in the value list is formed by concatenating a “search” value, the pipe character (“|”), and an associated “return” value. Value “pairs” are separated by carriage returns, as with any other FileMaker value list.

Format
Table_Lookup
Parameters
Paired_Value_List - The “table” containing any number of “paired” values.

Search_Value - The value to be “looked up” in the table to find an associated value.
Data Type Returned
Text
Examples
Assume the variable $Branch_Codes contains
“San Francisco|21¶Los Angeles|22¶Bakerfield|43¶Pasadena|55”
(not including the quotes, of course).

Table_Lookup( $Branch_Codes , “Bakersfield” ) returns 43
Description
Adds a carriage return character to the beginning of the “paired” value list, and then searches the resulting string for a sub-string created by concatenating:

1. A carriage return
2. The search value
3. The pipe character (”|”)

If the search value is found, returns the characters between the separating pipe character and the next carriage return (the associated “return” value). Otherwise returns a null value (””).
FileMaker Versions Supported
7, 8, 8.5, 9, 10, 11, 12, 13, 14, 15, 16
Code
/*
This function assumes you have created a "paired" value list formatted like this:

"San Francisco|21¶" &
"Los Angeles|22¶" &
"Bakersfield|43¶" &
"Pasadena|55"

The function searches for a specified "search value" as the first value of a "pair", and, if found, returns the second value of the "pair".

If the search value is not found in the “table”, returns null ("").

Note: This function relies on these 2 other custom functions:

Clean_Field (look in the "Field Clean-Up" category)
Get_Value (look in the "Value Lists & Script Parameters" category)

*/

Let
(
[

Pos = Position( "¶" & Paired_Value_List ; Clean_Field( Search_Value ) & "|" ; 1 ; 1 ) ;

Entry = If( Pos = 0 ; 0 ; PatternCount( Left( Paired_Value_List ; Pos ) ; "¶" ) + 1 ) ;

Content = Get_Value( Paired_Value_List ; Entry )

] ;

Case
(
IsEmpty( Search_Value ) ; "" ;
Pos = 0 ; "" ;
Get_Value( Content ; 2 )
)

)