ShipWorks can be configured to upload shipment details to an ODBC data source in a couple of ways. The simplest way is mapping the ShipWorks data directly to a table. The other way is writing a SQL query providing the capability for ShipWorks to update external systems in a manner that fits your needs. 



Upload Shipment Details to a Specific Table

This is the simplest way for ShipWorks to upload shipment details to your ODBC data source. The same sort of mapping that was performed for importing orders into ShipWorks is used to tell ShipWorks how you'd like data sent back to your ODBC data source. However, this simplicity comes at the expense of a few assumptions being made on the part of ShipWorks. You can only upload shipment details to a single table and all uploads are done using UPDATE statements issued to the ODBC data source. In other words, ShipWorks will not create any additional rows in your database when uploading; it will only modify existing data (rows) based on the table that is selected and the mapping that is configured. 

We find that this upload strategy works well in the majority of situations. For those cases where this approach is not feasible, ShipWorks allows you to write your own SQL query that is specific to your needs. 


Upload Shipment Details Using a Custom Query

Sometimes the table-based upload strategy mentioned above may not be feasible. This is dependent on how your shipment data is structured in your ODBC data source. For example, you may need to create new rows rather than just updating existing rows or you may want to upload shipment details to multiple tables in your ODBC data source. 

The remainder of this article discusses writing the queries Shipworks will use to dynamically inject shipment details (tracking number, carrier, service, etc.) into your ODBC datasource. We'll also provide a few example queries to help you get going.

The same XML schema used by the ShipWorks' powerful template/reporting engine in combination with XPath can be used to write custom queries to upload shipment details to an ODBC data source. There are a number of different data points in ShipWorks that can be used when uploading shipment details; for the purposes of this article, we're just going to focus on the common shipment fields. 

A few XPath expressions for accessing common shipment data are listed below:  All of these XPath expressions will give you the most recently created shipment.

Data Element
XPath expression
Shipping carrier
//Shipment[last()]/ShipmentType
Shipping service //Shipment[last()]/ServiceUsed
Tracking number of the shipment
//Shipment[last()]/TrackingNumber
Ship date
//Shipment[last()]/ShippedDate
Date the label was created
//Shipment[last()]/ProcessedDate
Whether the shipment is a return
//Shipment[last()]/ReturnShipment
Status of the shipment
//Shipment[last()]/Status
Total cost of the shipment
//Shipment[last()]/TotalCharges
ShipWorks user name of the individual that created the label
//Shipment[last()]/ProcessedUser/Username

You are not limited to using only these values when constructing your SQL queries; any field available in the XML source can be used. Check out the example SQL queries below to see how these are used to construct a custom query for uploading shipment details. Please see the W3C Schools website for more information on XPath.


Sample Upload Queries

Note: these queries target SQL Server; there may be slight syntactical differences for other databases.

Usage scenario: Data needs to be uploaded in the form of INSERT statements

In this example, a query inserts shipment details of the most recently created shipment into a table called MyShipments 
INSERT INTO [MyShipments]
(
MyOrderNumber,
ShipDate,
Carrier,
Service,
Tracking,
FulfilledBySystem,
FulfilledByUser
)
VALUES
(
{//Order/Number},
'{//ShippedDate}',
'{//ShipmentType}',
'{//ServiceUsed}',
'{//TrackingNumber}',
'ShipWorks',
'{//ProcessedUser/Username}'
)

 

Usage scenario: Shipment information from ShipWorks need to be uploaded to multiple tables.


In this example, a query updates two tables:


1. Update order in MyOrder table to indicate the order is shipped

2. Insert a new record to the MyShipment table with the shipment details 

UPDATE [MyOrder]
SET OrderStatus = 'Shipped'
WHERE MyOrderNumber = {//Order/Number}

INSERT INTO [MyShipments]
(
MyOrderNumber,
ShipDate,
Carrier,
Service,
Tracking,
FulfilledBySystem,
FulfilledByUser
)
VALUES
(
{//Order/Number},
'{//ShippedDate}',
'{//ShipmentType}',
'{//ServiceUsed}',
'{//TrackingNumber}',
'ShipWorks',
'{//ProcessedUser/Username}'
)

 

Usage scenario: The schema of the ODBC data source only supports a single shipment per order, and we always just want to retain the information for the latest shipment.


This example illustrates a technique for determining whether to do an INSERT or UPDATE.

 

IF EXISTS (SELECT MyOrderNumber FROM [MyShipments] WHERE MyOrderNumber = {//Order/Number})
BEGIN
-- A record already exists in the MyShipments table, so we just want to update it
UPDATE [MyShipments]
SET
ShipDate = '{//ShippedDate}',
Carrier = '{//ShipmentType}',
Service = '{//ServiceUsed}',
Tracking = '{//TrackingNumber}',
FulfilledBySystem = 'ShipWorks',
FulfilledByUser = '{//ProcessedUser/Username}'

WHERE
MyOrderNumber = {//Order/Number}
END
ELSE
BEGIN
INSERT INTO [MyShipments]
(
MyOrderNumber,
ShipDate,
Carrier,
Service,
Tracking,
FulfilledBySystem,
FulfilledByUser
)
VALUES
(
{//Order/Number},
'{//ShippedDate}',
'{//ShipmentType}',
'{//ServiceUsed}',
'{//TrackingNumber}',
'ShipWorks',
'{//ProcessedUser/Username}'
)
END