Tools of the Trade - Diagnosing orders with errors

Hello world.

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:

https://world.episerver.com/Modules/Forum/Pages/Thread.aspx?id=194165

Thanks for reading! I hope that this will help you in your diagnosing and debugging endeavors!

Jafet “Jeff” Valdez