problem with IF, THEN, ELSE IF, THEN, ELSE 4

[voirin]

Hi, I was hoping someone could help me. I have created a formula field '@mystatus' with the following (crystal syntax):

if "revoked" in {PRIMARYCODE_1.DISPLAYVALUE} then
"Revoked"
else if "withdrawn" in {PRIMARYCODE_1.DISPLAYVALUE} then
"Withdrawn"
else
"OK"

I have applied @mystatus to each row. I am finding that I am getting rows listed with "Revoked" and others with "Withdrawn" in this column. However all the other rows are empty. Why is this? I want the other rows (which have neither the text "Revoked" or "Withdrawn") to have the value "OK".

I am grouping by @mystatus.

Any help most appreciated!

cheers, Voirin

 

[kskid]

You almost had it. Just reassemble

if {PRIMARYCODE_1.DISPLAYVALUE} = "revoked" then
    "Revoked"
else if {PRIMARYCODE_1.DISPLAYVALUE} = "withdrawn" then
    "Withdrawn"
else
    "OK"

-lw

 

[voirin]

Thanks for the reply but that's not it. The reason I have

"revoked" in {PRIMARYCODE_1.DISPLAYVALUE}

is that I want to determine if the string "revoked" is contained in a larger string of words, for example the string "This application is revoked". This function works fine!

All I want to know is why my IF-THEN-ELSE statement doesnt print "OK" if the first two conditions are not met. This statement is contained in a formula field and I am grouping by this field.

Any help most appreciated!

cheers, voirin

 

[dgillz]

Try this:

if Instr(Uppercase({PRIMARYCODE_1.DISPLAYVALUE}),"REVOKED")>0 then "Revoked"
else if Instr(Uppercase({PRIMARYCODE_1.DISPLAYVALUE}),"WITHDRAWN")>0 then "Withdrawn"
else "OK"

This will find if the substring of "REVOKED" or "WITHDRAWN" appears in your field, and the Uppercase() function will find it regardless of case.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[voirin]

Thanks for that!

However my problem still continues... i get the following output as an example:


------------+------------
Status ProductID
------------+------------
45
73
23
Revoked 18
Withdrawn 46
------------+------------


However, I want it to print out:


------------+------------
Status ProductID
------------+------------
OK 45
OK 73
OK 23
Revoked 18
Withdrawn 46
------------+------------

Note, i am grouping on the 'status' column which uses the IF-THEN-ELSE statement.

Any help really appreciated.

Cheers, Voirin

 

[WarrenRoss]

Your first formula should work just fine, although you may want to incorporate dgillz's idea of converting your strings to uppercase, just to be safe.

This sort of problem usually occurs because your IF statement conditions are not correct, although I can't see anything wrong with this simple statement. Could you perhaps provide some sample data (especially {PRIMARYCODE_1.DISPLAYVALUE})?

 

[MisterMo]

Try

stringVar MyResult;

MyResult := 'OK';

if {PRIMARYCODE_1.DISPLAYVALUE} = "revoked" then
MyResult := 'Revoked';
if if {PRIMARYCODE_1.DISPLAYVALUE} = "withdrawn" then
MyResult := 'withdrawn'
else
MyResult

this way you will always get something

Mo

 

[Madawc]

Blank output probably means nulls. 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 or spaces. 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'.

Nulls for 'string' data are less common but perfectly possible. And Crystal has an unhappy habit of stopping when it finds a null in a formula, even if that field is tested for being null later on. So I'd say the test should be

if not isnull({PRIMARYCODE_1.DISPLAYVALUE}) 
and "revoked" in {PRIMARYCODE_1.DISPLAYVALUE} then
    "Revoked"
else if not isnull({PRIMARYCODE_1.DISPLAYVALUE})
     and "withdrawn" in {PRIMARYCODE_1.DISPLAYVALUE} then
    "Withdrawn"
else
    "OK"

Madawc Williams (East Anglia, UK)

 

