Track Days in Each Status from a Quickbase Status Log

A project log table is one of the most useful things you can add to a Quickbase app. Every time a project changes status, a new log record gets created with the status and a Start Date. After a few months, someone always asks the obvious question: how long did each project spend in each status?

That's the question in this Quickbase Community thread. The asker wanted the number of days between one log record's Start Date and the next one's, and guessed it could be done with a report formula. It can't really. A formula on one record can't see "the next record" unless you give it a way to. You could get there with formula queries, but they get slow and fiddly on a busy log table.

The approach below is simpler and fast: link each status record directly to the one before it, and have a pipeline stamp an End Date on the old record when a new one shows up. After that, days in status is a one-line formula and the reporting is a plain summary report.

How it works

There are four moving parts:

1. Projects knows its latest log record. A summary field on Projects, Maximum of Record ID# of its log records, always holds the newest log record's ID. Record IDs only go up, so the highest one is the latest.
2. A new log record remembers the one before it. When you add a status record, you copy that "latest" ID into a numeric Previous Log ID field before the record is saved. At that moment the latest log record is still the previous status.
3. The previous record gets closed. A pipeline runs when the new record is created, finds the record in Previous Log ID, and sets its End Date to the new record's Start Date.
4. Every record can work out its own duration. End Date minus Start Date, or today minus Start Date if the status is still open.

No formula queries anywhere. Each record only ever looks at itself.

What you need before you start

• A Projects table and a Project Status Log table, related one-to-many (one project, many log records). Use your own table names. The steps are the same.
• On the log table: a Status field, a Start Date (Date) field, and the relationship's reference field to Projects (I'll call it Related Project).
• Ideally, status records get added through a form, for example from an "Add Status" button on the project. If records come in through imports or other pipelines, see the section on records that skip the form near the end.

Step 1: Add a Latest Log ID summary field on Projects

1. Open the Project Status Log table and go to Settings > Table-to-table relationships.
2. Open the relationship to Projects.
3. Under summary fields, click Add a summary field.
4. Choose Maximum of Record ID# and name it Latest Log ID.

On a project with log records 6, 7, 8 and 9, Latest Log ID shows 9.

Step 2: Look it up into the log table

In the same relationship, add a lookup field and pick Latest Log ID. Quickbase will name it something like Project - Latest Log ID. This field now appears on every log record and shows the parent project's newest log ID.

On its own this field isn't enough. As soon as the new record is saved, it becomes the newest log record, so the lookup shows the record's own ID. When I tested this, every record showed its own Record ID# in the lookup right after saving. That's why the next step copies the value into a regular field while the record is still being added.

Step 3: Copy the value into Previous Log ID while adding

1. On the log table, create a Numeric field called Previous Log ID. It must be Numeric because it will hold a Record ID# and become a reference field in Step 4.
2. Put Project - Latest Log ID and Previous Log ID on the add form. You can hide them later with a form rule or make them read-only. They just need to be on the form for the rule to work.
3. Add a form rule:
   • When the record is being added, and Project - Latest Log ID is not blank
   • Then change Previous Log ID to the value in the field Project - Latest Log ID

While the form is open, the lookup still shows the old latest record (say 9), so 9 is what gets copied. Form rules re-check when field values change, so it still works if the user picks the project on the form instead of starting from the project's "Add Status" button.

The very first status record for a project has no previous record. Latest Log ID is blank for that project, so the rule does nothing and Previous Log ID stays empty. That's what you want.

Step 4: Relate the log table to itself

This step is optional, but it makes the chain easy to follow and gives you a clickable link to the previous status.

1. Go to Settings > Table-to-table relationships on the log table and click + New Relationship.
2. Choose the Project Status Log table as the parent and the child. Quickbase allows a table to be related to itself.
3. When asked for the reference field, don't take the new field Quickbase suggests. Choose your existing Previous Log ID field.
4. Add lookups of the parent's Status and Start Date, and rename them Previous Status and Previous Start Date so it's clear which end of the relationship they come from.

