How to Keep Only Highest Quantity Record when Pushing Collection to SharePoint

[TitanicTips23]

Hi everyone,

I almost have my first PowerApps project ready to go, largely due to the help of this incredible community! Hoping the bright minds here can help me with a feature to push it over the finish line.

I have a collection of records that result as a combination of selecting certain items in a gallery on. As items are selected, a record is created in my collection colShoppingBasket with the orderer's information and the product info. Screenshot below for reference.

When the user submits the order, it pushes to SharePoint via the following formula:

Collect('My SharePoint List', Sort(DropColumns(colShoppingBasket,"Total"),Quantity,SortOrder.Descending))

This works great as the SharePoint list columns coincide directly with my collection. However, I would like to be able to remove records that do not contain the highest quantity for a given order. For example, with the screenshot, I would only like for the record with quantity of 4 to post to SharePoint. An additional complexity is that there may be multiple products in a collection. For example, product 1 might have 5 records, with the highest quantity being 5, product 2 might have seven records with the highest quantity being 7, etc. etc.

Any insight or ideas on how to approach this are greatly appreciated!

[WarrenBelz · Accepted solution]

Hi @TitanicTips23 ,

You need to "add back" all the columns other than Product as below

DropColumns(
   AddColumns(
      GroupBy(
         Sort(
            colShoppingBacket,
            Quantity,
            SortOrder.Descending
         ),
         "Product",
         "Data"
      ),
      "Quantity",
      First(Data).Quantity,
      "Facility",
      First(Data).Facility,
       . . . . . . .
   ),
   "Data"
)

There is a slightly less complex way if you have a unique identifier on each record.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[TitanicTips23]

Hi @WarrenBelz , once again, your solution is spot on for my needs. I went ahead and marked as the solution and gave your response a thumbs up 👍

I do have a couple of follow up questions for you if you don't mind:

1. Can you help me understand the purpose of "Data" in this use case? I see the similarities in the AddColumns and GroupBy functions here as it's similar to another problem you helped me solve. But I am confused as to how "Data" factors in.

2. Can you explain the "slightly less complex way" and how I might implement that as a solution? Should I revisit this, for example.

[WarrenBelz]

Hi @TitanicTips23 ,

You can call that column whatever you want - Grouped is another name often used and actually more properly describes it. When the GroupBy function is used, records are produced for the unique column value/s (you can group by more than one column) also including this Table record column containing all the rest of the fields and records that match each grouped set. You can then access this Table with AddColumns and use a single record/field (the easiest is the First or Last) to construct your final record set.

To your second question, do you have a unique identifier and if so, what is the name and type of column ?