Complex If Statement Problems 1

[wilekoyote76]

I'm working on a formula that is not calculating all the fields properly. What I am trying to do is calculate the days since the last action on a contact and below I have the code for it.

CONTACT2.UDATERECD=Date ticket was opened
CONTACT2.URIDATREC=Reinspect the ticket for problems
CONTACT2.URODATREC=Reopen the ticket for various reasons (not problem related)
CONTACT2.USTATREP=Status was updated on this date

Status date can be newer than RI or RO date.
RI or RO can be newer than Status date
Nothing newer than when ticket opened for daterecd field

I'm having a problem getting all the days since last action formula I created to calculate all the filtered records. Any suggestions? I've commented out the code that I originally had in. I am new to crystal.



If (not(isnull({CONTACT2.URIDATREC})) or not(isnull({CONTACT2.URODATREC}))) then
If not(isnull({CONTACT2.URODATREC})) then
If {CONTACT2.URODATREC} >= {CONTACT2.UDATERECD} then
If {CONTACT2.URODATREC} >= {CONTACT2.USTATREP} then
numberVar Days_Since_Action2 :=DateDiff ("d", {CONTACT2.URODATREC}, CurrentDate)
Else
numberVar Days_Since_Action2 :=DateDiff ("d", {CONTACT2.USTATREP}, CurrentDate)
Else -98
Else If not(isnull({CONTACT2.URIDATREC})) then
If {CONTACT2.URIDATREC} >= {CONTACT2.UDATERECD} then
If {CONTACT2.URIDATREC} >= {CONTACT2.USTATREP} then
numberVar Days_Since_Action2 :=DateDiff ("d", {CONTACT2.URIDATREC}, CurrentDate)
Else
numberVar Days_Since_Action2 :=DateDiff ("d", {CONTACT2.USTATREP}, CurrentDate)
Else -99;

If {CONTACT2.USTATREP} >= {CONTACT2.UDATERECD} then
numberVar Days_Since_Action :=DateDiff ("d", {CONTACT2.USTATREP}, CurrentDate)
Else numberVar Days_Since_Action :=DateDiff ("d", {CONTACT2.UDATERECD}, CurrentDate);


//Else
// If not(isnull({CONTACT2.URODATREC})) then
// If {CONTACT2.URODATREC} >= {CONTACT2.UDATERECD} then

//Else If {CONTACT2.USTATREP} >= {CONTACT2.UDATERECD} then
// numberVar Days_Since_Action :=DateDiff ("d", {CONTACT2.USTATREP}, CurrentDate)
// Else numberVar Days_Since_Action :=DateDiff ("d", {CONTACT2.UDATERECD}, CurrentDate);

//If {CONTACT2.URIDATREC} >= {CONTACT2.USTATREP} then
//numberVar Days_Since_Action :=DateDiff ("d", {CONTACT2.URIDATREC}, CurrentDate)
//If {CONTACT2.URODATREC} >= {CONTACT2.USTATREP} then
// numberVar Days_Since_Action :=DateDiff ("d", {CONTACT2.URODATREC}, CurrentDate)
//IF (isnull({CONTACT2.USTATREP})) then
// numberVar Days_Since_Action :=DateDiff ("d", {CONTACT2.UDATERECD}, CurrentDate);

 

[wilekoyote76]

If I do the following, it fixes all the field calculations for RI and RO dates and even sees the status changes for the calculation. It, however, does not calculate if there is No Status Date and also it doesn't calculate if it was just summarized.

If (not(isnull({CONTACT2.URIDATREC})) or not(isnull({CONTACT2.URODATREC}))) then
If not(isnull({CONTACT2.URODATREC})) then
If {CONTACT2.URODATREC} >= {CONTACT2.UDATERECD} then
If {CONTACT2.URODATREC} >= {CONTACT2.USTATREP} then
numberVar Days_Since_Action2 :=DateDiff ("d", {CONTACT2.URODATREC}, CurrentDate)
Else
numberVar Days_Since_Action2 :=DateDiff ("d", {CONTACT2.USTATREP}, CurrentDate)
Else -98
Else If not(isnull({CONTACT2.URIDATREC})) then
If {CONTACT2.URIDATREC} >= {CONTACT2.UDATERECD} then
If {CONTACT2.URIDATREC} >= {CONTACT2.USTATREP} then
numberVar Days_Since_Action2 :=DateDiff ("d", {CONTACT2.URIDATREC}, CurrentDate)
Else
numberVar Days_Since_Action2 :=DateDiff ("d", {CONTACT2.USTATREP}, CurrentDate)
Else -99;

ELSE
If {CONTACT2.USTATREP} >= {CONTACT2.UDATERECD} then
numberVar Days_Since_Action :=DateDiff ("d", {CONTACT2.USTATREP}, CurrentDate)
Else numberVar Days_Since_Action :=DateDiff ("d", {CONTACT2.UDATERECD}, CurrentDate);

 

[IanWaterman]

YOu might be better if you converted everything to local date vars

@last action
local datevar open;
local datevar reopen;
local datevar reinspect;
local datevar Status;

set each var

if isnull({CONTACT2.UDATERECD}) then
open:= Date(1900,01,01) else open:= {CONTACT2.UDATERECD};

repeat for each date and set default 01/01/1900 or 01/01/2100 to suit your logic. Don't for get the ; at then end of each statement.

Then perform your if then else on the vars

Ian

 

[GJParker]

Try this to give you your last date and then base your other calculations on the result.

Local DateVar LastDate;

If not(isnull({CONTACT2.URIDATREC})) Then
    LastDate := {CONTACT2.URIDATREC};

If not(isnull({CONTACT2.URODATREC})) and {CONTACT2.URODATREC} > LastDate Then
    LastDate := {CONTACT2.URODATREC};

If not(isnull({CONTACT2.UDATERECD})) and {CONTACT2.UDATERECD} > LastDate Then
    LastDate := {CONTACT2.UDATERECD};

If not(isnull({CONTACT2.USTATREP})) and {CONTACT2.USTATREP} > LastDate Then
    LastDate := {CONTACT2.USTATREP};

LastDate

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[IanWaterman]

Gary Obviously read your post correctly ;-). His solution is much better.

Ian