web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Sum data based on uniq...
Power Apps
Answered

Sum data based on unique value to attain overall total

(0) ShareShare
ReportReport
Posted on by BrendanZ1 333

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.

 

Categories:
Building Power Apps
  • v-yutliu-msft Profile Picture
    v-yutliu-msft Microsoft Employee on at

    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,

  • BrendanZ1 Profile Picture
    BrendanZ1 333 on at

    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

  • Verified answer
    v-yutliu-msft Profile Picture
    v-yutliu-msft Microsoft Employee on at

    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,

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Congratulations to the August Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
11manish Profile Picture

11manish 405 Super User 2026 Season 2

#2
Mohsin Ali Profile Picture

Mohsin Ali 368

#3
WarrenBelz Profile Picture

WarrenBelz 244 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans