Print Page | Close Window

Set a field to NULL

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=20106
Printed Date: 05 May 2024 at 9:55am


Topic: Set a field to NULL
Posted By: lolly54
Subject: Set a field to NULL
Date Posted: 10 Oct 2013 at 1:08am
I have a tricky question...

If the field {Product.Number} is 0, can I set it to NULL in crystal?

Anyone? Thanks!


Replies:
Posted By: DBlank
Date Posted: 10 Oct 2013 at 3:34am
Not really. There is a trick to do this with strings but I dont think it works for numbers.
However if it is for display you can conditionally suppress it, if it is for calculations you can use shared variable formula or Running Totals to exclude them from your calculations.

Posted By: lolly54
Date Posted: 10 Oct 2013 at 9:53pm
I tried suppressing it if "currentfieldvalue = 0", however, the number is still showing. In the end, I set the font colour condition to display white if it is 0. This is not the perfect way to do, at least it is "working" now.

Thanks! :)

Posted By: DBlank
Date Posted: 11 Oct 2013 at 4:11am

Is it possible it is not actually zero but displaying as zero because it is rounding down to display the integer. Others have had this issue and resoved it using a <1 condition. You could use a round(x,0) or for suppression

round(currentfieldvalue,0)=0
 
It does not make sense that your conditional color formula works but perhaps it is written just differently enough to account for something.
Also be aware that if you are summing the field it will sume the actual field value not the displayed (or hidden) 0 so it can alter your expected results.

Posted By: Traceyc
Date Posted: 15 Oct 2013 at 2:58pm
You can set a 0 number to null by creating a formula with nothing in it and saving it. Reference it as: if{Product.Number} = 0 then {@Null} else {Product.Number} (or something similar). This works perfectly when dealing with strings. If you get an error saying something like it wants a number or wants a string just use tonumber({@Null}). Also works with dates, just use date({@Null}).

Posted By: Shruthi
Date Posted: 21 Jul 2016 at 7:44pm
The other simplest way to set a 0 to NULL is, changing the font of 0 to white. This can be done by
1) Right click on the required field and select 'Format Field'
2) Select 'Font'
3) Then select 'Color'
4) Write the formula as

if {@number}=0 THEN crWhite
Note : @number is a formula field


Print Page | Close Window