Expand my Community achievements bar.

Join us for our Coffee Break Sweepstakes on July 16th! Come ask your questions or share your use cases on Creative Briefs for a chance to win a piece of Workfront swag!
SOLVED

Aggregate m any project values into one sum value at program level

Avatar

Level 10

Hi Fusion Community,

I have a series of modules in a scenario that does the following:

  • Identifies the program
  • Iterates all of the projects under that program
  • Iterates all of the tasks under each project
  • Aggregates the "Total Billable Planned Hrs This Month - Task" field values from all tasks, and updates the "This Month's Projected Burn - Project" field on each project

This is where I'm stuck.  I'd like to aggregate the "This Month's Projected Burn - Project" field values from each project under the program, and update the sum value in the "This Month's Projected Burn" field at the program level.  I'm stuck trying to figure out how to aggregate many project values into one value at the program level.

 

Here's what I have in Fusion:

Screenshot 2023-06-15 at 6.13.44 PM.png

 

Does anyone know how I can do this?

Thanks,
Nick

Topics

Topics help categorize Community content and increase your ability to discover relevant content.

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

To read that back, this is what I think you want to do.

  • You have a custom field on projects that has a number
  • You want to store the sum of all of those projects in a given program on that program in  a custom field

If that's correct, here's what I would do.

Pull in all of the programs you want to do this for, along with their projects. On the projects, include pulling in the project field that has your source number, so on your program search, include projects:DE:Your_custom_field. Iterate thru the programs. Then, place the array of projects into a variable, with these functions => sum(map({project array};DE:Your_custom_field)), which will give you the sum. Then write back that number to your program. Note, you don't need the variable, you can just place the functions in the callback, the var is just something I tend to do.

View solution in original post

7 Replies

Avatar

Correct answer by
Community Advisor

To read that back, this is what I think you want to do.

  • You have a custom field on projects that has a number
  • You want to store the sum of all of those projects in a given program on that program in  a custom field

If that's correct, here's what I would do.

Pull in all of the programs you want to do this for, along with their projects. On the projects, include pulling in the project field that has your source number, so on your program search, include projects:DE:Your_custom_field. Iterate thru the programs. Then, place the array of projects into a variable, with these functions => sum(map({project array};DE:Your_custom_field)), which will give you the sum. Then write back that number to your program. Note, you don't need the variable, you can just place the functions in the callback, the var is just something I tend to do.

Avatar

Level 10

Thanks for this, Chris.

I think I'm close, but something's not adding up...

  • It's showing 0 in the variable module outputs
  • It's showing the program ID as the output for updating the custom field on the program (last module)
  • I have 4 operations and outs on the last module that is supposed to update the custom field on the program

Here's what I have:

 

The modules I created based on your instruction

Screenshot 2023-06-16 at 11.17.15 AM.png

 

Search Program module (there will only ever be one program, as this scenario is based off of individual projects updates)

Screenshot 2023-06-16 at 11.11.47 AM.png

 

Iterate projects module

Screenshot 2023-06-16 at 11.12.10 AM.png

 

Set sum custom field from all projects (variable module)

Screenshot 2023-06-16 at 11.12.51 AM.png

 

Screenshot 2023-06-16 at 11.12.59 AM.png

 

Update custom field on program 

Screenshot 2023-06-16 at 11.24.48 AM.png

 

Screenshot 2023-06-16 at 11.25.28 AM.png

 

 

 

 

Avatar

Community Advisor

Map fuction.png

 

If you do it my way, you don't need to iterate thru the projects, just place the project array inside the map module (above), and that's the number you push to your program.

 

The alternative, if you're going to iterate thru the projects anyway, you can just use the numeric aggregator module and map in your number property, and the output of that module is what you use to push to your program.

 

As to why the multiple program updates, that's because you are iterating thru the project list and not aggregating back up, so it's updating the program for each project in the program. To get it back down to a single update, you'd need to add some time of aggregator prior to the program update, and map it to the project iterator.

Avatar

Level 10

I think I misunderstood your initial suggestion; I get it now (no need to iterate projects, just iterate programs).  Thanks.

I'm assuming to iterate programs it's just a Search module?

Screenshot 2023-06-16 at 2.35.39 PM.png

 

I can't seem to capture the "This Month's Projected Burn - Project" field from the program search module (569) when creating the sum/map variable.  Is it because this field is collections under each project array?

Screenshot 2023-06-16 at 2.32.53 PM.png

 

 

 

Avatar

Community Advisor

Yeah, no need to iterate thru projects. In the map module, just type in the field name.

Avatar

Level 10

Perfect...that worked!
Thanks again for your help, Chris!

Avatar

Level 10

Hi Chris!
Sorry to bother you, it's just that I have question related to the above item you helped me with.

 

The solution you provided was related to a scenario where there was only ever one program in the Event Trigger because the scenario was based off of individual project updates.

 

I want to do the same type of thing as my earlier request - look at a program, iterate down through the projects and to the tasks in order to get sum actualWorkRequired from them - and then aggregate this value up to the related program.  The thing is, the scenario I'm working with now looks at a data store full of many different programs, as opposed to an Event Trigger for 1 record.

 

This screenshot shows you where I'm getting stuck at the end:

NickVa1_0-1715209291491.png

 

Any help is appreciated.
Thanks,

Nick