Level 2

Solved

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

Forum|Forum|2 years ago
April 28, 2023
5 replies
2440 views

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

Workfront

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

Best answer by Jason_JB

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

Madalyn_Destafney

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

V

Vin01Author

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

Madalyn_Destafney

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

AndrewKo

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

Jason_JBAccepted solution

Level 5

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

V

Vin01Author

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

Jason_JB

Level 5

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.

S

Sheri_Whitten

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.