Expand my Community achievements bar.

Submissions are now open for the 2026 Adobe Experience Maker Awards.

[Follow-up] Calculated Custom Fields Workshop

Avatar

Employee Advisor

Screen Shot 2023-05-11 at 12.36.17 PM.png

Thank you to all who attended and participated in today's Calculated Custom Field workshop with the Adobe Workfront Customer Success team. There were so many great ideas shared in the chat, so I'll add those as replies below.

If you missed the session, no worries - here is a link to the recording (expired May 2024) and a copy of the slide deck with instructions and ready-to-use formulas is attached. We discussed best practices and tips for creating custom forms and walked through 3 calculated custom field examples:

  1. Identify the date(s) of status changes - use this to understand how long a work item was in a specific status (ex: On Hold)
  2. Link to a tab on an object (ex: Link to Hours tab -- creating a clickable URL for users "Click here to log time")
  3. Calculate the number of days between Actual Start and Planned Start or Actual Completion and Planned Completion

I encourage those in attendance to share the formulas and a short description of the calculated custom fields your organization uses so that others can take advantage! 

UP-LIKE IDEA [from Helen]: Save a custom report or list view as global View 

 

15 Replies

Avatar

Employee Advisor

[From Karlton]

Custom Field Name: Days a User is Inactive

Format: Number

Calculated Expression: ROUND(DATEDIFF($$NOW,{lastLoginDate}),0)  

Avatar

Employee Advisor

[From Monique]

Status Comments History Field.

TYPE: Text field

NOTES: Points to itself and a field called Status Comments

FORMULA:
LEFT(IF(LEFT(Status Comments History,LEN(IF(ISBLANK({Status Comments}),"-",{Status Comments})))={Status Comments},Status Comments History,CONCAT(IF(ISBLANK({Status Comments}),"-",{Status Comments})," (",$$NOW,") | ",{Status Comments History})),2000)

Avatar

Employee Advisor

[From Madalyn]

Custom Field Name: Project Entry to Completion field

Format: Number

Calculated Expression: ROUND(DATEDIFF({actualCompletionDate}, {entryDate}),1)

 

Custom Field Name: Project Current to Completion field

Format: Number

Calculated Expression: IF({status}="CPL",ROUND(DATEDIFF({DE:Date Went Current},{actualCompletionDate}),2))

 

Custom Field Name: Date Project went Current field

Format: Date

Calculated Expression: IF(ISBLANK({DE:Date Went Current}),IF({status}="CUR",$$NOW),{DE:Date Went Current})

Avatar

Community Advisor

Thanks for adding for me, @NicholeVargas  : )

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

Avatar

Community Advisor

There's a lot of IFs in here but was fun to build.  Our mgmt wanted planned completion dates to show quarters instead of actual date as they tended to move...so instead of 2/15/2023, it would show 1Q/2023, providing wiggle room conversations.

 

Custom Field Name: Planned Completion by Quarters 

Format: Text

Calculated Expression: 

IF(MONTH({plannedCompletionDate})=1,CONCAT("Q1","/",YEAR({plannedCompletionDate})),IF(MONTH({plannedCompletionDate})=2,CONCAT("Q1","/",YEAR({plannedCompletionDate})),IF(MONTH({plannedCompletionDate})=3,CONCAT("Q1","/",YEAR({plannedCompletionDate})),IF(MONTH({plannedCompletionDate})=4,CONCAT("Q2","/",YEAR({plannedCompletionDate})),IF(MONTH({plannedCompletionDate})=5,CONCAT("Q2","/",YEAR({plannedCompletionDate})),IF(MONTH({plannedCompletionDate})=6,CONCAT("Q2","/",YEAR({plannedCompletionDate})),IF(MONTH({plannedCompletionDate})=7,CONCAT("Q3","/",YEAR({plannedCompletionDate})),IF(MONTH({plannedCompletionDate})=8,CONCAT("Q3","/",YEAR({plannedCompletionDate})),IF(MONTH({plannedCompletionDate})=9,CONCAT("Q3","/",YEAR({plannedCompletionDate})),IF(MONTH({plannedCompletionDate})=10,CONCAT("Q4","/",YEAR({plannedCompletionDate})),IF(MONTH({plannedCompletionDate})=11,CONCAT("Q4","/",YEAR({plannedCompletionDate})),IF(MONTH({plannedCompletionDate})=12,CONCAT("Q4","/",YEAR({plannedCompletionDate}))))))))))))))

