eBay Blackthorne and UPS WorldShip Keyed Import Field Mapping

1) Create a DSN for eBay Blackthorne. I called mine EB.

2) Make a new query in eBay Blackthorne. View SQL and paste the following in. I filtered the eligible orders so that only orders in “Ready To Ship” and are not archive items are eligible to be imported into UPS WorldShip.

SELECT Sales.OrderID, Buyers.eBayUserid, Buyers.Email, Sales.ShippingCo, [WeightLbs]+([WeightOz]/16) AS ActualShippedWeight, IIf([addresses].[Company]=””,[Addresses].[FirstName] & Trim(IIf([Addresses].[Initial] Is Null,””,” ” & [Addresses].[Initial])) & ” ” & [Addresses].[LastName],[Addresses].[Company]) AS CompanyOrName, IIf([addresses].[Company]<>””,[Addresses].[FirstName] & Trim(IIf([Addresses].[Initial] Is Null,””,” ” & [Addresses].[Initial])) & ” ” & [Addresses].[LastName]) AS Attention, Addresses.AddressLine1, Addresses.AddressLine2, Addresses.City, Addresses.State, Addresses.Zip, Addresses.Country, Addresses.Phone, “Y” AS Notification1, Email AS UPSFaxOrEmail, IIf(addresses.Company=””,”Y”,”N”) AS Residential
FROM 1
ORDER BY Sales.OrderID DESC;

3) The following are the field mappings for UPS WorldShip. I made “OrderID” the default keyed import field.

ODBC Data Source Field (Table::Field)
WorldShip Field (Container::Field)

EBWorldShipOrders::eBayUserid
Ship To::Customer ID
EBWorldShipOrders::Residential
Ship To::Residential Indicator
EBWorldShipOrders::CompanyOrName
Ship To::Company or Name
EBWorldShipOrders::Attention
Ship To::Attention
EBWorldShipOrders::ShippingCo
Shipment Information::Service Type
EBWorldShipOrders::AddressLine2
Ship To::Room/Floor/Address 2
EBWorldShipOrders::Phone
Ship To::Telephone
EBWorldShipOrders::Zip
Ship To::Postal/ZIP Code
EBWorldShipOrders::City
Ship To::City
EBWorldShipOrders::Email
Ship To::Email Address
EBWorldShipOrders::UPSFaxOrEmail
Shipment Information::Notification Recipient 1 Type (fax or Email)
EBWorldShipOrders::State
Ship To::State/Prov
EBWorldShipOrders::AddressLine1
Ship To::Street Address
EBWorldShipOrders::Country
Ship To::Country
EBWorldShipOrders::Phone
Shipment Information::Notification Recipient 1 Telephone
EBWorldShipOrders::ActualShippedWeight
Package::Weight
EBWorldShipOrders::OrderID
Package::Reference 1
EBWorldShipOrders::CompanyOrName
Shipment Information::Notification Recipient 1 Company or Name
EBWorldShipOrders::Attention
Shipment Information::Notification Recipient 1 Contact Name
EBWorldShipOrders::UPSFaxOrEmail
Shipment Information::Notification Recipient 1 Fax or Email
EBWorldShipOrders::Notification1
Shipment Information::QVN Ship Notification 1 Option

4) You’ll need to edit the “Service Type” in the map to translate external field data such “UPSGround” to “Ground” or “UPS2ndDay” to “2nd Day Air”

  1. Sales INNER JOIN Addresses ON Sales.ShippingAddressID = Addresses.AddressID) INNER JOIN Buyers ON Sales.BuyerID = Buyers.BuyerID) INNER JOIN Listings ON Sales.ListingID = Listings.ListingID
    WHERE (((Sales.StatusID)=15) AND ((Sales.IsArchive)=False[]
0 0 vote
Article Rating
3 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Mike

I’m having an issue with this. Seems no matter what I change in the map, UPS Ground always pushes to my DB as ‘Ground’.

Any thoughts?

tekgems

eBay Blackthorne uses “UPSGround” as a service type for UPS Ground. In the mapping portion of the instructions, you’ll need to edit the “Service Type” map. You’ll need to enter in all the values that eBay Blackthorne uses then use the appropriate values that UPS WorldShip understands.

For example:

UPSGround -> Ground
UPS2ndDay -> 2nd Day Air

You should know that eBay Blackthorne is constantly evolving and their database structure occasionally change. So, as time passes, the query may need to be modified.

tekgems

http://tekgems.com/images/sold-forum/ups-service-type.jpg

Within the field mapping section of UPS WorldShip, you also have to set up translations between what shipping method is used in eBay Blackthorne to valid shipping methods that UPS WorldShip recognizes.