FedEx Ship manager integration

Although this isn't really a module, I wanted to let everyone know that you can use the database integration features of FedEx ship manager for insanely easy shipping with zencart.

You need an account with fedex, but its possible to configure the integration settings so that all you have to do is type in an order number, and you're printer will spit out a label. We use it to automatically create labels for multiple boxes within a single order as well.

If anyone has any questions, I'd be happy to help but please post here so others can see solutions.

Thanks!

can you explain what to do to get this running?

Do you need to use the FedEx integrator with FedEx ship manager software? I just got my account with fedex and I am about to install ship manager and saw that option.

This is great news, it will make shipping soooo much easier!

1. Download, install, and configure the correct ODBC connector for MySql for your operating system.
2. Download and install FedEx Ship Manager.
3. Run through the Integration Wizard after setting up your account number.
4. Use the "one label at a time" method.
5. You'll need to pull data from 3 tables:
   zen_orders
   zen_orders_products
   zen_products
6. Map delivery addresses and the like from zen_orders
7. Map weight to the product weight field in zen_products by keying the zen_orders order id field to the matching zen_orders_products order id field, then map the zen_order_products product id field to the matching zen_products product id field.
8. If you ship each product individually like we do, map fedex number of packages to the quantity field in zen_orders_products.
9. The best way I've found for setting up the lookup field is to map it to the order number. When set up this way, typing in the order number will spit out a shipping label for each product in the order that has a valid shipping weight.

I don't have the time to write a full guide but this should give you an idea of how to do it. If you have any questions, please post and I'll reply when I have time.

Thanks,
Preston

Any quesitons? I'd be happy to help.

Are you talking about being able to use this where the cart is on a linux server? The function of the mentioned ODBC connector for MySql?

Sorry I never saw your reply. If you have a mysql database on your server, you can use the ODBC driver from mysql to interface your local machine with your cart database.

And no, you select the odbc driver for your local operating system and install it. Then you configure the settings to point to the mysql database on your website.

Okay, I'm going to see if I can figure out how to do this and help someone remotely set this up for her new cart. Unfortunately, I do not have the FedEx account and will not be able to see what they see. My friend has not worked with code at all. Any further tips or screen shots, especially to set up the connections to the various tables?

Also, on the mysql page http://dev.mysql.com/downloads/connector/odbc/5.1.html , I'm guessing for a 32 bit system (what she likely has) that the choice to download and run is this?:
Windows (x86, 32-bit), MSI Installer
Connector-ODBC

Is this file needed?
Windows (x86, 32-bit), ZIP Archive
Connector-ODBC

I'm guessing I need to provide her with the standard password/user/database connection info for her database.

You write:
"3. You'll need to pull data from 3 tables:
zen_orders
zen_orders_products
zen_products
4. Map delivery addresses and the like from zen_orders
5. Map weight to the product weight field in zen_products by keying the zen_orders order id field to the matching zen_orders_products order id field, then map the zen_order_products product id field to the matching zen_products product id field.
6. If you ship each product individually like we do, map fedex number of packages to the quantity field in zen_orders_products.
7. The best way I've found for setting up the lookup field is to map it to the order number. When set up this way, typing in the order number will spit out a shipping label for each product in the order that has a valid shipping weight."

This is the point where a screen shot and more advice for her would be invaluable.

I am guessing the three tables you refer to are "orders", "orders_products" and "products".

This is what they look like:

