Making a Case statement work for Overdue jobs.

[carlstarus]

I am creating a database for a Landscape contractor business. ( my brother's )

I have Job table with a "date_start_job" field, a "date_end_job" field, and also a "duration_job" calculation field which is ( "date_start_job" - "date_end_job" ) .

There is also a number field called "estimate_duration_job".

What I want to do is have a message appear that shows all jobs which are overdue.

Overdue jobs are jobs where the "date_end job" is bigger than the ("date_start_job" + "estimate_duration_job") but also

where the current date is bigger than ("date_start_job" + "estimate_duration_job")

I created the following calc. but unfortunately it doesn't do the job. My problem is that I can get it to calculate if the "date_end_job" is populated, but if this field is empty it will not calculate. See calculation :

Case ( duration_job > duration_estimate_job ;

"This Job has overrun by " & duration_job - duration_estimate_job & " " & "Days" ;

)

I tried this other calculation and got wrong results as well

Case (

IsEmpty ( date_end_job ) ;

and

(date_start_job + duration_estimate_job ;> ( Get ( CurrentDate );

"This Job has overrun by " & duration_job - duration_estimate_job & " " & "Days"

)

) )

Any help is greatly welcomed.

Edited January 11, 2009 by Guest

[comment]

Try:

Let ( [

end = Case ( date_end_job ; date_end_job ; Get (CurrentDate) ) ;

dur = end - date_start_job

] ;

Case (

dur > duration_estimate_job ;

"This job has overrun by " & dur - duration_estimate_job & " days"

)

)

---

Make sure the calculation is unstored, so that it updates with current date.

Edited January 11, 2009 by Guest

[carlstarus]

Thank you very much for your reply. I am realy gratefull for your help. It worked like a charm.

[LaRetta]

Let ( [

jobDone = NOT IsEmpty ( date_end_job ) ;

daysOver = Case ( jobDone; date_end_job - date_start_job - estimate_duration_job ; Get ( CurrentDate ) - date_start_job - estimate_duration_job )

] ;

Case ( daysOver > 0 ; "This Job " &

Case ( jobDone ; "has overrun " ; "is overrunning " ) & "by " & daysOver & " Day" & Case ( daysOver > 1 ; "s" )

)

)

Edited January 11, 2009 by Guest

[LaRetta]

That's what happens when I take break before responding! Well, I'm leaving mine, Michael, because yours doesn't display nicely if there is only 1 day. ROFLMAO!!

UPDATE: Oh, and I ditched the calculation of actual duration because I suspected it wasn't necessary by itself and I dislike calcs whose only purpose is to be used in another calc. I might be wrong here however ...

2ND UPDATE: And Michael's calc is prettier in general but I would certainly correct the text portion. :wink2:

Edited January 11, 2009 by Guest
Added update and then 2nd update

[carlstarus]

Thank you for trying to help, but the calc. :

Let ( [

jobDone = NOT IsEmpty ( date_end_job ) ;

daysOver = Case ( jobDone; date_end_job - date_start_job ; Get ( CurrentDate ) - date_start_job - estimate_duration_job )

] ;

Case ( daysOver > 0 ; "This Job " &

Case ( jobDone ; "has overrun " ; "is overrunning " ) & "by " & daysOver & " Day" & Case ( daysOver > 1 ; "s" )

)

)

didn't realy work, because it seems to ignore the end date when this is entered.

[LaRetta]

I had corrected my calc. YOu might check it again.

Comment's calc is actually better but I wouldn't want to modify his to include my text result (which I think IS better). :blush2:

Edited January 11, 2009 by Guest

[carlstarus]

I have attached a copy of the DB for you to check is you could. The calc doesn't seem to update.

Thank you for your help.

[LaRetta]

No attachment? I'll attach mine. I suspect that you missing this part:

Make sure the calculation is unstored, so that it updates with current date.

I suppose I should have repeated it but when I saw it on his calc, I assumed you understood that, since Get ( CurrentDate ) was in the calculation, it would need to be unstored.

If I'm still missing something, please let us know. :wink2:

Jobs.zip

[carlstarus]

You are completely right of course. Thank you! It works beautifuly.