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.
Concept
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.
Cautions
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.
Steps to Configure
- Go to Setup → Reporting Setup.
- Under Reports on Action Toolbar → .. for Orders (& Invoices) → Select Group Reports
- Click on Add at the top of the list, and select “New SQL Report”
- Give the report a name, IE: Multi Order Shipment Update
- Select the SQL Radio Button, and use this code:
UPDATE Shipments SET 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 FROM TransHeader WITH(nolock) LEFT OUTER JOIN Shipments WITH(nolock) ON TransHeader.ID = Shipments.TransHeaderID WHERE (TransHeader.ID IN ())
- Under the Options Radio Button, select “Execute This SQL”
- Go to the Options Tab, Change the Parameter Type for Order to Order
- Make sure the Parameter Type for ShipmentNumber to String
- Save
Running the Report
- Go to Explore Orders
- 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
- 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
- Type in the Shipment Number that you want to make the selected orders match (for example: 1031-saa)
- Click on Execute
Source
Contributor: kking, Cyrious
Date: 8/7/2014
Version: Control 5.1
You could leave a comment if you were logged in.