ODBC

[Jose Lerebours]

Richard Posted:
--------------------------------------------------
A question for those using fp ODBC.

I need to pass information to and get back from UPS Worldship.

I created an Access file as that is what Worldship is written in. 

ODBC record: PO #, store #, name, address, city state and zip - From my
application
 Tracking #, pickup date, number of cartons, weight, cost -
from Worldship

I am thinking through the procedure and since I have very little ODBC
experience, would like to know if I am approaching this correctly.

I am plan to use the low level method.
 1. When an order is created, an odbc record is created with the po
#, store #, name, address, city state and zip. 
 (This gives my application control of the shipping address
witch UPS uses)
 2. Once shipped, when the order is accessed using @entsel, I want to
check if the order is a ups shipment and if it is, check to see if there
is
a tracking #.
 a. No number - read the Access record and get the shipping
information
 1. no value in record - not been shipped yet
 2. tracking # - read into filepro record
 b. Number present - do nothing more

Since my client ships a large number of orders each month(about 8,000),
I
would like to run a procedure that deletes records for those orders that
are
30 days or more old from the odbc database. This should keep the
database
fairly small and make access fairly quick.

At the current time the tracking #'s are being update from a csv file
created when Worldship close at the end of the day. This procedure will
be
more real time. Its other advantage is a standardized point for store
address. When changed in my application, the correct address will be
used
by UPS. No, I don’t need to go back and change the address once an
order
has been shipped.

Any suggestions will be greatly appreciate.
------------------------------------------------------------

I wrote this a very long time ago.  No ODBC available back then ... or 
nothing one could use in a production environment.  What was the problem
with it, oh yeah, I was on a UNIX box and ODBC was read only in WINDOWS
if memory serves me well.

Any way, the way I worked things out was:
1) Created a filePro file using the World Ship data structure, thus
keeping
all records in filePro native format
2) I had crons running to export from UPS file to a flat file which then
FTP to an UPS Station we had in the building
3) The operators had preset times where they handled all UPS related
matters
and used UPS built-in "import" to import the data we uploaded via FTP
from
our filePro application.
4) Once imported, they were able to print all labels and we were done

This worked for ever.  Once written, I was never asked to go back to it
again.  We even used things UPS normally does not use
1) Tracking number - a standard
2) Reference numbers
3) Contact Information
4) email notification request for ...
5) and a bunch of other things I cannot remember

UPS gave us everything we asked for to better understand their data
structure
and trained our staff on the import process.  It was very simple and
everyone
involved was very happy with the end results.

Heck, I think I did this just as UPS launched this service some years
ago.

I never knew they had written this in access.

Good luck!


--
Jose Lerebours
http://www.fpgroups.com
954-559-7186
filePro + PHP Solution Developer