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:
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.
DB prior to order:
Here is my final order:
And the updated DB:
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):
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.
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
