Expand my Community achievements bar.

Join us LIVE in San Francisco on November 14th for Experience Makers The Skill Exchange. Don't miss out on this free learning event!
SOLVED

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

Avatar

Level 2

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

1 Accepted Solution

Avatar

Correct answer by
Level 7

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

View solution in original post

10 Replies

Avatar

Community Advisor

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.

If this helped you, please mark correct to help others : )

Avatar

Level 2

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

Avatar

Community Advisor

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.

If this helped you, please mark correct to help others : )

Avatar

Level 2

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

Avatar

Correct answer by
Level 7

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

Avatar

Level 2

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

Avatar

Level 7

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.

Avatar

Community Advisor

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.

 2023-05-01 09_07_11-Traffic.png

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

2023-05-01 09_08_25.png2023-05-01 09_08_40.png

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