I have a group of records, one of the fields is a a Customer Name (not unique). On another table I have a list of names that have been added to a blacklist. I need a calculation field that will compare the customer name to the black list to see if they have been flagged.

Create a relationship on the name, and then test for IsEmpty( related::name ).

I do not think I can use a name match to achieve my objective. To add a further point of clarification. The names on the 'black list' are typically only a portion of customer name field, not an entire match.

An example - *are customer names that need to be blacklisted

Customer Names:

Smith, James

Jones, Edward and Stacy

Hertz, Sam Living Trust*

Hartford LTD*

JPMorgan Chase Bank*

Blacklist:

Bank

LTD

Trust

the blacklist serves as a means to identify a specific type of property owner.

1. How many records are there in each group?

2. Is this a one-time operation, or do you need to this periodically?

the customer name list is 1.5million, the black list is about 100 names, if I had to guess I would say that about .5% of the customer names need to be blacklisted. The process needs to run as often as the database is updated, which is typically bimonthly.

How about this way, then?

BlackWords.fp7.zip

Note that this assumes the blacklist contains individual words - otherwise it could get pretty complex (and slow).

I think this will work quite nicely, many thanks.

Any way to index the cFlag? running a query on cFlag for 1.5million unstored seems to be taking some time.

You could populate it by script. Also, If you update by importing, only new/updated records will be in the found set immediately after the import; if you then constrain the found set, it should be much quicker.