For fun, several friends and I constructed an elaborate FileMaker Pro
database containing all songs to reach the Billboard Top 40 charts (and many
that didn't chart) from 1955 forward.

I have a script set up to do an Import of changed files that various
participants contribute from time to time (they may be correcting or adding
information to existing records, or adding completely new records that
weren't previously in the database).

Because there are many songs with the same title that are in fact entirely
different songs, I have an import script set up that uses the artist, title
and chart position fields as match fields. If all of these match, the newly
imported record *replaces* the one currently in the database. If there is
not a perfect match, the imported record is *added* to the set.

We've encountered a problem that surfaces when one of the match fields has
quotation marks in it. For example, if I import several changed records that
have Nat "King" Cole as the artist, the match is not recognized, even though
all fields do indeed match. These records are added to the database, rather
than replacing the records that are there. Thus, we end up with duplicate
records. Same thing if the quotes appear in the title field (e.g., "Nag" by
The Halos, in which the quotes are actually part of the title).

I thought this might be an issue of "Smart Quotes" vs. "Straight Quotes"
throwing things off if the data were coming from a different source. But
this is not the case. I've actually experimented by exporting and importing
records from the same database to ensure that no data in the match fields
can possibly have changed. I've even retyped the fields with quotes in them
to make sure there isn't some rogue ASCII character there that's screwing
things up. Despite all efforts, the imported records still come in as new
records, rather than replacing the records already there.

I'm at a loss to know what's causing this, and even more at a loss to know
how to solve it. One of the participants thinks we should ditch any artist
or title with quote marks present, but I'm anal enough to want these to be
rendered exactly as they appear on the record label, so I'd like to keep
them if I can.

Anyone have any suggestions? Thanks in advance for your help.

Hi,

you could make a new calculation field, which concatenates the three match
fields and strips out all unwanted characters. Something like :

Substitute (Artist_name;"""","") & Substitute (Song_title;"""";"") &
Chart_position

Note that you can also embed the Substitute function, so if you would also
like to strip out all spaces use :

Substitute(Substitute(Artist_name;"""";"");" ";"")

Regards,

Peter

"BigStar303" <bigstar303 (AT) aol (DOT) comma> schreef in bericht
news:20030806103450.18517.00000902 (AT) mb-m18 (DOT) aol.com...