FedEx Integration

Simple integration into the FedEx shipping software can be accomplished using the Atrex ODBC driver and a custom view.
 
Creating the View:
 
  1. 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.
  2. Click on the "User Defined Views" tab and click the "+" button on the navigation control to create a new view.  
  3. Enter U_FedEx in the View Name field.
  4. 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
  5. You can leave the description field blank, or enter something like "Atrex/FedEx Shipping Information View".
  6. Click the Check (post) button on the navigation control to save the View.
  7. Click OK to save the changes.
  8. 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>