cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
taramoe
Level: Powered On

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!  🙂

7 REPLIES 7
Dual Super User
Dual Super User

Re: Sum a SharePoint list column to a second SharePoint list column

@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!



Community Support Team
Community Support Team

Re: Sum a SharePoint list column to a second SharePoint list column

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.
taramoe
Level: Powered On

Re: Sum a SharePoint list column to a second SharePoint list column

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

Community Support Team
Community Support Team

Re: Sum a SharePoint list column to a second SharePoint list column

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.
taramoe
Level: Powered On

Re: Sum a SharePoint list column to a second SharePoint list column

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?
Community Support Team
Community Support Team

Re: Sum a SharePoint list column to a second SharePoint list column

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.
taramoe
Level: Powered On

Re: Sum a SharePoint list column to a second SharePoint list column

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?

Helpful resources

Announcements
firstImage

New Ranks and Rank Icons in April

Read the announcement for more information!

firstImage

Better Together Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Community Summit North America

The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (9,965)