asked on

Null values and conditional showing

Hi everybody. I assume it's easy... I hope I'm not wrong :)

I would like to have function that will act like this:
IF IsNULL({@Field}) Then
formula = " "
else
formula = {@Field}
End IF

Something like scaled down version of COALESCE function, with just one parameter. If the field is null, I want string that has something (not empty string, so my formating still show), or field itself.
Reason is that Crystal doesn't like NULLs, so my fields are not shown at all. In most cases, that's exactly what I want, so option Convert Nulls to Default is not checked on reports. But sometimes, I want to show them, underline empty ones, color them etc. Only sometims. So, instead of changing that option, can anybody tell me how to make this visible on all reports? I'm using CR 9 Advanced.
I tried to make it custom function, but got an error that ISNull() can not be used in custom functions...

Any ideas?
Thanks.
Lady Linet

Mike McCracken

You are very close. The @field must be a text type

IF IsNULL({@Field}) Then
" "
else
{@Field}
End IF

mlmcc

ladylinet

ASKER

I tried it in custom function, and error I'm getting is:
"This function can not be used in custom functions".
with cursor pointing to ISNULL.

Reason I want custom functin is that I have to do check for lot of fields, and I was hoping to at least move logic into function, instead of writing if...else... end if all the time...

Thanks.
Linet

Mike McCracken

Try this

IF IsNULL(@Field) Then
' '
else
@Field
End IF

mlmcc

Mike McCracken

When you say function are you referring to formulas or is funstion a new feature in CR9?

mlmcc

ladylinet

ASKER

Yes, I think it's new in CR9. They are called custom functions - way to share your common functions, encapsulate your logic instead of repeating etc.
I've already have that if isnull()...then...else but I'm tired of it, and tought to encapsulated just that part into custom function...

Lady Linet

Mike McCracken

How do you build a custom function? If what you are showing is the entire function then whatyou want can't be done.

I suspect there is more to the function than that.

mlmcc

ladylinet

ASKER

No, that's all - if value is null, I would like to replace it with whatever default value I pass as second parameter. Error I'm getting is with IsNULL function - looks like ISNULL is not applicable, even if it's not on list of not applicables functions.
Why do you think this can not be done?

Thanks.
Lady Linet

ladylinet

ASKER

Looks like another unanswerable question...
Thanks to mlmmc for trying...

Lady Linet

Mike McCracken

I think I have been very stupid.

Try

IF IsNULL(?Field) Then
' '
else
{?Field}
End IF

@ is for formulas ? is for parameters

mlmcc

ladylinet

ASKER

I'm getting an error for ISNULL function.

Lady Linet

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ladylinet

ASKER

Thanks for trying, looks like it's impossible...

Mike McCracken

If you tried what I had given you there was a problem

Should have been

IF IsNULL({?Field}) Then
' '
else
{?Field}

or

IF IsNULL({?Field}) Then
" "
else
{?Field}

Use Crystal Syntax. I just built that as a formula. Not sure how to do it as a function since that is a new feature in CR9

mlmcc