Concatenate multiple fields into single virtual field?

[Lauren]

Hi everyone,

I searched the forum already and I can't seem to find what I need to help me with my use case. I have a table Participant_Registration_tbl1 that has 10 fields pertaining to what system a participant works in (for example, System_1, System_2, System_3...System_10). I opted to separate our options for specific questions as unique fields instead of using multi select listboxes, essentially. I want to create a tabular report that displays a column showing all the systems that the person selected. Data type is text and uses the text of the field (so for example, System_1 might be [@field:healthcare] and the data when selected would be 'Health Care'.  I want to use a virtual field to concatenate these fields to display into one column, ideally in a bulleted list (although I know that's asking a lot). Next best case would be to concatenate them together using commas, but I don't want it to show a bunch of commas and spaces if the person only selected one out of 10 options. Is there any way to do this? 

 

To summarize, if the person selected both 'Health Care' and 'Social Work' but no other options of the 10 fields, I want it displayed as:

• Health Care
• Social Work

OR:

      Health Care, Social Work

but NOT:

    Health Care, , , Social Work, , , , , ,

 

Thanks in advance!

[CoopperBackpack]

Hello @Lauren,

I hope I understood your request correctly.

First, this thread can be helpful 

 

1) In the Table please add the Formula field with the following formula (use your field names)

'<ul>' + 

COALESCE('<li>'+ NULLIF([@field:System_1],'')+'</li>', '') + 
COALESCE('<li>'+ NULLIF([@field:System_2],'')+'</li>', '') + 
COALESCE('<li>'+ NULLIF([@field:System_3],'')+'</li>', '') + 
COALESCE('<li>'+ NULLIF([@field:System_4],'')+'</li>', '') + 
COALESCE('<li>'+ NULLIF([@field:System_5],'')+'</li>', '') + 
COALESCE('<li>'+ NULLIF([@field:System_6],'')+'</li>', '') + 
COALESCE('<li>'+ NULLIF([@field:System_7],'')+'</li>', '') + 
COALESCE('<li>'+ NULLIF([@field:System_8],'')+'</li>', '') + 
COALESCE('<li>'+ NULLIF([@field:System_9],'')+'</li>', '') + 
COALESCE('<li>'+ NULLIF([@field:System_10],'')+'</li>', '') 

+ '</ul>'

The result will be the following:

 

2) On the Report DataPage, for Search use the 'Contains' comparison type

On the Results page use 'Render value as HTML' to display the values as a list

If you need to change the bullet list style, please add the CSS code to the Header section of the Results page (disable the HTML editor before pasting the code):

<style>
ul{
   list-style-type: disc !important;
}
</style>

Result:

[Lauren]

@CoopperBackpack This is amazing and is exactly what I was looking for! Thank you so much for your clear, detailed instructions. This is perfect and will be used across multiple reports. 

[ianGPT]

Hello,

Just wanted to share the syntaxes you can use in Caspio's Calculated field/value and formula. For reference you may check https://howto.caspio.com/function-reference/