Jump to content
Sign in to follow this  
Amy3515

finding duplicates in multiple fields

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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:

email_status.png

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:

fmrecord.png

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.