Calculated field to count number of comma separated values entered in Single line text field | Community
Skip to main content
T
truptikhairnar
May 2, 2023
Solved

Calculated field to count number of comma separated values entered in Single line text field

  • May 2, 2023
  • 3 replies
  • 1966 views

Hello, I need help in creating a calculated field which should give me the count of values entered in single line text field separated by comma.

E.G - I have created below single line field ABC = 111,234,32,456,777

So, here there are 5 terms/values entered in above field which are separated by comma.

But, I need calculated field which will show "5" number .

Please help me, how can I get this count using calculated field.

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Doug_Den_Hoed__AtAppStore

 

Hi @truptikhairnar (and @skyehansen),

 

I noodled around on this today, and provided you do not use any commas within the values of your multi-select, invite you to use the following formula on a calculated parameter (e.g. Multi Select Count) to calculate the number of selected values in a target multi-select parameter (e.g. Multi Select Parm), which will support up to 100 values (or even more) with no additional maintenance required:

 

IF(LEN({DE:Multi Select Parm})>0,1+LEN(REPLACE({DE:Multi Select Parm},",",",,"))-LEN({DE:Multi Select Parm}),0)

 

Thanks for the cool challenge, and enjoy!

 

Regards,

Doug

3 replies

KellieGardner
Community Advisor
KellieGardnerCommunity Advisor
Community Advisor
May 3, 2023

To my knowledge doing a count in Workfront is limited. It's not a custom data expression available. 

 

https://experienceleague.adobe.com/docs/workfront/using/reporting/reports/calculated-custom-data/calculated-data-expressions.html?lang=en

T
truptikhairnarAuthor
May 4, 2023

Yes, we dont have any option available for this case.

skyehansen
Community Advisor
skyehansenCommunity Advisor
May 4, 2023

hi, you could try this (I got it years ago, from one of the professional services folks) and see if it works.

 

You can see that it only counts up to 8 (it looks like anything over 8 also gets called 8), so it might not be a good solution for you.

 

Hopefully you can see why it's not a sustainable solution! As long as you are expecting a small number of commas and no more, it could be workable.

 

Since I got this several years ago, there has been a change in how calculated fields are now constructed in Workfront. I've made a good faith attempt putting in the DE's and the curly braces, but since I've never had the need to use the calculated expression, you will have to test it in your particular situation.

 

Comma Counting
This calculation counts the number of commas in a field, but stops counting after 8

 

IF(ISBLANK({DE:CommaContainingField}),"0",IF(SEARCH(",",{DE:CommaContainingField})<0,"0",IF(SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField})+1)<0,"1",IF(SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField})+1)+1)<0,"2",IF(SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField})+1)+1)+1)<0,"3",IF(SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField})+1)+1)+1)+1)<0,"4",IF(SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField})+1)+1)+1)+1)+1)<0,"5",IF(SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField})+1)+1)+1)+1)+1)+1)<0,"6",IF(SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField},SEARCH(",",{DE:CommaContainingField})+1)+1)+1)+1)+1)+1)+1)<0,"7","8")))))))))

T
truptikhairnarAuthor
May 4, 2023

Thanks for reply. But, unfortunately, it will not work for me as the values will be more than 8 and max count is upto 100.

Doug_Den_Hoed__AtAppStore
Community Advisor
Doug_Den_Hoed__AtAppStoreCommunity AdvisorAccepted solution
Community Advisor
May 5, 2023

 

Hi @truptikhairnar (and @skyehansen),

 

I noodled around on this today, and provided you do not use any commas within the values of your multi-select, invite you to use the following formula on a calculated parameter (e.g. Multi Select Count) to calculate the number of selected values in a target multi-select parameter (e.g. Multi Select Parm), which will support up to 100 values (or even more) with no additional maintenance required:

 

IF(LEN({DE:Multi Select Parm})>0,1+LEN(REPLACE({DE:Multi Select Parm},",",",,"))-LEN({DE:Multi Select Parm}),0)

 

Thanks for the cool challenge, and enjoy!

 

Regards,

Doug

    T
    truptikhairnarAuthor
    June 9, 2023

    Thanks Doug.

    Terms & ConditionsAccessibility statement

    Loading footer...