Checking For duplicates

ghilton

I have created a marketing database which collects email addresses and sends out mailshots. What I would like to do is search for duplicate reckords and remove them leaving just one in the database.

I cannot think of a way to do this and any help would be most greatfull

Thanks

tford

Graham,

One way would be to add an attribute on the same BO with the email addresses: BoName.Duplicate

A rule could then use the COUNT function to turn on the Duplicate flag if the count is >1.

Tom

ghilton

Thanks Tom that sounds like a good idea, I will look into this and develope it.

Thanks again for the help, still dunno why you are not working for AwareIM as you are brilliant at this?

Cheers Buddy

tford

One other idea. To eliminate duplicate email addresses from happening the the first place, check out the rule that exists in any new BSV on the RegularUser BO. It's designed to eliminate the possibility of duplicate user names.

Tom

ghilton

great one I will have a look

Thanks

ghilton

reading the manual it does say that I can only use COUNT on a BO and not BO attributes?

tford

Grahsm,

With COUNT the attribute would be used in the WHERE clause.

I noticed your other post about this topic. It's easiest if you keep one thread together.

Tom

ghilton

Sorry I was just trying to have input from others who me have tried this!

I have a couple of issues I am trying to work through and seem to be going no where.

I cannot find any examples of the count statement so I am finding this hard to grasp! As I am not understanding the syntax needed

tford

Graham,

In the Rule Language Reference, it gives an example:

COUNT Transaction WHERE (Transaction IN Account.Transactions)

You might try to get this to work first:

1) If COUNT Contacts WHERE Contacts.EmailAddress=ThisContacts.EmailAddress > 1 Contacts.Duplicate='Yes'

I think this syntax will work but not 100% sure

2) Do a query of all Contacts with Contacts.Duplicate='Yes' . I would NOT try to have the query list all but one of them until you are able to get this process to work.

Tom

ghilton

Thanks tom I will have a go

ghilton

Hmmmmm, this is what I get -

Syntax error at token "Contacts" (column 25).

Token "(" was expected.

I have tried different combinations to no avail

tford

Try with (

If COUNT Contacts WHERE (Contacts.EmailAddress=ThisContacts.EmailAddress > 1 ) Contacts.Duplicate='Yes'

Are you using the Context Helper?

ghilton

Yes When I try it this way it does not like the '>'

ghilton

Syntax error at token ">" (column 74).

This is what is say's when trying to save

tford

Please use the context helper!!! I am doing this quickly by memory as I am working on another project myself.

If COUNT Contacts WHERE (Contacts.EmailAddress=ThisContacts.EmailAddress) > 1 Contacts.Duplicate='Yes'

Tom

ghilton

I am using the helper but it still doe not work I have tried all different ways with and with out the helper and it still does not want to work. Yor last post creats - Syntax error at token "Contacts" (column 78-) which the helper is looking to add an 'And' or 'Or' before the last 'Contacts'?

ghilton

Well I cannot make this work no matter what I try?