Time in each status in a non-linear process

[Jessica_Chung]

Our Airtable tracks the progress of legal documents through client submission, business team review, legal team review, client review, then completed. We want accurate reporting on the cumulative number of days that a document sits in each status. The challenge is that our process is NOT linear - documents may go to legal team review status, then to client review status, then back to legal team review for a few more days. We can’t anticipate how much back & forth will take place, so we can’t iron out the process to be linear with additional status columns.

When we use automations to timestamp each status change using the Last Modified Time field and the DATETIMEDIFF formula, we get incorrect data, since a second move to legal team review status, for example, will overwrite the first date(s) that the record was in legal team review.

[augmented]

Hi Jessica. I have the outline of an idea of how to get where you want to go. I’ll leave the devil’s details to you.

I would create another table that records the handoffs of a document (or set of) between the client and teams. After designing the table with something like 1) link to master record(s) in your current workflow (? - you must decide the granularity), 2) reviewer (biz, legal, client), 3) received date, 4) handoff/complete date, and 5) status (working, send to biz, send to legal, send to client, complete).

I would use the status field to trigger (the send options) an automation that would create a new record when the documents need to be in “someone else’s court”. In the new record, you need to fill in the appropriate reviewer, received date, link to master record, and status (working) In the triggering record, make sure you update the status to complete and the handoff/complete date to TODAY(). You can also use these automations to notify team leaders that they have the ball. Or post msgs to slack channels or whatever. [ALL OF THIS WITHIN THE AUTOMATIONS]

This way, every transaction, has two dates that you can do differences on the dates and rollup for some summary stats. You are also not limited by the number of “handoffs” between groups.

I hope this rough idea helps you.

[Justin_Barrett]

Welcome to the community, @Jessica_Chung! :grinning_face_with_big_eyes: Another way to approach this would be to trigger an automation on each status change, assuming that you’re using a single select field as the status indicator. The automation would run a script that tracks the transition of the document between status points, stores the aggregated data as JSON in a text field (which can remain hidden), and also outputs a summary in another text field.

For example, the first time a script runs upon a document being set to the first status level, it would record a timestamp entry for that value, along with setting an indicator that this is the current status. Each subsequent status change would capture both the end of one status and the beginning of the next with the same timestamp, along with changing the current-status indicator. The design of such a script could be very similar to one that I wrote a while back as part of (ironically enough) a time tracking system that works across multiple independent records. If you’d like to explore this options, message me and we can see what it would take to adapt to your use case.