Magento

I have had UPS Worldship configured to work with Magento 1.3 for over a year.  I just upgraded to 1.4.1 and the table (vi_sales_order...) I was pulling the UPS shipment data from via a ODBC connection no longer exists.  What Magento table(s) have the Order number, Customer name, and the shipping address information?

This same question relates to Fedex Shipping Manager and what table(s) do we pull the shipment data from?

How are all the website handling shipping if they are not using UPS Worldship or Fedex Shipping Manager software??

Bob Sorenson
iCom

I am in the process of dealing with this issue now - for a new store which is on 1.4.1.1 - and may be able to answer some of your questions. I’m not sure if I am using the correct database jargon for these tables and fields but hopefully it makes sense. At the end I asked a question that I have regarding this process.

In the table: sales_flat_shipment_grid you will find:
order_increment_id : the Magento Order number
increment_id : the Magento Shipment number
shipping_name : The full name of the recipient
order_id : this is a key value which appears in other tables

In the table sales_flat_order_address you will find:
parent_id : this matches the order_id value in sales_flat_shipment_grid. However, there are two instances of this value in sales_flat_order_address. One for billing address and one for shipping address.
entity_id : this is a key value in this table. Each parent_id has two entity_ids one for billing address one for shipping. The second entity_id for each parent_id refers to the shipping address needed by WorldShip. The main address fields can be found in this table. Their field names match their contents for the most part. There is a column for email but it doesn’t contain any values in this table in my database. The customer (billing) email address can be found in:

In the table sales_flat_order
entity_id : this is a key value which matches order_id in sales_flat_shipment_grid and parent_id in sales_flat_order_address .
customer_email : customer (billing) email address.
shipping_description : UPS shipping method

For the write back from WorldShip to Magento:
In the table sales_flat_shipment_track
order_id :  this is a key value which matches order_id in sales_flat_shipment_grid and parent_id in sales_flat_order_address and entity_id in sales_flat_order
number : UPS tracking number

The problem I am having is finding a way to associate increment_id in sales_flat_shipment_grid (the Magento Shipment number) with the entity_id for the shipping address from sales_flat_order_address. So that my shipper can enter the Magento Shipment number into WorldShip and pull the shipping address. Currently, if I set up relationship between tables based on the shared order/entity/parent id value, WorldShip see both the billing and shipping address and automatically chooses the first which is the billing address.

Upon further investigation of Magento tables, I now see that the large sales_flat_order contains all the data I need, either directly or through key field data shared across other tables. Here is the breakdown:

Table: sales_flat_order
shipping_address_id This key links to sales_flat_order_address‘s entity_id which pulls in shipping address for WorldShip
increment_id This is the Magento Order number which will be used as defined entry key to import data into WorldShip
customer_email Customer’s (billing) email address - used as recipient#1 in WorldShip
shipping_description Shipping method used as shipping method in WorldShip

Table: sales_flat_order_address
entity_id Contains shipping address via sales_flat_order‘s shipping_address_id

Table: sales_flat_shipment_track
number UPS tracking number is written from WorldShip back to Magento in this field

The biggest thing for me here was finding that the shipping_address_id in sales_flat_order matched the entity_id in sales_flat_order_address. This allows my shipper to enter an obvious, known value - the Magento Order Number - into WorldShip and only pull in the shipping address.

mgseeley - 10 March 2011 04:49 AM

Upon further investigation of Magento tables, I now see that the large sales_flat_order contains all the data I need, either directly or through key field data shared across other tables. Here is the breakdown:

Table: sales_flat_order
shipping_address_id This key links to sales_flat_order_address‘s entity_id which pulls in shipping address for WorldShip
increment_id This is the Magento Order number which will be used as defined entry key to import data into WorldShip
customer_email Customer’s (billing) email address - used as recipient#1 in WorldShip
shipping_description Shipping method used as shipping method in WorldShip

Table: sales_flat_order_address
entity_id Contains shipping address via sales_flat_order‘s shipping_address_id

Table: sales_flat_shipment_track
number UPS tracking number is written from WorldShip back to Magento in this field

The biggest thing for me here was finding that the shipping_address_id in sales_flat_order matched the entity_id in sales_flat_order_address. This allows my shipper to enter an obvious, known value - the Magento Order Number - into WorldShip and only pull in the shipping address.

Mgseeley -

I think you solved the problem for me.  I am going to try your solution on the clients Worldship setup tomorrow.  If the ‘shipping_address_ID’ matches the ‘entity_ID’ then I can pull all of the ‘Ship To’ fields Worldship needs.

Thank you very very much for the insight!!

Bob Sorenson
iCom

mgseeley:

I’m hoping that you can help me understand something… I totally get pulling data from the DB and all the associated fields.  The part that I’m having trouble wrapping my head around is writing the tracking number back to Magento from worldship.  In the table sales_flat_shipment_track, these entries are dynamically created through the backend when you click on “Ship” in the order details page.  Can Worldship create these entries into Magento?  I’d like to do the following:

1.  Create a “shipment” entry that would change an order state to “Complete”
2.  Write tracking information back into Magento
3.  Have system send email to customer with tracking information…

Is this all possible with Worldship.  I just don’t quite get if creating an entry in sales_flat_shipment_track will initiate the “shipment” process to do the things above…

Addition:

I was having trouble pulling the states from sales_flat_order_address (region), but I did notice that there was another column, region_id in sales_flat_order_address.  This may have been because UPS Worldship doesn’t recognize the fully spelled state name, so I had to put in a translation.  It just started to not pull the state at all.

The table directory_country_region contains all the translations for region_id, so I connect these two tables and got the state/region abbreviation that way - it includes the abbreviation for all states/provinces/regions including Canada and Armed Forces locations…