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

Numeric Aggregator to roll up hour records at project level

Avatar

Level 10

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:

NickVa1_0-1675117662884.png

 

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.

 

NickVa1_1-1675118001632.png

 

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

 

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

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 iterationhours Variable in hours iterationoverall scenariooverall scenarioWorkfront project search collections settingsWorkfront project search collections settingsTask FilterTask FilterHours filterHours filterTotal number of hoursTotal number of hourshour variablehour variable

View solution in original post

21 Replies

Avatar

Community Advisor

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.

Avatar

Level 10

Hi Chris...

 

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

Screen Shot 2023-01-31 at 12.55.58 PM.png

 

 

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

Screen Shot 2023-01-31 at 12.56.31 PM.png

 

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

Screen Shot 2023-01-31 at 1.00.19 PM.png

 

 

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

  

Screen Shot 2023-01-31 at 1.01.21 PM.png

Avatar

Community Advisor

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.

Avatar

Community Advisor

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)).

Avatar

Level 10

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

Avatar

Community Advisor

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.variable.pngworkfront module.png

Avatar

Level 10

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
    NickVa1_0-1675200165256.png

  2. Sum hours from these tasks where the hour entryDate is last month.  My filter here took care of this
    NickVa1_1-1675200201073.png

     

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

Avatar

Correct answer by
Community Advisor

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 iterationhours Variable in hours iterationoverall scenariooverall scenarioWorkfront project search collections settingsWorkfront project search collections settingsTask FilterTask FilterHours filterHours filterTotal number of hoursTotal number of hourshour variablehour variable

Avatar

Level 10

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.

NickVa1_0-1675288741280.png

 

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

NickVa1_1-1675288883231.png

 

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

NickVa1_2-1675289327880.png

 

Hours list results

NickVa1_3-1675289353293.png

 

 

TaskID array aggregator list

NickVa1_4-1675289380546.png

 

 

Avatar

Community Advisor

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.

 

 

Avatar

Level 10

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).

 

NickVa1_1-1675359727662.png

 

NickVa1_2-1675359745216.png

 

 

 

NickVa1_0-1675359711622.png

 

Avatar

Community Advisor

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?

Avatar

Level 10

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

NickVa1_0-1675362157846.png

 

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

NickVa1_1-1675362214809.png

 

 

Avatar

Community Advisor

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

Avatar

Level 10

Like such?

NickVa1_0-1675366767915.png

 

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

NickVa1_1-1675366997377.png

 

Avatar

Community Advisor

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.

Avatar

Level 10

That worked; yes!  

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

NickVa1_0-1675370427361.png

 

NickVa1_1-1675370446231.png

 

 

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

 

Avatar

Community Advisor

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".

Avatar

Level 10

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.