One problem we are often asked to solve is how to integrate a company’s shipping software – FedEx, UPS, DHL, etc. – with the Fourth Shift shipping process. This usually involves two major features at the basic level.
- Connecting the shipping software to a SQL view so that the correct address information can be automatically populated in the shipping software. This prevents typing errors and eliminates the need for double entry.
- Pushing the shipping information – weight, freight (including surcharges, etc.), and pro numbers for tracking purposes back into the ERP system.
How do we accomplish this? The first objective is fairly simple as both UPS and FedEx support integration using an ODBC connection. The major hurdles here are:
- Creating a view that contains all the information needed to support the shipment transaction – Name, Ship To Address, etc.
- Setting up database security to allow access to this view.
- Connecting to the view and mapping the fields in there to the fields in the shipping software including mapping a unique value to search and retrieve the correct data with.
So! Pretty easy huh??
Well… there are some pitfalls.
First of all, there is not really one view in Fourth Shift (SQL versions 7.x etc) that has all of the information you really need. I use the shipment number as my key lookup field and had to sew the CO, Shipment, Customer, and Ship To views together to get everything I needed. Also make sure to use _Nolock_* views or you can lock the application up with the dreaded deadly embrace record locking problem!
Next, you may have some database security fun. If you also have the Fourth Shift client installed on the workstation with the shipping software, you will already have an odbc connection to the FSDB?? database that is your main ERP database. If you create your view in there and make sure it has the Fourth Shift security roles assigned to it then you should be good to go. If you, like me, prefer to put everything custom in a different database then you will need to create a connection to that database and probably create a sql user with read and write access to both your database and the FSDB?? one (the exception is if your database is on a completely different SQL instance and you have created a linked server with a proxy account with the security handled in there). One advantage to this is when you are searching for your view after connecting you don’t have to scroll through a list of EVERY TABLE and EVERY VIEW in the FSDB?? database and only have to look through your (hopefully) shorter list of objects.
Finally, you get to run through the wizard in the shipping application and map your fields to the fields in the application. FedEx has a shorter list of fields than UPS (which is crazy extensive). Refer to the software manual for an explanation of the fields in their application database. Then you set up a lookup value (again I use the shipment number) and VOILA! you have an import script that will pull data from Fourth Shift into your shipping application!
HELPFUL HINT: Barcode a traveler document with the shipment number so you can easily scan the lookup value at the shipping station.
If you thought that was fun, the export is a blast too!
For this process you will need to create a table in SQL to hold the results of the shipment. You will want a unique key field that you set as identity with an auto incrementing value which is always good practice with SQL tables. You will also want a shipment id field so you can attach the info to the correct shipment, a pro number field, a freight (possibly multiples depending on how complex your freight processing workflow is) field(s), a weight field, and a processed field with a bit or integer that you can set to zero or false as a default and later flag as processed to prevent multiple processing.
Again you will need to run through your import export wizard in the shipping software to connect to your data source and map the outgoing data to the correct fields. Be careful as there are several fields in the shipping software related to charges so finding the correct ones may involve some trial and error. Once you have hooked everything up you are not only feeding information from your ERP to the shipping application but also collecting shipment data!
All done right!?
NOPE!
Now, you have to consume that information and feed it back into Fourth Shift. I use SSIS (SQL Server Integration Services – included with SQL Standard and above with the exception of the Web Edition for 2008) to run a stored procedure that combines the shipment data with the Customer Order data to create Shipping and Bill Of Lading transactions. You could schedule a simple Stored Procedure in the SQL Scheduling Agent but using SSIS allows you to send notifications and/or perform other conditional tasks such as emailing an administrator when a transaction fails or perhaps generating an advance ship notice via email using SQL DBMail to let the customer know that their product has shipped and what the tracking number is for that shipment.
You might ask, cool but how do I actually GET THOSE TRANSACTIONS INTO FOURTH SHIFT????
Three methods.
- One, create a comma delimited text file and import it into Fourth Shift either on a nightly batch or by using a recovery server and flag those transactions as processed in your freight table. One big problem here is that you can lose these text files and you can encounter file locking issues, etc. So it is the old school way, it works most of the time, it is a big mess when things go wrong.
- Two, use Visiwatch if you have it to process the transactions and submit them to FSTI (Fourth Shift Transaction Interface) assuming you own the FSTM module. This is a better way because there is a definite log of what gets processed along with the results and notification of failures can be executed immediately or in batch. The downside is you have to write this routine (or contact me!) and Visiwatch is based on pretty old technology so it has a personality to say the least.
- Three, buy the amazing FSTI Connector product developed by Mindlin Consulting! It is written and architected using the latest .net technologies as well as a SOA architecture and provides an easy to manage transaction queue that you can access and manage using a web console we created. For those super nerds among you it also exposes all 850+ Fourth Shift transactions via a web service so you can integrate to it using virtually anything. This naturally is my preferred method as I am able to simply insert the Shipping transactions into the queue table in SQL and go on my merry way.
This is the simplest form of integration. We have also done unattended batch mode integration where a shipment in Fourth Shift triggers a freight transaction in the FedEx / UPS software, automatically printing the labels and executing the shipment as well as pumping the results back into Fourth Shift. We even managed to deal with 3rd Party billing which was kinda cool.
If you have any questions on how to implement the solution above, please feel free to contact us and we will be more than happy to assist!