Skip to main content
Solved

Sums of fields using Workflows

  • May 7, 2024
  • 4 replies
  • 184 views
MWitczak
Hero (Customer)
Forum|alt.badge.img+10

Is it possible to use sums on fields in workflows?

 

Here’s the SQL query for what I am trying to get using workflows:

select sum(quantity)
  ,activity_seq
from SCH_OF_WORK_EST_RES_SUM
where project_id = <<project id here>>
and resource_type_db = 'PROJECT_RESOURCES'
group by activity_seq

 

So I would like to get the sum of the PROJECT_RESOURCES values for each particular activity sequence and then insert this value at the activity level to a certain field.  Trouble is, I don’t know how to get the sum using workflows.  I’m sure it’s somewhere in the documentation or examples, but I’m not sure where.

 

This is the entityset I’m pulling the values from currently:

 

${Reference_SchOfWorkEstimateResourceSummary_Set[0].ActivitySeq}

$[Reference_SchOfWorkEstimateResourceSummary_Set[0].HoursQuantity}

 

 

Best answer by kamnlk

For this you may have to create another collection/list with the only the activity sequences and use a multi-instance loop to go through each element.  And inside the loop use the IFS projection task to do the update.

I created another sample workflow to showcase this

 

 

4 replies

K
Hero (Employee)
Forum|alt.badge.img+9
  • kamnlk
  • Hero (Employee)
  • 137 replies
  • May 7, 2024

you could use a script task to do this calculation in the workflow

Sample code:

var workset = execution.getVariable("Reference_SchOfWorkEstimateResourceSummary_Set");
var myMap = {};

for (var i = 0; i < workset.length; i++) {

actSeq = workset[i].ActivitySeq;
qty = workset[i].Quantity;

if (actSeq  in myMap ) {
   myMap[actSeq] = myMap[actSeq] + qty;
} else {
    myMap[actSeq] = qty;
}

}

i attached a sample workflow to show case this.

MWitczak
Hero (Customer)
Forum|alt.badge.img+10
  • MWitczak
  • Author
  • Hero (Customer)
  • 68 replies
  • May 8, 2024

Thank you kamnlk!  This is great!

One more question if you will indulge me.  How do I separate out the activity_seq and summed hours to use in an IFS Projection?  I’m trying to use this data to update lines on Activity Estimate.

 

Much appreciated!

K
Hero (Employee)
Forum|alt.badge.img+9
  • kamnlk
  • Hero (Employee)
  • 137 replies
  • Answer
  • May 9, 2024

For this you may have to create another collection/list with the only the activity sequences and use a multi-instance loop to go through each element.  And inside the loop use the IFS projection task to do the update.

I created another sample workflow to showcase this

 

 

MWitczak
Hero (Customer)
Forum|alt.badge.img+10
  • MWitczak
  • Author
  • Hero (Customer)
  • 68 replies
  • May 9, 2024

Once again, thank you.  This achieves the intended purpose.


Terms & ConditionsCookie settingsAccessibility statement