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

[Nate0173]

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

[WarrenBelz]

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]

@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 · Accepted solution]

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.

[Nate0173]

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

[Nate0173]

@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]

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]

@WarrenBelz 

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

Really appreciate you looking into this!

[WarrenBelz]

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]

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