My checkbox formula is not working. Can you see what's wrong? 1

[JonAtHQ]

Hi All,

There's a checkbox in my db. If it's checked, I want to print "-Alternate Site". If it's not checked, I want it to print "-Production Site".

Am I correct in that there can be three values for a checkbox? Null, 0 and 1? I think it is Null until someone checks the box, then it is 1. If someone removes the check, then the value in the field becomes 0. Correct?

Anyway, the following formula is not working for me. It's giving me completely random results:

if isnull ({location.siteName}) then ''
Else
if {PlanSegment_Location.alternate} = 1
then '- Alternate Site'
else '- Production Site'

Can anyone figure out what's going on?

Thx.

 

[Madawc]

I've not come across check-boxes before. I suggest you display the raw data first, to see what you've got.

Also your formula seems to be testing two different fields, {location.siteName} and {PlanSegment_Location.alternate}.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[fisheromacse]

null, 0 & 1 are common values, but i have seen checkboxes use T & F, Y & N, etc.

if you put the field {PlanSegment_Location.alternate} in the report, what values are shown?

 

[JonAtHQ]

I took you upon on your suggestion to print the field values. Here is what's happening:

[li]When the field is "brand new", that is where no entry was made, (which I would assume to mean 'null'), then nothing is printing on the report. Just a blank space.[/li]
[li]When the field has a checkmark added, a 1 prints.[/li]
[li]When the field has the checkmark "removed", a 0 prints.[/li]
So ... how can I test for that initial value?

Can I construct an if, then, else to query for a 1 or 0, and then anything else?

 

[JonAtHQ]

Follow-up from OP:
I tested for 1, then 0, then null, and it is still not printing anyting for the null value. Here is the formula:

if isnull ({location.siteName}) Then ''
Else if ({PlanSegment_Location.alternate}) = 1 Then '- Alternate Site'
Else if {PlanSegment_Location.alternate} = 0 Then '- Production Site'
Else if isnull ({PlanSegment_Location.alternate}) then '- Production Site'

 

[IanWaterman]

Should test for NULLS first, otherwise when encountered Crystal formaul stops and returns NULL

Try

if isnull ({location.siteName}) Then ''
Else if isnull ({PlanSegment_Location.alternate}) then '- Production Site'
Else if ({PlanSegment_Location.alternate}) = 1 Then '- Alternate Site'
Else if {PlanSegment_Location.alternate} = 0 Then '- Production Site'

You have mixed fields if the first If is true then the formula stops at that point.

Try without that and see what happens

if isnull ({PlanSegment_Location.alternate}) then '- Production Site'
Else if ({PlanSegment_Location.alternate}) = 1 Then '- Alternate Site'
Else if {PlanSegment_Location.alternate} = 0 Then '- Production Site'

Ian

 

[JonAtHQ]

Yet another follow-up from OP:

The problem is resolved!

I changed the drop-down at the top of the Formula Editor from "Exceptions for Nulls" to "Default Values for Nulls".

Next up for me is to see how I can optimize the formula to take advantage of this.

Thanks all.

Jon