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
One thing I can see is the extra line in the UpdateIf - it should be (also had logic wrong way - now fixed -  on original post (dangers of free-typing and not testing)

UpdateIf(
         IrrigationShoppingList,
         IrrDesc = colrrParts.IrrDesc,
         {Quantity: colrrParts.Quantity + 
          IrrigationShoppingList.Quantity}
      )

So you can better understand my logic on this - comments are below

ForAll(                     
   ColrrParts(               //loop all items of through collection ColrrParts
      If(
         IsBlank(
            LookUp(         // see if the item is already in IrrigationShoppingList
               IrrigationShoppingList,
               IrrDesc = colrrParts.IrrDesc,       //matching records
               IrrDesc                 //select one field - not technically necessary
            )
         )
      ),
      Patch(                 //If not there add new record
         IrrigationShoppingList,
         Defaults(IrrigationShoppingList),    
         {
            IrrDesc: colrrParts.IrrDesc,                  //item description
            Quantity: colrrParts.Quantity                //add quantity
         }
      ),
      UpdateIf(                 //If already there update existing record
         IrrigationShoppingList,
         IrrDesc = colrrParts.IrrDesc,       //find the right record to update
         {Quantity: colrrParts.Quantity + 
          IrrigationShoppingList.Quantity}     //add quantity to existing quantity
      )
   )
)

[Nate0173]

@WarrenBelz 

Because I have not been able to isolate the issues, I decided to break the entire code into each function individually (ForAll, Patch, and UpdateIf). With the ForAll function, I changed the code to either navigate to the EditScreen or the HTML_Receipt screen instead of keeping the original code.

ForAll:

ForAll(
    ColIrrParts(
        If(
            IsBlank( 
                LookUp( 
                    IrrigationShoppingList, 
                    IrrDesc = ColIrrParts.IrrDesc,
                    IrrDesc 
                ) 
            ),
            Navigate(EditScreen),
            Navigate(HTML_Receipt)
        )
    )
)

Error: Invocation of unknown or unsupported function.

Error: Invalid number of arguments: received 1, expected 2.

Patch:

Patch(
    IrrigationShoppingList,
    Defaults(IrrigationShoppingList),
    {
        Category: ColIrrParts.Category,
        IrrDesc: ColIrrParts.IrrDesc,
        Quantity: ColIrrParts.Quantity
    }
)

Error: The type of this 'Category' does not match the expected type 'Text'. Found type 'Table'.

Error: The function 'Patch' has some invalid arguments.

UpdateIf:

UpdateIf( 
    IrrigationShoppingList, 
    IrrDesc = ColIrrParts.IrrDesc, 
    { 
        Quantity: ColIrrParts.Quantity + 
        IrrigationShoppingList.Quantity
    } 
)

Error on the "=" symbol: Invalid argument type.

Error on "Quantity" in both "ColIrrParts.Quantity" and "IrrigationShoppingList.Quantity": Invalid argument type. Expecting one of the following: Number, Text, Boolean.

Complete Code:

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

[WarrenBelz]

OK @Nate0173 ,

Firstly on this one

ForAll(
   ColIrrParts(
      If(
         IsBlank( 
            LookUp( 
               IrrigationShoppingList, 
               IrrDesc = ColIrrParts.IrrDesc,
               IrrDesc 
            ) 
         ),
         Navigate(EditScreen),
         Navigate(HTML_Receipt)
      )
   )
)

You cannot have a Navigate with a ForAll - the screens would be flicking all over the place. In your case, the only thing you should be doing in the ForAll is patching.

The next bit

Patch(
   IrrigationShoppingList,
   Defaults(IrrigationShoppingList),
   {
      Category: ColIrrParts.Category,
      IrrDesc: ColIrrParts.IrrDesc,
      Quantity: ColIrrParts.Quantity
   }
)

I cannot see your data, but it is saying that colIrrParts.Category is a table - when applying a Patch, the source and destination data must be of the same type and construction. Have a look at this in View > Collections, then if a table, look at the heading that contains the value you are after. If it is (for example) Value, then the Patch is ColIrrParts.Category.Value.

The last one

UpdateIf( 
    IrrigationShoppingList, 
    IrrDesc = ColIrrParts.IrrDesc, 
    { 
        Quantity: ColIrrParts.Quantity + 
        IrrigationShoppingList.Quantity
    } 
)

Both of these need to be numeric fields - it is telling you they are not. You might try

 Quantity: Value(ColIrrParts.Quantity) + 
        Value(IrrigationShoppingList.Quantity)

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 it makes sense why navigate will not work on the ForAll function.

For the Patch function, I was able to clear all the errors by referring to the gallery items instead of the collection ColIrrParts. Will this work?

Patch(
    IrrigationShoppingList,
    Defaults(IrrigationShoppingList),
    {
        Category: GalleryPartsCart.Selected.Category,
        IrrDesc: GalleryPartsCart.Selected.IrrDesc,
        Quantity: GalleryPartsCart.Selected.Quantity
    }
)

Instead of this:

Patch( 
    IrrigationShoppingList, 
    Defaults(IrrigationShoppingList), 
    { 
        Category: ColIrrParts.Category, 
        IrrDesc: ColIrrParts.IrrDesc, 
        Quantity: ColIrrParts.Quantity
    } 
)

For UpdateIf, I again referred to the gallery item instead of the ColIrrParts collection item to get rid of some errors. The only error I have now is on "IrrigationShoppingList.Quantity". The error is "Invalid argument type. Expecting one of the following: Number, Text, Boolean." I cannot figure out how to pull the value of the quantity in this collection. It seems like it needs the item in the collection specifically identified. Here is my code:

UpdateIf( 
    IrrigationShoppingList, GalleryPartsCart.Selected.IrrDesc = IrrDesc, 
    { 
        Quantity: GalleryPartsCart.Selected.Quantity + 
        IrrigationShoppingList.Quantity
    } 
)

[WarrenBelz]

Hi @Nate0173 ,

Maybe for the second one

UpdateIf( 
   IrrigationShoppingList, 
   GalleryPartsCart.Selected.IrrDesc = IrrDesc, 
   { 
      Quantity: Value(GalleryPartsCart.Selected.Quantity) + 
      IrrigationShoppingList.Quantity
   } 
)

But you are back to patching one record at a time - is this what you want to do?

[Nate0173]

@WarrenBelz 

I want to patch all records at one time from the collection (ColIrrParts). Does this mean I cannot use the "GalleryPartsCart.Selected.IrrDesc" because it patches only one item at a time? If that's the case, I'm back to using "ColIrrParts.Quantity" which gives me an error "Invalid argument type. Expecting one of the following: Number, Text, Boolean."

Also, I'm guessing that means under the "Patch" function, I need to be referring to "ColIrrParts" instead of "GalleryPartsCart" or I will have the same issue of patching one item at a time instead of all items at one time. Correct?

[WarrenBelz]

@Nate0173 ,

That is exactly what I am saying and goes right back to the start of this post.

A Patch is not a difficult process to debug - you just need to ensure that the source and destination data types (text, numeric, boolean etc) and structures (single value, complex/table) are the same. I cannot do this for you as I cannot see any of it.

You have a Collection to test - collect this and go to View > Collections and see what is in the field you are having issues with. 

You have a data source - go to the Field definition and make sure this is the same.

In the case of ColIrrParts.Quantity, I think it needs to be numeric on both ends

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

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