Calculated Field spanning 2 custom fields only calculates on one | Community
Skip to main content
MoniqueEvans
MoniqueEvans
New Participant
Jul 6, 2020 6:41 PM UTC
Question

Calculated Field spanning 2 custom fields only calculates on one

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.

    5 replies

    MoniqueEvans
    MoniqueEvansAuthor
    New Participant
    Jul 7, 2020 12:33 PM UTC

    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.

    Translate
    Doug_Den_Hoed__AtAppStore
    Doug_Den_Hoed__AtAppStore
    New Participant
    Jul 7, 2020 1:10 PM UTC

    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

    Translate
    Doug_Den_Hoed__AtAppStore
    Doug_Den_Hoed__AtAppStore
    New Participant
    Jul 7, 2020 6:32 AM UTC

    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 + "]"

    )

    )

    )

    )

    )

    )

    )

    )

    )

    Translate
    AdinaPi1
    Level 9
    Jul 7, 2020 2:21 AM UTC

    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.

    Translate
    MoniqueEvans
    MoniqueEvansAuthor
    New Participant
    Jul 7, 2020 11:37 AM UTC

    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

    Translate
    Terms & ConditionsAccessibility statement

    Loading footer...