Tools of the Trade - Diagnosing orders with errors
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.
First a little bit of background
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.
So what is this "Tool of the trade"?
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.
Ok, now I can see the data, what do I do with it?
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.
My orders never have any issues, any other use cases?
First, I’d like to say that I’m impressed! :D
Secondly, here’s some other examples of where I use this query:
- Teaching the structure of Carts/PurchaseOrders in Episerver Commerce to new developers.
This is kind of a nerdy approach, but it’s been very useful so far. Most developers are used to the database way of structuring things. Showing the data and structure like this I can explain what each level of abstraction is responsible for and what data they contain, but also the fact that the system is extensible because you can add your own meta fields.
- While developing new order handling features
I can use this to make sure that the database is correct along the way and spot any unexpected issues with the data being stored.
- When joining a new project that’s been going on for a while.
It’s useful to get details on what meta fields have been added so that you know what you have to work with. This of course also reduces the risk of you adding a field that stores the same data as an existing one.
But hey, don’t take my word for it!
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
It's very nice that you start sharing your experience and knowledge as blog posts. Keep it up and you might earn you-know-what very soon.
One comment - as we always say database schema is blackbox and we don't guarantee backward compatibility on it, it's recommended to use the stored procedure at least. The SQL you wrote can be replaced by this
which is safer and you don't have to worry about missing this or that data
Cool, yeah that seems useful for inspecting order data. The output is a bit messier but also includes more stuff. :D
Anyway your example didn't really work for me but this did:
For Purchase Orders
And heres a version for searching for Carts instead:
You are using Commerce 10.x or earlier. ApplicationId was removed in Commerce 11.
Stay on the edge!