Problem with null fields in selection formula 1

[agorjest]

Hello,
I am on CR version 2008, using it to report on building maintenance workorders. Trying to pull workorder records using a combination of fields, including 3 different date fields. I'm getting the right results with the date fields.
However, due to inconsistencies of various people entering data, many of the workorders have blank (null) "work type" fields. And I am not able to pull the workorders that have a null work type field using the selection formula below. On reports with less complex selection formulas, I usually am able to get around this problem using a simple "OR" statement like this:

isnull ({workorder.worktype}) or {workorder.worktype} in ["PM","CM"]

But it is not working with this formula. Any help on how to include workorders with null "work type" fields would be appreciated.


Selection formula:

if (isnull ({workorder.worktype}) or {workorder.worktype} in ["AD","CC","CG","CM","EM","EV"]) then
(
({workorder.status} in ["COMP", "CLOSE"]
and
({workorder.reportdate}>= {?Start Date} and
{workorder.reportdate}<= {?End Date}) and
({workorder.actfinish} >= {?Start Date} and
{workorder.actfinish}<= {?End Date}))
or
({workorder.status} in ["INPRG", "PENDING", "WSCH", "WMATL", "APPR", "WAPPR", "WPCOND"]
and
{workorder.reportdate}>= {?Start Date} and
{workorder.reportdate}<= {?End Date})
)
else
{workorder.worktype} in ["PM"]
(
({workorder.status} in ["COMP", "CLOSE"]
and
({workorder.targstartdate}>= {?Start Date} and
{workorder.targstartdate}<= {?End Date}) and
({workorder.actfinish} >= {?Start Date} and
{workorder.actfinish}<= {?End Date}))
or
({workorder.status} in ["INPRG", "PENDING", "WSCH", "WMATL", "APPR", "WAPPR", "WPCOND"]
and
{workorder.targstartdate}>= {?Start Date} and
{workorder.targstartdate}<= {?End Date})
)

 

[lbass]

Try:

(
(
isnull ({workorder.worktype}) or
trim({workorder.worktype}) = "" or
{workorder.worktype} in ["AD","CC","CG","CM","EM","EV"]
) and
(
(
{workorder.status} in ["COMP", "CLOSE"] and
{workorder.reportdate}>= {?Start Date} and
{workorder.reportdate}<= {?End Date} and
{workorder.actfinish} >= {?Start Date} and
{workorder.actfinish}<= {?End Date}
)
or
(
{workorder.status} in ["INPRG", "PENDING", "WSCH", "WMATL", "APPR", "WAPPR", "WPCOND"] and
{workorder.reportdate}>= {?Start Date} and
{workorder.reportdate}<= {?End Date}
)
)
) or
(
{workorder.worktype} = "PM" and
(
{workorder.status} in ["COMP", "CLOSE"] and
{workorder.targstartdate}>= {?Start Date} and
{workorder.targstartdate}<= {?End Date} and
{workorder.actfinish} >= {?Start Date} and
{workorder.actfinish}<= {?End Date}
)
or
(
{workorder.status} in ["INPRG", "PENDING", "WSCH", "WMATL", "APPR", "WAPPR", "WPCOND"] and
{workorder.targstartdate}>= {?Start Date} and
{workorder.targstartdate}<= {?End Date}
)
)

-LB

 

[agorjest]

lbass,
Thanks for your response. I tried your formula exactly as you wrote it but it returned no records. Looking back at my original post, I think I forgot the if and else around the {workorder.worktype} in ["PM"] line. Not sure it would make any difference, but here is the correct selection formula I was using:


