If...then...else problems

[BradW]

Hello all, and thanks for all help provided!

I have a Crystal Pro 9 problem with an Oracle database, which I am connecting to using the native Oracle drivers.

In one table there are four boolean fields that I am working with. I want to highlight the record if any one or more of the four fields are yes.

Here is the formula that I have written

If {field 1} = "Y" (this database uses Y/N for booleans)
then "X"
Else If {field 2} = "Y"
then "X"
Else If {field 3} = "Y"
then "X"
Else If {field 4} = "Y"
then "X"
Else ""

I am placing the formula in large bold type at the beginning of the detail line, and for those that have any one of the 4 fields as a yes, it should show up as an X to highlight the record.

However, the behavior is not what I expected. If the {field 1} is yes then it shows the X however if {field 1} is not yes but any of the other fields are yes, it shows a blank. It appears to evaluate the first if and then stop. I have tried switching the order of the fields and it does it the same regardless of which field is first. Always shows the X if the first field in the formula is yes but never if the first is no even if at least one of the other is yes.

If I write this as four seperate formulas it works just fine for each one.

My current solution is to use the four seperate formulas and to "stack" them so that any of the ones that are yes just show overlayed X characters, however ultimately, I would like to figure out why this is not working as it should and fix it so it is done in one formula rather than having four to maintain.

Thanks for reading all this and thanks for all help.

 

[Ngolem]

I generally assign the value and diplay it (I find it more reliable)

WhilePrintingRecords;
Stringvar result := "";

If {field 1} = "Y" then
result := "X"
Else If {field 2} = "Y" then
result := "X"
Else If {field 3} = "Y" then
result := "X"
Else If {field 4} = "Y" then
result := "X"
Else
result := "";

result;


Then again perhaps (since you don't seem to care if one or all are = "Y") a better formula might be

WhilePrintingRecords;
Stringvar result ;

If {field 1} = "Y" or {field 2} = "Y" or {field 3} = "Y"
{field 4} = "Y" then
result := "X"
else
result := " "; //I prefer a blank space to a null
result;

hope this helps.



Jim Broadbent

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

 

[BradW]

Thanks, I'll give this a try.

 

[BradW]

Ngolem,

I tried your suggested formulas and they produced the exact same results as mine. If the first field is a Y value then it shows the result otherwise it shows blank even if the 2nd or 3rd or 4th fields are Y. Very confounding, I've never seen this behavior before from any database.

Thanks for your suggestion, any other ideas?

 

[GMcNamara]

Can any of these fields be null? Crystal is hopelessly difficult when it comes to having null values in comparisons. This would explain why you only get an X when the very first value is Y. So, try checking if each value is null first such as:


If (not(isNull({field 1})) and {field 1} = "Y") or
(not(isNull({field 2})) and {field 2} = "Y") or
(not(isNull({field 3})) and {field 3} = "Y") or
(not(isNull({field 4})) and {field 4} = "Y") then
result := "X"
else result := "";

result;

 

[BradW]

Thanks, I had not considered that null aspect as it should be Y/N, but on further review, the guy who designed this datbase may have let null be a valid value (after all, he used y/n for a boolean) so I'll try that. I appreciate your help!

 

[Ngolem]

Brad

My/your formulas should work

Try this as an experiment

WhilePrintingRecords;
Stringvar result ;

If {field 4} = "Y" or {field 2} = "Y" or {field 3} = "Y" or
{field 1} = "Y" then
result := "X"
else
result := {field 1} + "/" + {field 2} + "/" + {field 3} + "/" + {field 4} ;

result;

the only difference is that we placed {field 1} as the last of the list. We know the formula works for {field 1} so let us see if it was a position problem. If it fails then we will see exactly what is in these fields when the formula fails.

Perhaps lowercase y and n are being allowed then another alternative is

If ucase({field 1}) = "Y" or ucase({field 2}) = "Y" or
ucase({field 3}) = "Y" or ucase({field 4}) = "Y" then
result := "X"
else
result := " ";
result;


Jim Broadbent

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