cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ajazz
Level: Powered On

Combining duplicate items in collection

Hey all!

I am looking for a solution to combine duplicate items in a collection.

I have a form that adds quantity to a itemnumber in a collection. In some cases the itemnumber's quantity will be added through multiple submissions. However, multiple quantity submission per itemnumber causes duplicates. This is a problem as i need to have merged quantity for each itemnumber in my collection and gallery.

Is there any ways to merge items in a collection or gallery.

Help will be highly appreciated.

Thanks a lot!

image.png

 

The picture demonstrates a gallery of the collection. On top is itemnumber, type and quantity on bottom

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Combining duplicate items in collection

@ajazz

OK, here's a mini-tutorial on how to SUM the quantity of by part number.  Put this code in the OnSelect property of a new button.

 

//Create a collection with data for the mini-tutorial
ClearCollect(
    ItemsCollection,
    {ItemID: "0117-0100", ItemDesc: "RAL7035", Quantity: 120},
    {ItemID: "0117-0100", ItemDesc: "RAL7035", Quantity: 100},
    {ItemID: "0117-0100", ItemDesc: "RAL7035", Quantity: 120},
    {ItemID: "0743-0006", ItemDesc: "", Quantity: 84},
    {ItemID: "0743-0006", ItemDesc: "", Quantity: 22}
);
//Group records in the collection by ItemID and ItemDesc
ClearCollect(
    ItemsGrouped,
    GroupBy(
        ItemsCollection,
        "ItemID",
        "ItemDesc",
        "Quantities"
    )
);
//Add a new column to the grouped colllection with the total quantity for each part
ClearCollect(
    ItemsQuantityTotal,
    AddColumns(
        ItemsGrouped,
        "Total Quantity",
        Sum(Quantities,Quantity)
    )
);

 

Click the button and go view the Collections created in PowerApps main menu.

 

itemsCollection.PNG

itemsGrouped.PNG

itemsQuantityTotal.PNG

 

I hope this helps!

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

9 REPLIES 9
Super User
Super User

Re: Combining duplicate items in collection

@ajazz 

You can use the GROUPBY function in PowerApps to group the columns of your collection and sum the amounts.  This would effectively merge any duplicates.  Would you like me to demonstrate this technique with the data you have already provided?

 

Link to MS Documentation of GROUPBY.  An example of how to SUM data is included.

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-groupby

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

ajazz
Level: Powered On

Re: Combining duplicate items in collection

Thank you @mdevaney for you reply!

I will be delighted for a demonstration 🙂

Super User
Super User

Re: Combining duplicate items in collection

@ajazz

OK, here's a mini-tutorial on how to SUM the quantity of by part number.  Put this code in the OnSelect property of a new button.

 

//Create a collection with data for the mini-tutorial
ClearCollect(
    ItemsCollection,
    {ItemID: "0117-0100", ItemDesc: "RAL7035", Quantity: 120},
    {ItemID: "0117-0100", ItemDesc: "RAL7035", Quantity: 100},
    {ItemID: "0117-0100", ItemDesc: "RAL7035", Quantity: 120},
    {ItemID: "0743-0006", ItemDesc: "", Quantity: 84},
    {ItemID: "0743-0006", ItemDesc: "", Quantity: 22}
);
//Group records in the collection by ItemID and ItemDesc
ClearCollect(
    ItemsGrouped,
    GroupBy(
        ItemsCollection,
        "ItemID",
        "ItemDesc",
        "Quantities"
    )
);
//Add a new column to the grouped colllection with the total quantity for each part
ClearCollect(
    ItemsQuantityTotal,
    AddColumns(
        ItemsGrouped,
        "Total Quantity",
        Sum(Quantities,Quantity)
    )
);

 

Click the button and go view the Collections created in PowerApps main menu.

 

itemsCollection.PNG

itemsGrouped.PNG

itemsQuantityTotal.PNG

 

I hope this helps!

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

ajazz
Level: Powered On

Re: Combining duplicate items in collection

Thanks again!

I still have trouble getting it to work..

First problem i see is that itemnumbers can not be prefixed, as they get added over time.
Moreover i have trouble getting groupby function to work on different screens.

Is there any way i can get the solution to work over two different screens?

First screen is showing the form, where the user can submit itemnumber, type and quantity. This will be added to collection_01.
The form is submitted to the collection_01 over mutiple times through the button "move quantity".
When every itemnumber is moved, the user click the button "done", which navigates to the overview screen, that contains a gallery of the moved itemnumbers and quantities.
Screenshot_2019-11-01 Transfer order powerapp - Saved (Unpublished) - PowerApps(1).png
On the overview page (the first picture from title post) i would like to show grouped item number and their quantities.
This information needs to be send into a database afterwards.

I appreciate your efforts!

Super User
Super User

Re: Combining duplicate items in collection

@ajazz 

First problem i see is that itemnumbers can not be prefixed, as they get added over time.

I have no idea what this means.

 

Is there any way i can get the solution to work over two different screens?

Collections are global so they can be accessed across multiple screens.

ajazz
Level: Powered On

Re: Combining duplicate items in collection

ClearCollect(
    ItemsCollection,
    {ItemID: "0117-0100", ItemDesc: "RAL7035", Quantity: 120},
    {ItemID: "0117-0100", ItemDesc: "RAL7035", Quantity: 100},
    {ItemID: "0117-0100", ItemDesc: "RAL7035", Quantity: 120},
    {ItemID: "0743-0006", ItemDesc: "", Quantity: 84},
    {ItemID: "0743-0006", ItemDesc: "", Quantity: 22}

To me this doesnt make sense? Why should you specify the values for the clearcollect? Especially when new values are entered all the time

Super User
Super User

Re: Combining duplicate items in collection

 


@ajazz wrote:
ClearCollect(
    ItemsCollection,
    {ItemID: "0117-0100", ItemDesc: "RAL7035", Quantity: 120},
    {ItemID: "0117-0100", ItemDesc: "RAL7035", Quantity: 100},
    {ItemID: "0117-0100", ItemDesc: "RAL7035", Quantity: 120},
    {ItemID: "0743-0006", ItemDesc: "", Quantity: 84},
    {ItemID: "0743-0006", ItemDesc: "", Quantity: 22}

To me this doesnt make sense? Why should you specify the values for the clearcollect? Especially when new values are entered all the time


 

Sorry, but we are misunderstanding each other here.  In my previous post I said "OK, here's a mini-tutorial on how to SUM the quantity of by part number".  My goal was to provide you an example that was very close to what you were doing rather than give the exact solution.  Specifically, that collection you are referencing is just my fake data.  You should not be using this fake data in your app and instead make a reference to your gallery.  Hopefully my explanation makes sense.

 

 

 

 

 

ajazz
Level: Powered On

Re: Combining duplicate items in collection

It's me that apologise!
I got it to work now. Thanks a lot and have a great weekend!

Super User
Super User

Re: Combining duplicate items in collection

@ajazz 

I wish you a great weekend too!  Sometimes it is hard to communicate well over the forums.  No worries.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,249)