Shipping Integration

From Fishbowl

Jump to: navigation, search

The Small.ShipExpress.png ShipExpress module and the Small.ShipStation.png ShipStation module integrate with nearly all carriers. Fishbowl also has a built-in Small.UPS Ready.png UPS Ready module. Alternatively, Fishbowl can integrate with programs like FedEx and UPS WorldShip by utilizing a technology called Open Database Connectivity or ODBC.

Contents

ODBC setup

Fishbowl 2017 and later run on a database management system called MySQL (for Fishbowl 2016.14 or earlier, click here). MySQL has an ODBC Driver (Open Database Connectivity) that allows other programs like FedEx and WorldShip to connect with the Fishbowl database. To set up the ODBC connection, complete the steps below on each computer that will be using a shipping application.

  1. Download the 32-bit ODBC Windows MSI installer by clicking here.
  2. Complete the MySQL ODBC installer by selecting the Typical installation.
  3. Open ODBC Data Sources (32-bit). On newer versions of Windows this can be found by pressing the Windows key and typing ODBC 32. On older versions of Windows, open C:\Windows\SysWOW64\odbcad32.exe.
  4. Click the System DSN tab and then click the Add button.
    ODBC MySQL.png
  5. Select the MySQL ODBC 5.3 Unicode Driver and then click Finish.
  6. A new MySQL ODBC Setup window will appear. Enter the following information in the appropriate fields.
    MySQL ODBC setup.png
    • Data Source Name (DSN): Fishbowl
    • TCP/IP Server: For the server computer, enter localhost. For client computers, enter the IP address of the Fishbowl Server computer, for example: 192.168.1.10.
    • Port: Change the port number to 3305.
    • User: gone
    • Password: fishing
    • Database: Select the database name displayed when double-clicking on the Fishbowl Server tray icon.
      MySQL database name.png
  7. Click the Test Connection button to verify the connection is successful and then click OK.

ODBC troubleshooting
Jump to top of page

Below is some troubleshooting information for the ODBC setup.

FedEx integration

Follow the steps below to complete the FedEx Integration:

  1. Complete the ODBC setup.
  2. Install the latest version of FedEx Ship Manager. The installer can be downloaded by completing this form.
  3. Turn on the ability to show hidden files. (Windows 7 instructions)
  4. To download the .xml files for Fishbowl 2017 or later, click here. Descriptions for older mapping files can be found here.
    NOTE: To use ShipNum2017.xml, contact Fishbowl Support to create an additional database view.
    FishbowlSingle2017.xml will not work if services are not set for the FedEx carrier. Services will map by the numerical code shown before the service in the image below.
    FedEx code.png
  5. Paste the .xml files into the FedEx Profiles folder. The location of the Profiles folder varies with different versions, but is typically in one of the following locations.
    • C:\ProgramData\FedEx\Integration\Profiles
    • C:\Documents and Settings\All Users\Application Data\FedEx\Integration\Profiles
  6. Open FedEx Ship Manager.
  7. Click the Integration menu and select FishbowlSingle.
  8. The Lookup Value window will appear. If not, ensure the ODBC driver is installed correctly. Enter the sales order number and click OK.
    FedEx Lookup Value.png
    • NOTE: The sales order must be in the Shipping module to show up in FedEx and the order number must be entered exactly as it appears in Fishbowl.
  9. The order should have now pulled in. If the carton weight was set in Fishbowl, this will pull into FedEx. Otherwise, the weight can be added in FedEx. Once the information is correct, click Ship. This will print a label and send the tracking number, cost, and updated weight back into Fishbowl to finish.

FedEx troubleshooting
Jump to top of page

Below is some troubleshooting information for the FedEx integration.

Very slow import

Mapping Files not showing up under Integration

Mapping Files disappearing after FedEx restart

Order Not Found Import error

Cannot export tracking information

Freight

Two Fishbowl Databases – One FedEx Ship Manager install.

Alphabetic characters Currently, alphabetic characters will not work in some fields. Fishbowl is in the process of getting shipnum to work which should resolve this limitation.

Create a new FedEx mapping
Jump to top of page

