creating an SQL import/export map

On Mar 5, 4:01 pm, knightsbridge wrote:

Addresses only is not very hard. Adding packages is much more work. Here is an example We run orders through UPS prior to tendering the merchandise so we can charge actual shipping and merchandise total at one time. So this query is based on orders that are on hold, not completed orders. But you can change the query to the transaction table instead of transactionhold table. First create a view using this query, it is keyed off the transaction number of an order on hold. You can see this number by going into the POS and recalling an order on hold. This query has two parts, the top query gets addresses when there is no shipto address set. The bottom query gets the orders with shipto addresses. There is a union of the two queries to make the results easy to work with.

SELECT TOP (100) PERCENT ReferenceNumber, Company, Name, Address, Address2, City, State, Zip, EmailAddress, Phone, RefID, NotifyEmail FROM (SELECT dbo.TransactionHold.ID AS ReferenceNumber, CASE dbo.Customer.Company WHEN '' THEN dbo.Customer.FirstName + ' ' + dbo.Customer.LastName ELSE dbo.Customer.Company END AS Company, CASE dbo.Customer.Company WHEN '' THEN '' ELSE dbo.Customer.FirstName + ' '

• dbo.Customer.LastName END AS Name, dbo.Customer.Address, CASE dbo.Customer.Address2 WHEN '' THEN NULL ELSE dbo.Customer.Address2 END AS Address2, dbo.Customer.City, dbo.Customer.State, dbo.Customer.Zip, CASE dbo.Customer.EmailAddress WHEN '' THEN NULL ELSE dbo.Customer.EmailAddress END AS EmailAddress, dbo.Customer.PhoneNumber AS Phone, dbo.TransactionHold.ReferenceNumber AS RefID, CASE dbo.Customer.EmailAddress WHEN '' THEN '0' ELSE '1' END AS NotifyEmail FROM dbo.Customer INNER JOIN dbo.TransactionHold ON dbo.Customer.ID = dbo.TransactionHold.CustomerID WHERE (dbo.TransactionHold.ShipToID = 0) UNION ALL SELECT TransactionHold_1.ID AS ReferenceNumber, CASE dbo.ShipTo.Company WHEN '' THEN dbo.ShipTo.Name ELSE dbo.ShipTo.Company END AS Company, CASE dbo.ShipTo.Company WHEN '' THEN '' ELSE dbo.ShipTo.Name END AS Name, dbo.ShipTo.Address, CASE dbo.ShipTo.Address2 WHEN '' THEN NULL ELSE dbo.ShipTo.Address2 END AS Address2, dbo.ShipTo.City, dbo.ShipTo.State, dbo.ShipTo.Zip, CASE Customer_1.EmailAddress WHEN '' THEN NULL ELSE Customer_1.EmailAddress END AS EmailAddress, Customer_1.PhoneNumber AS Phone, TransactionHold_1.ReferenceNumber AS RefID, CASE Customer_1.EmailAddress WHEN '' THEN '0' ELSE '1' END AS NotifyEmail FROM dbo.ShipTo INNER JOIN dbo.TransactionHold AS TransactionHold_1 ON dbo.ShipTo.ID = TransactionHold_1.ShipToID INNER JOIN dbo.Customer AS Customer_1 ON TransactionHold_1.CustomerID = Customer_1.ID WHERE (TransactionHold_1.ShipToID 0)) AS DERIVEDTBL ORDER BY ReferenceNumber

Create an ODBC connection from your UPS machine to your database Create an import map in UPS, follow these options in worldship UPS online connect, keyed import, More, Create/Edit Map, Enter a Name, Create, Pick your ODBC source and enter username/pw Your view should be listed in the ODBC tables drop down, select it Use reference number as your primary import key Align the rest of the fields with the shipment information, select the field on the left, then where it goes to on the right, then click connect Save when you are finished To use it go to UPS online connect, keyed import, then pick the name of the keyed import you created, a little box will appear, type the transaction number in that box and press enter, the address information should flow into the correct spots

I have found using keyed import is very easy, I also recommend turning off the Update Address Book feature (and deleting the current entries) because it has a tendancy to try to silently autocorrect your address to an older address used by the same customer. Since all your addresses are stored in RMS you dont need to use the UPS address book. You can also use the same view to do keyed import into DHL's software with no changes to the query.

This should get you all set up Matt Obiwholesale.com