Help with Formula for If isnull.

[deborahyr]

Can someone tell me what's wrong with this formula?
if isnull ({INDIVID.AET_CORPTITLE}) then {AET_INDSPT.SPT} else
if isnull ({AET_INDSPT.SPT}) then {@titlenocode} else {INDIVID.AET_CORPTITLE}

I want to show ee's corporate title if they have one, if not show me their succession title if they have one, else show their job title.

I'm using Crystal XI. The corporate title and job title are on the same table and is linked by id to the succession table that has the succession title. Thanks.

 

[staciann]

Hi. Try this:

if not isnull({INDIVID.AET_CORPTITLE}) or {INDIVID.AET_CORPTITLE}<>""
then {INDIVID.AET_CORPTITLE}
else if isnull({INDIVID.AET_CORPTITLE}) or {INDIVID.AET_CORPTITLE}=""
then {AET_INDSPT.SPT}
else if isnull({INDIVID.AET_CORPTITLE}) and isnull ({AET_INDSPT.SPT})
then {@titlenocode}


Hope that helps,
Staci

 

[deborahyr]

Thanks for the quick response! Unfortunately, it still does not work. I went down a similar path as I was awaiting a response to this post. I can't figure it out...

 

[jdemmi]

try this

if isnull ({INDIVID.AET_CORPTITLE}) then
if isnull({AET_INDSPT.SPT}) then {@titlenocode}
else {AET_INDSPT.SPT}
else {INDIVID.AET_CORPTITLE}

-- Jason
"It's Just Ones and Zeros

 

[Madawc]

ardl's formula is nearly there. What it needs is AND rather than or, you want the value if it is not null and not spaces.

The rule with nulls is that a formula stops when it hits one, no good testing for null later on. One of the features you have to get used to.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[deborahyr]

Thank you for your suggestions - this is what worked perfectly.

if not isnull({INDIVID.AET_CORPTITLE}) and {INDIVID.AET_CORPTITLE}<>""
then {INDIVID.AET_CORPTITLE}
else if not isnull({AET_INDSPT.SPT}) and {AET_INDSPT.SPT}<>""
then {AET_INDSPT.SPT}
else if isnull({INDIVID.AET_CORPTITLE}) and isnull ({AET_INDSPT.SPT})
then {@titlenocode}