Hi, I have searched around for someone with a similar problem but can’t find exactly my issue, perhaps someone can help.
For work projects, we have an Airtable base to track the status and components of our collections (think fashion collection). Now, I want to track how long each collection dwells in each ‘status’ without having to input that information manually. We have a big team working together and each person will likely not remember every time to log how long they were working on their part of the project.
We have a status table with a single select field with options like ‘design’, ‘marketing’, ‘graphics’, etc.
I have a last modified field that tells us the last time the status field was changed. But I want a log of every time it was changed, and what it was changed to. I know I can look at the expanded record but that’s rather tedious.
So I’ve created a linked table called ‘Status Time Log’. I’ve set up an automation that creates a new record in that table when the Status field is changed on the Status table. It logs the time it was changed, but what I cannot figure out how to do is log what the status was changed to. My initial reaction was to create a lookup field in the ‘Status Time Log’ table that corresponds to the Status single select, but obviously the problem is this is continuously updating. If the record ‘status’ changes again, then it updates on all the logged items, which defeats the purpose of seeing when the status was changed from ‘design’ to ‘marketing’ (as an example). I need a way to log the status that is automatic once and then static. Any ideas? Thanks in advance!
