Using Pipelines to generate a Historical Status table with a start and end date/time for status changes

Question

Hi, I'm struggling with something - hoping for some guidance.
I have a parent table with a Status field (New, Pending, Closed).&nbsp; When the record is created, a pipeline triggers to capture the Status, and the Date/Time in a realted Child Table.&nbsp; I am capturing every status change in the same way so I have a history.
In the Child Table I'd like to calculate how long each step takes.&nbsp; I have the Step Date/Time as a starting value, but how can I capture the End Step Date/Time?&nbsp; This would be the same value as the following record's Step Date/Time, but I'm not sure how to get that value into the previous record.
Any suggestions?

------------------------------Troy MacPherson------------------------------

chayceduncan · Answer

Will it be an issue if you don't have history? If you're ok with this going forward - a simple solution would be to:&nbsp;

In your relationship between parent and status changes - summarize the MAX record ID# of your status change.
In your child table - create a new relationship to itself ( a looped relationship ) and rename the related field to 'Related Next Status Change'
In your Pipeline - when you create the status change record - include the value from (1) and push into into the related field from (2).&nbsp;
In your relationship from (2) - add a summary field of 'Date Created' and rename the field to 'Next Status Change'.
Make a formula that does [Next Status Change] - [Date Created]

In effect what you're doing is directly relating to status changes together to where you can summarize when it leaves that status - and then calculate the gap between when it entered and then left that status.&nbsp;
------------------------------Chayce Duncan------------------------------

troymacpherson · Answer

Great idea - I'll try that!------------------------------Troy MacPherson------------------------------

Forum Discussion

Using Pipelines to generate a Historical Status table with a start and end date/time for status changes