CREATE TABLE IF NOT EXISTS orders (
orders_id int(11) NOT NULL auto_increment,
customers_id int(11) NOT NULL default '0',
customers_name varchar(64) collate latin1_general_ci NOT NULL default '',
customers_company varchar(64) collate latin1_general_ci default NULL,
customers_street_address varchar(64) collate latin1_general_ci NOT NULL default '',
customers_suburb varchar(32) collate latin1_general_ci default NULL,
customers_city varchar(32) collate latin1_general_ci NOT NULL default '',
customers_postcode varchar(10) collate latin1_general_ci NOT NULL default '',
customers_state varchar(32) collate latin1_general_ci default NULL,
customers_country varchar(32) collate latin1_general_ci NOT NULL default '',
customers_telephone varchar(32) collate latin1_general_ci NOT NULL default '',
customers_email_address varchar(96) collate latin1_general_ci NOT NULL default '',
customers_address_format_id int(5) NOT NULL default '0',
delivery_name varchar(64) collate latin1_general_ci NOT NULL default '',
delivery_company varchar(64) collate latin1_general_ci default NULL,
delivery_street_address varchar(64) collate latin1_general_ci NOT NULL default '',
delivery_suburb varchar(32) collate latin1_general_ci default NULL,
delivery_city varchar(32) collate latin1_general_ci NOT NULL default '',
delivery_postcode varchar(10) collate latin1_general_ci NOT NULL default '',
delivery_state varchar(32) collate latin1_general_ci default NULL,
delivery_country varchar(32) collate latin1_general_ci NOT NULL default '',
delivery_address_format_id int(5) NOT NULL default '0',
billing_name varchar(64) collate latin1_general_ci NOT NULL default '',
billing_company varchar(64) collate latin1_general_ci default NULL,
billing_street_address varchar(64) collate latin1_general_ci NOT NULL default '',
billing_suburb varchar(32) collate latin1_general_ci default NULL,
billing_city varchar(32) collate latin1_general_ci NOT NULL default '',
billing_postcode varchar(10) collate latin1_general_ci NOT NULL default '',
billing_state varchar(32) collate latin1_general_ci default NULL,
billing_country varchar(32) collate latin1_general_ci NOT NULL default '',
billing_address_format_id int(5) NOT NULL default '0',
payment_method varchar(128) collate latin1_general_ci NOT NULL default '',
payment_module_code varchar(32) collate latin1_general_ci NOT NULL default '',
shipping_method varchar(128) collate latin1_general_ci NOT NULL default '',
shipping_module_code varchar(32) collate latin1_general_ci NOT NULL default '',
coupon_code varchar(32) collate latin1_general_ci NOT NULL default '',
cc_type varchar(20) collate latin1_general_ci default NULL,
cc_owner varchar(64) collate latin1_general_ci default NULL,
cc_number varchar(32) collate latin1_general_ci default NULL,
cc_expires varchar(4) collate latin1_general_ci default NULL,
cc_cvv blob,
last_modified datetime default NULL,
date_purchased datetime default NULL,
orders_status int(5) NOT NULL default '0',
orders_date_finished datetime default NULL,
currency char(3) collate latin1_general_ci default NULL,
currency_value decimal(14,6) default NULL,
order_total decimal(14,2) default NULL,
order_tax decimal(14,2) default NULL,
paypal_ipn_id int(11) NOT NULL default '0',
ip_address varchar(96) collate latin1_general_ci NOT NULL default '',
PRIMARY KEY (orders_id),
KEY idx_status_orders_cust_zen (orders_status,orders_id,customers_id),
KEY idx_date_purchased_zen (date_purchased),
KEY idx_cust_id_orders_id_zen (customers_id,orders_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=3 ;

CREATE TABLE IF NOT EXISTS orders_products (
orders_products_id int(11) NOT NULL auto_increment,
orders_id int(11) NOT NULL default '0',
products_id int(11) NOT NULL default '0',
products_model varchar(32) collate latin1_general_ci default NULL,
products_name varchar(64) collate latin1_general_ci NOT NULL default '',
products_price decimal(15,4) NOT NULL default '0.0000',
final_price decimal(15,4) NOT NULL default '0.0000',
products_tax decimal(7,4) NOT NULL default '0.0000',
products_quantity float NOT NULL default '0',
onetime_charges decimal(15,4) NOT NULL default '0.0000',
products_priced_by_attribute tinyint(1) NOT NULL default '0',
product_is_free tinyint(1) NOT NULL default '0',
products_discount_type tinyint(1) NOT NULL default '0',
products_discount_type_from tinyint(1) NOT NULL default '0',
products_prid tinytext collate latin1_general_ci NOT NULL,
PRIMARY KEY (orders_products_id),
KEY idx_orders_id_prod_id_zen (orders_id,products_id),
KEY idx_prod_id_orders_id_zen (products_id,orders_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=3 ;

CREATE TABLE IF NOT EXISTS products (
products_id int(11) NOT NULL auto_increment,
products_type int(11) NOT NULL default '1',
products_quantity float NOT NULL default '0',
products_model varchar(32) collate latin1_general_ci default NULL,
products_image varchar(64) collate latin1_general_ci default NULL,
products_price decimal(15,4) NOT NULL default '0.0000',
products_virtual tinyint(1) NOT NULL default '0',
products_date_added datetime NOT NULL default '0001-01-01 00:00:00',
products_last_modified datetime default NULL,
products_date_available datetime default NULL,
products_weight float NOT NULL default '0',
products_status tinyint(1) NOT NULL default '0',
products_tax_class_id int(11) NOT NULL default '0',
manufacturers_id int(11) default NULL,
products_ordered float NOT NULL default '0',
products_quantity_order_min float NOT NULL default '1',
products_quantity_order_units float NOT NULL default '1',
products_priced_by_attribute tinyint(1) NOT NULL default '0',
product_is_free tinyint(1) NOT NULL default '0',
product_is_call tinyint(1) NOT NULL default '0',
products_quantity_mixed tinyint(1) NOT NULL default '0',
product_is_always_free_shipping tinyint(1) NOT NULL default '0',
products_qty_box_status tinyint(1) NOT NULL default '1',
products_quantity_order_max float NOT NULL default '0',
products_sort_order int(11) NOT NULL default '0',
products_discount_type tinyint(1) NOT NULL default '0',
products_discount_type_from tinyint(1) NOT NULL default '0',
products_price_sorter decimal(15,4) NOT NULL default '0.0000',
master_categories_id int(11) NOT NULL default '0',
products_mixed_discount_quantity tinyint(1) NOT NULL default '1',
metatags_title_status tinyint(1) NOT NULL default '0',
metatags_products_name_status tinyint(1) NOT NULL default '0',
metatags_model_status tinyint(1) NOT NULL default '0',
metatags_price_status tinyint(1) NOT NULL default '0',
metatags_title_tagline_status tinyint(1) NOT NULL default '0',
PRIMARY KEY (products_id),
KEY idx_products_date_added_zen (products_date_added),
KEY idx_products_status_zen (products_status),
KEY idx_products_date_available_zen (products_date_available),
KEY idx_products_ordered_zen (products_ordered),
KEY idx_products_model_zen (products_model),
KEY idx_products_price_sorter_zen (products_price_sorter),
KEY idx_master_categories_id_zen (master_categories_id),
KEY idx_products_sort_order_zen (products_sort_order),
KEY idx_manufacturers_id_zen (manufacturers_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=4 ;

[/SCR]

OK in reply to your first post:

You can download either the zip or msi file. Both will allow you to install the odbc connector. Once installed, you'll need to configure the machine's odbc dsn or machine profiles to include her website's mysql database server's IP address and login credentials. With most hosting providers, you'll also need to allow her local computer's public ip address to access your databases remotely.

In reply to your second post:

zen is simply the table prefix for the installation I did. That is why it is prepended to each of the table names. Why do you have an SQL statement instructing mysql to create tables? Use the fedex integration wizard to select the fields in each table through the provided gui. Once you've set up a dsn or machine odbc link on her computer, you'll be able to select it within ship manager and it will list available tables to map shipping fields to. Map the lookup field to the order number. This will allow you to type in an order number and have all of the shipping information automatically populate the fedex ship manager fields.

This is a rather complicated setup, so you'll probably have to take remote control of her machine and do the ship manager integration yourself. It took me about three hours but I went in blind. With a rough outline as to the order of the steps which I provided you should be able to do it in an hour and a half. During the field matching, just think through what should go where logically. Also, be sure to run some test orders. I seem to remember there were a few fields which I mapped incorrectly because zencart stores the correct information somewhere you wouldn't think it would be.

Let me know if you need anything further.

Preston

Thanks for the reply.

The SQL statement was there only because I just grabbed the structure info from an sql download I did. Made it easier to see what fields might be involved.

I've never done anything remotely on someone's computer before. She is on satelite and has connection issues and I have a wireless broadband internet using a dying technology (no longer supported by Cisco Systems) that just got some radio frequency filters slapped on a week ago per the FTC (?) resulting in a degraded and spotty connection (though it seems to be working okay at the moment). I may be able to work something out but unsure for now.

Before I pursue this further, it occurs to me the FedEx Ship Manager is normally used on a local database by non-web based businesses. When you access a database remotely on a server, you can't encrypt the data being passed. Or does the ODBC connector somehow do this?

I'm just wondering if this could be a security issue.

[edit -- I was doing a google search and it appears the FedEx Ship Manager uses SSL -- I'll see how far I can get with this]

You wrote: "With most hosting providers, you'll also need to allow her local computer's public ip address to access your databases remotely."

Just to let people know what is involved in getting a connection (I had to find out), the mysql 3360 port needed to be added to your firewall cnf.

From my excellent servint support staff:

1. Edit /etc/csf/csf.conf as root
2. Add 3306 on the line starting TCP_IN within the the double quotes (")
3. Save and reload CSF with:
   csf --restart
4. Confirm with:
   iptables -L -n |grep ":3306"

You also have to change Access Hosts for the database. For those with a cpanel, click MySQL® Databases (bottom right corner)
Find "Access Hosts:" at the bottom of the page and add your IP address (a string of numbers). Restricting access to particular ips is much more secure than opening access to everyone.

I haven't yet got this set up on her computer but hope to this weekend (need to find out how to get to the config area in her operating system first -- I'm using XP).

I've made a lot of headway. But I'm not there yet.

What I am doing is trying to get it to be fully functional on my computer. I've installed the FedEx module, gotten the database hookup and mapped the customer fields.

What I find is difficulty with getting countries and states to properly carry over. The options they have for mapping I don't understand and don't seem to work.

For now for the US, I set up a default profile for US customers.

Although I enabled the dropdown state selection for zencart, it seems to save the whole state and it is that name that is entered as delivery_state I have mapped to fedex. Since the full name isn't valid, it ends up empty and I have to manually enter the two letter state field through FedEx drop down.

How did you handle getting the zencart to save the two letter state prefix instead of the full state name for delivery_state (in the order table)?

I figure I am going to have to set up a separate profile for Canada, England, and any other countries unless you have tips for getting that to work too.

You're looking in the wrong table. Shipping information doesn't necessarily correspond to the customer's address. Rather than looking for shipping info in the customers table, look in the orders table and establish a primary/foreign relationship with the order number. State and country are stored there in the correct format for fedex as well. I don't get on this forum too often so if you need assistance feel free to email me at ptmitch2009######################. I'd be more than happy to let you post anything I write here on the forum to help others as well, but if you want a quicker response email is better for me.

Thanks,
Preston

Looks like my email got blacked out, I didn't realize it would do that. Its just missing the at (google's free email service) dot c o m. :)

Looks like I spoke too soon. Take a look at the conversion tab within the integration wizard. It lets you match zencart database values to fedex values for states and countries etc. Be sure to still use the delivery info from the orders table though.

Thanks for the reply.

I seem to have gotten things working for me (no time yet to get this set up for my friend). After opening FedEx Ship Manager, I clicked on "Integration" and selected "FedEx Integration Assistant". Went on to select the database (available from the ODBC linkup list FedEx accessed) and chose single labels and on the next page "after I enter a lookup value". For clarity, I was not mapping customer info from the customer table but customer info from the delivery info in the orders table so there will hopefully be no shipping address errors.

For the profile, I checked Address1, Address2, City, Country, Contact Name, State/Profince, Company, Phone, Postal Code and "Account Number" (what I used was the order number). So I used orders as the table for all and matched them with:

delivery_street_address
delivery_suburb
delivery_city
delivery_country
delivery_company
delivery_name
customers_telephone
delivery_postcode
delivery_state
and orders_id

I used orders_id as the key (you click on a box "Select Index" to add the key). So that is what you enter in the search box when you are transferring the data to the fedex form.

I needed to map zencart's values to the state/province and country because they weren't showing up properly (the states would not automatically load). Since this cart is still a test cart and had only a few test orders, I realized (after talking to a fedex rep) that the reason I was not able to set up the profile to enter the all the states was because it functions by accessing the database and pulling your choices into a drop down menu. I added the two test states. When I was told I could actually send my friend the profile and she could put it in the appropriate folder, I figured I might be able to edit the profile manually and did so, adding each state.

The location of the xml profile:
C:\Documents and Settings\All Users\Application Data\FedEx\Integration\Profiles

I understand this is the default location. I also used a newer version of FedEx Ship Manager (v. 2472-a). Check the FedEx site for the newest version.

This is what I added for the states between the state conversion tags:

<conversion>
<select value="Alabama">AL - Alabama</select>
<select value="Alaska">AK - Alaska</select>
<select value="Arizona">AZ - Arizona</select>
<select value="Arkansas">AR - Arkansas</select>
<select value="California">CA - California</select>
<select value="Colorado">CO - Colorado</select>
<select value="Connecticut">CT - Connecticut</select>
<select value="Delaware">DE - Delaware</select>
<select value="District of Columbia">DC - District of Columbia</select>
<select value="Florida">FL - Florida</select>
<select value="Georgia">GA - Georgia</select>
<select value="Hawaii">HI - Hawaii</select>
<select value="Idaho">ID - Idaho</select>
<select value="Illinois">IL - Illinois</select>
<select value="Indiana">IN - Indiana</select>
<select value="Iowa">IA - Iowa</select>
<select value="Kansas">KS - Kansas</select>
<select value="Kentucky">KY - Kentucky</select>
<select value="Louisiana">LA - Louisiana</select>
<select value="Maine">ME - Maine</select>
<select value="Maryland">MD - Maryland</select>
<select value="Massachusetts">MA - Massachusetts</select>
<select value="Michigan">MI - Michigan</select>
<select value="Minnesota">MN - Minnesota</select>
<select value="Missouri">MO - Missouri</select>
<select value="Montana">MT - Montana</select>
<select value="Nebraska">NE - Nebraska</select>
<select value="Nevada">NV - Nevada</select>
<select value="New Hampshire">NH - New Hampshire</select>
<select value="New Jersey">NJ - New Jersey</select>
<select value="New Mexico">NM - New Mexico</select>
<select value="New York">NY - New York</select>
<select value="North Carolina">NC - North Carolina</select>
<select value="North Dakota">ND - North Dakota</select>
<select value="Ohio">OH - Ohio</select>
<select value="Oklahoma">OK - Oklahoma</select>
<select value="Oregon">OR - Oregon</select>
<select value="Pennsylvania">PA - Pennsylvania</select>
<select value="Puerto Rico">PR - Puerto Rico</select>    
<select value="Rhode Island">RI - Rhode Island</select>
<select value="South Carolina">SC - South Carolina</select>
<select value="South Dakota">SD - South Dakota</select>
<select value="Tennessee">TN - Tennessee</select>
<select value="Texas">TX - Texas</select>
<select value="Utah">UT - Utah</select>
<select value="Vermont">VT - Vermont</select>
<select value="Virginia">VA - Virginia</select>
<select value="Washington">WA - Washington</select>
<select value="West Virginia">WV - West Virginia</select>
<select value="Wisconsin">WI - Wisconsin</select>
<select value="Wyoming">WY - Wyoming</select>
</conversion>

I don't know what countries she wants to include so I only added the United States for now.

I hope this helps someone else too!

I'll check back after I get things set up on my friend's computer to fill in whether there were any additional problems.

Oh, and I made the customer address choice in zencart to require a dropdown menu for the state/province. This way there are no misspellings of the state and it will map correctly.

The order table was the only table I ended up using to create the mapping profile.

I have a quick question. With the FedEx Ship Manager, you have the option to hold labels for later printing. I am unsure if they are held on the FedEx site or on "my" computer. I would be happy if someone else logging into the same account on a different computer could retrieve the labels from fedex.

I have a quick question too... Is this what we need installed and running by June 1st?

Fedex is doing a major update for their API, will this integration replace the current mods I am using for Fedex labels and rate generation?

This is a major deal, I'm very surprised there are no threads in the forum regarding this!