Follow the steps below to create a new FedEx mapping.

  1. Go to Integration
  2. Choose FedEx Integration Assistant
  3. Click Continue twice
  4. Choose Create a new profile
  5. Click Continue
  6. Choose Import
  7. Click Continue
  8. Choose ODBC and choose Fishbowl from the list
  9. Choose Single Edit
  10. Click Continue and click Continue again
  11. Click on Recipient and check the checkbox for Address 1. Click Ok.
  12. Click Continue
  13. Under the Table dropdown, choose ACCOUNT. Under the Field dropdown choose ID.
  14. Click Continue
  15. A box will pop up asking for a key index. Highlight ID under ACCOUNT and then click on the Lookup dialog box box. A colored line will be created. Click Ok.
  16. Click Continue and Continue again.
  17. Click Finish
  18. Choose to save the file and name it FINDME. Now you can go search for that file.
Remove FedEx country mapping
Jump to top of page

Follow the steps below to remove the country mapping from FedEx.

  1. Go to Integration
  2. Choose FedEx Integration Assistant
  3. Click Continue twice
  4. Choose the mapping file you want to modify (Fishbowl Single)
  5. Click Continue
  6. Click Import at the top of the screen
    FedEx 1.png
  7. Then choose Information
    FedEx 2.png
  8. Click on Recipient and uncheck the checkbox for Country. Click OK.
    FedEx 3.png
  9. Click on the Finish tab at the top of the screen and then click Continue and finally click Finish. You will be prompted to save. Choose Yes and finish that out. You can now go try the import again.

UPS integration

Fishbowl includes a Small.UPS Ready.png UPS Ready module which means that UPS features are built into Fishbowl and directly accessible with no additional software required. The instructions below are for integrating with UPS WorldShip which does require additional software and configuration.

Follow the steps below to complete the UPS WorldShip Integration:

  1. Complete the ODBC setup.
  2. Install the latest version of UPS WorldShip. The installer can be downloaded by completing this form.
  3. To download the .dat files for Fishbowl 2017 or later, click here. Descriptions for older mapping files can be found here.
  4. Paste the .dat files into the UPS Shipment folder. The location of the Shipment folder varies with different versions, but is typically in one of the following locations.
    • C:\UPS\WSTD\ImpExp\Shipment
    • C:\UPS\UOWS\ImpExp\Shipment
    • C:\ProgramData\UPS\WSTD\ImpExp\Shipment
  5. Open UPS WorldShip.
  6. Click the Import/Export Data menu, click Automatic Export After Processing Shipment, and then click More.
  7. Select Fishbowl Export from the list and click Export.
  8. Click on Import/Export Data menu, click Automatic Export During End of Day, and then click More.
  9. Select Fishbowl Export from the list and click Export.
  10. Click on Import/Export Data menu, click Keyed Import, and then click More.
  11. Select FishbowlSO, FishbowlPO, or FishbowlXO (Sales Order, Purchase Order, or Transfer Order) from the list and click Import.
  12. The Enter Import Key window will appear. If not, ensure the ODBC driver is installed correctly. Enter the sales order number and press Enter on the keyboard.
    UPS Enter Import Key.png
    • NOTE: The sales order must be in the Shipping module to show up in UPS and the order number must be entered exactly as it appears in Fishbowl.
  13. The order should have now pulled in. If the carton weight was set in Fishbowl, this will pull into UPS. Otherwise, the weight can be added in UPS. Once the information is correct, click Process Shipment. This will print a label and send the tracking number, cost, and updated weight back into Fishbowl to finish.

Click here for UPS troubleshooting information.

UPS troubleshooting
Jump to top of page

Below is some troubleshooting information for the UPS integration.

Mapping files not showing up under Keyed Import

Order Not Found Import error

Found multiple records

  1. Delete all values from the Ref 2 field in WorldShip. These numbers refer to the specific cartons, which are from to the original shipment. The correct Ref 2 codes cannot be imported.
  2. Correct the shipping service class and package weight(s), if necessary
  3. Process the shipment, and manually copy the resulting tracking number and cost into Fishbowl.

Cannot export tracking information

Mail Innovations Shipments

