selection formula not working... strange problem... 2

[pptvictor]

Hi,

I am using CR 8.5, pervasive db for ACCPAC. I'm trying to write a report which outputs a bunch of stuff. Basically, there are two tables which contain the data i need, and my client has a report to access these tables but it's too slow because it uses a subreport whenever it need data from the second table. I am converting it to use left outer joins instead.

My problem is this:

My selection formula looks something like this:
( LOTS OF STATEMENTS BLAH BLAH BLAH ... )
AND
(
(
{GLPOSTO_WORKCODE.OPTFIELD} = "WORKCODE" and
{GLPOSTO_SITE.OPTFIELD} = "SITE" and
{GLPOSTO_STATE.OPTFIELD} = "STATE" and
{GLPOSTO_DATEINCURRED.OPTFIELD} = "DATEINCURRED"
)
OR
(
isNull({GLPOSTO_WORKCODE.OPTFIELD}) and
isNull({GLPOSTO_SITE.OPTFIELD}) and
isNull({GLPOSTO_STATE.OPTFIELD}) and
isNull({GLPOSTO_DATEINCURRED.OPTFIELD})
)
)

That is pretty much what I want. However, I can't figure out why it is not working. This selection formula will output, for example, 700 records. I know this number is wrong because the original report will output, for example, 800 records. If I write:

( LOTS OF STATEMENTS BLAH BLAH BLAH ... )
AND
(
{GLPOSTO_WORKCODE.OPTFIELD} = "WORKCODE" and
{GLPOSTO_SITE.OPTFIELD} = "SITE" and
{GLPOSTO_STATE.OPTFIELD} = "STATE" and
{GLPOSTO_DATEINCURRED.OPTFIELD} = "DATEINCURRED"
)

I will get 700 records.

If I write:

( LOTS OF STATEMENTS BLAH BLAH BLAH ... )
AND
(
isNull({GLPOSTO_WORKCODE.OPTFIELD}) and
isNull({GLPOSTO_SITE.OPTFIELD}) and
isNull({GLPOSTO_STATE.OPTFIELD}) and
isNull({GLPOSTO_DATEINCURRED.OPTFIELD})
)

I will get the other hundred records I am expecting. Is there something fundamentally wrong that I can't see? Thanks.

-Victor

 

[Naith]

Place the IsNull section at the forefront of your criteria. There's an issue with IsNull in 8.5 where if IsNull is not the initial clause, it'll get ignored.

Naith

 

[Madawc]

Any field that might be null needs to be tested for null, before you test anything else. Otherwise it stops when it hits the null.

Better to break down the complex selection code into a number of boolian formula fields. E.g. @WorkCode

not isNull({GLPOSTO_WORKCODE.OPTFIELD}) and
{GLPOSTO_WORKCODE.OPTFIELD} = "WORKCODE"

This will return 'true' if the field is "WORKCODE" and otherwise false. In the selection, or wherever else you refer to it, you can say @WorkCode or not @WorkCode.

With the combination you seem to be using, you'll probably also need an @AllNull boolian that tests for all five fields being null. I assume that if half were null and half had the desired codes, this would not meet your criteria.

Madawc Williams (East Anglia, UK)

 

[pptvictor]

Thanks very much guys! now that's something i never would have guessed... I wasted all afternoon trying to get that out. It works great now. Thanks for the tips on programming style madawc, but I had to get the report back to my client asap, so all i did was go:

(a bunch of statements)
and
(
(all null cases)
or
(other cases)
)

ie I didnt change anything except a bit of reordering.

madawc - "With the combination you seem to be using, you'll probably also need an @AllNull boolian that tests for all five fields being null. I assume that if half were null and half had the desired codes, this would not meet your criteria."
exactly right.

This anomoly with the null cases - does it exist in later versions of crystal?

 

[Madawc]

As far as I know, it's part of the Crystal philosophy. If you're doing a quick and simple report, it maybe helps to have nulls ignored automatically.

Madawc Williams (East Anglia, UK)