incorrect result using null values, or statements and not in list

[dbradley]

Hi, I am looking for some clarity in how Crystal Reports XI handles "or" statements and the "not in ....." statements. I have 2 formulas below, the top one works fine and I get the proper results, but the bottom one returns the same result as the top one -- the fields table1.cd1, table1,cd2, etc do contain null values (however I have the "convert database nulls to default" turned on Crystal reports.

can someone help me, so that I can make the bottom formula return the proper result?


THIS WORKS:

If {table1.class} in ["1","6"] and
(
({table1.TYPE}="A" and
({table1.CD1}in ["15","21","23"] or
{table1.CD2}in ["15","21","23"] or
{table1.CD3}in ["15","21","23"] or
{table1.CD4}in ["15","21","23"] or
{table1.CD5}in ["15","21","23"]))
or
({table1.TYPE}="B" and
({table1.CD1}="15" or
{table1.CD2}="15" or
{table1.CD3}="15" or
{table1.CD4}="15" or
{table1.CD5}="15" ))
)
then {table1.BALANCE} else 0.00

THIS DOES NOT WORK: gives me the same balance as above formula

If {table1.class} in ["1","6"] and
(
({table1.TYPE}="A" and
(not({table1.CD1}in ["15","21","23"]) or
not({table1.CD2}in ["15","21","23"]) or
not({table1.CD3}in ["15","21","23"]) or
not({table1.CD4}in ["15","21","23"]) or
not({table1.CD5}in ["15","21","23"])))
or
({table1.TYPE}="B" and
({table1.CD1}<>"15" or
{table1.CD2}<>"15" or
{table1.CD3}<>"15" or
{table1.CD4}<>"15" or
{table1.CD5}<>"15" ))
)
then table1.balance else 0.00

---------------------------
Thanks
DB

 

[synapsevampire]

Your use of parentheticals states that there are different comparisons, the first says table.class in 1,6 and table A or table b criteria exists, your second says table.class in 1,6 and table A OR just the Table B criteria.

-k

 

[dbradley]

Hi, thanks for the quick reply -- I have been looking at this all day and I can't find the solution, is it possible for someone to show me the proper parenthesis ??

-----------

DB

 

[synapsevampire]

Sorry, I misunderstood.

Unfortunately you didn't state what you want it to return.

Try

If {table1.class} in ["1","6"]
and
(
{table1.TYPE}="A"
and
not({table1.CD1}in ["15","21","23"])
and
not({table1.CD2}in ["15","21","23"])
and
not({table1.CD3}in ["15","21","23"])
and
not({table1.CD4}in ["15","21","23"])
and
not({table1.CD5}in ["15","21","23"])
or
(
{table1.TYPE}="B" and
(
{table1.CD1}<>"15" and
{table1.CD2}<>"15" and
{table1.CD3}<>"15" and
{table1.CD4}<>"15" and
{table1.CD5}<>"15" )
)
)

-k

 

[dbradley]

Hi, the problem is that the 15, 21, 23 could be in any of the 5 cd1, cd2, cd3, cd4, cd5 fields. And I need to return if it is in any one of the 5, so that is why I used "or"

---------------
DB

 

[synapsevampire]

But you're doing a NOT check, so I'm not sure what the intent is anymore.

Your first formula appears to do that...

-k

 

[dbradley]

Hi -- sorry as I look back at this I have not made my intent real clear, whoops. I guess I have been looking at this too long.

My objective is two different formulas -- one to give me the records that meet the conditions (the 1st "in" formula) and then a seperate formula that gives me the records that do not meet the conditions (the 2nd "not in....." formula)

I would like to have both formulas as I need to display the data that way.

So, the top formula works fine, it gives me the results/records I expect. But the second formula is not "subtracting" out the records, it gives me the same balance result as the top formula.

I am not sure how to adjust my 2nd formula to exclude the records I need. I thought it might have to do with doing a "not in...." in combination with the multiple "or" statements & as I mentioned, all 5 of these fields do contain nulls.

I hope that helped.


-----------
DB

 

[dbradley]

Does anyone have more advice on this problem I am having?

-------
DB

 

[lbass]

What you are trying to do is unclear. If you really want the opposite of your first formula for your second formula, try:

If not(
{table1.class} in ["1","6"] and
(
({table1.TYPE}="A" and
({table1.CD1}in ["15","21","23"] or
{table1.CD2}in ["15","21","23"] or
{table1.CD3}in ["15","21","23"] or
{table1.CD4}in ["15","21","23"] or
{table1.CD5}in ["15","21","23"]))
or
({table1.TYPE}="B" and
({table1.CD1}="15" or
{table1.CD2}="15" or
{table1.CD3}="15" or
{table1.CD4}="15" or
{table1.CD5}="15" ))
)
)
then {table1.BALANCE} else 0.00

-LB

 

[dbradley]

Hi, I will try to explain what I am doing, or looking for:

I am looking for a formula that will give me the balance when the product is
-either a class 1 or 6
- but exclude if the type is "A" and if there is a 15, 21, 23 in any of the 5 fields (cd1, cd2, cd3,cd4,cd5)
- or exlude if the type is "B" and if there is a 15 in any of the 5 fields (cd1, cd2, cd3, cd4, cd5)
-otherwise just give me 0.00

I hope this helps explain what I am trying to accomplish.

----------
DB