Reference a custom field from another object in a calculated field

[NickVa1]

Hi WF Community,

I'd like to do the following, when creating a new calculated field on a project form, and wondering how to go about it:

• Create a calculated field called ABC on a task form
• Create a calculated field called XYZ on a project form that aggregates field ABC from all related tasks

I'm uncertain how to reference fields from a child object.

Thanks,

Nick

[William-- · Accepted solution]

Hi Nick, 

Short answer is, you can't. Collections aren't accessible in a calculated field's expression. 

A calculated field can only reference objects found in the "references" tab of the object in the API Explorer. So a task can reference its project, but a project can't reference its tasks. 

If you have Fusion, a scenario can be designed to aggregate your task or issue data within Fusion and then write that to a calculated field on a project form. The expression of that calculated field only references itself so the value can only be overwritten by Fusion.

IF(ISBLANK({DE:field_name}),"",{DE:field_name})

Sorry that wasn't the answer you were looking for.

[NickVa1]

Thanks, WE.  I now recall that information, after you explained it.  

We do have Fusion, and that was my backup plan, so thanks for detailing a solution.
Cheers!

[ChristinaJay]

Any chance you can share this Fusion recipe?

[NickVa1]

Hi @ChristinaJay  - see attached screenshots.  Go top to bottom and left to right for proper sequence.  Cheers!

[KatharineSu]

Hi @NickVa1 We are running into something similar.

Until the campaign object is released, we are using a workaround for campaign relationships. In other words, we want to tie projects to campaigns in a one to many ability.

1. We create projects whose type is campaign and each campaign gets two calculated fields assigned:
   1. One that is the campaign custom ID used for internal reporting and UTM deployment
   2. Another that is a CONCAT of multiple fields, including the above ID, and more importantly is a user friendly/recognizable campaign name that users can pick from.
2. We separately have a field called "related campaign" that is going to managed by our intake manager. Anytime a campaign project is created, the intake manager will copy the value of the CONCAT campaign name into this field.
3. When a user creates a project that is a deliverable (a paid ad, for example), they'll be able to use a multi-select dropdown on "Related Campaign" thereby connecting all content and experience deliverables to that campaigns under which they are activated. 

In an ideal world, after 1.b above, Workfront would automate and add the newly generated value to a running list that makes up the "Campaign Related" field and which could be used for reporting. Going to post this in the ideas section because it feels like a good workaround if they don't release campaigns.