AnsweredAssumed Answered

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

Outcomes