What's wrong with this formula and/or selection criteria

[Hypermommy]

Hi all,

We have a report that did work in Crystal 8.5 but is now not working in 9.0. It acts as if it's not reading the selection criteria correctly and/or not processing the formula correctly. I think it's the formula because when I put a text field in the database to show the @RegionBundle value, it comes up blank when I select a city but when I select "All" it comes up as TRUE.

I've included both below. Anyone see something we're missing?

Selection criteria:

(({?Class Type} = "ALL&quot or ({VW_ALCOHOL_RPT.CLASS_ID} = {?Class Type})) and
{VW_ALCOHOL_RPT.DATE_SEIZED} >= {?Start date} and
{VW_ALCOHOL_RPT.DATE_SEIZED} <= {?End Date} and
{CLNT.CLNT_CDE} startswith &quot;40&quot; and
{@Region Bundles} and
isnull({VW_ALCOHOL_RPT.DIS_DATES})


Formula for @RegionBundles

if {?Region } = &quot;Fort Lauderdale Region&quot;
then
{enf_regn.enf_regn_cde} = &quot;FL&quot;
else
if {?Region } = &quot;Fort Myers Region&quot;
then
{enf_regn.enf_regn_cde} in [&quot;SA&quot;,&quot;FT&quot;]
else
if {?Region } = &quot;Jacksonville Region&quot;
then
{enf_regn.enf_regn_cde} in [&quot;AX&quot;,&quot;GA&quot;,&quot;CA&quot;]
else
if {?Region } = &quot;Miami Region&quot;
then
{enf_regn.enf_regn_cde} in [&quot;IA&quot;,&quot;KW&quot;]
else
if {?Region } = &quot;Orlando Region&quot;
then
{enf_regn.enf_regn_cde} in [&quot;RL&quot;,&quot;AY&quot;,&quot;RK&quot;]
else
if {?Region } = &quot;Tampa Region&quot;
then
{enf_regn.enf_regn_cde} in [&quot;TA&quot;,&quot;CL&quot;,&quot;AK&quot;]
else
if {?Region } = &quot;Tallahassee Region&quot;
then
{enf_regn.enf_regn_cde} in [&quot;AL&quot;,&quot;EN&quot;,&quot;PY&quot;,&quot;IV&quot;]
else
if {?Region } = &quot;West Palm Beach Region&quot;
then
{enf_regn.enf_regn_cde} in [&quot;ES&quot;,&quot;FT&quot;]
else
if {?Region } = &quot;Fort Pierce Region&quot;
then
{enf_regn.enf_regn_cde} = &quot;FP&quot;
else
if {?Region } = &quot;ALL&quot;
then
{ENF_REGN.ENF_REGN_CDE} in [&quot;FL&quot;,&quot;SA&quot;,&quot;FT&quot;,&quot;AX&quot;,&quot;GA&quot;,&quot;CA&quot;,&quot;IA&quot;,&quot;KW&quot;,&quot;RL&quot;,&quot;AY&quot;,
&quot;RK&quot;,&quot;TA&quot;,&quot;CL&quot;,&quot;AK&quot;,&quot;AL&quot;,&quot;EN&quot;,&quot;PY&quot;,&quot;IV&quot;,&quot;ES&quot;,&quot;FT&quot;,&quot;FP&quot;]
else
if not isnull({?Region })
then
{ENF_REGN.ENF_REGN_DESC} = {?Region };


-= Hypermommy =-

 

[synapsevampire]

I find it odd that it ever worked given it's current state.

A formula test such as this should return a True or False, not pass values.

Move the regionbundles formula into the record selection formula itself, then it should construct SQL and pass correctly.

If you want all when all is selected, then change that to:

if {?Region } = &quot;ALL&quot;
then
true

Which will pass nothing, which means everything is returned.

And I don't think that a parameter will be null within Crystal (unless it's passed from a program), so replace the null check with (the not was also missing parens) the following, and make it the first test in the IFs:

if not(isnull({?Region }))
and
{?Region } <> &quot;&quot;
then
{ENF_REGN.ENF_REGN_DESC} = {?Region };

-k

 

[synapsevampire]

I find it odd that it ever worked given it's current state.

A formula test such as this should return a True or False, not pass values.

Move the regionbundles formula into the record selection formula itself, then it should construct SQL and pass correctly.

If you want all when all is selected, then change that to:

if {?Region } = &quot;ALL&quot;
then
true

Which will pass nothing, which means everything is returned.

And I don't think that a parameter will be null within Crystal (unless it's passed from a program), so replace the null check with (the not was also missing parens) the following, and make it the first test in the IFs:

if not(isnull({?Region }))
and
{?Region } <> &quot;&quot;
then
{ENF_REGN.ENF_REGN_DESC} = {?Region };

-k

 

[Ngolem]

I agree with SV....this is an impossible occurance

*********************************
if not isnull({?Region })
then
{ENF_REGN.ENF_REGN_DESC} = {?Region };
**********************************

Crystal will not let you proceed unless you enter a value into the parameter dialog . There is not requirement for brackets though as SV said around the &quot;IsNull&quot; but that is not the problem

Probably this section of the formula was to catch a bad spelling during data entry....that is my guess since there is no default to protect against this. This is how I would end this formula

...

if {?Region } = &quot;ALL&quot;
then
{ENF_REGN.ENF_REGN_CDE} in [&quot;FL&quot;,&quot;SA&quot;,&quot;FT&quot;,&quot;AX&quot;,&quot;GA&quot;,&quot;CA&quot;,&quot;IA&quot;,&quot;KW&quot;,&quot;RL&quot;,&quot;AY&quot;,
&quot;RK&quot;,&quot;TA&quot;,&quot;CL&quot;,&quot;AK&quot;,&quot;AL&quot;,&quot;EN&quot;,&quot;PY&quot;,&quot;IV&quot;,&quot;ES&quot;,&quot;FT&quot;,&quot;FP&quot;]
else
{ENF_REGN.ENF_REGN_DESC} = {?Region };

Remove the reference to isnull altogether




Jim Broadbent

 

[Hypermommy]

Jim,

You're spot on... it was for debugging. I *MAY* have found the problem. I've put some output in my local copy of the report that lets me know the values of
&quot;>&quot; & {ENF_REGN.ENF_REGN_DESC} & &quot;<&quot;
and
&quot;>&quot; & {?Region} & &quot;<&quot; when the report runs.

Then I ran it with something that worked (&quot;ALL&quot to see if the two are the same. It appears they might not be. So I trimmed both of the above in that last line of code. I've sent the report back to the report writer for her to verify (or not) the solution.

But there has to be an easier way to check code. I know Crystal's got the syntax checker in the formula editor but is there any way to step through the logic of a formula to make sure that's right?

Thanks all!

-= Hypermommy =-

 

[lbass]

Also, I think &quot;isnull({VW_ALCOHOL_RPT.DIS_DATES})&quot; should be the first line of the selection formula, as nulls must be evaluated first.

-LB

 

[Ngolem]

Glad I could be of help...

Crystal has no real formula evaluater other than the basic test when the formula is created...this will highlight basic syntax errors that will not allow the formula to function at all.

Basically to debug a formula I just completely comment-out the formula and then starting at the beginning Un-comment sections of the formula and test for expected output...until I reach the bad piece.

Jim Broadbent