Calculated Field spanning 2 custom fields only calculates on one | Community
Skip to main content
MoniqueEvans
Community Advisor and Adobe Champion
MoniqueEvansCommunity Advisor and Adobe Champion
Community Advisor and Adobe Champion
July 6, 2020
Question

Calculated Field spanning 2 custom fields only calculates on one

  • July 6, 2020
  • 3 replies
  • 629 views

I have two very similar custom fields that I am trying to report on using one calculated field, so I can report to leadership with all projects represented. I essentially need all of the options to funnel into two categories. The calculated field I created works perfectly for the first field (Packaging Project Type) but gives N/A for the second field (Request Type). Has anyone ever ran into that issue? I checked my forms and projects and there is never a time where both fields appear on the same project.

Field 1: Packaging Project Type - Options: Corrections / DMOD / Promo / New

Field 2: Request Type - Options: Update / DMOD / Non-NPD / Promo / NPD

Calculated Field: IF(Packaging Project Type="CORRECTIONS","Non-New",IF(Packaging Project Type="DMOD","Non-New",IF(Packaging Project Type="PROMO","Non-New",IF(Packaging Project Type="NEW","New",IF(Request Type="Update","Non-New",IF(Request Type="DMOD","Non-New",IF(Request Type="Promo”,”Non-New",IF(Request Type="NPD","New",IF(Request Type="Non-NPD","Non-New")))))))))

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

    3 replies

    A
    AdinaPi1
    Level 9
    July 7, 2020

    Do you have that calculated field on both forms?

    Also, I thought case matters (you have NEW instead of New), but you said it worked for the first field.

    MoniqueEvans
    Community Advisor and Adobe Champion
    MoniqueEvansCommunity Advisor and Adobe ChampionAuthor
    Community Advisor and Adobe Champion
    July 7, 2020

    Yes the field is on both forms.

    Good eye! Case does matter I was typing quickly when I wrote the options, the case in the calc is the correct version with NEW and PROMO

    Doug_Den_Hoed_AtAppStore
    Community Advisor
    Doug_Den_Hoed_AtAppStoreCommunity Advisor
    Community Advisor
    July 7, 2020

    Hi Monique,

    In such cases, I find it helpful to paste the formula into a text editor and indent the IF statements to track the logic. By doing so in this case, I noticed there is no final ELSE statement, so I added it in bold, and invite you to give it a try.

    Regards,

    Doug

    ---------------------------

    IF(Packaging Project Type="CORRECTIONS"

    ,"Non-New"

    ,IF(Packaging Project Type="DMOD"

    ,"Non-New"

    ,IF(Packaging Project Type="PROMO"

    ,"Non-New"

    ,IF(Packaging Project Type="NEW"

    ,"New"

    ,IF(Request Type="Update"

    ,"Non-New"

    ,IF(Request Type="DMOD"

    ,"Non-New"

    ,IF(Request Type="Promo”

    ,”Non-New"

    ,IF(Request Type="NPD"

    ,"New"

    ,IF(Request Type="Non-NPD"

    ,"Non-New"

    ,"UNEXPECTED VALUE: Packaging Project Type = [" + Packaging Project Type + "] Request Type = [" + Request Type + "]"

    )

    )

    )

    )

    )

    )

    )

    )

    )

    MoniqueEvans
    Community Advisor and Adobe Champion
    MoniqueEvansCommunity Advisor and Adobe ChampionAuthor
    Community Advisor and Adobe Champion
    July 7, 2020

    Thanks Doug! Unfortunately this isn't working either, still getting N/A for all the projects using Request Type.

    I never learned about ELSE statements properly but looking at the number of single and double quotes in your statement it feels unbalanced but I'm not 100% sure.

    Doug_Den_Hoed_AtAppStore
    Community Advisor
    Doug_Den_Hoed_AtAppStoreCommunity Advisor
    Community Advisor
    July 7, 2020

    Ah; right you are, Monique: I had two single quotes in a row, but have converted my suggestion to use square brackets instead, to help see the values that are dropping into that final else.

    If you can send a screenshot of a list report that shows Package Project Type as a column, Request Type as a column, this latest bracket version calc as a column, and enough rows of data to show the possible combinations, perhaps a pattern will present itself.

    Regards,

    Doug

    Terms & ConditionsAccessibility statement

    Loading footer...