# Calculate elapsed days using related records

Question asked by EssexBiker on Oct 20, 2017
Latest reply on Oct 20, 2017 by EssexBiker

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:

COMPLAINTS

Pk_ComplaintID (Serial Number)

DateStart (Auto enter, DATE)

DateEnd (Date)

ComplaintDocs

Pk_ComplaintEventID

CreateDate (Auto enter, DATE)

fk_ComplaintID

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!

 ComplaintID ComplaintDoc ID1 ID1 ID2 ID2 ID3 ID3 ID4 ID4 ID5 ID6 ID7 ID8 ID9 ID10