Save Time Using Calculated Fields to Capture Dates, Details and Data.

[#1]

Do you find yourself scrolling through multiple pages of the update stream just to find the date a particular project status changed? Or do you store information on a portfolio that all tasks within need to reference? Did you know that you can capture these pieces of information in a calculated custom field so that it’s easily accessible and can be used in reporting?  

Let’s walk through a few examples:

Example #1: For all new projects, you want to track the exact date the status changed to “On Hold.” Create a calculated custom field to track this date change.

IMPORTANT: These steps do not retroactively input historical date changes. This impacts new projects created moving forward, only if the custom form is attached prior to the status change. And this calculated custom field will track only the first status change date. If the project status moves from On Hold to Current and then On Hold again, only the first status change date will be tracked.  3

Go to Setup and select Custom Forms.

Choose an existing project custom form to edit, or create a new project custom form. 

From the Add a Field tab, make sure New Field is selected. Then choose Calculated from the list. 

Name your field using the Label text box. For this example, name the calculated field Date of On Hold.  

Select Date from the Format drop-down menu. 

Click Done, then Apply. This creates your calculated custom field in the database. 

Select the field to add the calculation. Paste in the following:

IF(ISBLANK(Date of On Hold),IF(Status="ONH",$$TODAY,Date of On Hold),Date of On Hold)

NOTE: If you name the calculated field something other than Date of On Hold, you must change that in the formula above in all three places you see the field name. 

You now see a checkbox for “Update previous calculations (in the background)” has been added to this calculated field (at the bottom of the left panel). By selecting that checkbox, the calculated field will automatically be filled in without you having to manually recalculate the form. 

Click Done, then Save + Close. 

PRO TIP: Attach this form to project templates so all new projects that are created moving forward can track the status change date, if the project moves to On Hold. 

Example #2: Your organization has a Marketing portfolio for all design projects. The specifications for each type of asset are listed in the portfolio description. You have been assigned a task to design an asset and need the exact dimensions. 

Create a calculated custom field so you can view these dimensions at the task level—either within the task details custom form section or in a custom view—without navigating back to the portfolio. 

Go to Setup and select Custom Forms.

Choose an existing task custom form to edit, or create a new task custom form. 

From the Add a Field tab, make sure New Field is selected. Then choose Calculated from the list. 

Name the field using the Label text box.  

Type Project.Portfolio.Description in the Calculation text box.

Select Done. 

Click Apply. A checkbox for “Update previous calculations (in the background)” has been added to this calculated field (scroll down in the left panel). By selecting that checkbox, the calculated field will automatically be filled in without you having to recalculate the form manually. 

Hit Done. Then, Save + Close your custom form. 

Navigate to your assigned task and select Task Details > Custom Forms. This new calculated field has been populated with the portfolio description.

PRO TIP: If you have a custom field on the portfolio you want to reference at the task level, follow that same format, but replace Description with {DE:Custom Field Name}. Your formula would look like this: Project.Portfolio.{DE:Custom Field Name}. 

If there is a specific calculated field your organization needs, ask the Workfront Community, or submit a case to Support.

[#2]

Hi @jon_chen,

I tried to implement the second example, but I can't save the field as Workfront tells me that it is an invalid custom expression.

Does the new experience of Workfront still support it?

Thanks Patrick

[#3]

Hi @patrickh4509724,

The new format to follow would be:

{project}.{portfolio}.{description}

Any calculations that you had in there prior to the switch to the new format should've been automatically updated.

[#4]

Hey - I am trying to do option 1 today and am getting an error. I am trying to create this calculated field with the status: Discovery so I entered:

IF(ISBLANK(Discovery),IF(Status="DSC",$$TODAY,Discovery),Discovery)

and am receiving the error message: 

This is an invalid custom expression, please try again.

[#5]

Hi @andreadmw - Try IF(ISBLANK({DE:Discovery}),IF({status}="DSC",$$TODAY,{DE:Discovery}),{DE:Discovery}). Remember to add "DE:" before the custom field name.

[#6]

Thanks @_Manish_Singh . I realized what I was doing wrong. I appreciate you looking into it. The original instructions worked.

[#7]

Hey @andreadmw  , 

How did you get the original instructions to work?  We are having issues with this part of the instructions.  We don't see update...

You now see a checkbox for “Update previous calculations (in the background)” has been added to this calculated field (at the bottom of the left panel). By selecting that checkbox, the calculated field will automatically be filled in without you having to manually recalculate the form.