Reporting Text help for multiple IF statements in a column.

[KristinaWa1]

I am trying to create a custom calculation in a column on a report that will pull in specific custom form fields based on the value of another custom form field.

To better explain, we have data flowing into one custom form through an integration that is housing deliverable data for everything that is related to the estimate. We open up one project per deliverable so need to be able to only show the information relevant that one deliverable.

Current use case: We have information flowing down for up to four emails, but I want to creative a custom dashboard that can be part of users layouts on the project view that will only pull in the data relative to the email this project is for. So I am trying to get a text mode that will say IF custom form field Group equal Email 1 show Email 1 Versions, IF custom form field Group equal Email 2 show Email 2 Versions, IF custom form field Group equal Email 3 show Email 3 Versions, IF custom form field Group equal Email 4 show Email 4 Versions, IF custom form field Group equal Email 5 show Email 5 Versions. I have about 20 custom fields that will change out for Email 1 Versions, but I believe the text mode would stay the same within each column.

Does anyone know if this possible?

[Heather_Kulbacki]

Hi Kristina, It is possible. What I generally do is build my first IF statement and make sure that's working, then continue to build my additional statements, one at a time, and nest them it - this helps prevent me from missing a paren somewhere - that seems to be my biggest downfall when I try to build the whole thing at once.

So I'd do: IF(Group=Email 1,Email 1 Versions)

so basically if Group = Email 1, then give me Email 1 Versions, if it doesn't equal Email 1 give me nothing. If that works, then I'd build and add my next IF statement

IF(Group=Email 2,Email 2 Versions)

so again if Group = Email 2, then give me Email 2 Versions, if it doesn't equal Email 2 give me nothing, then I add that into the first IF:

IF(Group=Email 1,Email 1 Versions,IF(Group=Email 2,Email 2 Versions))

so IF Group = Email 1, then give me Email 1 Versions, if it doesn't equal Email 1 take a look at this next IF statement and just keep building from there

[AnthonyIm · Accepted solution]

Hi - like Heather said, try it with just one first to make sure that works. Then what I do is list out all the IF statements as if they were single (with a FALSE or TRUE for the space I don't want) and then replace the False or True to build the huge statement.

For Example, I'd do this first...

IF({DE:Group}="Email 1",{DE:Email 1 Versions},FALSE)

IF({DE:Group}="Email 2",{DE:Email 2 Versions},FALSE)

IF({DE:Group}="Email 3",{DE:Email 3 Versions},FALSE)

etc. etc.

And then starting from the bottom, replace the False above with that statement. You end up with

IF({DE:Group}="Email 1",{DE:Email 1 Versions},IF({DE:Group}="Email 2",{DE:Email 2 Versions},IF({DE:Group}="Email 3",{DE:Email 3 Versions},FALSE)))

And when you are all done, change the last False (or True) to "" (no space in between the quotation marks)

IF({DE:Group}="Email 1",{DE:Email 1 Versions},IF({DE:Group}="Email 2",{DE:Email 2 Versions},IF({DE:Group}="Email 3",{DE:Email 3 Versions},"")))

Hope that is helpful.