cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

Sum data based on unique value to attain overall total

Hi all,

 

My title is probably not as helpful as I would like, but what I am trying to achieve is a stock management system, whereby the user can receive stock in, then track its usage and will also be able to see live, the stock on hand etc.

 

In the system, the user might receive say 10 different types of stock, and each stock item will have batchcodes that will differ over time. So after a few weeks or months, the data representing what has been ordered/received would look like this:

 

StockItemBatchCodeDeliveryDateQuantityBarcode
JuiceA1235110/05/2020

10

Z-0001
SugarS1237510/05/20205Z-0022
Ice CreamDD550111/05/202012Z-0012
BreadCB40115/05/202050Z-0044
JuiceA1235115/05/20208Z-0001
BreadCB40118/05/202075Z-0055
JuiceA1455918/05/202025Z-0002
SugarS2324518/05/20209Z-0025

 

What I need to do is Sum the data so that I can get the total quantity per BatchCode, so the data would then look like:

StockItemBatchCodeQuantityBarcode
JuiceA12351

18

Z-0001
SugarS123755Z-0022
Ice CreamDD550112Z-0012
BreadCB401125Z-0044
JuiceA1455975Z-0002
SugarS232459Z-0025

 

On the flip side, I also have the Stock Usage, which tracks against the batchcode/barcode so that ultimately I have the same system working for stock usage as I do stock receival. Then with the totals set against them, I should be able to determine stock on hand:

StockReceivalTotal - StockUsageTotal = X

 

At the moment I have tried gathering this information via a Flow, but haven't been able to build a successfully working one, and I have since tried GroupBy function with a collection. The latter, I have been able to get a gallery showing the BatchCode and the total stock received against it, and patched to a SharePoint list. But I have been unable to get the patch to work so that it either updates existing records, or uses the Defaults property to create a new record if it doesn't exist. 

Lastly, even if I can achieve this, I can't get my head around how I can then ensure that the StockUsage data is compared against the right StockReceival data.

 

I guess what I am after is some guidance on how I can use Powerapps and SharePoint lists to bring this to life, and if there are any tricks to being able to group data (in this case by BatchCode) and be able to reference a unique ID that can be matched against when it comes to the stock usage data.....

 

Thanks for any feedback or suggestions.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Sum data based on unique value to attain overall total

Hi @BrendanZ1 ,

Do you mean that:
in  StockReceival list, if you receive new stock, you want to update the same batch code's record, not create new record?

If so, you just need to use an if statement to justify whether to update existing record or create new record.

For example:

If(
IsEmpty(Filter(StockReceival,BatchCode=BatchCodetextinput.Text)),
Patch(StockReceival,Defaults(StockReceival), 
                    {BatchCode:BatchCodetextinput.Text,Quantity:Value(Quantity.Text),....}
      )
//if there's no same BatchCode,create new item
Patch(StockReceival,LookUp(StockReceival,BatchCode=BatchCodetextinput.Text),
                    {Quantity:Value(Quantity.Text)+
                              LookUp(StockReceival,BatchCode=BatchCodetextinput.Text,Quantity)
                     }
      )
//if there's already new BatchCode, add Quantity
)

 

 

Best regards,

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

View solution in original post

3 REPLIES 3
Highlighted
Community Support
Community Support

Re: Sum data based on unique value to attain overall total

Hi @BrendanZ1 ,

Do you want to get table2 from table1?

If so, you just need to use GroupBy and Sum function.

Try this:

DropColumns(
             AddColumns(
                         GroupBy(table1,BatchCode,"samecode"),
                         "StockItem",First(samecode).StockItem,
                         "Quantity",Sum(samecode,Quantity),
                         "Barcode",First(samecode).Barcode
                         ),
             "samecode")

 

About your second demand, actually I do not quite understand.

Could you describe more clearly by using example?

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper IV
Helper IV

Re: Sum data based on unique value to attain overall total

Hi @v-yutliu-msft thanks for the help so far. I have tried your syntax and am able to create a collection with the additional columns against the sum of the stock recieved.

 

The next part which I wasn't as clear on, is how I track the stock as it is being used. As an example the data is collected via PowerApps into a SharePoint list (same as stock being received) as so:

BarCodeStockNameBatchCodeDateQuantity
Z-0001JuiceA1235120/05/205
Z-0022SugarS1237520/05/201
Z-0001JuiceA1235121/05/203
Z-0044BreadCB40123/05/2020
Z-0002JuiceA1455923/05/2010

 

I must point out that currently (and preferably), I am collecting the data on two different lists (one for receival and one for usage due to the barcode system). I have toyed with the idea of having one list with a field that categorises whether the item being added is receival or usage, but not sure that this would make any of this any easier.


So like the StockReceival, I am able to do the same for StockUsage by following the same steps you have provided, but the key, is for me to be able to then compare the totals between StockReceival and StockUsage to determine how much I have left in stock. At the moment I can create two galleries that provide me with total stock received per batch code, and how much stock is used per batch code, but I am not sure how I can then use that data so that in my PowerApp, I can show the user the running total of what stock is left to use. Hopefully that makes sense.

 

As mentioned, I have tried to build a flow to do the above and populate a separate sharepoint list, but had some issues with it, so I am looking for ways to do it through PowerApps instead. I have tried patching the results of the above collections but instead of updating existing fields, it just adds the same data as new items. Example:


In the SP List I might have 50 units against say Bread with the batch code of CB401 and then I recieve new stock under the same batch code so the total received in the collection is now 100. When I go to patch that to the list, rather than changing the existing entry, I get a duplicate.....

 

Hopefully that makes sense too.

 

Thanks again for your help

Highlighted
Community Support
Community Support

Re: Sum data based on unique value to attain overall total

Hi @BrendanZ1 ,

Do you mean that:
in  StockReceival list, if you receive new stock, you want to update the same batch code's record, not create new record?

If so, you just need to use an if statement to justify whether to update existing record or create new record.

For example:

If(
IsEmpty(Filter(StockReceival,BatchCode=BatchCodetextinput.Text)),
Patch(StockReceival,Defaults(StockReceival), 
                    {BatchCode:BatchCodetextinput.Text,Quantity:Value(Quantity.Text),....}
      )
//if there's no same BatchCode,create new item
Patch(StockReceival,LookUp(StockReceival,BatchCode=BatchCodetextinput.Text),
                    {Quantity:Value(Quantity.Text)+
                              LookUp(StockReceival,BatchCode=BatchCodetextinput.Text,Quantity)
                     }
      )
//if there's already new BatchCode, add Quantity
)

 

 

Best regards,

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

View solution in original post

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

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