Calculated field CASE statement examples | Community
Skip to main content
PoppyJennings
PoppyJennings
New Participant
August 21, 2024
Question

Calculated field CASE statement examples

  • August 21, 2024
  • 1 reply
  • 1511 views

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

    1 reply

    lgaertner
    lgaertner
    New Participant
    August 21, 2024

    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
    PoppyJenningsAuthor
    New Participant
    August 22, 2024

    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
    Doug_Den_Hoed__AtAppStore
    New Participant
    August 22, 2024

     

    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

      Terms & ConditionsAccessibility statement

      Loading footer...