Now every log record shows what came before it, and the embedded report on a log record shows the status that came after it.

Step 5: Close the previous record with a pipeline

Now the old record needs its End Date. Build a pipeline in Quickbase Pipelines:

1. Trigger (A): Quickbase > Record Created, in your app's Project Status Log table. Add a condition so it only runs when Previous Log ID is not empty. Under the trigger's fields, include Start Date and Previous Log ID.
2. Step B: Quickbase > Look Up Record in the same table. Look it up by Record ID# = {{a.previous_log_id}}.
3. Step C: Quickbase > Update Record. Set the record to update to the one found in Step B, and set End Date = {{a.start_date}}.

The exact Jinja names depend on your field labels. Use the field picker in the pipeline designer and it will put in the right ones. You can also skip the Look Up step and point Update Record straight at Record ID# {{a.previous_log_id}}, but the lookup step makes it easy to see in the pipeline's activity log which record got closed.

If the status Start Date is always "the day the record was created," set the Start Date field's default value to Today so nobody has to type it.

Step 6: Add a Days in Status formula

On the log table, create a Formula - Numeric field called Days in Status:

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

• Subtracting one date from another gives you a duration, and ToDays() turns that duration into a number of days.
• If End Date is blank, the status is still open, so the formula counts up to today. The current status keeps growing until it gets closed.
• If your Start and End fields are Date / Time fields instead of Date fields, use Now() in place of Today() so you're subtracting the same kind of value. ToDays() still gives you days, including fractions of a day.

If your last status, such as "Done" or "Closed," should never count as time spent, leave it blank for that status:

If([Status] = "Done", null,
  ToDays(If(IsNull([End Date]), Today(), [End Date]) - [Start Date])
)

Step 7: Report on time in each status

Create a Summary report on the Project Status Log table:

• Group by: Status (in the order you like. A Status sort order field helps if you have one.)
• Summarize: Days in Status, with Average. Add a second column with Total if you want both.
• Filters: optional, for example only projects started this year, or only closed log records (End Date is not blank) if you'd rather not let open statuses pull the averages up.

You can also make a Table report grouped by Related Project to show one project's full timeline, or add a summary field on Projects ("Total days in Design") if you want the numbers on the project itself.

What it looked like when I tested it

I built this in a test app with two projects. Here's the Office Move Planning log after six status changes, with each End Date set to the next record's Start Date, exactly what the pipeline in Step 5 does:

And the summary report across both projects, grouped by Status with the average:

A couple of things I noticed while testing:

• The first record per project has no Previous Log ID, and nothing breaks. The pipeline condition skips it.
• Open statuses count up to today. The second project's "In Progress" record kept growing every day until a newer status was added. That's usually what you want on a dashboard, but filter on End Date is not blank if you only want finished statuses in your averages.
• Status records need to be added in order. The whole approach relies on the newest record having the highest Record ID#. If someone back-fills an old status change today, it will look like the latest one. If that happens a lot in your app, have users fix the Start and End Dates by hand on those records.

Records that skip the form

Form rules only run on forms. If log records are also created by imports, the API or another pipeline, Previous Log ID won't get filled in for those records. You can't fix this by reading the Latest Log ID lookup in the pipeline, because by the time the pipeline runs, the new record is already the latest.

Instead, look for the one log record on that project that is still open:

1. Trigger (A): Record Created in the log table.
2. Step B: Search Records in the log table where Related Project equals {{a.related_project}}, End Date is empty, and Record ID# is not equal to {{a.id}}.
3. For each record found (there should only be one), Update Record to set its End Date to {{a.start_date}}, then update record A so Previous Log ID = that record's Record ID#.

This version doesn't depend on the form at all, so it's a good choice if most of your status changes come from automations. Either way, you end up with the same chain of linked records and the same Days in Status formula.

Wrapping up

The trick is to stop asking each record to find its neighbor at report time, and instead store the link once, when the record is created. With one summary field, one lookup, a form rule, a small pipeline and a one-line formula, "how long did this project sit in Design?" turns into a summary report you can drop on any dashboard.