Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 605
  • Last Modified:

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
0
ladylinet
Asked:
ladylinet
  • 7
  • 6
1 Solution
 
mlmccCommented:
You are very close.  The @field must be a text type

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

mlmcc
0
 
ladylinetAuthor Commented:
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
0
 
mlmccCommented:
Try this

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


mlmcc
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

mlmcc
0
 
ladylinetAuthor Commented:
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
0
 
mlmccCommented:
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
0
 
ladylinetAuthor Commented:
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
0
 
ladylinetAuthor Commented:
Looks like another unanswerable question...
Thanks to mlmmc for trying...

Lady Linet
0
 
mlmccCommented:
I think I have been very stupid.

Try

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


@ is for formulas  ? is for parameters

mlmcc
0
 
ladylinetAuthor Commented:
I'm getting an error for ISNULL function.

Lady Linet
0
 
mlmccCommented:
Unless IsNull doesn't work with parameters I don't know.

mlmcc
0
 
ladylinetAuthor Commented:
Thanks for trying, looks like it's impossible...
0
 
mlmccCommented:
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
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now