cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Patch items from one temporary collection into another temporary collection adding quantities together when match found.

I have two temporary collections (not SharePoint Lists):

ColIrrParts is my collection of parts used on a single job.

IrrigationShoppingList is my collection of all parts used on all jobs for the week.

 

I'm trying to add the items in my ColIrrParts collection to my IrrigationShoppingList collection by looking up each item. If the item from ColIrrParts is not found in IrrigationShoppingList, then add the item information from ColIrrParts to the IrrigationshoppingList collection. If the item is found, add the quantity in ColIrrParts to the existing quantity in IrrigationShoppingList.

 

I know I'm not even close with my formula, but here is what I have:

Patch(IrrigationShoppingList, Defaults(IrrigationShoppingList), LookUp(ColIrrParts, IrrDesc = IrrigationShoppingList, IrrDesc), {Quantity: TextInputQuantity.Text+TextInputShoppingQuantity.Text})

2 ACCEPTED SOLUTIONS

Accepted Solutions

Thanks @Nate0173 ,
I need to know which piece of code is generating the error. You have also left out a parameter on the lookup

If(
   IsBlank(
       LookUp(
          IrrigationShoppingList,
          IrrDesc = LabelCartDescription.Text,
          IrrDesc
       )
   ),
   Patch(
      IrrigationShoppingList,
      Defaults(IrrigationShoppingList),
      {
          IrrDesc: LabelCartDescription.Text,
          Quantity: Value(TextInputQuantity.Text) +
          Value(TextInputShoppingQuantity.Text)
      }
   ),
   UpdateIf(
      IrrigationShoppingList,
      IrrDesc = LabelCartDescription.Text,
      {
          Quantity: Value(TextInputQuantity.Text) +
          Value(TextInputShoppingQuantity.Text)
      }
   )
)

 

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.

 

View solution in original post

I found the solution. I grouped the items in a new collection and then combined the quantities together in yet another separate collection. This allowed me to combine the items with the same description together and add the quantities of the matching items together so my final collection showed each item only once with the combined total quantity for each item.

 

First the code on the button that copies the items from Collection1 to Collection2 (note: this button is only available when Collection2 is empty):

Collect(IrrigationShoppingList, ColIrrParts);
ClearCollect(ItemsByDes, GroupBy(IrrigationShoppingList, "IrrDesc", "Category", "Quantity"));
ClearCollect(ItemCount, AddColumns(ItemsByDes, "TotalQuantity", Sum(Quantity, Quantity)))

Here is another way of seeing the same code without my specific collection and column naming conventions:

Collect(Collection2, Collection1);
ClearCollect(Collection3, GroupBy(Collection2, "ColumnNameDescription", "ColumnNameCategory", "ColumnNameQuantity"));
ClearCollect(Collection4, AddColumns(Collection3, "TotalQuantity", Sum(ColumnNameQuantity, ColumnNameQuantity)))

When Collection2 is NOT empty, a different button appears when the user wants to add additional items to Collection2. This is the OnSelect code:

ForAll(
    ColIrrParts,
        Patch(
            IrrigationShoppingList,
            Defaults(IrrigationShoppingList),
            {
                Category: ColIrrParts[@Category],
                IrrDesc: ColIrrParts[@IrrDesc],
                Quantity: ColIrrParts[@Quantity]
            }
        )
);
ClearCollect(ItemsByDes, GroupBy(IrrigationShoppingList, "IrrDesc", "Category", "Quantity"));
ClearCollect(ItemCount, AddColumns(ItemsByDes, "TotalQuantity", Sum(Quantity, Quantity)));

Same code without my specific collection and column naming conventions:

ForAll(
    Collection1,
        Patch(
            Collection2,
            Defaults(Collection2),
            {
                Category: Collection1[@ColumnNameCategory],
                IrrDesc: Collection1[@ColumnNameIrrDesc],
                Quantity: Collection1[@ColumnNameQuantity]
            }
        )
);
ClearCollect(Collection3, GroupBy(Collection2, "ColumnNameIrrDesc", "ColumnNameCategory", "ColumnNameQuantity"));
ClearCollect(Collection4, AddColumns(Collection3, "TotalQuantity", Sum(ColumnNameQuantity, ColumnNameQuantity)))

 

View solution in original post

17 REPLIES 17
Super User III
Super User III

Hi @Nate0173 ,

The below may not be the final solution - I may need to know where IrrDesc is shown on your screen as noted below.

