AnsweredAssumed Answered

Removing last comma in a substitution calculation

Question asked by mckinlay on Oct 20, 2018
Latest reply on Oct 21, 2018 by mckinlay

I have a field (Race) that gathers race data based upon OMB categories added via a checkbox set:







My goal is to have the contents of a calulation field (used for exporting) to have one row for the selected values so a record would look like:


for a person selecting Asian and White.


I used the function:

Substitute ( List ( Race ) ; ¶ ; "," ) 

This yields:



I can't for the life of me come up with a way to define the calculation to get rid of the hanging comma!



Additional information that might complicate things is that the Race field has an auto-enter calculation that replaces the existing value:


Case( PatternCount ( Self ; "Unrecorded" )= 1; "Unrecorded";
SortValues ( Self )


This is done to prevent people checking all the boxes and also ensuring the sort order remains consistent no matter the election order.


I've added my an example of what I'm doing just in case


I uploaded an updated version FM file I'm using to find the solution. Thanks to all who helped out!