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

(0) Share

Report

Posted on by BrendanZ1

333

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

Categories:

Building flows

I have the same question (0)

All responses (13)

Answers (1)

v-alzhan-msft on at

Like (0)

Report

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.

Was this reply helpful? Yes No
BrendanZ1 333 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
BrendanZ1 333 on at

Like (0)

Report

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:

Was this reply helpful? Yes No
Verified answer

v-alzhan-msft on at

Like (0)

Report

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.

Was this reply helpful? Yes No
BrendanZ1 333 on at

Like (0)

Report

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....

Was this reply helpful? Yes No
v-alzhan-msft on at

Like (0)

Report

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.

Was this reply helpful? Yes No
BrendanZ1 333 on at

Like (0)

Report

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

Was this reply helpful? Yes No
v-alzhan-msft on at

Like (0)

Report

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.

Was this reply helpful? Yes No
BrendanZ1 333 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
v-alzhan-msft on at

Like (0)

Report

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.

Was this reply helpful? Yes No