Filter Selection Formula - Using IF

[dcrosier]

I have created the following formula to filter my data out. What I am attempting to do is to show the job only if there is a current problem. I have setup the formula to reverse the workflow. ie. The last task to be completed is tested first.

if {FactSched} < currentdate or {FactSched} < {FactStart}then true
	08/02/01	08/14/01	08/02/01	07/27/01

else if {CutSched} < currentdate or {CutSched} < {CutStart}then true
	07/25/01	08/14/01	07/25/01	07/31/01

else if {MtlSched} < currentdate or {MtlSched} < {MtlStart}then true
	07/18/01	08/14/01	07/18/01	07/24/01

else if {EngSched} < currentdate or {EngSched} < {EngStart}then true
	No Date	08/14/01	No Date	No Date

else if {PurchSched} < currentdate or {PurchSched} < {PurchStart}then true
	05/17/01	08/14/01	05/17/01	06/06/01

The dates are there so that you can see the data. My problem is that this job is showing up on the report and I don't understand why.

HELP!!!!

 

[kenhamady]

Filter usually means the selecton formula, which selects records that are TRUE. This record is showing up because it qualifies for the first test of the first rule, giving you a TRUE and then stopping.

08/02/01 is less than 08/14/01 Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[dcrosier]

How can I get it to test the second half of the First test?

I need it to test to see whether the Scheduled Start is less than the Current Date OR whether the Scheduled Start is less than the Actual Start.

This should be done for EACH Work Center. So that the Job shows up only when it is late. This way Jobs fall on and off the report as Work Centers get back on track.

Any suggestions?

Thanks.
Dawn

 

[kenhamady]

OR means it can meet either test to qualify. If it can meet either test then it doesn't have to look at the second test when it meets the first test.

If you want records that meet both tests, use AND not OR. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[dcrosier]

I have tried that. Unfortunately it is still returning jobs to the report whose Actual Date is less than the Scheduled Date.

I have also tried reversing the order of the test. I tested the Scheduled vs. Actual Date before comparing to the Current Date.

These jobs just REALLY want to be on the report.

Any other thoughts? I really appreciate the help.

Dawn

 

[kenhamady]

In your first post example, if you replace all of the ORs with ANDs, does the record that you have used as an illustration show up? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[dcrosier]

Yes, I have replaced all the OR's with AND's and the illustrated record still comes back to haunt me.

Second thing I tried was to Change all the Or's to AND's and reverse the order of the test:

IF FactSched < FactStart AND FactSched < CurrentDate then true
else if ...

Will it help to know that I am working with 8.5? I wouldn't have thought that would make a difference, but you never know do you?

Any other thoughts?
Dawn

 

[kenhamady]

This formula is in the record selection formula or in a formula field?

If this is the selection formula (as it should be), try just the following as a selection formula:

{FactSched} < {FactStart} and see if this record still comes up. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[dcrosier]

Yes, That gets rid of the record. It cut my report down from 8 pages to 2.

Yes, this formula is in the Selection Editor. One thing I did notice is that even though I placed the initial formula on a field called Job - That field is no longer in the list. All the fields in the formula are listed instead.

Another thing I have tried - is to group on Job and then place the formula on the Group Selection. I could not tell a difference between the two options.

Are you sick of me yet?

Dawn

 

[kenhamady]

I have no idea what this means:

&quot;&quot;... I placed the initial formula on a field called Job - That field is no longer in the list. All the fields in the formula are listed instead. &quot;

I would now rebuild the formula one line at a time, testing after each line, and see when the bad records start to return. That will tell you where the problem is. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[FoxG]

Hi Dawn.

Just a quickie...

if your using nested ifs, are you enclosing each clause with brackets?

Ie.

If a=b then (if c=d then do_this else do_that)
else do_the_other

Crystal can get confused easily if brackets are missing

Geoff