Something is unusual with your data here. ValueListItems does NOT return a list with a return after the last value and thus there should be no final comma to remove. Is there, by any chance some value in your value list that is a non printing character such as a space? ValueLIstITems also appears to be not the right thing to use here as it will return a list of ALL values from the value list, not just those selected for a given record.
Substitute ( CheckboxField ; ¶ ; ", " )
Should be all that you need to get the same list with commas. (And make sure that you do not have any additional text in the check box field as the checkbox value list will hide this data.) Perhaps there is a return at the end if you are using scripts or other methods that use one of the values functions such as RightValues, LeftValues, MiddleValues, FilterValues... They all make life difficult by appending a return to the end of the list of values which you usually then have to remove.
Here's a calculation that removes the extra character if it is present and inserts the word "and" where needed:
I am assuming that MediumList is a field formatted with multiple check boxes
Let ( [ TheList = IF ( Right ( MediumList ; 1 ) = ¶ ; Left ( MediumList ; Length ( MediumList ) - 1 ) ; MediumList ) ; //remove trailing return if it exists
Vcount = ValueCount ( TheList ) ;
LeftItems = LeftValues ( TheList ; Vcount - 1 ) ;
LeftItems = Left ( LeftItems ; Length ( LeftItems ) - 1 ) ; // strip off the extra return
TheList = If ( Vcount > 1 ; LeftItems & " and " & GetValue ( TheList ; vCount ) ; TheList ) // insert " and " if needed
Substitute ( TheList ; ¶ ; ", " )
$string = Substitute ( FilterValues ( ValueListItems ( Get ( FileName ) ; "BaseList" ) ; Base-Flatworks ) ; ¶ ; ", " )
to remove the lastcharacter: $new_string = Left( $string; Length ( $string )-1)
To substitute the last ",":
$a = PatternCount ( $new_string; ",")
$b = Position ( $new_string; ","; 1; $a)
Replace ( $new_string; $b; 1; " and ")
all together: $string_no_last_comma = Replace ( $new_string; Position ( $new_string; ","; 1; PatternCount ( $new_string; ",")); 1; " and ")
Thanks, Phil! Although my value lists are not unusual with no complex formatting or non-printing characters, the long form I was using did not list all the values, only the ones selected for the record. However the simpler script is much nicer! Let ( ... ) is working perfectly, just what I wanted. Thanks again!