Expand my Community achievements bar.

# Workfront

The Community Advisors application is now OPEN for the second class of 2024. Apply to become a part of this exclusive program!
SOLVED

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

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

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.

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

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

Level 2

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

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

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

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.

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

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

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.

Level 2

Thank you Jason, this was helpful!