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
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
@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.
@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.
@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 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.
> 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?
