Crystal 8.5 - problem using Null in formula 1

[christinetjx]

I have only run into this problem since upgrading to 8.5.
I run a report that displays closed hotline tickets sorted by assigned groups, i.e.:

Desktop 18
Remote Support 23
Infrastructure 14

My problem:
Sometimes tickets are closed without being assigned to a particular group. In cases such as this, the assigned group field displays as a blank:

5
Desktop 18
Remote Support 23

In earlier versions of crystal, i used a formula so that the 'Assigned' field would display as 'Unassigned' if the tkt was closed in that fashion. My formula:

if GroupName ({ocmlm1.assigned_to})="" then "Not Assigned"

This formula worked fine in Crystal 7. However, it does not work in 8.5 The field continues to display as a blank with the number of closed calls posted next to it:

7
Desktop Support 18

I would like this blank field to display as 'Not Assigned'.
Any suggestions are greatly appreciated.

 

[Madawc]

Having come to Crystal from mainframe languages, I got a 'cultural shock' when encountering null. It means 'no data': Mainframe languages mostly treat this as the same as zero.
It is actually a finer shade of meaning, the difference between 'Yes, we have no bananas' and 'I don't know how many bananas we have, it could be some, it could be zero'. In Crystal, the entry is 0 or null and can be tested for.
Note that Crystal assumes that anything with a null means that the field should not display. Always begin with something like

if isnull({your.amount}) then 0 
		         else {your.amount}

Or else

if isnull({your.amount})  
then "no value found" 
else ToText({your.amount})

The 'ToText' allows you to mix numbers and text, and also has interesting format options.

Madawc Williams (East Anglia, UK)

 

[christinetjx]

Madawc, thank you for the quick reply. My background is that of an Admin and formula's do not come easy to me. I'm still having a problem. what is wrong w/these entries?

if GroupName ({ocmlm1.assigned_to})then 0 else ToText (GroupName ({ocmlm1.assigned_to}))

or this one...

if GroupName ({ocmlm1.assigned_to})then 0 else ToText ("Not Assigned")

 

[ianoctdec]

Dear christjx,
Your formula should look something like this:

if is null({ocmlm1.assigned_to}) or {ocmlm1.assigned_to} = '' then
'Not assigned'
else
({ocmlm1.assigned_to})

You should group on this formula. If you have already grouped on the field ({ocmlm1.assigned_to}), then put the formula in the group header as the name of the group.

Hope this helps,
Dana

 

[tomk01]

there is a space in the formula. there is no space in "isnull

 

[christinetjx]

THAT DID IT!!!! I have been working on this for sooo long, thank you thank you thank you both dana and tomk01!!!! can i ask you to explain this piece:

if isnull({ocmlm1.assigned_to}) or {ocmlm1.assigned_to} = '' then
'Not assigned'

what is this actually saying? why did ocmlm1.assigned_to have to be written twice in that one line?

Pls know this if for my own information, i try my best to understand this stuff so i don't have to bother anyone. If you don't have time to answer, i understand. Thank you so much anyways, you two have made my day....

 

[Madawc]

Null is no data, and different from a space-filled field. You needed to test for both.

Madawc Williams (East Anglia, UK)

 

[ianoctdec]

Dear christjx,
Let me explain more in detail. If a field in the table has no value that means it is either null or space-filled. So the first line of the formula is testing for both these situations. More correct would be:

if isnull({ocmlm1.assigned_to}) or
trim({ocmlm1.assigned_to}) = '' then
'Not assigned'

The function trim eliminates spaces from its string argument so the comparison with the empty string '' makes more sense.
Glad to being of help!

Dana

 

[christinetjx]

thank you again for your assistance. GREATLY appreciated.