Solved

Sums of fields using Workflows

M
Rank
Userlevel 3
Badge +7

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}

 

 

icon

Best answer by kamnlk 9 May 2024, 08:06

View original

4 replies

K
Rank
Userlevel 3
Badge +5

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.

1 Attachment
M
Rank
Userlevel 3
Badge +7

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
Rank
Userlevel 3
Badge +5

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

 

 

1 Attachment
M
Rank
Userlevel 3
Badge +7

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

Reply


Terms & ConditionsCookie settings