Crystal Reports - Formulas and Null Values

[JMSBR]

Hi

I am having a problem with a formula which is in a formula field on my report. The formula does what I need it to if I change the report options to "Convert Database NULL Values to Default" and evaluate the fields with <> "". However for other reasons I need to be able to evaluate Null values as Nulls, so I have replaced the <> "" with not isnull.

I have read somewhere that I need to evaluate the Null values first before the rest of the query, however I am really struggling to rearrange the formula to do this.

If anybody has any ideas, your help would be really appreciated.

Currently the formala is as follows

if {Contact.contact_organisation} = {@Comp \ Addr 1} then
(
if {Contact.contact_sub_building} = {@Address 2} then
(
if not (isnull ({Contact.contact_Building})) then
(
if NumericText (left ({Contact.contact_Building},1)) and Length ({Contact.contact_Building}) < 9 then
{Contact.contact_Building} & " " & {Contact.contact_street}
else
{Contact.contact_Building}
)
else
(
if {Contact.contact_building_number} <> "0" and not (isnull ({Contact.contact_street})) then
{Contact.contact_building_number} & " " & {Contact.contact_street}
else
(
if {Contact.contact_building_number} = "0" and not (isnull ({Contact.contact_street})) then
{Contact.contact_street}
else
(
if not (isnull ({Contact.contact_dependent_locality})) then
{Contact.contact_dependent_locality}
else
{Contact.contact_locality}
)
)
)
)
else
(
if {Contact.contact_Building} = {@Address 2} then
(
if {Contact.contact_building_number} <> "0" and not (isnull ({Contact.contact_street})) then
{Contact.contact_building_number} & " " & {Contact.contact_street}
else
(
if {Contact.contact_building_number} = "0" and not (isnull ({Contact.contact_street})) then
{Contact.contact_street}
else
(
if not (isnull ({Contact.contact_dependent_locality})) then
{Contact.contact_dependent_locality}
else
{Contact.contact_locality}
)
)
)
else
(
if {Contact.contact_building_number} & " " & {Contact.contact_street} = {@Address 2} or {Contact.contact_street} = {@Address 2} then
(
if not (isnull ({Contact.contact_dependent_locality})) then
{Contact.contact_dependent_locality}
else
{Contact.contact_locality}
)
else
(
if {Contact.contact_building_number} & " " & {Contact.contact_street} = {@Address 2} then
(
if not (isnull ({Contact.contact_dependent_locality})) then
{Contact.contact_dependent_locality}
else
{Contact.contact_locality}
)
else
(
if {Contact.contact_dependent_locality} = {@Address 2} then
{Contact.contact_locality}
)
)
)
)
)

 

[lbass]

You didn't identify which formula was causing the problem. Please the content of it also.

-LB

 

[JMSBR]

Sorry its a bit difficult to explain the problem. Essentially we have 8 address fields which we need to combined into the first 3 lines of the address.

What I am finding is that if the criteria are met for the first section of code ie where {Contact.contact_sub_building} = {@Address 2} it returns the correct data but it doesn't go on to evaluate the other sections where this criteria isn't met.

So the following code works:

if {Contact.contact_organisation} = {@Comp \ Addr 1} then ( if {Contact.contact_sub_building} = {@Address 2} then ( if not (isnull ({Contact.contact_Building})) then ( if NumericText (left ({Contact.contact_Building},1)) and Length ({Contact.contact_Building}) < 9 then {Contact.contact_Building} & " " & {Contact.contact_street} else {Contact.contact_Building} ) else ( if {Contact.contact_building_number} <> "0" and not (isnull ({Contact.contact_street})) then {Contact.contact_building_number} & " " & {Contact.contact_street} else ( if {Contact.contact_building_number} = "0" and not (isnull ({Contact.contact_street})) then {Contact.contact_street} else ( if not (isnull ({Contact.contact_dependent_locality})) then {Contact.contact_dependent_locality} else {Contact.contact_locality} ) ) ) )

Also if I then comment out the section above the following code then starts to work ok. i.e {Contact.contact_Building} = {@Address 2}.

I hope that makes more sense, as I said in the original post if I change the not (isnull( for <> "" and change the report option to "Convert Database NULL Values to Default" the code does do what we want.

 

[HowardHammerman]

Sometimes adding "whileprintingrecords" as teh first line helps.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[JMSBR]

Thanks for the idea, but unfortunately I have tried it and still have the issue.

 

[kray4660]

For something this complex. I would remove it from the selection criteria and put into a formula and see how it is evaluating for each detail. And a lot of times I will break up the sections of the formula to make sure they are evaulating the way I expect it to.

I hope this helps.

 

[Madawc]

As kray4660 says, break it up. One method is to put the tests in a statement, without an IF, as a boolian. E.g.

NumericText (left ({Contact.contact_Building},1)) and Length ({Contact.contact_Building}) < 9

Display these on a test detail line for unselected data. It should be True or False, but will be blank when you hit a null.

A select statement can just cite the name of the boolian and the test will be applied.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

This is way too complex. I think you should create a set of formulas for fields that can be null, like this:

if isnull({table.field}) then
"" else
{table.field}

Then reference these sets of fields as appropriate in your formula.

-LB

 

[JMSBR]

Hi

Thanks for all your replies, I broke it down in to its parts as suggested and if I evaluated the null first it started working. However because of the complexity of the script I ultimately went with lbass' solution and created a formula field for each field.