Custom field track latest date/time & user to bring into report

[Vin01]

I have a custom text field and I wanted to create a calculated field that will track the last time a change was made to that field. Looking to capture date/time and if possible the user that made the change. 

We have system tracking turned on for this field, but not sure if I can pull in the latest update into a task report. 

Thanks,

Vinay

[Madalyn_Destafney]

Hi there! You should be able to create a journal entry report for this - you can get pretty detailed there to track status changes, text changes in a field, etc.

[Vin01]

Hi Madalyn, thanks for the quick reply.

Yeah I'm aware of the journal report. But they want to be able to see this within a task report. So that's why I'm wondering if a calculated field can capture this so that I can bring that field into the report. 

Thanks,

Vinay

[Madalyn_Destafney]

You can add a Last Updated By name column into your task report (and a Last Update Date), but it would show the last time any update was made on that task, not necessarily the specific task field...
OR
You can create a journal entry report that is filtered only to the custom field you're after, and have a column with Task Name. So basically turn your journal entry report into a task report.

[AndrewKo]

Hi Vin01, 

You can leverage a Journal report to monitor for field changes. The Journal report will enable you to capture the field's old value, new value, the date the field was updated, and the user that made the update. 

Thank you,

Andy Koprowski

[Jason_JB · Accepted solution]

Hi Vinay -

At one point we had a similar need to track the prior values of a field, and I needed to be able to pull it into reports that included additional data - essentially ruling out journal entry reports.

@Doug_Den_Hoed__AtAppStore helped me work through a couple iterations of calculated fields to track the data. I think the same concepts should work with who made the edit. I have found the last modified by that Workfront captures to be less than ideal because system jobs, fusion, and bulk updates can cause an admin to be linked as the last updating user.

We've had some business process changes, so we aren't currently using the fields - meaning it's possible some recent WF upgrades may require some tweaks to the formula, but I think the concept still works.

I have fields that answer:

1. what was the last value

2. last change date

3. a text based journal of just that field

Here are the formulas of the calculated fields:

last value (DE: Pi Health Score is the field being tracked; DE: Pi Health Score - Prior is the field it is being tracked in)

IF(LEFT(IF(ISBLANK({DE:Pi Health Score}),IF(ISBLANK({DE:Pi Health Score - Prior}),"",

LEFT(IF(LEFT({DE:Pi Health Score - History}, IF(ISBLANK({DE:Pi Health Score}),0, SUM(SEARCH("(",{DE:Pi Health Score - History}), -1))) =ROUND({DE:Pi Health Score},2),{DE:Pi Health Score - History},CONCAT(IF(ISBLANK({DE:Pi Health Score}),"-",ROUND({DE:Pi Health Score},2))," (",$$NOW,") | ",{DE:Pi Health Score - History})),1000)),LEFT( IF(LEFT({DE:Pi Health Score - History}, IF(ISBLANK({DE:Pi Health Score}),0,SUM(SEARCH("(",{DE:Pi Health Score - History}), -1))) =ROUND({DE:Pi Health Score},2),{DE:Pi Health Score - History},CONCAT(IF(ISBLANK({DE:Pi Health Score}),"-",ROUND({DE:Pi Health Score},2))," (",$$NOW,") | ",{DE:Pi Health Score - History})),1000)),2)={DE:Pi Health Score},{DE:Pi Health Score - Prior},{DE:Pi Health Score})

last change date (Pi Health Score - Last Change Date)

IF(LEFT(IF(ISBLANK({DE:Pi Health Score}),IF(ISBLANK({DE:Pi Health Score - Prior}),"",

LEFT(IF(LEFT({DE:Pi Health Score - History}, IF(ISBLANK({DE:Pi Health Score}),0, SUM(SEARCH("(",{DE:Pi Health Score - History}), -1))) =ROUND({DE:Pi Health Score},2),{DE:Pi Health Score - History},CONCAT(IF(ISBLANK({DE:Pi Health Score}),"-",ROUND({DE:Pi Health Score},2))," (",$$NOW,") | ",{DE:Pi Health Score - History})),1000)),LEFT( IF(LEFT({DE:Pi Health Score - History}, IF(ISBLANK({DE:Pi Health Score}),0,SUM(SEARCH("(",{DE:Pi Health Score - History}), -1))) =ROUND({DE:Pi Health Score},2),{DE:Pi Health Score - History},CONCAT(IF(ISBLANK({DE:Pi Health Score}),"-",ROUND({DE:Pi Health Score},2))," (",$$NOW,") | ",{DE:Pi Health Score - History})),1000)),2)={DE:Pi Health Score},{DE:Pi Health Score - Last Change Date},$$NOW)

Change History (Pi Health Score - History)

IF(ISBLANK({DE:Pi Health Score}),IF(ISBLANK({DE:Pi Health Score - Prior}),"",

LEFT(IF(LEFT({DE:Pi Health Score - History}, IF(ISBLANK({DE:Pi Health Score}),0, SUM(SEARCH("(",{DE:Pi Health Score - History}), -1))) =ROUND({DE:Pi Health Score},2),{DE:Pi Health Score - History},CONCAT(IF(ISBLANK({DE:Pi Health Score}),"-",ROUND({DE:Pi Health Score},2))," (",$$NOW,") | ",{DE:Pi Health Score - History})),1000)),LEFT( IF(LEFT({DE:Pi Health Score - History}, IF(ISBLANK({DE:Pi Health Score}),0,SUM(SEARCH("(",{DE:Pi Health Score - History}), -1))) =ROUND({DE:Pi Health Score},2),{DE:Pi Health Score - History},CONCAT(IF(ISBLANK({DE:Pi Health Score}),"-",ROUND({DE:Pi Health Score},2))," (",$$NOW,") | ",{DE:Pi Health Score - History})),1000))

[Vin01]

Hi Jason, thanks for the information. I'm looking at your Last Change Date code. I know you mentioned the following: 

Pi Health Score is the field being tracked

DE: Pi Health Score - Prior is the field it is being tracked in

What are the following two fields:

Pi Health Score - History

Pi Health Score - Last Change Date

Thanks,

Vinay

[Jason_JB]

Good morning -

Pi Health Score - History is created through "change history" above

Pi Health Score - Last Change Date is created through "last change date" above

I tweaked above to show that.

[Vin01]

Thank you Jason, this was helpful!   

[Sheri_Whitten]

I did something similar using @Doug_Den_Hoed__AtAppStore's Targeted Auditing Proof of Concept on a project report, where the Status column is a calculated field showing our custom statuses and the Status History column is another calculated field that shows each time the Status field is changed.

 

Here's what those fields look like on the custom form.

It's worked great for us, maybe Doug's method will work for you.

[Vin01]

Thank you for the information Sheri!