Calculated Field spanning 2 custom fields only calculates on one

[MoniqueEvans]

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")))))))))

[AdinaPi1]

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]

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]

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]

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]

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