Try our conversational search powered by Generative AI!

Shipment Lineitems


Working on the latest version on Epi/Episerver Commerce.

I am having to create some custom reports for our client. One of the reports requires the following:

  • PO Number
  • Date of order
  • Warehouse
  • Delivery status
  • Payment status
  • Total (item cost in £s)
  • Postage (in £s)
  • Gross (Total + postage)
  • Refunds (in £s)

Each order can have split shipments, so different warehouses (stores) fulfilling orders. Looking at the database schema

I can't see any direct relationship between shipment and lineitem tables. As warehousecode in the LineItems table is now obsolete we would now have to use the Shipment table to find out the warehouse/store.

So the question is, at a database level, how can I found out which split shipment is shipping which lineitem so I can work out which warehouse/store is dealing with that lineitem.

May 20, 2019 12:00

I would suggest to go with apis approach instead of querying database directly. It is safer and more future proof. The relationships between shipments and lineitems are handled at api level anyway

May 20, 2019 12:34


Thanks, while going down the API root is possible these are going to be heavy queries and surely benifit from query execution plans and optimisation. I'm not sure how well episerver could handle thousands and thousands of orders?

May 20, 2019 12:37

For the purpose of report which is usually run only daily at low traffic, loading orders by batches of thousands should not be a problem

May 20, 2019 13:22

Hi Quan,

I cannot guarantee when the reports will be ran! And as the requirements are for the report to be outputted into CSV there will be no pagination, so in the case all orders (between any possible date range) will be loaded. This is my concern.

May 20, 2019 13:25

I don't get it. What is the problem with loading by batches and process data and save to the csv files when you are done with a batch?

May 20, 2019 13:29
  • Hit the database to get total rows using FindPurchaseOrders, this in turn calls [ecf_Search_PurchaseOrder] which in turn calls [mdpsp_avto_OrderGroup_PurchaseOrder_Search]
  • Hit the database in calling [ecf_Search_PurchaseOrder] which in turn calls [mdpsp_avto_OrderGroup_PurchaseOrder_Search] with search parameters
  • Repeat the above step multiple times per batch
  • Iterate/transform results

As you can see its going to be pretty heavy on the database!

May 20, 2019 13:36

Reporting is supposed to be data heavy. Unless you run your report every few minutes it would not be a problem. I don't know what you are doing with your reports but sales data can "appended" - for example daily, instead of getting full data every time

May 20, 2019 14:34
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.