Forum Discussion

TroyMacPherson's avatar
TroyMacPherson
Qrew Cadet
4 months ago

Formula help - calculating duration between dates in separate rows of data

Hello, looking for help on the following problem.  I have a child table tracking step changes to a parent record.  Each Step change has a From Step, To Step and Step Date:

Record IDID NumberFrom StepTo StepStep Date
11001InitialSecondary1/1/2024
21001SecondaryTertiary1/3/2024
31002InitialSecondary1/15/2024
41002SecondaryTertiary1/17/2024

 

I am trying to calculate the time between steps by ID Number.  

I think I need some way to pull the Step date of the previous record related to the ID number into the prior row to run the duration calculation, but I'm not sure how best to do this. When its the last step related to the ID number, this value can be today().

Here is what I think I need a calculation to do:

Record IDID NumberFrom StepTo StepStep DateStep End Date
11001InitialSecondary1/1/20241/3/2024
21001SecondaryTertiary1/3/2024Today
31002InitialSecondary1/15/20241/17/2024
41002SecondaryTertiary1/17/2024Today

 

Any suggestions would be appreciated!

formulas
  • DonLarson's avatar
    DonLarson
    Qrew Elite
    4 months ago

    This is not an uncommon problem.     You can use a Pipeline to capture the Date Created of the new record and write it to the prior record.   

    In your example when RID 2 is created,  the Pipeline would search for RID 1 and then write the value of Step Date from 2 to Step End Date in 1.

    Now you just write a Formula Duration field in each record to calculate the difference between End and Start.

  • MikeTamoush's avatar
    MikeTamoush
    Qrew Commander
    4 months ago

    Formula Queries, as Mark mentioned, would be quite good at this. However, if you have a lot of records (like he also said), the query will either not work or stall your app. If you run into that, Dons way works but you could also create a relationship to itself, and then use the Pipeline to relate 1 record to the next. This way you could use lookup fields instead. A different way, but I am not sure if it's any better?