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

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

Format
Update_Value( Value_List , Index , New_Value )
Parameters
Value_List - The existing value list.
Index - The position of the value to be updated (replaced), cleared, or added (if the Index is greater than the number of values in the existing value list)
New_Value - The value to be used to replace or clear any value currently in the position specified by the Index.
Data Type Returned
Text
Examples
Update_Value( “ab¶cd¶ef¶gh¶ij¶” , 4 , “xyz” ) returns “ab¶cd¶ef¶xyz¶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, that value is replaced with the New_Value provided. (If the New_Value is empty, then the value is effectively cleared, but it’s place in the list is maintained).

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 can be used to replace or clear a value at a specified position in a value list. It can also be used to add a value in any position past the end the current value list (inserting extra returns 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.
*/

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 & "¶" ;
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 ) ) & "¶" ; "" )
)

)