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

Ohh I like this solve, is there any way you can capture the users who completed the task?

We have tasks that touched multiple times in one master project. If we could capture the user of the current status would be awesome.

Hey all - we also have a helpful blog about using calculated fields to keep track of dates and details that might be useful in this use case as well.

Hi Jeremy,

For more on the subject, I invite you to read this Targeted Auditing Proof of Concept post.

Regards,

Doug