Build up contents of string variable using IF statements 1

[Knoxville]

Using Crystal Reports XI.

On the database I am using I have a number of boolean fields. I have created a formula field that builds up a string variable based on the contents of the boolean fields like this....

Local StringVar strConditions;

IF {database.boolean1} = TRUE
THEN strConditions := strConditions + "Description1, "
ELSE
strConditions;

IF {database.boolean2} = TRUE
THEN strConditions := strConditions + "Description2, "
ELSE
strConditions;

IF {database.boolean3} = TRUE
THEN strConditions := strConditions + "Description3, "
ELSE
strConditions;

IF {database.boolean4} = TRUE
THEN strConditions := strConditions + "Description4, "
ELSE
strConditions;

This works great.

However I tried to create a similar formula for another task but testing a number of fields for the same value....

Local StringVar strProducts;

IF {database.field1} = 17
or {database.field2} =17
or {database.field3} = 17
or {database.field4} =17
or {database.field5}=17
or {database.field6} =17
or {database.field7} =17
or {database.field8} =17
THEN strDrugs := strProducts + "Product1, "
ELSE strProducts;


IF {database.field1} = 5
or {database.field2} = 5
or {database.field3} = 5
or {database.field4} = 5
or {database.field5}= 5
or {database.field6} = 5
or {database.field7} = 5
or {database.field8} = 5
THEN strDrugs := strProducts + "Product2, "
ELSE strProducts;

IF {database.field1} = 8
or {database.field2} = 8
or {database.field3} = 8
or {database.field4} = 8
or {database.field5}= 8
or {database.field6} = 8
or {database.field7} = 8
or {database.field8} = 8
THEN strDrugs := strProducts + "Product3, "
ELSE strProducts;

When I run this it doesn't work as I'd expect.
Values seem to be allocated OK if one or concurrent IF statements are met, i.e. you can get "Product1, " or "Product 2, " or "Product1, Product2, "

BUT as soon as an ELSE statement is processed then the whole string is set back to the default of an empty string. So even where I had "Product1, Product 2, " before, this gets set to empty string when an ELSE is hit.

I figure that when the value of strProducts is picked up in the ELSE it's picking up the default status of a string varibale rather than the one that's been built up so far. But why does it do it? And why doesn't the same thing happen in my first example?

I have tried:

- Leaving out the ELSE altogether
- ELSE strProducts := strProducts

But they have the same result.

Can anyone help or suggest a better method?

Thanks

 

[MisterMo]

There is a bit of confusion with your variables

strDrugs := strProducts + "Product2, "

Try this

Local StringVar strProducts;

strProducts:= "";

IF {database.field1} = 17 
or {database.field2} =17 
or {database.field3} = 17
or {database.field4} =17
or {database.field5}=17
or {database.field6} =17
or {database.field7} =17 
or {database.field8} =17
THEN strProducts := strProducts + "Product1, " ;
IF {database.field1} = 5 
or {database.field2} = 5 
or {database.field3} = 5
or {database.field4} = 5
or {database.field5}= 5
or {database.field6} = 5
or {database.field7} = 5 
or {database.field8} = 5
THEN strProducts := strProducts + "Product2, ";
IF {database.field1} = 8 
or {database.field2} = 8 
or {database.field3} = 8
or {database.field4} = 8
or {database.field5}= 8
or {database.field6} = 8
or {database.field7} = 8 
or {database.field8} = 8
    THEN strProducts := strProducts + "Product3, "
ELSE 
strProducts;

Mo

 

[MisterMo]

You do realise that if you have

{database.field3} = 17
and {database.field7} = 5
and {database.field1} = 8

then all your conditions will be true making

strProducts = Product1, Product2, Product3,

which also requires a trim at the end to remove the space and the comma

Mo

 

[Knoxville]

Thanks for your help, Mo

Regarding the first post:

- Sorry about the varible name - I'd amended the original to make it more generic & forgot to change the name in that part of the formula - d'oh!
- I've tried the suggested change already but I get the same result.

Regarding the second post: That's OK, it's what I'm trying to acheive.

 

[MisterMo]

Could any of the fields be NULL ?

Mo

 

[Knoxville]

They may well be. I'll check it out.

Thanks Mo

 

[Knoxville]

Yes, default values for the field is NULL.

I would have thought these would just fall straight into the ELSE clause. Does Crystal deal with NULL values in a different way?

 

[MisterMo]

yes it does, if the value is null the if statement does not work unless you cater for it eg.

if isnull {database.field1} = false and {database.field1} = 17

but this in your case can be very tiresome

go to file>Report Option> and tick the set Database null value to default option.

this will give a zero to all null fields and fix yor problem

Mo

 

[GJParker]

If you ar etrying to build the string for the whole recordset then the variable nust be changed froma Local variable to Shared Variable.

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[Knoxville]

Thanks Mo, that worked great!

It's OK Gary I don't need to build up a string for the whole recordset just one record at a time.