Freight Shipments

Keyed Import box not showing up

Export Failing after processing the shipment

Two Fishbowl Databases – One UPS WorldShip install.

Endicia integration

Follow the steps below to complete the Endicia integration.

Order Lookup

  1. Complete the ODBC setup.
  2. Create a Professional account on Endicia.com and download the Endicia Professional Suite. Run the installer and install Dazzle and Endicia Professional.
  3. Open Endicia Professional and enter your Account Number, Pass Phrase, and Address.
  4. Click the Edit menu, choose Preferences, click on the Data Source and Post-Back tab, and then click the Configure button next to Order Lookup (or click here for Batch Print instructions).
    Endicia Edit Order Lookup Data Map.png
  5. Click the New button in the Data Source area.
    Endicia New Data Source.png
  6. Click Next, choose the ODBC data source option, and then click Next.
    Endicia Create ODBC Step.png
  7. Select Fishbowl from the dropdown. Leave the User Name and Password fields blank, and then click Next and OK to complete the wizard.
    Endicia ODBC Data Source.png
  8. If the Create a SELECT Statement window pops up, click Cancel to dismiss it.
    Endicia Select Statement Wizard.png
  9. Click the Advanced Editor button in the SELECT Statement area.
    Endicia Advanced Editor.png
  10. Paste the following query into SQL Editor window and then click OK. The query used before 2017 can be found here.

    SELECT so.num AS ordernumber, shipcarton.id as groupcode, so.cost, shipcarton.freightweight as weight, so.customercontact as name, so.shiptoname AS company, so.shiptoaddress as address1, so.shiptocity as city, stateconst.code as state, so.shiptozip as postalcode, countryconst.abbreviation as country, carrier.name as carrier, shipcarton.cartonnum, so.email as email, so.phone as phone
    FROM so
    LEFT JOIN countryconst ON so.shiptocountryid = countryconst.id
    LEFT JOIN stateconst ON so.shiptostateid=stateconst.id
    LEFT JOIN carrier ON so.carrierid = carrier.id
    LEFT JOIN shipcarton ON so.id = shipcarton.orderid
    LEFT JOIN customercontactview ON so.customerid=customercontactview.custid
    JOIN customer ON so.customerid = customer.id JOIN account ON customer.accountid = account.id
    LEFT JOIN carrierservice ON carrierservice.id = so.carrierserviceid
    WHERE so.num= '$ORDERNUMBER' AND ordertypeid = 20

    Endicia Query.png
  11. Select the following fields in the Connected Fields area:
    • Select the following fields on the Package Details tab:
      Order Number -> ORDERNUMBER, Mail Class -> CARRIER, Group Code -> GROUPCODE, Value -> COST, Weight -> WEIGHT
      Make sure to check the Weight is in pounds checkbox.
    • If desired, the shipping carrier selected on the Fishbowl order can be imported and mapped to Endicia's mail class. If this step is skipped, Endicia will not import the carrier from Fishbowl and will instead use whatever service is currently selected in Endicia's Mail Class field. The mail class can always be changed prior to printing the label. To import the carrier that is selected on the Fishbowl order, click the Edit Mail Class Codes button. In the textbox, enter the name of the carrier in Fishbowl. In the dropdown, select the Endicia Mail Class that corresponds to the Fishbowl carrier and then click the + button. Additional Fishbowl carriers can be created in the Small.Carrier.png Carrier module.
      Edit Mail Class Code.png
    • Select the following fields on the Recipient Address tab:
      Name -> CUSTOMERCONTACT, Company -> SHIPTONAME, Address 1 -> SHIPTOADDRESS, City -> SHIPTOCITY, State -> CODE, Postal Code -> SHIPTOZIP, Country -> ABBREVIATION, Phone -> CONTACTNUM
    • Select the following fields on the Rubber Stamps tab:
      Rubber Stamp 1 -> SHIPCARTONID
    • OPTIONAL: Select CUSTOMEREMAIL in the Email field on the Recipient Address tab to automatically send emails via Dazzle.
    • OPTIONAL: Select the BILLTO fields on the Return Addresses tab to bring in a different return address.
  12. Click OK to close the Order Lookup Data Map window, then click the Configure button in the Post-Back Data section.
    Endicia Edit Order Lookup Data Map.png
  13. Click the New Data Map button in the top-left corner of the Post Back Data Map window.
    Endicia New Data Map.png
  14. Click Next, choose the ODBC data source option, and then click Next.
    Endicia Create ODBC Step.png
  15. Select Fishbowl from the dropdown. Leave the User Name and Password fields blank, and then click Next and OK to complete the wizard.
    Endicia ODBC Data Source.png
  16. Click the Select button to the right of the table field and choose the SHIPPINGIMPORT table.
    Endicia Table Select.png
  17. Select the following fields on the Package Details tab.
    Order Number -> IGNORE, Postage Amount -> CARTONCOST, Tracking Number -> TRACKINGNUM, Transaction ID -> ID, Group Code -> SHIPCARTONID, Actual Weight -> CARTONWEIGHT
    Make sure to check the Weight is in pounds checkbox.
  18. Click OK to close the Post Back Data Map window and then click the Options button in the Post-Back Data area.
    Endicia Edit Order Lookup Data Map.png
  19. Place a checkmark next to Automatically post back data.
    Endicia Automatically Post Back Data.png
  20. Click OK to close the Post-Back Options window and then click OK to close the Preferences window.
  21. Click the Order Lookup tab in the main window, enter a sales order number, and then click the Retrieve Order button to retrieve the order from Fishbowl.
    Endicia Order Lookup.png

