FedEx Integration

Simple integration into the FedEx shipping software can be accomplished using the Atrex ODBC driver and a custom view.

Creating the View:

Select "Options - Customization/Company Setup - User Defined Fields and Sizes" from the main Atrex menu, then read and accept the warning about changing field sizes.
Click on the "User Defined Views" tab and click the "+" button on the navigation control to create a new view.
Enter U_FedEx in the View Name field.

Enter the following in the Definition field:
For Atrex 20:

select t.number, t.transdate, t.custnum, t.shipvia, t.custpo, t.shipping,
if ((length(t.shipname) + length(t.shipcompany) > 0) then t.shipcompany else c.company) as fe_company,
if ((length(t.shipname) + length(t.shipcompany) > 0) then t.shipname else LTrim(coalesce(c.firstname, '') + ' ' + c.lastname)) as fe_shipname,
if ((length(t.shipname) + length(t.shipcompany) > 0) then t.shipadd else c.add1) as fe_shipadd,
if ((length(t.shipname) + length(t.shipcompany) > 0) then t.shipadd2 else c.add2) as fe_shipadd2,
if ((length(t.shipname) + length(t.shipcompany) > 0) then t.shipcity else c.city) as fe_shipcity,
if ((length(t.shipname) + length(t.shipcompany) > 0) then t.shipstate else c.state) fe_shipstate,
if ((length(t.shipname) + length(t.shipcompany) > 0) then t.shipzip else c.zip) fe_shipzip
from invoice t
join cust c on t.custnum = c.custnum

For Atrex 13 through 19:

select t.number, t.transdate, t.custnum, t.shipvia, t.custpo, t.shipping, 
if (t.shipname is not null then t.shipname else LTrim(coalesce(c.firstname, '') + ' ' + c.lastname)) as fe_shipname,
if (t.shipname is not null then t.shipadd else c.add1) as fe_shipadd,
if (t.shipname is not null then t.shipadd2 else c.add2) as fe_shipadd2,
if (t.shipname is not null then t.shipcity else c.city) as fe_shipcity,
if (t.shipname is not null then t.shipstate else c.state) fe_shipstate,
if (t.shipname is not null then t.shipzip else c.zip) fe_shipzip
from invoice t join cust c on t.custnum = c.custnum

You can leave the description field blank, or enter something like "Atrex/FedEx Shipping Information View".
Click the Check (post) button on the navigation control to save the View.
Click OK to save the changes.
Run the "Options - Batch Processing" function with exclusive access to the database to create the view.

Configuring the ODBC Driver:

Instructions on how to configure the Atrex ODBC driver are available at http://support.1000years.com/KB/a4/configure-the-atrexelevatedb-odbc-driver.aspx .

Once the View and the ODBC driver are configured, your FedEx technician can access the data using the ODBC DSN entry. To retrieve information about a specific invoice and retrieve it into the FedEx shipping software, use the following query, replacing <number> with the number of the invoice that you want to retrieve:

select * from U_FedEx where number = <number>

To update the invoice record with the tracking number, use the following query, replacing <number> with the number of the invoice that you want to update and <TrackingNumber> with the FedEx tracking number:

update invoice set tracknum = '<TrackingNumber>' where number = <number>

Article ID: 96, Created: 2/10/2015 at 2:22 PM, Modified: 2/25/2022 at 6:34 PM

Was this article helpful?

Share this article