TABLE OF CONTENTS

Background
Requirements
Here's How
Selecting Your Data Source
Import Settings
Column Mapping
Mapping Your Data to ShipWorks
Upload Options
Upload Column Mappings
Store Information
Store Setup

Background

ShipWorks gives you the ability to connect to an external data source through ODBC (Open Database Connectivity).  The ODBC connection can be used to both import data into ShipWorks and to export data from ShipWorks to an external data source.

Requirements

ShipWorks 5.4.XX or greater is required. 
The ODBC option is only available to Enterprise pricing plan subscribers.   

Here's How

Existing ShipWorks Users
1.  In ShipWorks, click on the Manage tab.  Then, click the Stores button. Click the Add Store button.

MANAGE > STORES

New and Existing ShipWorks Users

2.  In the Online Store setup screen, click on the drop-down menu under 'What Platform do you sell on?' and select ODBC.

Online Store Setup


3.  Click Next.


Selecting Your Data Source


4.  On the Setup Import Data Source screen, click the Data Source: drop-down menu and select the data source to which you wish to connect.


NOTE:  If the data source to which you are attempting to connect is not shown in the list, you can click the Manage Data Sources button to open the Windows ODBC Data Source Administrator dialog from which you can add a custom data source.  You may need to consult with your system administrator for information on how to add your data source. 


Setup Import Data Source


5.  If required, enter the username and password.


Username and Password


6.  It is possible to test the connection to your data source by clicking on the Test Connection button.  A dialog box will notify you of a successful connection or display an error message if the connection was not successful.


7.  Once you have a successful connection, click Next.


Successful Connection to ODBC


Import Settings


8.  There are several options available to you on the Import Settings screen.  Let's take a look at these options.


Name:  You can assign a custom name to your data source within the ShipWorks software.




Load Map:  If you previously created an ODBC connection in ShipWorks, you can use this button to import a saved map of your ODBC settings.  Since we are setting up your ODBC connection for the first time, we will skip this step.




Downloads:  Allows you to specify how orders are imported into ShipWorks.  

Orders by last modified date:  Selecting this option tells ShipWorks to only import orders that are newer than the date and time of the last import.

All orders:  Selecting this option tells ShipWorks to import all orders from your data source each time an import occurs.



Column Source:  Allows you to specify from where you wish to import your data.

Importing From a Single Table

Table:
  If all of your data will be imported from a single table, select this option.  With TABLE selected you can then select the table from which your data will be imported.

Table Option 

Importing Data Using a Custom Query

Query:  If your data will be imported from multiple tables or if you need to write a custom query, select this option.  You will then have the option to enter your custom query.

PLEASE NOTE:  Custom query is an advanced feature meant to be used by customers who are familiar with SQL, etc.  The software does not validate the queries used and incorrectly specified queries may adversely affect or corrupt your data.  Click OK to accept this statement.

Custom Query Warning

You will now be able to enter your query.  You can test your query by clicking on the EXECUTE QUERY button.  Remember, this will execute a live query against your data source.  The top 25 results will be listed for your review.


Once you have all of your settings selected correctly on the Import Settings screen, click Next.


Column Mappings

On the Column Mappings screen you will map the data from your data source to the available fields in ShipWorks.  

9.  Use the Item Settings section to specify how line items on your orders are to be handled.

All Line Items On The Same Row 

A Single Row:  Select this option if all of the line items for an order are on a single row.
 Single Row Items

Next, select the maximum number of line items that the orders will contain.  Note that the Column Mappings column will automatically add a separate line item mapping for the number of items that you select.
 
Number of Items

If the line items on the orders have additional attributes that need to be mapped, select the number of attributes. 





Line Items Are On Multiple Rows

Multiple Rows:
  Select this option if the line items are on separate rows.



Next, select the column that uniquely identifies each order.  


 If the line items on the orders have additional attributes that need to be mapped, select the number of attributes.


Mapping Your Data To ShipWorks

