Shopping Cart Collection Lookup Function Not Working

[Leu0101]

Hey Power Apps Community,

I've dug thru the power apps forum and online but have yet to find the answer I seek. I'm currently using a shopping cart collection. In the first part of my script I am trying to subtract the quantity of the check out collection item against the quantity of the inventory SharePoint list item. 

From my search and own editing here is what I have came up with. However, I believe the lookup function from a year or so ago no longer works in this scenario from what I have found online. Here is what I've got:

Collection = ShoppingCart

In-Stock Asset Manager = Inventory SharePoint Site

Quantity = Column Name of SharePoint Site which holds the Number Column

Quantity = The column name within the collection

ForAll(ShoppingCart, Patch('In-Stock Asset manager', {ID:ID}, {Quantity: Value(LookUp('In-Stock Asset manager',ID=ID,Quantity))-Quantity}))

What it returns is: The requested operation is invalid. Server Response: In-Stock Asset manager failed: Fetching Items Failed. Possible invalid string in filter query.

Any help, guidance, videos, or possible solutions would be helpful and greatly appreciated. This is on my checkout button. Although, I'm currently just testing this part of the formula to see it it actually subtracts from the inventory quantity. My full formula looks like this:

Collection = ShoppingCart

In-Stock Asset Manager = Inventory SharePoint Site

Quantity = Column Name of SharePoint Site which holds the Number Column

Quantity = The column name within the collection

Shopping Cart Orders = SharePoint List which holds the orders

ForAll(ShoppingCart, Patch('In-Stock Asset manager',{ID:ID},{Quantity: Value(LookUp('In-Stock Asset manager',ID=ID,Quantity))-Quantity}));Collect('Shopping Cart Orders',ShoppingCart);Clear(ShoppingCart);Navigate(ViewTicketScreen)

Thank you Power Apps Community!

[WarrenBelz]

Hi @Leu0101 ,

The issue may be the need to disambiguate the ID references - try this

ForAll(
   ShoppingCart As aCart, 
   Patch(
      'In-Stock Asset manager',
      {ID:aCart.ID},
      {Quantity: Quantity - aCart.Quantity}
   )
);
Collect(
   'Shopping Cart Orders',
   ShoppingCart
);
Clear(ShoppingCart);
Navigate(ViewTicketScreen)

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.

[Leu0101]

It's working now. I read another power apps community formula about how sharepoint hates ID:ID. So I change the collection ID to ID1 and it now works. 😄

[Leu0101]

@WarrenBelz I did get it work, however it lead me to my next issue which you may be able to help with. If a user goes back into their order and goes thru the inventory and checkouts the same item to add to his order because he forgot or needs another lets say a hammer in this situation, and they had already checked out a hammer on that order to which when he did check out the first time created a list item in my shopping cart orders sharepoint list showing his order number and 1 hammer checked out, upon checking out a second time it currently will not  update the list item as it already shows the order with one hammer. I assume since it already shows the order number and the hammer it doesnt do anything as it's already there. Could you provide me guidance in how I would go about updating the quantity in the shopping cart order sharepoint list at checkout from the collection ?  Here is the formula that is currently working for me minus the updating of an existing item already ordered in the shopping cart orders sharepoint list:

ForAll(ShoppingCart, Patch('In-Stock Asset manager',{ID:ID1},{Quantity: Value(LookUp('In-Stock Asset manager',ID=ID1,Quantity))-Quantity}));Collect('Shopping Cart Orders',ShoppingCart);Clear(ShoppingCart);Navigate(ViewTicketScreen)

I believe the addition need to be added before this part of the formula.  Collect('Shopping Cart Orders',ShoppingCart) Possibly needing a updateif() part of the formula. Any guidance would be appreciated and thank you for your previous support as well.

[WarrenBelz]

@Leu0101 ,

You are better served to stop people ordering the same item twice (I assume they have unique identifier).

On the "Save" of the item to the cart your would do something like 

If(
   CountRows(
      Filter(
         ShoopingCart,
         Identifier = ThisItem.Identifier
      )
   ) > 0,
   Notify("You have already added this item to your cart"),
   WhateveryourSaveCodeIsNow
)

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.

[Leu0101]

So if they select their order thru the app and go thru the inventory again and select the hammer it will not let them add another hammer if I followed that formula. I need them to be allowed to select another hammer and add it to the collection. Upon hitting the check out button, I would need a formula that will update the quantity of that item already listed in the Shopping Cart Orders sharepoint list.

To give you an idea of our app. Currently a maintenance worker logs in and is assigned a ticket. The ticket has a work order associated with it. They can go into the ticket and browse the gallery(In-Stock Asset Manager) of material items needed for the job. He finds a hammer in the inventory and and adds 1 hammer into the collection (shoppingcart). He then checks out. When he checks out it subtracts the quantity amount from "in-stock asset manager"  & then the order is submitted to a sharepoint list which holds the orders called "Shopping Cart Order". If he goes and to a job site a realizes he needs 2 hammers, he will come back to the shop, go into the app, go into his assigned work order ticket, go to the inventory, find the same hammer, add it to cart, check out, and update the order (Shopping Cart Order). This way we know how much material he used for that work order.

[WarrenBelz · Accepted solution]

@Leu0101 ,

I am sorry, but I am lost a bit here as to what the problem is - won't he simply order another hammer?

[Leu0101]

Well when he goes thru that process and checks out the second time it will not add it to the sharepoint list "shopping cart orders". It will add other items that are not already attached to "Shopping cart orders" but it will not add another item of the same ID if it's already listed under that order. That's why I'm looking to update the quantity of that list item.

[Leu0101]

@WarrenBelz I made a noob mistake. I had the barcode column set to enforce unique values which was why it would only take one item at a time. Thanks for taking the time out to give me some help and tips. Your time and knowledge is always appreciated! Thank you!