cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Nate0173 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
WarrenBelz Community Champion
Community Champion

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

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

Nate0173 Helper I
Helper I

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

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
WarrenBelz Community Champion
Community Champion

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

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.

Nate0173 Helper I
Helper I

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

@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

          }

     )

)

WarrenBelz Community Champion
Community Champion

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

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

Nate0173 Helper I
Helper I

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

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

Nate0173 Helper I
Helper I

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

@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

WarrenBelz Community Champion
Community Champion

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

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.

 

Nate0173 Helper I
Helper I

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

@WarrenBelz 

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

 

Really appreciate you looking into this!

 

PowerAppIrrigationPartsCartIssue1.jpgPowerAppIrrigationPartsCartIssue2.jpg

WarrenBelz Community Champion
Community Champion

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

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}
      )
   )
)

 

Nate0173 Helper I
Helper I

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

@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
MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!

Power Platform 2020 release wave 1 plan

Power Platform 2020 release wave 1 plan

Features releasing from April 2020 through September 2020

Top Solution Authors
Top Kudoed Authors
Users online (7,129)