The Column Mappings section allows you to map the data from your data source to the fields in the ShipWorks database.  Please note that any fields indicated with a red asterisk (
* )  requires mapping.


10.  To map the columns from your data source to the fields in ShipWorks, select the correct column form the Source Column drop-down menu for each row in the Column Mappings section, matching your source to the correct ShipWorks field.

Be sure to map the appropriate columns for the
Order, Address, and Item sections.

Column Mappings

Once all of your mappings are correctly completed, click Next.

Upload Options

11.  On the Upload Options screen, select how you would like for ShipWorks to upload shipment details for your store.  You have three available options.

a.  Do not upload shipment details:  Nothing is uploaded to any data source.
b.  Upload shipment details to the same data source:  Uploads shipment details back into the same data source that you have selected for importing.  (see this section below)
c.  Upload shipment details to a different data source:  Select this option if you need to upload your shipment details into a separate data source from the source you selected for import.  (see this section below)

Upload Shipment Details to the Same Data Source

12.  Select the upload Shipment details to the same data source option.  Then click Next.


Upload Options



13.  The Upload Settings screen has several options.  Let's explore them.


Name:  You can assign a custom name to your data source within the ShipWorks software.




Load Map:  If you previously created an ODBC connection in ShipWorks, you can use this button to import a saved map of your of your ODBC settings.  Since we are setting up your ODBC connection for the first time, we will skip this step.




Column Source:  Allows you to specify to where you wish to export your data.

Table:  If all of your data will be exported into a single table, select this option.  With Table selected you can then select to which table you will be exporting your data from the Table: drop-down menu.




Query:  If your data will be exported into multiple tables or if you need to write a custom query, select this option.  You can then enter your query in order to specify to where your data will be imported.

NOTE:  
Custom query is an advanced feature meant to be used by customers who are familiar with SQL, etc.  The software does not validate the queries used and incorrectly specified queries may adversely affect or corrupt your data.  

Click OK to accept this statement.

Custom Query Warning

You will now be able to enter your query.  You can test your query by clicking on the 
Execute Query button.  Remember, this will execute a live query against your data source.  The top 25 results will be listed for your review.

Upload Settings Query

Once you have all of your setting selected correctly on the 
Upload Settings screen, click Next. (proceed to step 14 below)


Upload Shipment Details to a Different Data Source


12.  Select the Upload shipment details to a different data source option.  Then click Next.


Upload Options - Different Source


13.  Please review steps 4 through 7 of this article for instructions on how to connect to an external data source.  Then, once connected to your data source, continue with step 14 below.


Upload Column Mappings


14.  On the Column Mappings screen, match the ShipWorks Fields to the source columns to which you would like the data to upload.  Fields with a red asterisk ( * ) are required fields.  Be sure to map the Shipment and Address fields as needed.


Column Mapping


Once all of your source columns are mapped, click Next.


Store Information


15.  On the Store Information screen, enter the store name and address for your ODBC store.  Then, click Next.



Store Setup Screen


On the Store Setup screen, you can select the number of days of orders that you would like ShipWorks to import on the initial download.  You can also specify whether or not you wish for ShipWorks to upload tracking information after a shipment is processed for this store.


16.  To select a number of days of orders (other than 30) for ShipWorks to download, click the Edit link. (Only available if you selected Orders by last modified date: in step 8.)


17.  Then, enter the number of days into the 'ShipWorks will download orders starting from: field. (Only available if you selected Orders by last modified date: in step 8.)



18.  To disable ShipWorks from automatically uploading shipment data after processing a shipment, uncheck the box next to 'Update my online orders with the shipment details.'.  (This option is not available if you selected Do not upload shipment details in step 11.)



19.  Click Next.


CONGRATULATIONS!!  You are now connected to your ODBC data source.  Click Finish.


Need More Help?

Please feel free to reach out to one of our awesome Customer Care Representatives in St. Louis.  We are happy to help.


For additional information on custom queries, please see Using a Custom query to upload shipment details with an ODBC store.