TroyMacPherson
24 days 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 ID ID Number From Step To Step Step Date 1 1001 Initial Secondary 1/1/2024 2 1001 Secondary Tertiary 1/3/2024 3 1002 Initial Secondary 1/15/2024 4 1002 Secondary Tertiary 1/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 ID ID Number From Step To Step Step Date Step End Date 1 1001 Initial Secondary 1/1/2024 1/3/2024 2 1001 Secondary Tertiary 1/3/2024 Today 3 1002 Initial Secondary 1/15/2024 1/17/2024 4 1002 Secondary Tertiary 1/17/2024 Today

Any suggestions would be appreciated!

### 3 Replies

• DonLarson
Qrew Commander

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.

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