Base Design: Logging time stamps for multiple status changes on a single property

[olireynoldson]

Hi everyone,

Background:

I'm in the process of creating a base which is to be used by our company and an external company to share information and update the status of job packs for solar installations.

Scenario

I have a single select field called "Job Pack Review Status" which contains the properties "Awaiting Review", "NMI" and "Approved". I want to keep track of the time when the external company changes the status to either "NMI" or "Approved" by logging a time stamp. However, there are scenarios where if the job pack may be rejected (NMI) and then resubmitted after being updated. In this instance the status would change back to Awaiting review before the external company makes an update. In this scenario I'd like to keep a second timestamp when the property is updated for the second time. 

 Question

Is it possible to log multiple time-stamps in separate columns based on the status of a single select field changing multiple times, or should we be splitting this into a separate single select field for each review. Eg. Job Pack Review_1, Job Pack Review_2 etc... My fear is that having multiple different columns for each of the fields runs the risk of the wrong status being changed.

Any guidance would be welcomed.

[TheTimeSavingCo · Accepted solution]

What if instead of either of those options, you just created one new record every time the status changed?

So you'd have a table called "History" or something, and whenever a status changed you'd have an automation create a new record in there to log:
1. The new status
2. The time of the change
3. Whatever else you want, e.g. who changed it

[olireynoldson]

@TheTimeSavingCo That's an interesting way of going about it. Let me give that a go.

I had thought of having a new submission every time a job pack was rejected and then just having a last updated timestamp for each. I'd then link all the submissions for the back to the main project to count the number of submissions and avg response time. However, I would loose the comments and it would be harder to track the current status.

[olireynoldson]

@TheTimeSavingCo This might warrant a new thread, so apologies ahead if I've overstepped.

Would there be a way to record the status change that took place as opposed to the latest status of the record? 

Additionally, would this method (separate table) also be needed to record the time when an update to the job pack was then submitted. As we'd like to calculate the time between each submission and review. I'm unsure which table would be best to calculate this.

eg. Job Pack Submission History creating the log on each change and then Job pack Review History creating the log on each status change. And then somehow linking these to record the time difference.

[TheTimeSavingCo]

> Would there be a way to record the status change that took place as opposed to the latest status of the record?
Hmm...possibly.  You could create a new field called "Current value" or something and have an automation that would trigger whenever the status was updated.

Its actions would be to:
1. Create a new record inside the "History" table
  - Log the new status
  - Log the value inside the field "Current Value" mentioned above
2. Update the original triggering record by pasting the new status into the "Current Value" field

This way you would be able to see what the status was changed from and to, which I think is what you're looking for?

> Additionally, would this method (separate table) also be needed to record the time when an update to the job pack was then submitted. As we'd like to calculate the time between each submission and review. I'm unsure which table would be best to calculate this.

Hmm, in addition to the "Current Value", what if you also created a date field for "Current Time", and then the idea would be you could log the time of the initial status setup, and then log the time of the change in status

With reference to the previous points, you'd end up with a record in "History" with:
1. The original status
2. The new status
3. The time the original status was set
4. The time the new status was set

And so you could then create a formula field that would find you the time difference between data 3 and 4 above?