Formula field giving unexpected results

[QPRphil]

I have defined a new formula field called Category, that can have one of three values based on the value of three different database fields. -

If {field1}="x" then
"Legacy"
else If {field2}="x" then
"Vibe"
else "Other"

No matter how I arrange the arguments in this statement, the only value that gets into the field Category, is the result of the first argument. In this case "Legacy", and all the other lines on the report have blanks for Category.

field1 can have a value of "x" or " ", field2 can have a value of "x" or " "; the remaining condition is based on both field1 and field2 being blank, and field3 having any other value.

What am I doing wrong?

 

[Naith]

Your code looks fine to me. Can you hit the Database tab and verify the database. And if the problem remains, switch the 1st and 2nd arguments of the formula around and report whether the behaviour remains constant.

Naith

 

[goranm]

Hi !

Maybe the problem is that you have to take care of null values.

Try this one, it maybe helps.

if not isNull({field1}) then
(
if {field1} = 'x' then
"Legacy"
)
else
if not isNull({field2}) then
(
if {field2} = "y" then
"Vibe"
)
else
"Other"

/Goran

 

[Madawc]

Check for nulls--which are very confusing for anyone coming to Crystal from a Mainframe background, as I did. You need to start with a command like
if not isnull ({field1})

Nulls are regarded as an absence of data, as distinct from a value that definitely should be zero or blank. Crystal will stop processing in a FOrmula Field if it comes across a null value before the null test.

Madawc Williams
East Anglia, Great Britain

 

[QPRphil]

The Nulls have it!

Thanks for your replies everybody. The not isNull step worked just fine. I will have to remember that in the future.

Thanks again to all.

Phil