Problem with Formula Field when the Underlying Dataset is Empty

[lagyossarian22]

I have a formula field in a report:

If {Nec_Gnav_ReportViewer_Data_AgentAlertsReport_Agen tAlertData.LevelOne} = true then Chr(254) else Chr(168)

It works fine unless the dataset the report template binds to is empty or null. Then it gives me an error saying it expects a string. So, I tried to trap for a null field like this:

If (Not IsNull({Nec_Gnav_ReportViewer_Data_AgentAlertsRepo rt_AgentAlertData.LevelOne})) Then
If {Nec_Gnav_ReportViewer_Data_AgentAlertsReport_Agen tAlertData.LevelOne} = true then Chr(254) else Chr(168)
Else Chr(168)

However, that did not work either -- probably because of the no data again.

I am at a loss here how to handle this. Any suggestions/guidance/help is much appreciated. Thanks in advance.

 

[MCuthill]

lagyossarian22,

I think it may be the location of your parathesis with the Not() clause. Please try the following:

If Not[red]([/red]IsNull({Nec_Gnav_ReportViewer_Data_AgentAlertsRepo rt_AgentAlertData.LevelOne})) Then
(
   If {Nec_Gnav_ReportViewer_Data_AgentAlertsReport_Agen tAlertData.LevelOne} = true then 
   Chr(254) else
   Chr(168)
) Else Chr(168)

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[lagyossarian22]

Mike,

Thanks for the suggestion. However, that did not fix my problem.

 

[IanWaterman]

You have to be more explicit with nulls

try

If IsNull({Nec_Gnav_ReportViewer_Data_AgentAlertsRepo rt_AgentAlertData.LevelOne}) Then Chr(168)
else
If {Nec_Gnav_ReportViewer_Data_AgentAlertsReport_Agen tAlertData.LevelOne} = true then Chr(254) else Chr(168)

Ian

 

[lagyossarian22]

Ian,

Thanks for the suggestion. However, that didn't fix the problem. I'm getting the following error:

A String is Required Here.
Details: errorKind
Error in file: <FILENAME>
Error in formula LevelOneDisplay:
'If IsNull({Nec_Gnav_ReportViewer_Data_AgentAlertsRepo rt_AgentAlertData.LevelOne}) Then Chr(168)'

A string is required here.
Details: errorKind.

 

[IanWaterman]

What datatype is this field?
Can you do a simple report and list out what data is held in it? Just add this field to blank report and in File->Report->options select distinct records only

Ian

 

[lagyossarian22]

Ian,

The underlying field is a boolean that gets tested to determine the output of the formula field which is a text type.

 

[IanWaterman]

Isnull() does not require a stringfield

It would imply that there is something wrong with your underlying data. Have you tried my suggestion to see what data is held in that field. If its being populated with a non boolean result that might be the issue.

Ian

 

[MCuthill]

In Crystal X, I have had to go under File -> Report Options -> and Check the "Convert Database NULL Values to Default" for some of my reporting. Depending on your version, perhaps this is something to look into.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[jlopeman]

Sometimes you have to allow for both null fields and blank fields.

IF isNull({table.field}) or {table.field} = '' THEN crSomeColor
ELSE
IF {table.field} = true THEN crSomeOtherColor