Avatar

Community Advisor

That's cool. You could also accomplish showing grouping by the native quarter grouping for planned completion if you want? Even if you use custom quarters, it'll adhere to those custom quarters...

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

Avatar

Community Advisor

True, but this calc field allowed us to do a chart by those percentages, which was also something mgmt wanted to look at 

Avatar

Community Advisor

Custom Field Name: Completion Range Percentage

Format: Text

Calculated Expression:  

IF({percentComplete}<"25","< 25%",IF({percentComplete}<="50","25-50%",IF({percentComplete}<="75","50-75%",IF({percentComplete}>="75","> 75%"))))

Avatar

Level 3

Hi Nicole! Is the video available to view?

Avatar

Employee Advisor

@ljorr16 Unfortunately, the recordings from our events only have a 1-year shelf life, so the link has expired. Based on customer feedback, this will likely be a topic for a future Scale Customer Success workshop so keep an eye out on Experience League! 

If you have any specific questions around the slide deck, though, let me know! 

Avatar

Level 3

Hi again, I saw in the pdf that there was a way to bring in a field that would allow a user to make an update from a project report. I'm stumped. Any hints? This would be SUPER useful for a group of users. I got as far as this: 

CONCAT("https://floridablue.my.workfront.com","/","project/","/updates")
 

ljorr16_0-1752010161187.png

Thank you!!

Avatar

Employee Advisor

@ljorr16 A user won't be able to log hours from a Project Report, however, this will provide a link for them to navigate to the Hours tab directly. 

If you've migrated to the Adobe Admin Console, the text/link for your calculated custom field would look like this instead: CONCAT("https://experience.adobe.com/#/@domain/so:domain-Production/workfront/project/",{ID},"/hours")

You'll want to replace the bold domain text with the domain of your Workfront instance (found in your URL). 
 
If you haven't migrated, the text/link for your calculated custom field would look like this: CONCAT("https://domain.my.workfront.com","/","project/view?ID="
,{ID},"&activeTab=list-project-hours")
Again, you'll want to replace the bold domain text with the domain of your Workfront instance.
 
Once you have created the calculated custom field (and have associated that custom form with all relevant objects), you can then move forward and build a project report. 

Create a new project report. Add this custom field as a column.

  • Under the Advanced Options area, add a Column Rule to this column. Set your rule to be: IF this field IS NOT BLANK, display text – Click here to log time. Add rule.
  • Switch to text mode. Add a line for link.url=DE:Link to Hours Tab (or name of custom field)
  • Save and close report.

Avatar

Level 3

I'm so sorry, I was trying to figure out how one could add a column that allowed a user to click to make a project update!

Avatar

Employee Advisor

Apologies for that! You should just be able to swap the word hours for updates and it should take a user to the Updates tab of the project. Again, they aren't going to be able to in-line edit and post an update through the report, but this would provide them with a single click to get there. 

-----

If you've migrated to the Adobe Admin Console, the text/link for your calculated custom field would look like this instead: CONCAT("https://experience.adobe.com/#/@domain/so:domain-Production/workfront/project/",{ID},"/updates")

You'll want to replace the bold domain text with the domain of your Workfront instance (found in your URL). 
 
If you haven't migrated, the text/link for your calculated custom field would look like this: CONCAT("https://domain.my.workfront.com","/","project/view?ID="
,{ID},"&activeTab=list-project-updates")
Again, you'll want to replace the bold domain text with the domain of your Workfront instance.

Avatar

Level 3

AMAZING! I think this will be super helpful. Thank you so much!!!