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

Custom Functions
Value Lists & Script Parameters

FileMaker makes extensive use of “Value Lists” -- that is, a series of values separated by carriage returns.

The functions in this category make it easy to create, parse, and search value lists to assist with passing multiple parameters to a script, packing multiple values into a script result (the value returned from a subscript to the calling script), updating or deleting individual values in a value list, and much more.

Just click on a custom function name in the list below to see all the details for that function.

Extend_Value( Value , Number )

Generates a string containing the given value, repeated the specified number of times.

Among other uses, this function can be invoked by the
Update_Value function to generate a string of return characters for use when adding a value to a location past the current end of the value list (i.e. adding the 100th value to a value list currently containing only 50 items, where 49 returns are needed to pad the end of the value list before the new value is appended).

Get_Parm( Number )

Used to extract individual values from a mutli-value string (value list) passed to a script, where the Parm function was used to package the individual vales.

Get_Result( Number )

Used to extract individual values from a mutli-value string (value list) returned by a script (script result), where the Parm function was used to package the individual vales.

Get_Value( Value_List , Number )

Extracts a single parameter value -- by position -- from a supplied return-delimited value list or from a parameter package created with the Parm function.

In_Named_Value_List( Value_List_Name , Value )

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.

In_Value_List( Value_List , Value )

Determines if a specified value is present in a specified list of values, and if so, to return the index to the value in that list.

In_Value_List_Key( Value_List , Value )

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¶

Insert_Value( Value_List , Index , New_Value )

Inserts a new value between two existing values in a value list, effectively lengthening the list by 1 value. Can also “insert” a new value in any position past the end the current value list (with extra returns inserted as needed to fill the "missing" values with null entries).

The Index parameter specifies the position of the existing value BEFORE which the new value is to be inserted. Specifying 1 will cause the new value to be added at the very beginning of the value list. Specifying zero will return the original value list WITHOUT inserting the new value. (This results in the new value becoming the Nth value in the list, where N = the Index specified.)

To replace or clear an existing value,or to add a new value at or past the end of a value list, use the
Update_Value function.

Parm( Value ) [for FileMaker 7, 8, 8.5, 9]

Used to package multiple values to be passed to a script (script parameters) or returned from a script (script result) into a multi-value string (value list)

Parm( Value ) [for FileMaker 10, 11, 12, 13, 14, 15, 16]

Used to package multiple values to be passed to a script (script parameters) or returned from a script (script result) into a multi-value string (value list).

Remove_Value( Value_List , Index )

Removes a value in the specified position, along with the associated delimiting return character, from a value list, effectively shortening the list by 1 value.

To replace or clear an existing value, use the
Update_Value function.

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.

Update_Value( Value_List , Index , New_Value )

Replaces or clears a value at a specified position in a value list. Can also be used to add a value in any position past the end the current value list (with extra returns inserted as needed to fill the “missing” values with null entries).

To completely remove a value (effectively shortening a value list, use the
Remove_Value function.

To insert a value between two existing values (effectively lengthening a value list, use the
Insert_Value function.