Getting timestamp from status changes
I have a couple calculated custom fields that I use for this, but they won't calculate what has happened in the past. Once they are set up, they will keep a running account of status changes and date, so once they're set up they're great as you move forward, but won't help with what has already happened.
I can get you the calculations if you're interested.
It's a two-step process with one calculated field to capture the current status, then a second field to capture that status, the date and any prior statuses/dates that were already in that field.
my first field is labeled: Current Status and has this calculation:
IF(Status="CUR","Production",IF(Status="GPO","HPF", IF(Status="PLN","Planning", IF(Status="EGZ","Scheduling", IF(Status="CPL","Completed", IF(Status="MRK","ProdBacklog",Status))))))
you'd need to substitute in the statuses you want to capture and their keycodes.
my second field is labeled: Status History and has this calculation:
LEFT(IF(LEFT(Status History,LEN(IF(ISBLANK(Current Status),"-",Current Status)))=Current Status, Status History,CONCAT(IF(ISBLANK(Current Status),"-",Current Status)," (",$$NOW,") | ",Status History)),2000)
the "2000" at the end of this calculation limits the field to 2000 characters.
They come out looking something like this:
Current Status
Production
Status History
Production (6/19/19) | HPF (6/19/19) | Planning (6/19/19) | Scheduling (6/19/19) | Production (5/31/19) | HPF (5/16/19) | Production (5/15/19) | Planning (5/13/19) | Production (4/25/19) | Completed (4/25/19) | Production (4/9/19) | Scheduling (4/9/19) | Production (4/8/19) | HPF (4/8/19) |
This project is one I do testing with so the status has jumped around a bit.
I know when you're creating a collection in a report column you can use "listdelimiter=<p>" to create line breaks. But I don't know how you would incorporate something like that in a calculated field.
I remember that bugger of a field... since the field references itself in the calculation, you'll need to create the field and save it before you can reference it in the calculation.
So create the field with no calculation, save your form. Then go back into the form and paste in the calculation.
