Skip to main content
Question

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

  • Dec 5, 2023 11:00 PM UTC
  • 0 replies
  • 117 views
Forum|alt.badge.img+6

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
------------------------------
    This topic has been closed for replies.

    Forum|alt.badge.img+9
    • ChayceDuncan
    • Qrew Colonel
    • Dec 5, 2023 11:29 PM UTC

    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
    ------------------------------

    Terms & ConditionsAccessibility statement