How to remove comma from value list calculation
My question is probably elementary, but I have been unable to find a similar question in the forums. I apologize if I am repeating a common question!
I am creating a database in FileMaker Pro 12 for an artist, and have fields for the medium of the artwork (Medium-Flatworks) and for the base on which the artwork is made (Base-Flatworks). These fields are on the layout as checkboxes with the value lists MediumList and BaseList. I have created the following calculation field (Base-Flatworks_c) for each to create a list of the selected values separated by commas:
Substitute ( FilterValues ( ValueListItems ( Get ( FileName ) ; "BaseList" ) ; Base-Flatworks ) ; ¶ ; ", " )
This results in the following format: "BaseA, BaseB, BaseC," with the last value being followed by a comma. How can I remove this last comma so that I can create a calculation field to look like:
Medium-Flatworks_c & " on " & Base-Flatworks_c
(for example: "ink, crayon, paint on cardboard, gouache")
It would be also great for the last value to start with an "and", so that the list would output as "ink, crayon, and paint" but this is less important.
Thank you for your help!