I am trying to keep track of elapsed days taken between each stage in a case file.
The two relevant, related tables, (and salient) fields are:
Pk_ComplaintID (Serial Number)
DateStart (Auto enter, DATE)
CreateDate (Auto enter, DATE)
There’s a one to many relationship, one Complaint that could have many ComplaintDocs
I would like to be able to calculate the working days elapsed between ComplaintDocs for each Complaint.
I have explored GetNthRecord, however, to no avail. As the previous ComplaintDocs record may not belong to a particular Complaint.
I guess that I would need to find only related records then use the last and previous one.
I’m not a complete newbie I have managed to calculate elapsed working days between the CreateDate of the Complaint and Close date of the Complaint, (or indeed elapse days, to date, if the case isn’t closed)
Let (CorrectDate = If (IsEmpty(DateEnd); Get (CurrentDate); DateEnd);
5 * Int ( ( CorrectDate - DateStart) / 7 ) +Middle ( "0012345501234544012343340123223401211234010012340" ;
7 * (DayOfWeek ( DateStart ) - 1 ) + DayOfWeek ( CorrectDate ) ; 1 )
&" Days ")
Any assistance to help me calculate the days elapsed between related docs would be really gratefully received.
I often think of things visually, it seems easier to explain!