finding duplicates in multiple fields

[Amy3515]

In my contacts table, I have an "email" field. If the email bounces, the address gets shifted to a "bad email" field. If the recipient opts out of communication, the email address gets moved to an "opt out" field. When I acquire new contacts, I would like to make sure that the new email is not in either the "bad email" or "opt out" field. How can I find duplicates in the multiple fields?

[aldipalo]

Welcome to the Forum!

I'm sure there are many different ways to approach this problem, but, I believe that really has to do with how you are acquiring the email addresses. You're not saying if this is a data mining approach where you have many addresses or if this is an opt-in list gathered one at a time from your web site or some other method. But, I'll take a stab at it. I am sure you'll get many other, probably more elegant, ways of approaching this.

If the addresses is either in the Bad address field OR the Opt-Out address field you can set up a calculated field (BadAddressField&OptOutField). Now you just need to set your script to check that field and you'll have your answer.

I suppose the real issue is how large is your list? If you have tens or hundreds of thousands of contacts whatever approach you take will be time consuming.

hth

[Søren Dyhr]

In my contacts table

Is this the only table?

--sd

[Amy3515]

I have many tables, but the 3 fields in question are all in the same table.

[Amy3515]

New lists are usually 2,000-3,000 names. Your solution doesn't seem to compare your calculated field to the new email address (but maybe I'm missing something). Right now, I have a layout with the 3 fields next to each other. I sort by name and scroll through to see if an email is in 2 columns, but I keep thinking there should be an easier way.

[comment]

This doesn't seem like a good approach. An e-mail address is an e-mail address. It can be "good" or "bad" or "opt out" - those are just attributes of the address (or the contact). Instead of 3 address fields, there should be one for the address and one for its status.

This way it's easy to establish a relationship based on matching the address, filtered by status.

[aldipalo]

Your import script would compare the fields. I would agree with comment though, it would be a lot simpler to just have a tag field that says the email is Good/Bad/Opt_Out.

The way you have it now the script would be something like:

Go To Layout(New Email Addresses)

Go to Record/request (First)

Set Variable($Email; Newaddresstable::Newemailaddress)

loop

Go To Layout(Contact Layout)

Show All records

Enter Find Mode() //Uncheck Pause

Set Field(Calcfieldaddress; $Email)

Perform Find()

If(Get Found Count) >=1

Go To Layout(New Email Addresses)

Go to Record/request (Next; Exit After Last)

Set Variable($Email; Newaddresstable::Newemailaddress)

else

New Record/Request

set field($Email;newemailaddress)

Commit Record

Go To Layout(New Email Addresses)

Go to Record/request (Next; Exit after last)

Set Variable($Email; Newaddresstable::Newemailaddress)

Endif

EndLoop

(this is just a quick, down and dirty example which probably needs some tweaking) :

What you want to do is set up your script to loop through the records in your new lists and do a find in your contacts list. But it seems that whatever you do if you are bringing in several thousand addresses at a time it will be time consuming.

I am sure someone else will have a more elegant and speedier way. Perhaps with a filtered relationship?

[Amy3515]

I think this makes the most sense. The layout evolved into 3 email fields as it developed, but I can re-merge them with a status field.

Thanks for all of the suggestions/help.

[jbernal]

Hi - you might find this solution helpful, too:

You can create multiple instances of the table and draw relationships between the relevant email fields; finally, you can define a calculation field that returns whether the email is also listed in either the bad or optout fields.

Here is what the relationships would look like:

Here is what the calculation field would look like:

Case(IsValid(email_bad::id);"Bad Email¶") &

Case(IsValid(email_optout::id);"Opt Out")

Finally, here is the result:

[comment]

You could also concatenate the two fields (email_bad & ¶ & email_optout), and match on that - thus saving a TO and a relationship.

However, neither is a good solution, because they do not deal with the real issue here - the fact that the data is not properly normalized. For example, with the given structure, it takes three requests merely to find an address.