sum of duration of tasks of a project

[xo66-it]

I have 2 lists in SharePoint: Products and workLoad. In the Products list, I have 3 fields: Title, requirements, and durationTot. I want to update the durationTot field for each modification in the Products list.

The Products list contains multiple values (on a multi-select column) derived from the Title field of the workLoad list. I want the sum of the respective ID values of the Title field from the workLoad list in order to write into the durationTot field of the Products list.

Here is an example of the lists:

Products

Title	requirements	durationTot
Product A	task 1;task 3;task 4	0
Product B	task 1	0
Product C	task 1;task 2;task 3	0
Product D	task 1;task 2;task 3;task 4	0
Product E	task 1;task 4	0

workLoad

Title	Duration
task 1	5
task 2	7
task 3	8
task 4	10

I want, for instance, if the requirements of Product A changes to "task 1;task 3", the durationTot field should be updated to 13, or if a new record is added (say Product F with only task 4), the durationTot should be updated to 10.

If this result were the output of an array, it would be even better, so depending on the cases, I could use different aggregations for the calculation of durationTot (perhaps in addition to sum, also Min or Max).

Thanks.

[Nived_Nambiar]

Hi @xo66-it 

Try this approach >

Here I have two lists

Now see the flow design- flow will trigger item is added/modified in Products SP List.

1. 

2. use two initialize variables as shown below

3. use trigger;'s output to check whether the created date is equal to modified date so to understand whether it is newly created item /not ?

if newly created item- in Yes branch set variable like below

if condition is false- do like below

a. Understand changes required in the Product list item using below action so that to prevent the case of multiple triggering 

Expression - 

concat(string(sub(int(split(triggerOutputs()?['body/{VersionNumber}'],'.')?[0]),1)),'.0')

b. check whether the duration column has changed as part of previous version - 

if yes - update the ToUpdate variable as false

if no - update the ToUpdate variable as true

After the condition - use another condition which runs based on ToUpdate variable as shown below

if the toUpdate variable is true- in yes branch do the following steps

Expression used- 

split(triggerOutputs()?['body/requirements'],';')

This expression split the requirement based on ; delimiter. so to loop through

Now use Get Items to get the items in workload list corresponding to task being iterated.

Now use increment variable action to increment the TotalDuration variable

Expression - 

int(first(outputs('Get_items')?['body/value'])?['Duration'])

After the loop- use update item to update the product list item with total duration value like below

Here i have considered all my columns as text type.

Hope this helps !

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[xo66]

Thanks @Nived_Nambiar ,
honestly the screenshots you've posted are not so clear, and I'm not an expert of Power Automate so I think I could have made some mistakes. The mumber fields I'm using are not text but numbers.
I thought the problem could be solved using this post: Instantly sum an array with Power Automate (tachytelic.net)

Thanks so much!!!

[xo66]

With more time I've tried to study better yuor workflow (please consider that my numbers are integers... non text).

I get this error:

"Action 'Apply_to_each' failed: Unable to process template language expressions for action 'Apply_to_each' at line '0' and column '0': 'The template language function 'split' expects its first parameter to be of type string. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#split for usage details.'.

Any idea? I'm Italian...

[Nived_Nambiar]

Hi @xo66  @xo66-it  - is this expression is getting failed ?

split(triggerOutputs()?['body/requirements'],';') 

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[xo66]

@Nived_Nambiar yes!

[Nived_Nambiar]

Hi @xo66 @xo66-it  - 

as per the error - it means that the 

split(triggerOutputs()?['body/requirements'],';') 

1. requirements column is not existing - it may be in different column name ?

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[xo66]

yes.. i've translated requirements to requisitiRD

follwing this I've modified to 

split(triggerOutputs()?['body/requisitiRD'],';')

Thanks

m

[Nived_Nambiar]

Hi @xo66  @xo66-it 

did this worked for you then ?

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[xo66]

Hi @Nived_Nambiar , id doesn't work.

I get this error:

"Action 'Apply_to_each' failed: Unable to process template language expressions for action 'Apply_to_each' at line '0' and column '0': 'The template language function 'split' expects its first parameter to be of type string. The provided value is of type 'Array'. Please see https://aka.ms/logicexpressions#split for usage details.'."

Thanks