Creating a custom calculated field for the date status is changed so we can report on it.

[CarriTi]

I am just starting of working with calculated fields so I am still trying to figure this out. I have done a lot of digging on this site to see if I can find the answers I'm looking for but I can't come up with the right custom expression I am looking for. Here is what I am trying to do. I would like to show the date status of a request is changed on a report so we are able to see when it reached each step of the process. I found this expression IF(Status='INP',IF(ISBLANK({DE:Status Timestamp Custom Field}),$$NOW,{DE:Status Timestamp Custom Field}),{DE:Status Timestamp Custom Field}) in one of the training materials and I feel like this is what I am looking for but when I try to create the field I get an error that it is not correct. I know I need to change the status key to match our keys. Any idea why? Any other helpful tips for someone just starting to dabble in calculated fields.

[Richard_Le_]

Hi Carri,

A common mistake that people make (myself included!) is that they forget to create and save the custom field before adding any calculations. Since the expression you are using is referencing itself (i.e., DE:Status Timestamp Custom Field), you have to make sure that the field has been committed to the database before it can referenced in any expression.

To use that particular expression you will need to:

1. Create a calculated field on your custom form called 'Status Timestamp Custom Field', ensure that the field type is set to Date or Date/Time.
2. Save and close the custom form (before you add the expression), this will commit the custom field to the database so that it can be referenced in any calculations.
3. Re-open the custom form and add the expression to the calculation field. You should now not get any error messages.
4. Save and close the form and you're done! Whenever this form is attached to a task, the field will be populated with the date / time that the task was first set to In Progress status

Hope this helps!

Best Regards,

Rich.

[CarriTi]

Hi Rich,

Thank you so much for your reply. That helped me out a ton. I do have another question I'm hoping you might be able to answer. Is there a way to make the date stick or not go away when the status changes to something new? In our report, we want to be able to see when the item went to SME review and then to final review (both of which are their own status? Any tips?

Thank you again for the help,

Carri Tiedt

[Richard_Le_]

Hi Carri,

No problem at all.

If you implemented the calculation as per your original post, the date will not change or go away when the status is changed. The date that the status was first changed to in progress should persist in that field. If the date in this field is dissapearing after a status change, then something is wrong with the calculation you have entered.

If you are wanting to track the dates for two different statuses, I can offer two suggestions:

1. Create another calculated field that is the same as original 'status timestamp' field, but reference the status code of the other status you want to track. You will now have two different fields that will capture the dates of each status
2. You use the Targeted Auditing method (bit more advanced) - with this field you can create an audit record of all status changes / dates etc., in the same field

Hope that helps! Let me know if you have any questions.

Best Regards,

Rich.