Match with only part of a field

[lonntj]

Is there a way to set up a relationship that will match any record whose match field contains (but is not necessarily equal to) a search string?

I have a database of 26K records, making up our mailing list, there are a number of people and companies we do not want to send mail to, and need some way to designate that. Easy enough, the problem is that the people we get our data from do not always enter Company names the same way. For example a company "Housing Authority" can be entered as First=Housing, last=Authority, or First="Housing Auth" last="", (And they're always coming up with new ways to do this.

Is there some way to set it up so that a match field containing "Housing" would match all these records?

Thanks!

[Ender]

There are ways to do this with multi-keys, but by creating a work-around for the real problem, you would be just delaying addressing it.

You should have a separate field (or file) for Company. Separate the company names into this field, and make sure you import or your users enter into it correctly.

Trying to use Contact Name fields for the Company name in no good.

You should also consider not using the Name fields at all for the relationship keys, as names have a way of changing. It's better to generate internal serial numbers, and use those as keys.

[lonntj]

The Idea here is to use the relationship to determine whether a record contains one of the subject names or not, since going through 20K records by hand isn't an option. I need some way to match the imported names to the names in the related DB. The ONLY way to match these records is by name (and although there is some variation in the names, there are usually one or two words that never change and are unique). There may be some way to do this with a script instead, but I think it will would much better with a relationship.

The ultimate result of the relationship is that there is another calculation field in the main DB which counts related records and if it finds one will flag the record. Right now I just have to create a record for each name variation which is possible now, but as this DB will grow to 100K plus records and we will be continually adding names to the related file, some kind of workaround will save considerable grief.