Trouble manipulating Stock of Item based on Quantity of Order

Hi all,

Unsure if this is related as much to Database as it is Workflows, but I couldn’t see a sub-category for workflows.

I’m building a shopping cart style function which needs to include a Stock feature per item, but I’m having issues accurately correcting the stock of the right items in my workflow. I have an overview of my database below, and the purchase flow is as follows:

  • Customer (typically unregistered user) will choose items from a menu page
  • Customer can select multiple items for their order (ie. 2 x Fanta)
  • As an item is added to cart, an Order is created and tied to the User as CurrentOrder
    – An Order contains a list of Ordered Items, Total Price, etc
    – An Ordered Item contains a Menu Item, Price and Quantity
    – A Menu Item contains Image, Description, Price and Stock (among others)
  • When the customer has paid for his CurrentOrder, it is removed from CurrentOrder and instead added as PlacedOrder (List of Orders) to indicate that the order is completed
    When an order is paid for/completed, I want the Stock of Menu Item to decrease depending on the Quantity of Ordered Item. (Highlighted in red underneath)

Now I’m at a point where the only hurdle I simply can’t wrap my head around is in the workflow where I move the Order from CurrentOrder to PlacedOrders, I also want it to decrease the Stock of all Menu Items in List of Ordered Items with their respective quantities.

A couple of caveat’s, just to be completely transparent;

  • A customer/Current User can order multiple different Menu Items (which in turn becomes Ordered Items)
  • A customer/Current User can order a quantity of each of those items
  • I don’t want to decrease/update the Stock when the item is added to the CurrentOrder because;
    – I would need to update it again as the customer removes the item from CurrentOrder
    – Opens a door to draining the Stock of an item artifically without submitting an order
    – It would be an increase in DB calls, that I think can be avoided
    – Any abandoned carts would need to be purged frequently to ensure up-to-date stock

Okay, with that backstory, I hope I have made my struggles clear - if not I’d be happy to elaborate further! On to my workflow trials - the following is set to happen as “Submit Order!”-button is clicked (still pending payment platform integration, so for now skipping this step).

In it’s purest form, I believe the following is accurate and that my biggest struggle is the missing expression:
Annotation 2020-08-15 103401

To ensure this is accurate, let’s try the following - just to decrease the stock with 1. So I’ll set my workflow as follow and ‘order’ a couple of different items.

Annotation 2020-08-15 103602

DB prior to order:
Annotation 2020-08-15 103745

Here is my final order:
Annotation 2020-08-15 103922

And the updated DB:
Annotation 2020-08-15 104036

So as expected it ran through all of the items, and subtracted 1 from the Stock. In other words, going back to the workflow screenshot above, I’m messing with the right Type of things, the right List to change and the correct field. The only thing I need to change is instead of “- 1” it should be “- the quantity of the respective Ordered Item”.

What makes sense to me is to find the Current User’s CurrentOrder’s Ordered Item’s, and then filter that for the Item that equals the Menu Item the workflow is looking at, and then taking the sum of Quantity.
I run into an issue with this as in the List Filter I don’t see the option to access a Menu Item any other way than through “This Ordered Item’s Item”. Screenshot. The field to change is yellow, but it evaluates to Blue when I close the List Filter meaning I can run this and see if it does what we want.

I have reset the stock of the items from before, so they’re back to 1-2-10-999 and then I submit the same order as before - but the stocks are not updated when I run the workflow above.

Now, running it without the filter and just taking the Sum of the Quantity (which should be 6 for our test order):

Annotation 2020-08-15 105054

We get the following result in the database, where it subtracts 6 from each of the item’s stock. Womp womp. BUT, it tell’s me that I’m messing with the right thing, I just need to figure out how to do it independently and pull up the right quantity.

Annotation 2020-08-15 105227

For previous workflows, I’ve used :filtered:first item’s Quantity for example, but doing this doesn’t give me a “Sum” option.

And now I feel lost even though I’m sure it’s something really simple that is holding me back. I’ve been stuck on this for the past two days now.

I would greatly appreciate any suggestions and things to try! Or if I’m doing this the wrong way, I’m very interested in what is best practice.

Thanks!
Emil

Hi! I am having the same issue… Were you able to find the solution?
Thanks!

Hey! Not yet, no, but I must admit I haven’t tried since. It’s still on my to-do list though, so I will have to revisit it eventually and I’ll post my solution here.

Please do the same if you find something that works before me!

Will do. Thanks!

Figured it out! And it’s actually quite straight forward.

When creating an Ordered Item I added a new field called “Order”, so basically which order does the ordered item belong to and set that to Current User’s Current Order. Besides this the only change I have made is to the “Make changes to a list of Menu Items” (the last screenshot of my original post) where I now do it as follow:

So the biggest change is that I Do A Search For an Ordered Item that matches the Menu Item I am trying to update and the current users current order.

Hope this helps, if not let me know and I can try and explain it further!

2 Likes

Hie, how is it that you have Order as a field under ordered_items?