ODBC: Use a Custom query to upload shipment details

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.

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.

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.

Scenario 1

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}'
)

Scenario 2

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}'
)

Scenario 3

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