Skip to main content

Tip: Track days in each status by linking each status record to the one before it

  • Nov 21, 2026 2:40 AM UTC
  • 0 replies
  • 6 views

Top Step
Forum|alt.badge.img+12

“How long did this project sit in each status?” comes up a lot, and the usual first idea is a formula that compares one log record’s date to the next one. A record’s formula can’t see “the next record” on its own, and formula queries get slow on a busy log table.

What works well is linking each status record to the one before it, then closing the old record when a new one shows up.

On the relationship from the Status Log table to Projects, add a summary field: Maximum of Record ID#, called Latest Log ID. Record IDs only go up, so this is always the newest log record. Look it up back into the log table.

On the log table, add a Numeric field called Previous Log ID and put both fields on the add form. Add a form rule: when the record is being added and the Latest Log ID lookup is not blank, change Previous Log ID to the value in that lookup. While the form is open the lookup still shows the old latest record, so that’s the ID that gets saved. The first status record for a project just stays blank.

Then a pipeline: Record Created on the log table, with a condition that Previous Log ID is not empty, and an Update Record step on Record ID# = Previous Log ID that sets End Date to the new record’s Start Date.

Now each record can work out its own duration:

ToDays(If(IsNull([End Date]), Today(), [End Date]) - [Start Date])

A summary report grouped by Status, averaging or totaling that field, answers the original question with no formula queries. If you want a clickable link to the previous status, relate the log table to itself using Previous Log ID as the reference field.

Full walkthrough with screenshots of each step: https://www.topstep.tech/blog/quickbase-days-in-each-status-log