Forum Discussion

TroyMacPherson's avatar
TroyMacPherson
Qrew Cadet
5 months ago

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

Hi, I'm struggling with something - hoping for some guidance.

I have a parent table with a Status field (New, Pending, Closed).  When the record is created, a pipeline triggers to capture the Status, and the Date/Time in a realted Child Table.  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.  I have the Step Date/Time as a starting value, but how can I capture the End Step Date/Time?  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
------------------------------

2 Replies

Sort By
  • ChayceDuncan's avatar
    ChayceDuncan
    Qrew Captain
    5 months ago

    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: 

    1. In your relationship between parent and status changes - summarize the MAX record ID# of your status change.
    2. In your child table - create a new relationship to itself ( a looped relationship ) and rename the related field to 'Related Next Status Change'
    3. In your Pipeline - when you create the status change record - include the value from (1) and push into into the related field from (2). 
    4. In your relationship from (2) - add a summary field of 'Date Created' and rename the field to 'Next Status Change'.
    5. 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. 



    ------------------------------
    Chayce Duncan
    ------------------------------

    • TroyMacPherson's avatar
      TroyMacPherson
      Qrew Cadet
      5 months ago

      Great idea - I'll try that!



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