If field doesn't exist, return value from another field

[ZacharyDe1]

Hi All,

We have a report that returns the users name for a specific role type in a project. The text looks like this:

displayname=Copywriter
listdelimiter=<br>
listmethod=nested(projectUsers).lists
type=iterate
valueexpression=IF({user}.{role}.{name}="Copywriter",{user}.{name})
valueformat=HTML

This works well if that role type exists in the project. If that role type does not exist in the project, the column is blank. I am trying to solve for this use case. If that role type doesn't exist, pull in the username from another specified role type, "Copy Leadership".

I've tried using nested IFs, ORs, and EXISTS but can't seem to figure it out. I am newer to this side of WF, any tips help!

[skyehansen]

https://experienceleague.adobe.com/en/docs/workfront/using/reporting/reports/calculated-custom-data/...

have you tried using IFIN?

[ZacharyDe1]

Yes, this works when only one of the two roles is on a project, however since most projects have both roles, using IFIN returns both values for those projects, which is not what we want. We only want one or the other, not both.

[skyehansen]

I think posting what you've been using as a nested IF would help us help you troubleshoot. In theory the syntax for an IF statement looks like this:

IF(condition, trueExpression, falseExpression)

So extrapolating here, the nested IF should look like this:

IF(condition, trueExpression, IF(condition2, trueExpression2, falseExpression))

Your IF statement looked like this:

IF(condition, trueExpression)

It's not wrong -- if you were going to keep going like this, your nested IF should look like this:

IF(condition, trueExpression, IF(condition2, trueExpression2))

Post what you've been trying and let's try and figure out what went wrong.

[ZacharyDe1]

When using this method, it returns both values when present, not just the first value once the condition is met as true

displayname=Copywriter
listdelimiter=<br>
listmethod=nested(projectUsers).lists
type=iterate
valueexpression=IF({user}.{role}.{name}="Copywriter",{user}.{name}, IF({user}.{role}.{name}="Copy Leadership",{user}.{name}))
valueformat=HTML

[skyehansen · Accepted solution]

ahhhh, I see what you're trying to get at, now. Yes, this is probably the best you're going to get, unfortunately.

If you were open to two reports, you could make one report where no copywriters exist (and just make a column for copy lead names) and another report if copywriters exist (and just list their names). Both reports would use exists filters to bring in this information. There's just no way to use an exists filter in your report view.

[ZacharyDe1]

Thank you. That's what I was thinking initially, using exists. Combining IF and EXISTS to do something like IF copywriter exists return their name, if not and copy leadership exists return their name. That way it would all be in one report and only one name would be returned each time.