Numeric Aggregator to roll up hour records at project level

[NickVa1]

Hi Fusion Community,

I'm trying to build a path that looks at all of the hour records from tasks belong to various project, and aggregate the sum hours at the project level (in a custom field called Last Month's Revenue Hours).

Here's what I've created, but for the life of me I can't get the hours to aggregate at the project level:

The result, below, just displays the number of hours records with the project ID associated to them.  I would expect the numeric aggregator would combine and sum the hour records, per projectID.  In the example below, I would expect to see 2 Operations in module 399's results: Key (projectID): 63d437500004910900788e21c9166c7e should be 75; and Key (projectID): 63d4385d0004ea53a2d4c487dbfa4ec5 should be 25.

Does anyone know where I'm going wrong?
Thanks!
Nick

[ChrisStephens]

What is your call on the module immediately prior to the aggregator, as well as the call prior to that (related hours/tasks)? I suspect what's happening is that it's not pulling all of those in at once. The 4 on the filter immediately prior tells me that you're making 4 distinct calls, as opposed to a single call.

[NickVa1]

Hi Chris...

My call on the module immediately  prior to the aggregator is on the Hours object (module 397 - get related hour entries):

My call prior to the above the Tasks object (module 395 - get related tasks):

 My call prior to that is on the Projects object (module 21 - get projects):

My call prior to that is on the Programs object (module 1 - search programs).  This is the trigger event.

  

 

[ChrisStephens]

Ok yeah, it's because those aren't being pulled into Fusion at the same time, it requests new hours for each task from each task, so when it aggregates it only sees the hours for each task.

If I recall correctly, you should be able to pull in the hours on your project search, under collections, which should bring in a list containing all the hours for that project. Then you can use the map function to get a simple array of the actual hour values, and you can plug that array into a sum function to get the total. Then update the project with the new total.

[ChrisStephens]

In case that was confusing, I'll try to make it a bit clearer. On your project search, in the collections area, activate the map function and add allHours:hours. Adding that piece will cause Workfront to give you all of the hour entries on that project, and the hours portion also causes it to give you the number of hours.

Then, to get a simple array of all the hours, add in map({hours collection from above};hours). Next, to sum up the hours, put what you got from the map function into sum({array from previous}). Or, you can combine them so it would be sum(map({hours collection from above};hours)).

[NickVa1]

Thanks for the help, Chris!
Clarifying questions:

1. When you say "...add in map({hours collection from above};hours)"

• Am I adding this to the Collections mapping on the project module?
• When you say "{hours collection from above}", are you referring to "allHours:hours"
• Essentially I would add this map({allHours:hours};hours) to the Collection mapping on the project module?

2. When you say "...to sum up the hours, put what you got from the map function into sum({array from previous})"

• Is this sum happening in a new module?  If so, is it just an update module at the project level?

Thanks!
Nick

[ChrisStephens]

Sorry, should have been more specific. I would create a variable, and do the calculation in there, and then you can map the variable where ever you need it to go.

[NickVa1]

That was helpful, thanks.
This works!  However, the two added layers I need to account for, that I was doing with my task and hours search modules, were these:

1. Only look at tasks with a revenueType value not equal to Not Billable.  My filter here took care of this
   
   
   
2. Sum hours from these tasks where the hour entryDate is last month.  My filter here took care of this
   

Is there a way to still incorporate these filters while doing aggregating the hours at the project level like you showed me?

[ChrisStephens · Accepted solution]

It's a little bit different, but here's how I would do it. On the project search pull in the list of tasks and hours, and on tasks pull in the revenue type, and on the hours pull in the entry date and the task ID.

Then, iterate thru the task list, filtering out any tasks that you don't want to know the hours for, and aggregate it into a simple list of task ID's.

Then, iterate thru the hour entries, filtering out for entry date, as well as checking to see if the hour's task ID is in the list you created previously, and aggregate those into a list of hours.

Then have a variable with sum({resulting hour list}).

This should give you the total number of hours that have been added onto tasks that are set to the revenue type(s) you want, and that were entered in the last month.

hours Variable in hours iterationoverall scenarioWorkfront project search collections settingsTask FilterHours filterTotal number of hourshour variable

[NickVa1]

Hey Chris!
First off, thank you so much for taking the time to provide a solution with details.  Super helpful, especially with my learning!

I've gotten most of the way through implementing your suggestion, and it's looking good, but I'm stuck here:

Module 425 (iterating hours):

1. I'm looking at the project search module (#21), and the array from allHours, yet module 425 (hours iterator) seems to be duplicating output bundles.  For example, task ID "63d438030004b96ef00ad7b1656b5520" with 25hrs is displaying twice; once in Operation 1 and again in Operation 2.  The same is happening for the other hours entries that qualify.  There should only be 4 hour entries, but 8 are appearing.

Screenshot of part of the results from the Project Search module (#21)

2. My filter (Hours last month) that looks to see if the hours iteration list (module 425) contains the taskID from the taskID array aggregator (module 424) is not working.  My filter says it fails this criteria, but when I look at the results from module 424 (taskID array aggregator), and compare to the results of module 425 (hours list iterator), it looks like there should be a match.

Filter

Hours list results

TaskID array aggregator list

[ChrisStephens]

The source module for the aggregators should be the iterator modules right before (the shaded region should go back to the iterator), it appears you have it set to the variable module. I think doing this will solve both problems, but I'm not 100% so if it doesn't solve the 2nd let me know.

[NickVa1]

Perfect! That update solved for the 1st issue (duplicating bundles/taskID's).

It's still not passing the filter {{contains(424.array; 425.taskID)}}, after module 425 (Hours List iterator).

[ChrisStephens]

The hour one should be connected to the iterator right before it too, so 427 should connect to 425.

If that still doesn't work, can you show me an output array from the task array, and what task ID it was looking for?

[NickVa1]

Ahh, yes, I forgot to update the Hours aggregator to look at the iterator.  I have since done that so the hours iterator, variable, and aggregated are all connected.  That didn't seem to solve the issue.

Here is the Task list aggregator (424) array output

And here's the hours list iterator (425) input array and output

[ChrisStephens]

Ah I see, I forgot something. On the array filter after 425, change it to contains(map({424.Array};TaskID);taskID).

[NickVa1]

Like such?

It doesn't appear to get past that filter still.  I think part of the Contains is missing at the end?

[ChrisStephens]

In the map function, type in "TaskID" (no quotes), don't map a value in, and the 425.taskID should be after the contains semicolon.

[NickVa1]

That worked; yes!  

I added the sum variable at the end, and it appears only to be grabbing the 1st bundle in each operation:

Am I able to sum these at the project level (by project)?

[ChrisStephens]

You'll have to do that same thing on that output, where you do the sum, so it would be sum(map({427.Array},Hours)), typing in "Hours".

[NickVa1]

Hi Chris...thanks for this.  I'm going to get to it this afternoon.  I've been pulled in a few directions today.  

I wanted to take a moment to give my sincere gratitude for the help you have provided.  You didn't lose interest or patience, and were great at answering my questions.  This was a helpful learning experience.  

[ChrisStephens]

No worries, I totally understand.