")" is not valid in this expression error when trying to use a custom expression in a calculated field

[Rscofidio]

Hi,

I am trying to create a calculated field that will show allow me to create a report off of a multi value field. I found this previous post that seems to address what I am trying to do:

https://experienceleaguecommunities.adobe.com/t5/workfront-questions/calculated-field-on-selection-o...

BUT when I try to add more that one IF statement I get the following error: ")" is not valid in this expression

Here is the code I am getting the error on:

IF({DE:Subject Area}="67813c5d0003fc7c694bb98d39de5ab5”,”Health: Caregiving")

IF({DE:Subject Area}="67813ba70003985be52dffadf69487dd”,”Health: Brain Health")

The red bracket at the end of the first statement is where I am seeing the error.

I did post to the original question referenced above but it is from a couple of years ago so I decided to post a new question as well.

Any thoughts on what I am doing wrong?

Thanks,

Robin

[Sven-iX]

You have 2 IF statements that are unconnected. 

Instead you need to nest them

IF({DE:Subject Area}="67813c5d0003fc7c694bb98d39de5ab5”,”Health: Caregiving", IF({DE:Subject Area}="67813ba70003985be52dffadf69487dd”,”Health: Brain Health"))

[Rscofidio]

Thanks for the reply!

I did try that but then I get an error on the first IF that says: The "IF" function must contain either 2 or 3 values

[Eric_D_Miller]

Looks like you may be missing your final fallback, try this:

IF(
  {DE:Subject Area} = "67813c5d0003fc7c694bb98d39de5ab5",
  "Health: Caregiving",
  IF(
    {DE:Subject Area} = "67813ba70003985be52dffadf69487dd",
    "Health: Brain Health",
    ""
  )
)

[Rscofidio]

Thanks for the reply Eric!

So that allowed for the expression to no longer have error (yay!) but the Subject area field is multi-value and this calculated field works if there is only one value chosen but if there are two values chosen in subject area I get N/A as a result.

Is there a way to show all the selected values?

[Eric_D_Miller · Accepted solution]

 Perhaps a CONCAT would work here?

CONCAT(
IF(
CONTAINS("67813c5d0003fc7c694bb98d39de5ab5",{DE:Subject Area}),"Health: Caregiving | ",""),
IF(
CONTAINS("67813ba70003985be52dffadf69487dd",{DE:Subject Area}),"Health: Brain Health | ","")
)

More here:
https://experienceleaguecommunities.adobe.com/t5/workfront-ideas/fix-order-of-items-in-selected-in-m...

[Rscofidio]

Thanks again! This looks like it is going to work. I justno need to add 30ish more subject areas.... I will confirm here tomorrow!

[Rscofidio]

Hi Eric,

This is working for me. Thanks so much for your help!!

Robin

[skyehansen]

everyone is giving you great advice and I won't add to it, but I just want to add that looking at your statements above and Sven's below, I noticed that a few of your quote marks are formatted (or what we call "curly quotes" rather than "straight quotes"). You should know that even this can sometimes generate an error, so one good thing to check for while troubleshooting is/are these types of formatting issues.