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
Solved! Go to 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.
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.
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.
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.
Views
Replies
Total Likes
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)).
Views
Replies
Total Likes
Thanks for the help, Chris!
Clarifying questions:
1. When you say "...add in map({hours collection from above};hours)"
2. When you say "...to sum up the hours, put what you got from the map function into sum({array from previous})"
Thanks!
Nick
Views
Replies
Total Likes
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.
Views
Replies
Total Likes
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:
Is there a way to still incorporate these filters while doing aggregating the hours at the project level like you showed me?
Views
Replies
Total Likes
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.
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):
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
Views
Replies
Total Likes
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.
Views
Replies
Total Likes
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).
Views
Replies
Total Likes
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?
Views
Replies
Total Likes
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
Views
Replies
Total Likes
Ah I see, I forgot something. On the array filter after 425, change it to contains(map({424.Array};TaskID);taskID).
Views
Replies
Total Likes
Like such?
It doesn't appear to get past that filter still. I think part of the Contains is missing at the end?
Views
Replies
Total Likes
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.
Views
Replies
Total Likes
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)?
Views
Replies
Total Likes
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".
Views
Replies
Total Likes
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.
No worries, I totally understand.
Views
Replies
Total Likes
Views
Likes
Replies