Patching multiple values to SharePoint

[axt3641]

Hello PowerApps Community 🙂
I am building an online shopping car. I'm using a Collection to collect the items and then patching them to a SharePoint List.   There is an Inventory number field in SharePoint that I would like reduced by the number of items (Qty) selected inside the Collection.

Collection = colMyOrder
SharePoint List = 'Company Store'
Inventory Column in SharePoint = 'Inventory'
Gallery = galProducts
CurrentInventory_Lbl = a Label that contains the Inventory number from SharePoint
Qty = a variable that has the number of inventory to be checked out from the Collection (which must be subtracted from the CurrentInventory)

Here is my current code:
ForAll(
colMyOrder,
Patch(
'Company Store',
galProducts.Selected,
{'Inventory (Inventory0)': Value(CurrentInventory_Lbl.Text) - Qty}
)
);

The issue:

This code works fine when I only check out 1 item from the Collection. The Inventory field is SharePoint is subtracted correctly. The issue is when I check out multiple items! When I do so, only the last Inventory field is updated whereas the other items stay the same

Example:
If I checked out 5x PENCILS, 2x DESKS, 7 ERASERS, the only Inventory in SharePoint that will be updated is the ERASERS because it is the last one to be added to the collection

If there is a way to make sure all inventory fields in SharePoint are updated, that would be much appreciated!

Thanks much as always and appreciate all the value that this community provides.

Cheers,

Andy

[mdevaney · Accepted solution]

@axt3641

Your code currently looks like this.

ForAll(
    colMyOrder,
    Patch(
        'Company Store',
        galProducts.Selected,
        {'Inventory (Inventory0)': Value(CurrentInventory_Lbl.Text) - Qty}
    )
);

The reason your PATCH always updates the same record is because of this code segment in the 2nd argument.  It tells PowerApps which record should be updated in 'Company Store'.

galProducts.Selected

The selected property will ensure the current selection in a gallery will be the target.  Instead, we want to target the "current" record in the ForAll loop.

ForAll(
    colMyOrder,
    Patch(
        'Company Store',
        ID = colMyOrder[@ID],
        {'Inventory (Inventory0)': Value(CurrentInventory_Lbl.Text) - Qty}
    )
);

This solution assumes the ID number referencing the item in the company store is contained in colMyOrder.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[axt3641]

Thanks for the reply, Mr.Devaney.

It makes sense why "galProducts.Selected' would be the issue since it only selects the currently selected value and not all the items in the collection!

I tried the following but it seems not to work. I think the reason is that I'm patching the ID field a bit differently.

ID = colMyOrder[@ID]

I am using the following in my Collection:

UpdateContext(
        {
            ctxPrevcartAdd: LookUp(
                colMyOrder,
                galProducts.Selected.ID = ItemID
            )
        }
    );
    If(
        ctxPrevcartAdd.Qty > 0,
        Patch (
            colMyOrder,
            ctxPrevcartAdd,
            {Qty: Value(Details_QtyTxt.Text) + ctxPrevcartAdd.Qty}
        ),
        Collect(
            colMyOrder,
            {
                ItemID: galProducts.Selected.ID,
                Qty: Value(Details_QtyTxt.Text),
                Title: galProducts.Selected.'Item Name'
            }
        )
    );

 
I looked up the Company Store SharePoint List and the "ID" column name is correct (as seen in the attachment).

I tried the following with your advice but still no dice:

ItemID = colMyOrder[@ItemID]
&
colMyOrder[@ItemID] = galProducts.Selected.ID
&
galProducts.Selected.ID = colMyOrder.ItemID

Any thoughts?
Cheers

[axt3641]

I added a Label to the GalProducts Gallery with the Item ID called "Item_ID_Lbl".
I tried doing this and still no dice...

galProducts.Selected.Item_ID_Lbl = colMyOrder[@ItemID]

[mdevaney]

@axt3641 

Thank you for providing the additional code.  More code is usually helpful but in this case I cannot see how it ties back to the original problem.  🤔

Let me make sure I understand the purpose of these things :

• myOrders -  this Collection holds one record for each Order.  A record may only contain 1 type of product but the quantity can be multiple units. 
• 'Company Store' - this SharePoint list contains one record for each Product and the quantity remaining.

We need some way to link these two tables together so the FORALL + PATCH can update all the products in 'Company Store'.  Is there any "Product ID" stored in myOrders that can be used to reference the ID of the Item in 'Company Store'?  I believe this is what we need to get the app working.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[axt3641]

Absolutely. Please see my text in red:

Let me make sure I understand the purpose of these things :

• myOrders -  this Collection holds one record for each Order.  A record may only contain 1 type of product but the quantity can be multiple units. 
  Correct. Whenever someone adds something to the Collection, it'll capture the following:
  - Title which is the Item Name
  - Qty which is the Quantity
  - ItemID which is should be the item ID!
  
  In the Patch, I used this to set the ItemID:
  ItemID: galProducts.Selected.ID
  (this appears to be working. I attached a screenshot of the collection after I added a few images to ItemID)
  
  
• 'Company Store' - this SharePoint list contains one record for each Product and the quantity remaining.
  The Company Store is a SharePoint List that I have that has a list of all the different inventory items. This list has:
  ID
  Item Name
  Inventory number  and a handful of other items

It would seem the ItemID in the colMyOrders should match up exactly with the SharePoint CompanyStore's ID field!
For some reason it doesn't seem to like it when I call the ItemID (maybe because I'm missing something after it)?
I tried adding a label call ItemId_Lbl.Text on the Gallery too but it's still not playing nice hah

[mdevaney]

@axt3641 

I very much appreciate the screenshots of your collections.  I wish every forum poster was as generous as you!

Here's an updated idea from me.  I believe the 2nd argument to PATCH will work.  I also rewrote the 3rd argument to deduct the Qty from the current inventory amount.  Lets see if you can adapt it to your app.

ForAll(
    colMyOrder,
    Patch(
        'Company Store',
        ID = colMyOrder[@ItemID],
        {'Inventory': LookUp('Company Store',ID=colMyOrder[@ItemID],'Inventory') - Qty}
    )
);

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[axt3641]

You are the man, Mr.Devaney! I'll give it a go and will update you shortly.
Hope you have a great New Year in the interim 🙂

[axt3641]

Hey Mr.D,
We should be good to go! I made a few quick modifications from the code that you sent me with some inspiration from IWMentors:

ForAll(
colMyOrder,
Patch(
'Company Store', {ID: ItemID},

{'Inventory (Inventory0)': Value(LookUp('Company Store', ID = ItemID, 'Inventory (Inventory0)')) - Qty}
)
);

We are good to go amigo! Quick question... It seems like the difference changer was a semi colon in the 2nd Patch Condition:
{ID: ItemID}. Any thoughts on why this worked and not our initial "=" code?

[mdevaney]

@axt3641 

Interesting.  I have not seen that syntax for the 2nd argument before.  You can bet that I am going to look into it though!  If I figure it out I will post back here 🙂