The purpose of this page is to show you how to create a SQL Group report that lets you select multiple orders, and have their shipment information match the information from a shipment that you specify.

Create a SQL report, that is run from the Group option of the Order Explorer, that you can specify a Shipment Number on, and update the Tracking Number and Shipment Date on all of their shipments.

HIGH This query uses an update statement that does change data. Be careful when running any update statements and if you're unsure, ask first before doing so.

  1. Go to Setup → Reporting Setup.
  2. Under Reports on Action Toolbar → .. for Orders (& Invoices) → Select Group Reports
  3. Click on Add at the top of the list, and select “New SQL Report”
    1. Give the report a name, IE: Multi Order Shipment Update
    2. Select the SQL Radio Button, and use this code:
UPDATE Shipments
  ModifiedByUser = 'ShipmentUpdateReport'
  ,ModifiedDate = GetDate()
  ,Shipments.TrackingNumber = (SELECT TOP 1 TrackingNumber FROM Shipments S WITH(nolock) WHERE S.ShipmentNumber LIKE )
  ,Shipments.ShipDate = (SELECT TOP 1 ShipDate FROM Shipments S WITH(nolock) WHERE S.ShipmentNumber LIKE )
  ,IsShipped = 1
  TransHeader WITH(nolock)
  LEFT OUTER JOIN Shipments WITH(nolock) ON TransHeader.ID = Shipments.TransHeaderID
  (TransHeader.ID IN ())
  1. Under the Options Radio Button, select “Execute This SQL”
  2. Go to the Options Tab, Change the Parameter Type for Order to Order
  3. Make sure the Parameter Type for ShipmentNumber to String
  4. Save
Running the Report
  1. Go to Explore Orders
  2. If on 5.1, you will see the checkboxes on the side to select orders. If on a previous version, there will be a “Group” button at the top that will show the checkboxes
  3. Check the orders you want to update, then go to the “Print” button on the Right. Drill down to Group Reports, and select the report that you created
  4. Type in the Shipment Number that you want to make the selected orders match (for example: 1031-saa)
  5. Click on Execute

Contributor: kking, Cyrious

Date: 8/7/2014

Version: Control 5.1

You could leave a comment if you were logged in.