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 ((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 2
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. 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][]

  2. Sales.IsArchive)=False[]