cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

@Anonymous

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
mdevaney
Super User III
Super User III

@Anonymous 

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

Anonymous
Not applicable

Thank you @mdevaney for you reply!

I will be delighted for a demonstration 🙂

@Anonymous

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

Anonymous
Not applicable

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!

@Anonymous 

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.

Anonymous
Not applicable

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

 


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

 

 

 

 

 

Anonymous
Not applicable

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

@Anonymous 

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

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (81,420)