[MisterMo]

That's why I use variables with a default value.

this way I don't need to check for null values or zeros


Mo

 

[voirin]

Hi everyone,

I managed to fix the problem (solution given by Madawc - thanks!), but I don't quite understand why it's fixed (as opposed to other solutions (e.g. MisterMo - thanks!)).

The solution that works is:

if not isnull({PRIMARYCODE_1.DISPLAYVALUE})
and "revoked" in {PRIMARYCODE_1.DISPLAYVALUE} then
"Revoked"
else if not isnull({PRIMARYCODE_1.DISPLAYVALUE})
and "withdrawn" in {PRIMARYCODE_1.DISPLAYVALUE} then
"Withdrawn"
else
"OK"

------------+------------
Status ProductID
------------+------------
OK 45
OK 73
OK 23
Revoked 18
Withdrawn 46
------------+------------


The following does not work:

stringVar MyResult;
MyResult := 'OK';
if "revoked" in {PRIMARYCODE_1.DISPLAYVALUE} then
MyResult := 'Revoked'
else if "withdrawn" in {PRIMARYCODE_1.DISPLAYVALUE} then
MyResult := 'Withdrawn'
else
MyResult

------------+------------
Status ProductID
------------+------------
45
73
23
Revoked 18
Withdrawn 46
------------+------------

One point to note is that I am using LEFT INNER JOIN's on tables extending 'from' a table i am extracting 'status' from ... 'to' a table I am extracting 'ProductID' from.

Why does checking for null work, but setting a default value doesnt work???

Any insight most appreciated!

cheers,
voirin

 

[MisterMo]

There are a couple of ways that you can try

this is another

stringVar MyResult;

MyResult := 'OK';

if "revoked" in {PRIMARYCODE_1.DISPLAYVALUE} then
    MyResult := 'Revoked';

if "withdrawn" in {PRIMARYCODE_1.DISPLAYVALUE} then
    MyResult := 'Withdrawn';

MyResult

The other formula should have worked just fine and so is this one.

Mo

 

[Madawc]

Crystal formulas always stop when you try to do something with a field that contains a null. It's part of the design philosophy - the original writers must have assumed you'd want a blank in such cases.

Does anyone know when isnull was introduced, incidentally? Or has it been there from the start? I first encountered Crystal 8.5, which had obviously had a long evolution already.

Madawc Williams (East Anglia, UK)

 

[Naith]

Because if the string is null, Mr Mo's formula will never execute.

This is because unless you explicitly reference nulls in your formula, any null fields can't have conditions applied to what isn't there, so the formula will just fail to execute.

It's like if you said to me, go to the shop and get me donuts. But if there's no donuts get me gum. But when I look out the window, there's no shop, so I don't bother doing any of what you told me to do. Unless you'd explicitly told me what to do if the shop wasn't there.

Naith

 

[GJParker]

Just as an extra point to this, there is an option in Crystal which can simplify the reporting of NULL values.

File menu -> Report Options -> Convert database NULL values to default

or this can be set globally for all reports

File menu -> options -> Reporting tab

This will convert NULL strings to '' and NULL numerics to 0.

With this set your original formula would have worked.

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[MisterMo]

It turns out that I had my "Convert database NULL values to default
"
so it worked for me and when I turned the option of it didn't.

I can see it happening but I am not happy with it

my logic was

here is a pound, go get me a donought, failing to do so give me back my pound.

hence setting the default value beforehand

CR logic seems to go like:

I have a pound, If I can't find what I am looking for the pound is gone.




Mo

 

[voirin]

Thanks for the info!

On a further note, I found that:

File menu -> Report Options -> Convert database NULL values to default

.. seem to work, however

File menu -> options -> Reporting tab -> Convert database NULL values to default

.. does not seem to work.

Since I want this report accessible to other users, I'd like the report to save this 'NULL conversion to default' setting. Is this possible?

Thanks again!

cheers, voirin