Syntax for cross-object references in valueexpressions.

[ShawnMe]

Hi,

I am trying to write an IF statement that enters either 1) the data from a task's custom field (Requester) or 2) the data from the same field on the project level. I can't find the syntax anywhere here or in the documentation.

I have tried

valueexpression=IF(ISEMPTY({DE:Requester}),{DE:project:Requester},{DE:Requester})

valueexpression=IF(ISEMPTY({DE:Requester}),{DE:project}:{DE:Requester},{DE:Requester})

valueexpression=IF(ISEMPTY({DE:Requester}),{project:DE:Requester},{DE:Requester})

valueexpression=IF(ISEMPTY({DE:Requester}),{project}:{DE:Requester},{DE:Requester})

and others that I probably shouldn't even admit to.

Is there a way to make this reference work?

Thanks

[AnthonyIm]

Hi - just out of curiosity, have you tried ISBLANK? That is what I usually use.

Try this one:

valueexpression=IF(ISBLANK({DE:Requester}),{project}:{DE:Requester},{DE:Requester})

[ShawnMe]

Argh. ISEMPTY is a remnant from my FileMaker days. Thanks for catching that, Anthony.

Unfortunately, replacing ISEMPTY with ISBLANK did not make any of the listed expressions work. So something is wrong with the project:Requester syntax.

[AnthonyIm · Accepted solution]

Interesting, I did a test in my enviroment with a field called Vantage Job Number and it worked fine. My calculation is

displayname=Calculation

textmode=true

valueexpression=IF(ISBLANK({DE:Vantage Job Number}),{project}.{DE:Vantage Job Number},{DE:Vantage Job Number})

valueformat=HTML

Check your valueformat field. It should be HTML or string. Not customDataAsAString

[AnthonyIm]

Actually, just to confirm, Requester is a free text field right? It isn't a typeahead field is it?

[ShawnMe]

Ah. The issue was using a period instead of a colon between {project} and {DE:Requester}. You actually used a colon the first time, which is what I thought it should be and had used in all of my attempts. Your second post corrected it. Thank you very much, again, Anthony.

[AnthonyIm]

HAHAHAHA! That's what I get for copying and just editing. ;) Should have typed it all out. Glad I sent you my code so you caught that.

[ShawnMe]

I'll do you one better. In going through another report just now, I realized that I previously made a similar reference successfully. Why I couldn't figure it out this time is anybody's guess.