If(
   isBlank(
      Lookup(
         IrrigationShoppingList, 
         IrrDesc = YourScreenControlWithThisValue
         IrrDesc
         )
      ),
      Patch(
      IrrigationShoppingList, 
      Defaults(IrrigationShoppingList),
      {
         IrrDesc: YourScreenControlWithThisValue,
         Quantity: Value(TextInputQuantity.Text) + 
         Value(TextInputShoppingQuantity.Text
      }
   ),
   UpdateIf(
      IrrigationShoppingList,
      IrrDesc = YourScreenControlWithThisValue,
      {
         Quantity: Value(TextInputQuantity.Text) + 
         Value(TextInputShoppingQuantity.Text
      }
   )
)

 

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.

@WarrenBelz 

Following your model, below is what I have in my code and I'm receiving the error, "Invalid argument type". I'm not sure what I'm missing.

If(

     IsBlank(

          LookUp(

               IrrigationShoppingList,

               IrrDesc = LabelCartDescription.Text

               )

          ),

          Patch(

          IrrigationShoppingList,

          Defaults(IrrigationShoppingList),

          {

               IrrDesc: LabelCartDescription.Text,

               Quantity: Value(TextInputQuantity.Text) +

               Value(TextInputShoppingQuantity.Text

          }

     ),

     UpdateIf(

          IrrigationShoppingList,

          IrrDesc = LabelCartDescription.Text,

          {

               Quantity: Value(TextInputQuantity.Text) +

               Value(TextInputShoppingQuantity.Text

          }

     )

)

Thanks @Nate0173 ,
I need to know which piece of code is generating the error. You have also left out a parameter on the lookup

If(
   IsBlank(
       LookUp(
          IrrigationShoppingList,
          IrrDesc = LabelCartDescription.Text,
          IrrDesc
       )
   ),
   Patch(
      IrrigationShoppingList,
      Defaults(IrrigationShoppingList),
      {
          IrrDesc: LabelCartDescription.Text,
          Quantity: Value(TextInputQuantity.Text) +
          Value(TextInputShoppingQuantity.Text)
      }
   ),
   UpdateIf(
      IrrigationShoppingList,
      IrrDesc = LabelCartDescription.Text,
      {
          Quantity: Value(TextInputQuantity.Text) +
          Value(TextInputShoppingQuantity.Text)
      }
   )
)

 

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.

 

View solution in original post

Thank you @WarrenBelz this works! It was the missing parameter in the LookUp.

@WarrenBelz 

After running my app with the code, it appeared to initially work, then upon further investigation, a couple things are not correct.

 

Issue #1: Only the first item in ColIrrParts is being searched for, found, and quantity added to the IrrigationShoppingList collection. None of the other items in the ColIrrParts collection are being added.

 

Issue #2: When adding a new item from ColIrrParts to IrrigationShoppingList, or adding an already existing item, it adds the quantity for that item in ColIrrParts to the quantity located in the first item in IrrigationShoppingList. It is not adding the quantities of the matching items. For example:

 

IrrigationShoppingList:

ItemA   Qty: 3

ItemB   Qty:1

 

When adding ItemC (Qty: 1) into IrrigationShoppingList, this is the result:

ItemA   Qty: 3

ItemB   Qty: 1

ItemC   Qty: 4 (Instead of the result being "1", it is adding quantity from ItemA to the quantity of ItemC in ColIrrParts)

 

Then, when adding ItemB (Qty: 2), this is the result:

ItemA   Qty: 3

ItemB   Qty: 5 (Instead of the result being "3", it is adding quantity from ItemA to the quantity of ItemB in ColIrrParts.)

ItemC   Qty: 4

Hi @Nate0173 ,

As I mentioned in my original post, my code was only showing the possible required structure based on what you had posted, which was a bit unclear as to what you were trying to do with the code you had started. Now we have working code, I need to get my mind around your process.

You have two collections and want to update one from the other either by merge or new records. What is a bit unclear to me presently is that all the values seem to come off controls on the screen. Maybe a screen shot and a bit of a description as to how and when you want to trigger this process will be helpful.

 

@WarrenBelz 

I attached a pdf hoping that this helps explain a bit better.

 

Really appreciate you looking into this!

 

PowerAppIrrigationPartsCartIssue1.jpgPowerAppIrrigationPartsCartIssue2.jpg

Hi @Nate0173
In order of events:

  1. ColrrParts gets collected from a list of parts and quantities selected. The shopping cart icon adds each part and quantity to the collection of parts.
  2. They then can modify the part quantities and the OnChange event updates anything modified to the ColrrParts collection.
  3. They then select the New Cart button and colrrParts is added to the IrrigationParts collection (good to see someone using Collect for this rather than Patch).
    So far, so good - a very solid process.
  4. So now you want to give them another go at adding things, hence the option on whether is there or not.
    The thing that was not evident to me was that they could add more than one item in the process.

I cannot test this (and free-typed it so watch the brackets and commas), but can give you the direction that this needs to take - ensure field names are correct.

ForAll(
   ColrrParts(
      If(
         IsBlank(
            LookUp(
               IrrigationShoppingList,
               IrrDesc = colrrParts.IrrDesc,
               IrrDesc
            )
         )
      ),
      Patch( 
         IrrigationShoppingList,
         Defaults(IrrigationShoppingList),
         {
            IrrDesc: colrrParts.IrrDesc,
            Quantity: colrrParts.Quantity
         }
      ),
      UpdateIf(
         IrrigationShoppingList,
         IrrDesc = colrrParts.IrrDesc,
         {Quantity: colrrParts.Quantity + 
           IrrigationShoppingList.Quantity}
      )
   )
)

 

@WarrenBelz 

Thanks again for your help on this!

 

I've been working on this code without success. Can you see what I'm missing? I receive "Invalid argument type".

ForAll3.jpg

Helpful resources

Announcements
News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

Power Apps Community Call

Power Apps Community Call- January

Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST

PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

Top Solution Authors
Top Kudoed Authors
Users online (4,530)