Calculated field CASE statement examples

[PoppyJennings]

Hi, does anyone have any examples of CASE() statements used in calculated fields on a custom form?

I am trying to use the following and while it doesn't say there are any errors, it also doesn't pull back any data!

I have 2 custom fields, one with a nested If statement and the other I am trying to set up with a Case statement.

DE:MA - Choose the region Calc2

IF({DE:MA - Choose the region}= "en-gb", "GB/",

IF({DE:MA - Choose the region}= "en-ie", "IE/",

IF({DE:MA - Choose the region}= "en-us", "US/",

IF({DE:MA - Choose the region}= "en-ca", "CA/",

IF({DE:MA - Choose the region}= "fr-ca", "FR-CA/",

IF({DE:MA - Choose the region}= "fr-fr", "FR/",

IF({DE:MA - Choose the region}= "fr-ma", "FR-MA/",

IF({DE:MA - Choose the region}= "fr-be", "FR-BE/",

IF({DE:MA - Choose the region}= "nl-be", "NL-BE/",

IF({DE:MA - Choose the region}= "de-de", "DE/"))))))))))

 - This returns FR-CA/ OR CA/ as expected.

DE:MA - Choose the region CASE

CASE(

{DE:MA - Choose the region} = "en-gb", "GB/",

{DE:MA - Choose the region} = "en-ie", "IE/",

{DE:MA - Choose the region} = "en-us", "US/",

{DE:MA - Choose the region} = "en-ca", "CA/",

{DE:MA - Choose the region} = "fr-ca", "FR-CA/",

{DE:MA - Choose the region} = "fr-fr", "FR/",

{DE:MA - Choose the region} = "fr-ma", "FR-MA/",

{DE:MA - Choose the region} = "fr-be", "FR-BE/",

{DE:MA - Choose the region} = "nl-be", "NL-BE/",

{DE:MA - Choose the region} = "de-de", "DE"

)

 - Does not return anything and shows N/A

Any help on what is wrong with my expression would be apprciated. Bonus points if you can help me wrte it without repeating {DE:MA - Choose the region}

all the time and just having this as the one input for it all!

Thanks

[lgaertner]

Hello PoppyJennings,

the CASE() function in Workfront works different to the IF() function.

Just have a look here.

It is used with other expressions to choose a value from a list, based on an index number.

Example:

CASE(DAYOFWEEK({entryDate}),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")

Works best with other expressions that return a number, such as DAYOFWEEK, DAYOFMONTH, and MONTH.

---

Regards

Lars

[PoppyJennings]

Thanks Lars,

I realise the IF function is different from the CASE function. 

I have a version using the IF function that is working but as you can see it means I am using a lot of nested If statements which is not the cleanest way of writing it out and managing it. Instead, I want to write it as a Case function/statement as this would be the most appropriate and cleanest way of doing this.

What I have written above for the case statement in the calculated field box doesn't show any errors (it is accepted as correctly written by Workfront) and yet it does not return any data.

As you can see, the input for the case statement in my case will not be a number.

Instead it will be a text entry "en-gb"

In this case, the returned value should be "GB/"

In the Adobe docs it only give the example for when the input value is a number but this isn't my case. I assume there are a lot of other people that also want to use a case function with inputs other than a number?

Thanks

[Doug_Den_Hoed__AtAppStore]

Hi @PoppyJennings,

Since CASE will not work in this...case...(noting that that this documentation confirms it is intended to resolve against an index number)...

To avoid the nested IFs, I invite you to consider this alternative, which works with text and although longer, might be easier to maintain:

CONCAT(

IF{DE:MA - Choose the region} = "en-gb", "GB/","")

,IF({DE:MA - Choose the region} = "en-ie", "IE/","")

,IF({DE:MA - Choose the region} = "en-us", "US/","")

,IF({DE:MA - Choose the region} = "en-ca", "CA/","")

,IF({DE:MA - Choose the region} = "fr-ca", "FR-CA/","")

,IF({DE:MA - Choose the region} = "fr-fr", "FR/","")

,IF({DE:MA - Choose the region} = "fr-ma", "FR-MA/","")

,IF({DE:MA - Choose the region} = "fr-be", "FR-BE/","")

,IF({DE:MA - Choose the region} = "nl-be", "NL-BE/","")

,IF({DE:MA - Choose the region} = "de-de", "DE","")

)

Regards,

Doug

[PoppyJennings]

Hi Doug,

Thanks for this. Are you saying Case functions in Workfront Calculated fields only accept numbers as an input?
Is that why you are saying mine won't work?

Thanks

[Doug_Den_Hoed__AtAppStore]

I am, @PoppyJennings, gently; and trying to soften the news by offering the CONCAT alternative.

Regards,

Doug 

[Lawson02]

When I read the documentation for CASE() I was so disappointed that it was numerical index based. @Doug_Den_Hoed__AtAppStore I very much like your CONCAT() method, I will be stealing this and most likely in the future using it with your status history log :). Thanks!