Sum a SharePoint list column to a second SharePoint list column

[Anonymous]

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

[efialttes]

@Anonymous 

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

[v-yamao-msft]

Hi @Anonymous ,

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.

Best regards,

Mabel

[Anonymous]

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?

[v-yamao-msft]

Hi @Anonymous ,

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

[Anonymous]

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?

[v-yamao-msft]

Hi @Anonymous ,

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.

Best regards,

Mabel

[Anonymous]

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]

Hello Team,

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

Please suggest, what I am missing