Using an ODBC Connection with Shipping Solutions Export Software

Shipping Solutions Professional includes a Data Exchange Manager (DXM) that allows you to import orders from your company’s accounting, order-entry, or ERP system to produce your export documents, file your export information electronically through the Automated Export System (AES), and fulfill your export compliance requirements.

By using the DXM, you can dramatically reduce the time it takes to complete your export documents, especially if your orders have a lot of line items, because you are pulling the entire order into Shipping Solutions instead of creating it in the software. You also eliminate the need for redundant databases because you don’t need to store and update your contact and product information in Shipping Solutions.

The DXM can utilize text files, an XML file, or access your data via an ODBC connection, which allows Shipping Solutions to directly communicate with your business application. This post discusses how to setup up the DXM using an ODBC connection.

Setting Up the Data Exchange Manager to Use ODBC

Your business application must be ODBC-compliant (Open Database Connectivity), and you must first establish an ODBC connection with your business application. Brief instructions for setting up the connection are at the bottom of this blog post.

Once you have created the ODBC connection with your accounting or ERP system, go to the Admin tab, Setup Data Exchange Manager for ODBC screen. Review the Welcome screen and click Next. Complete the steps on each of the tabs outlined below to configure the setup with Shipping Solutions Professional.

Profile Tab

Create a new profile or edit an existing profile. If you choose Edit an existing profile, a grid will pop up showing your existing profiles. If you choose to Create a new profile, a box will pop up where you can enter the name of your new profile. Click Next.

Data Source Tab

On this tab you identify the source of your shipment information. The databases that you can connect to via ODBC are listed in the box. Select the data source name and click Next. If your data source is not listed, contact your IT professional and review the general instructions below for setting up an ODBC connection.

Look Up Tab

On this screen you identify the field you want to look up in order to import the correct shipment information. For example, you may want to pull in shipment records via an invoice number or order number. All of the tables and views in your data source are listed in the DSN Lookup Table drop-down list. Select the table or view where the field is located. Once you have chosen the table, the possible fields will appear in the DSN Lookup Field drop-down list. Select the correct field. Click Next.

Destination Fields Tab

Now you need to identify which fields in Shipping Solutions you will be populating with information from your data source. The fields available in Shipping Solutions are divided into six groups. Click each group and check the boxes next to the fields you want to populate with information from your data source. You can also select fields that you want to populate with a default value. None of the fields are required.

The Contacts group includes names and addresses for all the parties in your shipment. The Header-Invoice group includes much of the information needed for your commercial or proforma invoice and your AESDirect filing. The Product Info group includes all the information about your line items.

The last three groups (NAFTA/Free Trade/Canada Customs/CARICOM, Ocean BL/IATA/IMO/Inland BL, and Shipper’s Letter of Instruction) are used less frequently than the first three because many ERP systems do not include fields that are specific to those documents.

There is a Check All box on each group’s screen. Please note that you will need to map every field that is checked and each group contains many fields, so there is probably no circumstance where you would want to use the Check All box. When you have finished selecting the fields, click Next.

Mapping Tab

Now you need to identify which field in your data source maps to each field you checked on the Destination Fields tab. The groups you used on the Destination Fields tab will appear in the drop-down list. Select a group. The fields you checked on the Destination Fields tab will appear in the first column.

The second column can be used to enter a default value for this field. For example, you may want to enter a default value for your company name and address. The default value will remain the same for every shipment.

However, most fields will use the third and fourth column only. All the tables and views in your data source will appear in the drop-down list of the DSN Table/View Name column. Select the correct table or view for the first field. Field names from the table or view you selected will appear in the DSN Field Name column. Select the correct field that corresponds to the Shipping Solutions Field Name. Map each group that appears in the drop-down list. Then click Next.

Relationships Tab

This tab is more complicated than the other tabs and requires a thorough knowledge of your data source, meaning your accounting, order-entry, or ERP system. This screen builds the relationships between the tables. A relationship is built between two matching fields where one field is in each table. For example, the matching field might be the order number, invoice number or another field that is not even part of the shipment data. You will need a relationship between each table in the data source that will act as a common thread to locate the information about a specific shipment throughout the various tables.

To add a relationship, click the Select Tables button. Each of the tables you used on the Mapping tab will appear in the list. If you need to build a relationship with a table that you did not use on the Mapping tab, click the Show All button to see all tables in your data source. Select two tables. One table will appear as the Primary Table and the other table will appear as the Secondary Table.

The fields in each table will appear in the boxes below the table names. Highlight a field in each box and click Add Relationship. The relationship will show up in the grid below. Click Select Tables again to add another relationship. You can use a table in more than one relationship, but you must have at least one relationship for each table. Continue building relationships until all tables have been used. Then click Next.

Summary Tab

This tab shows a summary of your ODBC configuration. Click the Back button to edit any settings. Click the Finish button to save your settings and close the Setup Wizard.

Create Your DXM Project

Once you have setup your ODBC configuration, you must create a Data Exchange Manager (DXM) project. Go to the Admin tab, Setup Data Exchange Manager screen. Click Create to create a new project. In the File Type box choose ODBC. In the Profile box type the name of the profile you just setup.

Many accounting and ERP systems may not include all the product information that may be required on your export forms. If so, you can supplement the data from your accounting or ERP system with data stored in the Shipping Solutions Product Database. On the Setup Data Exchange Manager window, click the Add Fields from Product Database check box, and then click on the Setup button.

Check the box next to the data fields you want to be added from the Products Database to your orders after importing from your accounting system. In order for this tool to work the Product ID field for the products you reference during the import process must match the Product ID in the Products database, and that Product ID must be unique for each item. If the box next to a field is checked, whatever is in the Product Database, even if it is blank, will overwrite what is brought through with the DXM.

Click Save to close the window and return to the main Setup window. While you are testing the DXM project, check the Logging in Debug Mode box. This will provide more detailed logging in case of errors. Save and Close your project.

Running the DXM

A user can run the DXM at any time by going to the Tools tab and clicking on Run Data Exchange Manager (Import Shipments). Select your ODBC project, and a Lookup ID box will appear. Enter the value for the shipment you want to import. The value was defined on the Lookup tab when setting up the ODBC project.

Click Run. The bottom box will show processing messages and should end with “Shipment ID’s Inserted [x]” where “x” is the Shipment Number inserted into your Shipping Solutions database. If there are errors, you can click the View Log button to see all the processing messages.

Create an ODBC Connection

You may need to consult with your IT department before setting up the ODBC connection. In general, the steps for creating the ODBC connection are:

1. On the computer running the Shipping Solutions client, open the ODBC Data Source Administrator (64 bit) window.
   
   
2. Click the System DNS tab.Click Add and choose the correct driver for your data source. You may have to download the correct driver from your ERP system vendor. Click Finish.
   
   
3. Give your data source a name (a DSN), a description and enter your Shipping Solutions SQL Server.
   
   
4. Use the ODBC data source wizard to complete the connection. Be sure to enter ssdata_sql_01 as the default database.