AnsweredAssumed Answered

Removing last comma in a substitution calculation

Question asked by mckinlay on Oct 20, 2018

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

asian

black

indian

pacific

white

 

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:

asian,white

for a person selecting Asian and White.

 

I used the function:

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

This yields:

asian,white,

 

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!

Outcomes