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

Custom Functions: Value Lists & Script Parameters
Insert_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.

Format
Insert_Value( Value_List , Index , New_Value )
Parameters
Value_List - The existing value list.
Index - The position of the value AHEAD of which the value specified is to be inserted in the existing value list.
New_Value - The value to be inserted into the existing value list.
Data Type Returned
Text
Examples
Insert_Value( “ab¶cd¶ef¶gh¶ij¶” , 4 , “xyz” ) returns “ab¶cd¶ef¶xyz¶gh¶ij¶”
Description
if the Index specified is zero, the Value_List if returned unaltered.

If the
Index specified is less than the number of values in the existing Value_List, the New_Value is inserted into the existing Value_List AHEAD of the value in the position specified by the Index. (This results in the new value becoming the Nth value in the list, where N = the Index specified, effectively lengthening the Value_List by 1 value.)

If the
Index specified is greater than the number of values in the existing Value_List, the Value_List is first padded on the right with the number of return characters needed to “add” the “missing” values, and then the New_Value is appended to the end of the Value_List.
FileMaker Versions Supported
7, 8, 8.5, 9, 10, 11, 12, 13, 14, 15, 16
Code
/*
This function inserts a new value between two existing values in a value list, effectively lengthening the list by 1 value.

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.
*/

Let
(
[

/*
count the items currently in the value list
*/
Value Count = ValueCount( Value_List ) ;

/*
remove any trailing return at the end of the value list, so we know its safe to add one later (eliminates dup returns)
*/
Value_List = Clean_Trailing( Value_List ) ;

/*
remove any trailing return at end of new value in case the Parm function was used to encode any returns in the actual value)
*/
New_Value = Clean_Trailing( New_Value )

] ;

Case
(
Index < 1 ; Value_List & "¶" ;
Value Count = 0 and Index = 1 ; New_Value & "¶" ;
Index > Value Count ; Value_List & Extend_Value( "¶" ; Index - Value Count ) & New_Value & "¶" ;
If( Index > 1 ; Clean_Trailing( LeftValues( Value_List ; Index - 1 ) ) & "¶" ; "" ) & New_Value & "¶" & If( Index <= Value Count ; Clean_Trailing( RightValues( Value_List ; Value Count - Index + 1 ) ) & "¶" ; "" )
)

)