Calculation to compare field contents

[Bmiller4evr]

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.

[Fitch]

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

[Bmiller4evr]

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.

[comment]

1. How many records are there in each group?

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

[Bmiller4evr]

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.

[comment]

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).

[Bmiller4evr]

I think this will work quite nicely, many thanks.

[Bmiller4evr]

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

[comment]

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.