Expand my Community achievements bar.

Don’t miss the Workfront AMA: System Smarts & Strategic Starts! Ask your questions about keeping Workfront running smoothly, planning enhancements, reporting, or adoption, and get practical insights from Adobe experts.
SOLVED

aggregating values and excluding items from workPerDate

Avatar

Level 8

I'm stuck, and I need some help.

I've got a Custom API Call module that pulls assignments for a particular user for a specific task date range--I'm most interested in the workPerDate entries for these assignments.  My test scenario yields 6 assignments and a total of 14 workPerDate entries:

 

custom API input.jpgcustom API output.jpg

 

My goal is twofold--I want to (1) exclude the entries prior to 5/26 (i.e., 5/23, 5/24, 5/25); and (2) total the minute values for the remaining entries (in other words, I'm trying to get assigned time for the 5/26-5/30 work week).

Could someone please provide some guidance on how to approach this?  I've been playing around with different iterate and aggregate modules, as well as array functions, all without luck (at one point I managed to aggregate the values for a single date, but that was about it).

Thanks in advance.

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

I'm running aggregation on certain WPD values as well. Specifically to understand user's capacity for today and next day. I have few modules less, but I'm not sure if this is simpler.

For me prerequisites are user ID, user timezone (since I run this for the global team)

Iteration runs through users:

Rafal_Bainie_0-1748365736182.png

1. iterate through users

2. set today's date, weekday

3. set start / end times for the assignment search 

4. find relevant assigments

5. iterate through assignments

6. get relevant value

7. aggregate relevant values

8. calculate capacity

9. aggregate to an array (for further handling)


Key is point 6 where I'm directly getting relevant values from WPD field:

Rafal_Bainie_1-1748366000501.png

I think this can potentially save you some transformations/operations and runtime.
Subsequent aggregation is simple:

Rafal_Bainie_0-1748366359118.png

 

 

Watchout: WPD are fetched relative to Fusion timezone, so if you have users distant from that timezone you can encounter issues (like I do). I haven't found a way to address that and I think Adobe support confirmed there may be none 🙂

 

Good luck!

Rafal

View solution in original post

3 Replies

Avatar

Level 8

Well, it's involved and super-clunky, but I made it work.  I'd appreciate if anyone has suggestions for streamlining; I'm still new to many of the concepts I'm trying to grasp here.  Again, in my test scenario I'm pulling 6 assignment records, with a total of 14 workPerDate entries.

 

KristenS_WF_0-1748309874816.png

 

I used a custom API call module to pull the assignments and then iterated through them.  For each assignment I did the following:

  • used another custom API call module to get the workPerDate entries for the assignment
  • transformed that info into JSON
  • split the JSON string into an array (separating the records by commas and removing the curly brackets)
  • iterated through this array
  • used the Replace function to add quasi-field names to the strings and added curly brackets back (e.g., changing "2025-05-29":180 to {"WorkDate":"2025-05-27","WorkValue":180}
  • passed this along as a JSON string and parsed it according to a data structure I created:

KristenS_WF_1-1748310631607.png

  • formatted the WorkDate "field" in the MM/DD/YYYY format
  • calculated the difference (in days) between this WorkDate and 05/26/2025 (I used a hard-coded date in my testing; in the future I'd like to pass along a calculated week-starting date, as well as create a similar calculation for week-ending date)

I then added a filter to allow only those bundles through where the difference in days was greater than or equal to zero.

Finally I summed all the WorkValue bundles that passed through the filter and then summed all those subtotals.

Here are the user's planned hours for this week in Planner:

KristenS_WF_2-1748311233064.png

And here's what my scenario yielded:

KristenS_WF_3-1748311284602.png

Again, I have a feeling there must be an easier/shorter way to get this result, so suggestions are welcome.

Thanks!

 

 

 

 

 

 

Avatar

Correct answer by
Community Advisor

I'm running aggregation on certain WPD values as well. Specifically to understand user's capacity for today and next day. I have few modules less, but I'm not sure if this is simpler.

For me prerequisites are user ID, user timezone (since I run this for the global team)

Iteration runs through users:

Rafal_Bainie_0-1748365736182.png

1. iterate through users

2. set today's date, weekday

3. set start / end times for the assignment search 

4. find relevant assigments

5. iterate through assignments

6. get relevant value

7. aggregate relevant values

8. calculate capacity

9. aggregate to an array (for further handling)


Key is point 6 where I'm directly getting relevant values from WPD field:

Rafal_Bainie_1-1748366000501.png

I think this can potentially save you some transformations/operations and runtime.
Subsequent aggregation is simple:

Rafal_Bainie_0-1748366359118.png

 

 

Watchout: WPD are fetched relative to Fusion timezone, so if you have users distant from that timezone you can encounter issues (like I do). I haven't found a way to address that and I think Adobe support confirmed there may be none 🙂

 

Good luck!

Rafal

Avatar

Level 8

Thanks, Rafal!  I'll give your scenario a look!