Greetings!

My office uses a FM Pro 4.0/Mac custom-made database to handle
invoicing, customer records, inventory and so forth in a small
publishing operation. We designed the database ourselves, and it is
pretty kludgy. We want to upgrade to 7.0, but haven't found the time to
do the work. Anyway, we need to export our customer records (a little
under 6000 records) for import to UPS's on-line shipping database. This
can be done (we had a test finally work after lots of tweaking), but the
UPS database import function is very picky about certain standards. If a
single record violates any of its standards, the whole import fails! It
would be much more convenient if certain records were excluded, and we
could fine tune those. I'm writing to ask for advice in making sure our
database meets those standards, because it is a lot of work to keep
getting an unhelpful "import failed" error without a pointer to what
caused it to fail.

In our test, we had two types problems, which we overcame by hand for 1%
of the entire database, but we don't want to have to work so hard on
this for 6,000 records.

1) Certain fields used in addresses are limited by UPS to 35 characters.
We cannot figure out how to check within FM Pro 4 for records which, in
a given field, exceed that length. Can anybody suggest a simple way to
do this? We can tweak by hand, but it would really help to get a found
set that contains only those records which violate this for a given
field. (The only way we've been able to figure this out to to export it
to Excel, set a monospaced font, and resize columns to accommodate just
that number of character, then scroll through the Excel worksheet. This
seems inefficient for an easily quantifiable task.)

2) Certain fields must not be empty. We can get a found set of records
for which a given field is empty, and we can put a dummy into these
fields, but is there a way to run a disjunctive set which will return a
set of records, each of which has one or more empty fields from a set?
Then we could just scroll through those records, putting something
appropriate into all those fields one one pass.

Thanks in advance for any useful suggestions!

George

George Nospam <gfowler (AT) nospam (DOT) net> wrote:


Make another field in the file, a calculation field with a number
result.

The formula for the calc is:

Length(AddressField)

This will give you the number of characters in the field. PUt the field
on a layout. Then you can find all the records longer than 35. (>35 in
the find)

How to change them? If you simply want to truncate, you can use Replace
and Substitute.

NOTE: BEFORE DOING ANY REPLACE OR SUBSTITUTE WORK, BACK UP YOUR
DATABASE. Replaces are NOT undo-able.

Do a replace in the field, after finding all the too-long records, using
a calculated result.

Substitute(AddressField, Left(AddressField, 35))

But first you may want to find all the addresses that have "Road"
spelled out and shorten it to Rd, same with Street, Lane, Avenue, Post
Office Box, and Boulevard using the same kind of Replace with a
calculated result. I bet that eliminates a lot of the too long
addresses.

Work on one set of records each time, do a find for "Lane" and do the
substitute/replace, then find "Avenue" and do the same thing.
Finding empty, use just the equal sign (=) in the find.

Do it field by field, finding each set of records and immediately doing
a Replace with a calculated result, using the placeholder text you want
to insert for that field.

This kind of data cleaning using Find, Replace, and Substitute on a
relatively small set of records shouldn't take more than an hour or so.
Back up as you go along so that if you mess up and hit the wrong button
on Replace you don't lose all your work. (very easy to do).

No need to flip to every record and make changes by hand.

Lynn Allen
--
Allen & Allen Semiotics www.semiotics.com
FSA Associate Filemaker Design & Consulting

Lynn allen wrote:

I believe what Lynn meant to say here was to use the command
Left(AddressField, 35)
to truncate the field to 35 characters


You will need the substitute command when doing a replace to get all
"Road" changed to "rd" etc.
Do a find for the text "road".
Replace the contents of the Address field with a calculated result,
this calculation is:
Substitute(AddressField, "Road", "rd")

You should however know there are 2 issues with the Substitute command;
1) it is case sensitive, in the above eg. a record containing the text
"Road" will be updated but "road" will not be so you may want to first
convert all your data to uppercase using the "Upper()" function.
2) All occurrences of the letters "Road" will be updated so if you have
the address "RoadIsland" you will end up with "rdIsland".


I will also reiterate Lynn's warning;
MAKE A BACKUP!!!
MAKE MORE BACKUPS AS YOU'RE GOING ALONG!!!

In article <1h1p9jw.1lulf9a13uy81kN%lynn (AT) NOT-semiotics (DOT) com>,
lynn (AT) NOT-semiotics (DOT) com (Lynn allen) wrote:

Thank you very much for your helpful and extremely explicit reply.
You're right, this doesn't look unduly complex.

George

1) You can create a field calculated to equal length ( field ), which will
give you the number of characters (including spaces ) in the field. If
you're changing the data only for UPS, though, and don't want or need to
modify the data permanently, you can define a calculation that equals the
result UPS needs, and export the calc. F'rinstance, if you want to truncate
the address just for UPS, you can have UPSAddress = left ( address , 25 ).
You could even take care of other things that way, such as case ( trim (
zip ) = "" ; "33333" ; zip ) or left ( substitute ( address , Road , Rd ) ,
35 ).

2) If you'd like to find records where field1 is empty or field2 is empty or
field3 is empty: Enter find. Set field1 to "=". Add a request (command N).
Set field 2 to "=". Add a request (command N). Set field 3 to "=". Perform
find.

For large numbers of fields, you may want to script this.

--

Chris Cain
Extensitech, Inc.
ccain (AT) extensitech (DOT) com


"George Nospam" <gfowler (AT) nospam (DOT) net> wrote

I, too, feed my customer info into UPS's Worldship. Lynn succinctly
answered the main questions, but here's a suggestion for another:

Rather than do all that, make a calc field that tests the original field
for content & sticks a dummy into it if empty.

If your must-be-filled field is, say, ContactName, where some companies
never gave you a person to ship to, then set up a calc (text result) for a
field you can call Contact_Calc or something:

If (IsEmpty(ContactName), "Shipping Manager", ContactName)

That will install the phrase "Shipping Manager in every empty Contact Name
field. Map this field to Worldship instead of Contact Name.

Incidentally, this may be clumsy for you, but I set up an intermediary
customer database specifically for Worldship's use. I included only those
fields Worldship needs, and made various calc fields for purposes like the
above. I fully update this database from my main customer database every
night, plus I have a routine where new customers that get harvested from a
small web sales operation are immediately imported into this.

It's all been working flawlessly for over a year.

Steve Brown