Skip to main content

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_SetR0].ActivitySeq}

$cReference_SchOfWorkEstimateResourceSummary_SetR0].HoursQuantity}

 

 

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.

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!

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

 

 

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

Reply


Terms & ConditionsCookie settings