cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
taramoe
Advocate II
Advocate II

Sum a SharePoint list column to a second SharePoint list column

I have a SharePoint list at a detailed level where the Program Code appears on multiple lines and each line has a different Unit total, for example.....

Program Code          Unit

AMS04                      4.0

AMS04                      3.2

AMS04                      1.3

50USE                       1.5

50USE                       3.5

 

I need to aggregate that number to a second SharePoint list that would only display.....

Program Code         Unit

AMS04                     8.5

50USE                      5.0

 

The Program Code in the second list is unique, so any time it changes in the first list, I need the second list to update, not add additional lines.  If the Program Code does not exist in the second list, it should be added with the aggregate Unit total.  I tried using the below solution but it's not aggregating as I had hoped and it doesn't look to see if the Program Code already exists, it just keep creating new rows.

 

https://powerusers.microsoft.com/t5/General-Flow-Discussion/Sum-a-column-from-one-sharepoint-list-wi...

 

I'm essentially starting from scratch so I don't have any screen shots to show and I'm stumped on how to build this.  Thank you for reading this and I would much appreciate some help!  🙂

8 REPLIES 8
efialttes
Super User
Super User

@taramoe 

Let me see if I understood... You want your flow to be executed everytime an item is created on Sharepoint List#1 (it looks like an orders' list), right?

 

So, everytime a new order arrives, you have to read its related program code and search for it in Sharepoint List#2, right? If a matching item i found on Sharepoint List#2, you need to add the new order number of units from Sharepoint List#1 to the accumulated number of units on Shareponit List#2 item. And if no match is found, you need to create a new item on Sharepoint List#2.

 

Are my assumptions right?

If so, I would suggest you to use "When an item is created" trigger (see here for more information) on Sharepoint List#1.

Then, I would add asharepoint  "Get items" action block on Sharepoint List#2, and filter it in order to get only items with program code matching with the one from item created on Sharepoint List#1. Such filter can be done in the "get items" action block itself or by adding a "Filter array" action block.

Then, I would evaluate the length of the output array by means of a Condition action block, if equals to 1, it means a match was found; in such case you shall get the matching item from Sharepoint List#1; get its ID, and add a Sharepoint "Update item" action block. If the evaluation fails (i.e. no matching item) then you just need to add a new item to Sharepoint List#2

 

Hope this helps

 

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



v-yamao-msft
Community Support
Community Support

Hi @taramoe ,

 

Do you want to sum a SharePoint list column then save the total to a second SharePoint list column?

 

Please see the following flow for a reference.

 

I am using Button trigger in my scenario, you could change it to “When an item is created” if you want to trigger the flow whenever a new item is created in the list.

 

Get items from List11 which contains two required columns, Code (type of Single line of text) and Unit (type of number).

 

Initialize variable, set name as Code, Type as Array.

 

Initialize variable2, set name as Unit, Type as Integer, Value as 0.

 

Add Apply to each, select Value from Get items. Add action Append to array variable within the Apply to each, select Code for Name, and dynamic content Code from Get items for Value.

 

Under Apply to each, add Compose with the following code:

union(variables('Code'),variables('Code'))

 

Add Apply to each 2, select from compose output, within Apply to each2, add action Get items, and input the following code in Filter query field:

Code eq ‘Current item’

Note: dynamic content Current item comes from Apply to each2.

 

Then add Apply to each 3, select value from Get items2. Within Apply to each3, add Compose 2 with the following code:

 

int(items('Apply_to_each_3')?['Unit'])

 

Then the action Increment variable, select name Unit, Compose 2 output for Value.

 

Under Apply to each 3, add create item to create the total to another list. Note: select dynamic content Apply to each 2 for Code, and variable unit for Unit.

1.PNG2.PNG3.PNG4.PNG

 

 

Best regards,

Mabel

 

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yamao-msft , thank you for your detailed reply.  I'm getting this error.  It runs with no errors until this step.  My column type in the target list (the 2nd list that is an aggregate/sum) is a number type. Ideas?

 

Flow Error Sum.jpg

Hi @taramoe ,

 

Please share the function you are using in the Compose 2 action.

What’s the column type in the original list?

On my side, it is of type number in the original list,

 

Best regards,

Mabel

 

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yamao-msft , 

This is what is in my Compose 2.  The column 'MW_x002d_WriteTo' is a Number field from the original list (not aggregated/summed list).

 

int(items('Apply_to_each_3')?['MW_x002d_WriteTo'])
 
Your solution had 'Create item' on the end where I want to search for whether a record already exists in the aggregated/summed list and create it if it doesn't exist and update it where it does exist.  What would be the best way to build this in?

Hi @taramoe ,

 

As long as the column 'MW_x002d_WriteTo' is of type Number, it should be working. Please make sure the field does contain value.

 

If you want to create if it doesn’t exist and update if it already exists, please take the following configuration for a reference.

The array variable is for checking if the new item already exists or not.

1.PNG2.PNG

 

Best regards,

Mabel

 

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yamao-msft , thank you for your help so far.  I'm finally coming back to work on this. 

 

The incremented value that is being populated is a count rather than a sum.  How do I fix this?

ravi4
New Member

Hello Team,

 

I have tried to replicate the solution. but I am not getting the Unit Sum, I am always getting 0.

 

Capture.JPG

image2.jpg

Please suggest, what I am missing

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,369)