Batch Print

The steps for batch print are identical to those listed for Order Lookup, with the following changes:

SELECT so.id, so.num AS ordernumber, so.customercontact AS name, so.shiptoname AS company, so.shiptoaddress AS address1, so.shiptocity AS city, so.cost, stateconst.code AS state, countryconst.abbreviation AS country, so.shiptozip AS postalcode, carrier.name AS carrier, shipcarton.freightweight AS weight, shipcarton.id as groupcode, shipcarton.cartonnum FROM so LEFT JOIN ship ON ship.soid = so.id LEFT JOIN countryconst ON so.shiptocountryid = countryconst.id LEFT JOIN stateconst ON so.shiptostateid=stateconst.id LEFT JOIN shipcarton ON ship.id = shipcarton.shipid LEFT JOIN carrier ON so.carrierid = carrier.id WHERE carrier.name = 'USPS' AND ship.statusid = 20 AND shipcarton.ordertypeid = 20

Click here for Endicia troubleshooting information.

Endicia troubleshooting
Jump to top of page

Below is some troubleshooting information for the Endicia integration.

Two Fishbowl Databases – One Endicia Account

  1. First, set up a second ODBC connection by following the same ODBC setup steps listed here. Instead of naming it Fishbowl, name it Fishbowl1. (or Fishbowl *other company name*. Ex: Fishbowl Apex)
  2. Once that is set up, follow the same Endicia setup steps listed here. Instead of choosing the Fishbowl connection, choose Fishbowl1. (Or whatever you named it)
  3. Now the tricky and most vital part of all of this.
  4. Depending on what database you want to work in, you have to change TWO settings EVERY TIME.
  5. Go to Edit > Preferences > Database tab.
  6. First, click on Edit Order Lookup Data Map.
  7. Under Data Source, choose the database you want to work in and click OK.
  8. Then, click on Edit Post-Back Map.
  9. Click on the folder button called, Open Data Map and choose the database you want to use and click OK. (MAKE SURE THIS SELECTION MATCHES WHAT YOU CHOSE UNDER Edit Order Lookup Data Map.)

Post back error (Error image below)

Endicia post back error.png

  1. Login to the Fishbowl Client and go to the Shipping module. Search for the order number, it will not be there.
  2. Go to the Picking module and pull up that order. Finish the pick and head back to the Shipping Module.
  3. Then go into Endicia and go to Tools > Post Back Data > and you will see that shipment record listed there. Highlight it and delete it.
  4. Then process the order through Endicia again.
Personal tools
Namespaces
Variants
Actions
Navigation
Other Pages
Toolbox
Print/export
Translate