Calculate the total of a SharePoint List column based on the value of another field

[BrendanZ1]

Hi all,

I am looking for some guidance on how to best approach a way to calculate figures held in a SharePoint list, and most likely saving them to a second list....

Scenario:

I am trying to build a Stock Control system that receives stock in, as well as tracks the movement and usage so that I have a live feed of what stock is actually on hand using powerapps as the front end.

At the moment I have built a list that collects the stock name, its batch code and I have the ability to assign it a bar code (there are a lot more fields, but not necessary for accomplishing this solution).

So I might have data that looks like this:

BarCode	StockName	BatchCode	DateReceived	Amount
Z-0001-T	ITEM1	EM1862	01/01/2020	1500
Z-0001-A	ITEMC	120506	04/01/2020	100
Z-0001-T	ITEM1	EM1862	10/02/2020	2000
Z-0002-T	ITEM1	EM1111	15/03/2020	1550

What I would like to do, is calculate the totals for all items with unique batch codes. So what I should end up with is:

BatchCode	TotalAmount
EM1862	3500
EM1111	1550
120506	100

So each time I receive stock with a matching batchcode and enter it into the first SP list, a flow then assess this and recalculates the total stock for that batch code. But I need to do this for every different batch code entered into the system.

Or maybe there is another way of achieving this through powerapps? In a nutshell, I need to be able to get a total for each stock item with the same batch code, so I can then track how much is on hand based on usage data being tracked on a different SP list.

Open to any suggestions.

Thanks

[v-alzhan-msft]

Hi @BrendanZ1 ,

Do you want to update the second list with the corresponding TotalAmount column for the corresponding BatchCode when a new item is created in the first list?

If yes, you could refer to screenshot below to create the flow:

Best Regards,

Alice

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

[BrendanZ1]

Hi @v-alzhan-msft , thanks for this.

I am working through the Flow and just want to check a few things.

My two lists are; StockReceivals and StockReceivalTotals, the latter being the one I want to contain the results of the flow. So are the lists as per your instructions the following:

StockReceivals - List 1

StockReceivalTotals - List 2

I'll try and work it out by trial and error in the meantime.

[BrendanZ1]

So far my attempts are not working. I am getting a successful flow, but in the outputs of the Get Items, the Value listed in Body is returning nothing.

Based on my response above, my flow looks like this:

**Note the "When an item is created or modified" has a trigger condition set against it: 

@equals(triggerBody()?['ReceiptComplete'],true)

This is so the flow is only triggered once the receipt of stock is confirmed as complete, in case a total amount is not entered when  the item is created.

 Also I am unable to see the output of the Update Item when I review the completed flow details....

Just in case my solution wasn't clear, hopefully the below diagram can help:

[v-alzhan-msft · Accepted solution]

Hi @BrendanZ1 ,

You could refer to screenshot below to create the flow:

Best Regards,

Alice

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

[BrendanZ1]

Hi @v-alzhan-msft ,

I am working through your solution, but when I get to the Increment Variable, I have selected Int, as per the instructions, but for the value, I am unable to select the appropriate column.

I am assuming where you have Amount, that is the total amount of stock received for that SharePoint list item?

With my Stock Receival list (in our case List 1), the column called TotalReceived, is a calculated field that gives the overall amount of stock received for that delivery. I'm not sure if being a calculated column, this would cause any issues with the Flow being able to use the data in that field?

We can recieve stock in different ways such as pallets, containers, boxes, bags etc. I have setup the StockReceival list so that a user can enter the number of pallets and/or boxes received for a product as well as input the size. Example: PalletSize = 1200kg, PalletNumberReceived = 3. Based on these inputs, the TotalReceived is 3600kg. Other times the pallet size might be 800kg.....

Otherwise I am not sure what else could be preventing me from being able to select the TotalReceived column from the dynamic content....

[v-alzhan-msft]

Hi @BrendanZ1 ,

You need to create the flow the same as mine.

The Amount column is a number type column in list1.

The array is used to get the total amount in list2.

Best Regards,

Alice

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

[BrendanZ1]

@v-alzhan-msft ok so one of my issues was that I was trying to use a calculated column for the data in TotalReceived. I have since created a workaround so that the same data is saved as a number.

Now when I get to the point of Get Items 2, as soon as I add the Dynamic content for BatchCode in the Filter Query, it closes off the Get Items and wraps it inside an Apply to each control automatically.

So my flow looks like this by this point:

I wasn't sure if you deliberately named the column BaseCode instead of BatchCode for this last part, so I replicated it and still threw this error. If I advance the Flow as per your design, I end up with Apply to each 3 for the Update Item step and I figure this will only cause some sort of issue with the execution.

[v-alzhan-msft]

Hi @BrendanZ1 ,

Please create the flow same as mine.

Best regards,

Alice   

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

[BrendanZ1]

hi @v-alzhan-msft , 

to the best of my knowledge, I am recreating your flow based on what you have provided. I am unable to stop the Get Items 2 from becoming wrapped in a new Apply_to_each control.....it is automatically closing the Get Items step the moment I select BatchCode from the dynamic content. I will try to do a screen capture and upload to show you and post it as soon as possible.

[v-alzhan-msft]

Hi @BrendanZ1 ,

Please update the filter query in your get items action as my screenshot:

The expression in the Filter Query should have ' ' as below:

ColumnName eq 'Dynamic content'

Best regards,

Alice   

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

[BrendanZ1]

Hi @v-alzhan-msft ,

I have done this, but only after the flow closed out the Get Items 2 on me and put it inside the Apply to each. As per your flow, the Get Items 2, should be sitting on its own before the second Apply to each step. I won't be able to post a video until I am back at home in about 3hrs, so in the meantime I'll try to explain my exact steps and what happens;

1. I select Get Items (this is just after the Apply_to_each step that holds the Increment variable)
2. I then select my site and list
3. I go to advanced settings and in the Filter query I type: BaseCode eq'
4. Then I select the dynamic content of BatchCode
5. As soon as this is selected, the Get Items step closes and is automatically inserted into an Apply_to_each control

So I am not given the opportunity to add the second ' to the end of the dynamic content as you have mentioned.

So where you have the Get Items 2 in between the Apply_to_each steps, mine is sitting inside the second Apply_to_each.....

[BrendanZ1]

@v-alzhan-msft sorry I thought it was possible to upload a GIF to this post but I can't see any option to. Is there a way I can get you to be able to view it? It is approx 2.4mb in size

[CayMaxx]

@BrendanZ1 @v-alzhan-msft  Did anyone ever find a solution? I have the same Problem as @BrendanZ1 had. 

Greetings, Max