if (isnull ({workorder.worktype}) or {workorder.worktype} in ["AD","CC","CG","CM","EM","EV"]) then
(
({workorder.status} in ["COMP", "CLOSE"]
and
({workorder.reportdate}>= {?Start Date} and
{workorder.reportdate}<= {?End Date}) and
({workorder.actfinish} >= {?Start Date} and
{workorder.actfinish}<= {?End Date}))
or
({workorder.status} in ["INPRG", "PENDING", "WSCH", "WMATL", "APPR", "WAPPR", "WPCOND"]
and
{workorder.reportdate}>= {?Start Date} and
{workorder.reportdate}<= {?End Date})
)
else
if {workorder.worktype} in ["PM"] then
(
({workorder.status} in ["COMP", "CLOSE"]
and
({workorder.targstartdate}>= {?Start Date} and
{workorder.targstartdate}<= {?End Date}) and
({workorder.actfinish} >= {?Start Date} and
{workorder.actfinish}<= {?End Date}))
or
({workorder.status} in ["INPRG", "PENDING", "WSCH", "WMATL", "APPR", "WAPPR", "WPCOND"]
and
{workorder.targstartdate}>= {?Start Date} and
{workorder.targstartdate}<= {?End Date})
)

 

[lbass]

if isnull ({workorder.worktype}) or
trim({workorder.worktype})="" or
{workorder.worktype} in ["AD","CC","CG","CM","EM","EV"] then
(
(
{workorder.status} in ["COMP", "CLOSE"] and
{workorder.reportdate}>= {?Start Date} and
{workorder.reportdate}<= {?End Date}) and
{workorder.actfinish} >= {?Start Date} and
{workorder.actfinish}<= {?End Date}
) or
(
{workorder.status} in ["INPRG", "PENDING", "WSCH", "WMATL", "APPR", "WAPPR", "WPCOND"] and
{workorder.reportdate}>= {?Start Date} and
{workorder.reportdate}<= {?End Date}
)
)

else

if {workorder.worktype} = "PM" then
(
(
{workorder.status} in ["COMP", "CLOSE"] and
{workorder.targstartdate}>= {?Start Date} and
{workorder.targstartdate}<= {?End Date}) and
({workorder.actfinish} >= {?Start Date} and
{workorder.actfinish}<= {?End Date}
)
or
(
{workorder.status} in ["INPRG", "PENDING", "WSCH", "WMATL", "APPR", "WAPPR", "WPCOND"] and
{workorder.targstartdate}>= {?Start Date} and
{workorder.targstartdate}<= {?End Date}
)
)

All I did the first time around was get rid of the if/then and use and/or logic, but here is the same thing with if/then syntax. You refer in your last post to it being the "correct" result--but it isn't working properly, so I think you need to explain what you are now getting and whether any other fields can be null.

-LB

 

[agorjest]

Got it to work. Thanks LB!
It was the "trim" function that did it. Do you recommend using "trim" in conjunction with isnull whenever I need to test for nulls?

 

[lbass]

Trim() was in my first suggestion, too. If trim() is what is making the difference then your field is not null or not null all of the time--instead it is blank, or contains a space. Trim removes the space, leaving "", the equivalent of a blank. A null is nothing--not even "".

-LB

 

[agorjest]

That's interesting LB, because I've gotten away with just an isnull expression when dealing with that field before. It is a 5 character string field in which many of the codes are only 2 letters plus 3 blank spaces. But since I haven't needed the trim function before, could it be the nature of this specific formula that is causing CR not to see the field as nothing (null), but rather as a blank space? Just curious. Thanks again for your help.

 

[lbass]

If that's true, you should be using trim like this also:

trim({workorder.worktype}) in ["AD","CC","CG","CM","EM","EV"]

...although maybe the "in" allows it to work anyway.

You should go into file->report options and see if "convert nulls to default values" is checked--this would convert null to "" for a string.

-LB

 

[agorjest]

I have the "Convert nulls to default values" checked for every report. Never seems to work properly, though. Null fields always seem to be a pain in CR.

Is there a difference between "in" and "=" in Crystal Syntax?

 

[lbass]

Yes. "AB" is "in" "ABC" or "AB ", but not equal to either.

-LB

 

[kenhamady]

If you are doing a 'between' or 'is one of' comparison then IN and = work the same. So in this example:

trim({workorder.worktype}) in ["AD","CC","CG","CM","EM","EV"]

or this example:

{order.amount} in 2 to 10

you could use an = instead of the 'in' and get the same result. But when there is a single literal on the right IN means 'is contained within' while = means is exactly equal to.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/