Forum Discussion
Separate Results of Calculated Column with Commas
Okay, I'm a SP List newbie and am trying to create a list of upcoming conferences for our marketing and sales staff. Included is a set of columns that shows each of our products and the percentage to which they'll be represented at each conference for budgetary purposes.
I've also created a fairly simple calculated column that takes these product-related columns and if there is any amount greater than 0, text for that product will be displayed.
=IF([PROD-1]>0,"PROD1","")&IF([PROD-2]>0,"PROD2","")&IF([PROD-3]>0,"PROD3","")&IF([PROD-4]>0,"PROD4","")&IF([PROD-5]>0,"PROD5","")
My problem is that I want these results separated by a comma if more than one product appears.
I've attempted to use JSON in the column formatting to this end:
"txtContent":"=join(@currentField, ', ')"But it doesn't do anything.
I specified the column name:
"txtContent":"=join([$PRO_x002d_TEST1], ', ')"Still nothing.
Seems like such a simple thing, and I know I must be missing something spectacularly easy. So what am I missing?
(help, please!)
HodgePodge47 Try using calculated formula like this:
=TRIM(IF([PROD-1]>0,"PROD1 ","")&IF([PROD-2]>0,"PROD2 ","")&IF([PROD-3]>0,"PROD3 ","")&IF([PROD-4]>0,"PROD4 ","")&IF([PROD-5]>0,"PROD5",""))Then apply JSON column formatting with:
{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "div", "txtContent": "=replaceAll(@currentField, ' ', ', ')" }Reference: SharePoint: Replace All Occurrences of Substring in a String using JSON Formatting
You can also use internal column name instead of @currentField in JSON. You can get the internal name of your SharePoint list columns by following this article: How to find the Internal name of columns in SharePoint Online?
Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.
2 Replies
HodgePodge47 Try using calculated formula like this:
=TRIM(IF([PROD-1]>0,"PROD1 ","")&IF([PROD-2]>0,"PROD2 ","")&IF([PROD-3]>0,"PROD3 ","")&IF([PROD-4]>0,"PROD4 ","")&IF([PROD-5]>0,"PROD5",""))Then apply JSON column formatting with:
{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "div", "txtContent": "=replaceAll(@currentField, ' ', ', ')" }Reference: SharePoint: Replace All Occurrences of Substring in a String using JSON Formatting
You can also use internal column name instead of @currentField in JSON. You can get the internal name of your SharePoint list columns by following this article: How to find the Internal name of columns in SharePoint Online?
Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.
ganeshsanap, you are a SUPERSTAR!
Thanks so much! That workaround worked beautifully. I'd looked at replaceAll previously, but couldn't figure out a way to make it work without appending a comma to single and last results. Obviously, I wasn't quite there.
