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

[NickVa1]

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:

Does anyone know how I can do this?

Thanks,
Nick

[ChrisStephens · Accepted solution]

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.

[NickVa1]

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

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

Iterate projects module

Set sum custom field from all projects (variable module)

Update custom field on program 

[ChrisStephens]

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.

[NickVa1]

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?

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?

[ChrisStephens]

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

[NickVa1]

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

[NickVa1]

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:

Any help is appreciated.
Thanks,

Nick