help with formula

[busi1]

I'm a using crystal reports 2008 & oracle 10g.

I'm grouping the report where the data from 2 different tables has to be displayed under the respective groups based on the logic.

Here's the formula

if ((DateAdd('d',30,{table1.APPDATE1}) < currentdate) or ({table2.DUEDATE2} <1 currentdate)) then "OverDue"

else if ( ( DateAdd('d',30,{table1.APPDATE1}) > currentdate and month(cdate(DateAdd('d',30,{table1.APPDATE1}))) = month(currentdate) )
or
( month(cdate({table2.DUEDATE2})) =month(currentdate) or month(cdate({table2.DUEDATE2})) > month(dateadd('m',1,currentdate)) )
) then "Due This Month"

else if ( ( DateAdd('d',30,{table1.APPDATE1}) > currentdate and month(cdate(DateAdd('d',30,{table1.APPDATE1}))) > month(currentdate) )
or
(month(cdate({table2.DUEDATE2})) > month(dateadd('m',2,currentdate))
) then "Due Next Month"
else "None"

The formula fails at the first 'OR' condition(only the if ((DateAdd('d',30,{table1.APPDATE1}) < currentdate) ).It displays the records under the 'over due' section and the rest of the records under a blank section(not even 'NONE' section).

 

[briangriffin]

This is amost always an issue with parentheses. Can't really test it, but try this:

if ((DateAdd('d',30,{table1.APPDATE1}) < currentdate) or ({table2.DUEDATE2} <1 currentdate)) then "OverDue"

else (if ( ( DateAdd('d',30,{table1.APPDATE1}) > currentdate and month(cdate(DateAdd('d',30,{table1.APPDATE1}))) = month(currentdate) )
or
( month(cdate({table2.DUEDATE2})) =month(currentdate) or month(cdate({table2.DUEDATE2})) > month(dateadd('m',1,currentdate)) )
) then "Due This Month")

else (if ( ( DateAdd('d',30,{table1.APPDATE1}) > currentdate and month(cdate(DateAdd('d',30,{table1.APPDATE1}))) > month(currentdate) )
or
(month(cdate({table2.DUEDATE2})) > month(dateadd('m',2,currentdate))
) then "Due Next Month")
else "None"

 

[busi1]

Thanks Brian, I tried it and it's still not working. One more thing not sure if this is the case. table2 is empty in the database (do you think as there is no data it's failing at first or condition) if so how can write to check if that column has no data and still go thru all the conditons in the control

 

[briangriffin]

See what this does for you - you'll have to replace my formula fields with the actual database fields.

Don't think the empty table 2 should affect this formula.

There was a problem in your first line:

...({table2.DUEDATE2} <1 currentdate)) then "OverDue"

Should be "... ({table2.DUEDATE2} < currentdate - 1)) then "OverDue"

if ({@AppDate} + 30 < currentdate  
or {@Duedate} < currentdate - 1)
then "OverDue"

else 

if ({@AppDate} + 30 > currentdate 
and month({@AppDate} + 30) = month(currentdate))
        
or 
          
(month({@Duedate}) = month(currentdate) 
or month({@Duedate}) > month(dateadd('m',1,currentdate)))    
         

then "Due This Month"

else "Other"

 

[busi1]

sorry Brian, I might have mistyped 1 in first line ...({table2.DUEDATE2} <1 currentdate)). There is no 1 in the line. I replaced you code with my dtabase fields and see the all the records are falling under overdue (some of the records are satisfying the 2nd condition and still seeing them under Overdue, not sure what's causing the problem.

 

[briangriffin]

You need to start piecing it together one block at a time. First comment out everything except this:

if ({@AppDate} + 30 < currentdate  
or {@Duedate} < currentdate - 1)
then "OverDue"

else 

"Other"

Then make sure all the Overdue records are being evaluated correctly.

Then add in the first half of the next block:

if ({@AppDate} + 30 < currentdate  
or {@Duedate} < currentdate - 1)
then "OverDue"

else 

if ({@AppDate} + 30 > currentdate 
and month({@AppDate} + 30) = month(currentdate))
then "Due This Month"

else "Other"

Make sure the additional records are being evaluated correctly, add the next block of code, etc. That way when you get an error it's usually easy to pinpoint the source.