|Number of votes:||5|
Welcome to my first blog post here on Episerver World! :D
Today I thought that I’d share one of my most commonly used tools to diagnose and debug errors on PurchaseOrders or Carts.
For the last couple of years, I’ve been involved in a project that has had some pretty complicated order handling logic. It has advanced return and exchange handling, different payment providers, different shipping methods, split shipments and multi warehouses while also having integrations on many of these processes that could fail for whatever reason.
With so many processes being able to affect an order in various stages there has unfortunately been some cases where an order has come out the other end all weird looking. Maybe this weirdness manifested itself in a button being disabled unexpectedly, a return failing to be processed even though it shouldn’t, some data missing or things of that nature.
It’s a very simple tool, really. Just some SQL SELECT queries that gathers up all the relevant information for an order in one place. While Commerce Manager is great in many ways in terms of displaying information, it still doesn’t display all the possible fields that could be relevant for you when you have your detective hat on.
Alright no more talking, here it is:
DECLARE @OrderGroupId int = '12345' -- Set this to the ordergroupId of the order you want to inspect SELECT * FROM OrderGroup og JOIN OrderGroup_PurchaseOrder po ON og.OrderGroupId = po.ObjectId WHERE og.OrderGroupId = @OrderGroupId -- or you can JOIN OrderGroup_ShoppingCart instead if you want to inspect Carts SELECT * FROM OrderForm oform JOIN OrderFormEx ofex ON oform.OrderFormId = ofex.ObjectId WHERE oform.OrderGroupId = @OrderGroupId SELECT * FROM Shipment sh JOIN ShipmentEx shex ON sh.ShipmentId = shex.ObjectId WHERE sh.OrderGroupId = @OrderGroupId SELECT * FROM LineItem l JOIN LineItemEx lex ON l.LineItemId = lex.ObjectId WHERE OrderGroupId = @OrderGroupId ORDER BY LineItemOrdering ASC SELECT * FROM OrderGroupAddress oga JOIN OrderGroupAddressEx ogae ON oga.OrderGroupAddressId = ogae.ObjectId WHERE oga.OrderGroupId = @OrderGroupId -- Add SELECTs for as many payment types that an order can have: SELECT * FROM OrderFormPayment ofp JOIN OrderFormPayment_Other ofpex ON ofp.PaymentId = ofpex.ObjectId WHERE ofp.OrderGroupId = @OrderGroupId SELECT * FROM OrderFormPayment ofp JOIN OrderFormPayment_GiftCard ofpex ON ofp.PaymentId = ofpex.ObjectId WHERE ofp.OrderGroupId = @OrderGroupId /* For payments you can JOIN these tables: OrderFormPayment_Other OrderFormPayment_GiftCard OrderFormPayment_CashCard OrderFormPayment_CreditCard OrderFormPayment_Invoice */
Pretty simple really. Based on an OrderGroupId we get data about the OrderGroup, all the OrderForms (including “ReturnForms”), Shipments, LineItems, OrderAdresses and Payments. On all the levels of abstraction you also get their additional metadata that you’ve added joined up, so it’s all in one convenient place for you to inspect.
Obviously, you’ll need to find a way to retrieve the OrderGroupId before putting it into this query. I’m actually running a bit more complicated version where I can use a custom order tracking number and an id from our main payment provider to retrieve the order data instead of an OrderGroupId. I omitted that in what I shared above because it’s not really general enough to be used by everyone.
What I look for are oddities. This could maybe be a field missing a value because it got nulled away in some dark corner of a Workflow. Or maybe a string MetaField containing serialized data that got cut off because you didn’t expect that a customer would ever use THAT many bonus checks.
Either way, after you’ve used this query in various stages of your order processing flow you’ll develop a feel for what seems out of place for orders in your project.
First, I’d like to say that I’m impressed! :D
Secondly, here’s some other examples of where I use this query:
Me and my colleagues have used it extensively, but I actually got inspired to share this more publicly after helping a fellow Episerver Developer on the forums figure out why a button in Commerce Manager didn’t work.
Here’s the thread:
Thanks for reading! I hope that this will help you in your diagnosing and debugging endeavors!
Jafet “Jeff” Valdez