Use of isnull in selection criteria vs. section formulas 1

[kpeeler]

I am using Crystal 8.5. I can make use of isnull logic successfully within the selection criteria but use of isnull reference to the same field within the borders & colors/background does not return the expected result.

In selection criteria:
isnull(Table.Field)
- This returns only records missing this field

In Borders&Colors/Background formula:
if isnull(Table.Field)
then cryellow else defaultattribute
- Field fails to turn yellow for isnull

I can write a separate formula that converts null values for the field to blanks and then successfully color the background based on blanks but this will require me to write 25 separate formulas for each field I am trying to highlight error conditions for, a very painful work around!

Why does Crystal recognize the null properties via selection criteria but not in the background formula? Am I missing a fundamental concept here? I have not altered any standard report settings.

 

[Madawc]

Crystal assumes that anything with Null is not wanted. A work-round is to create a formula field:

if isnull(Table.Field) then " "
else Table.Field

This formula field can then be used in place of Table.Field and coloured as you wish.

Another thing to watch with nulls is that you have to test for them before testing anything else. Saying
if field = 1 then "One"
else if isnull(field) then "Null"
else "Other"
will fail to show anything for Null, since it does the first test and stops upon finding a null

Madawc Williams
East Anglia, Great Britain

 

[hilfy]

I *think* this may be occuring because the null field is not actually "on" the report. Instead of doing separate formulas for each field, you could go into the File|Options menu. Go to the "Reporting" tab and select "Convert NULL Field Value to Default". This will give you the default "blank" value for null fields. This value will be an empty string for string fields, a 0 for number fields, etc.

-D

 

[kpeeler]

Reply to Madawc: Thanks for the reply. Your suggested formula is the same as my current work around formula. I'm trying to avoid this step.
When you say "Crystal assumes that anything with Null is not wanted" this does not make sense to me in the sense that my report selection criteria is entirely based on selecting for records with null values and is accurately returning those records. I'm trying to understand if Crystal truly does relate to isnull differently depending upon where it is used (I always use it in the first line of formulas as per your other suggestion).

Reply to Hify: Tried this and the report now selects no records at all.

 

[HowardHammerman]

I have also noticed some odd behavior regarding ISNULL

I often use the follwing formula with then field is of type character:

if len(trim({table.field})) then cryellow else crblack

Howard Hammerman,
Crystal Training and Crystal Material
On-site and public classes
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[lbass]

You will have to take some extra step--think of the null field as not being there for that record. Another approach is to add a text box to the field, since this can be formatted using isnull({table.field}).

-LB

 

[Ngolem]

Mawdac's approach is the only workable one...why because if the field is null then nothing will be revealed...it is like suppressing the field

so if you want to display a "Null" value you must convert that null to a single blank space using his formula and I will title it

//@DisplayNULL

WhilePrintingRecords;
if isnull(Table.Field) then " "
else Table.Field;

Now in the In Borders&Colors/Background formula for that field:

whilePrintingRecords;
if isnull(Table.Field) then
cryellow
else
crWhite; //I like defining the color of the default
// who knows...yellow could be the default







Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[kpeeler]

The easiest solution is that from Ibass. This way I'm not writing 2 formulas (i.e. one to convert the null values to blank and one to interpert those within the background formula). Thanks for everyones help.
Turns out the suggestion was avaiable on the Crystal Decisions web site as well:

Applies to:

All versions
Formatting fields with borders and background color
Blank or Null values in fields


Synopsis

How can you keep fields with blank or Null values to retain their borders and background color? Blank or Null values lose their border and background formatting.

Solution

1. Create a text object and place it, instead of the field, on the report .

2. Insert the field into the text object.
The mouse pointer will change to a paper icon with a '+' on it when you have successfully inserted the field.

3. Format the text object with borders and background color.

The text object will keep this formatting, even if the field is blank or Null.

An alternative solution can be found in Knowledge Base article C1479040.

 

[FVTrainer]

Jim-

Just curious: Why "whileprintingrecords"? You're not using a variable. Wouldn't the @DisplayNull formula evaluate the same if it takes place in the first pass (WhileReadingRecords)? The reason I ask is that by making @DisplayNull a 2nd pass formula, you eliminate the use of any of Crystal's summary calculations on the result.

 

[Ngolem]

FVTrainer - well there is no indication that this formula will be used in any type of summary calculation...my personal rule for formulas is that I would like to decide when and when not Crystal will evaluate a formula...rather than leave it up to crystal's imagination.

as a result you will consistantly see me use "WhilePrintingRecords" in "Any" formulas that are not used for grouping, sorting or summary operations or formulas that use "evaluateAfter()". It is just a standard I employ.

On re-reading the initial post though I question the need for this formula at all, frankly.

The user is "ONLY" grabbing records for a particular field that is NULL???? the Non-null fields are not selected.

So what is the need for a field of this record with conditional colors on the background?? {shrug}

Just put an empty text box with a yellow background...it would have the same effect.

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[kpeeler]

In response to Ngolem:
"The user is "ONLY" grabbing records for a particular field that is NULL???? the Non-null fields are not selected.

So what is the need for a field of this record with conditional colors on the background?? {shrug}"

I am selecting from records with 30 some odd fields of data - the error reports pulls any record for which one or more of these fields is missing (i.e. null). I then want to highlight only those missing fields for a given record - not all records are missing the same data and thus I am using a formula to conditional color those fields missing data. - thanks

 

[Ngolem]

ahhhh...you didn't say that in your post...or it wasn't clearly stated anyway...

so this color change applies to all selected fields then

to illustrate you may have 3 fields/record returned... with at least one of them null

then each of these fields must be a formula

//@Field1

WhilePrintingRecords;
if not isnull({Table.field1}) then
{Table.field1}
else
" "; //single space

If {Table.field1} is not a string then convert it to one for this formula to work

then in each conditional Background color use

whilePrintingRecords;
if Not isnull({Table.field1}) then
CRWhite
else
CRYellow;

do this for each field in the record...
AND yes...you must create a formula for each of 25 fields in your record....it is a pain but copying the formula and cutting/pasting making appropriate changes makes life easier....

why do you need to do this? because if a field is NULL it is completely suppressed so background has no effect.

at least it is clear now what you are trying to do.

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[Madawc]

Regarding the use of nulls:

When I started, I got confused that tests that I saw as identical were producing different results. This was because a formula field will return blank if a null is encountered, unless some isnull test is done first.

I think you'd find the same in record selection: the test for nulls must come first. I could be wrong on this: experiment and see what you get.

Madawc Williams
East Anglia, Great Britain

 

[kpeeler]

I appreciate all of the input. In summary there are 2 ways to handle this, each requiring 2 steps:

Method 1
a. Create a formula that converts the null values to blanks and use in place of the actual field:

Ex. Formula name = Conversion Formula 1

if isnull({Table.field}) then " "
else {Table.Field}


b. Create a formula to select background cover based on blank rather than null:

Ex. Within the background formula
if {@Conversion Formula 1} = " "
then cryellow
else defaultattribute



Method 2
a. Insert a text box and add the intended field that has null values.
b. Within the background formula of the text box use the following:

if isnull({Table.field})
then cryellow
else defaultattribute


Method 2 is optimal in that it does not require the creation of separate formulas to convert each original fields null values to blanks. I highly recommend the use of Option 2.