cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate II
Advocate II

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

Highlighted
Advocate II
Advocate II

Re: Combining duplicate items in collection

Thank you @mdevaney for you reply!

I will be delighted for a demonstration 🙂

Highlighted
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

Highlighted
Advocate II
Advocate II

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!

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

Highlighted
Advocate II
Advocate II

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

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

 

 

 

 

 

Highlighted
Advocate II
Advocate II

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!

Highlighted
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
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

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 (6,973)