Aggregate Calculations

ghilton

Hello

I am struggling abit here to find the right syntax what I have is

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

What I am trying to do to look for duplicate records then apply an attribute to yes.

Example - if I have 'sam@hotmail.com' in the database 7 times I want to apply the duplicate flag to 6 of them. I can then run a process to deleted all records where the duplicate flag = yes

aware_support

This rule should be on the Contacts object:

IF EXISTS Contacts WHERE (Contacts.EmailAddress = ThisContacts.EmailAddress AND Contacts.ID <> ThisContacts.ID) Then
ThisContacts.Duplicate = 'Yes'

(check if there is already a contact, different from the current one) with the same e-mail address as the current one).

ghilton

Thanks support I will try this and see what happens. One question though is I do have a contacts.id but the data is always imported and the index/id does not populate!

tford

Every BO has a BoName.ID attribute automatically.

ghilton

Cool, did not know that! Thanks Tom

ghilton

Cool this works a treat......

The other issue I have with this though is most of the data is going to be imported so I need to figure out a way of doing this on import. I also could use a process to check exsisting data for duplicates and applying the attibute

I have tried various ways but cannot get this right could some one shed some light on this please?

Merry Chritmas and a Happy New Year

A big thanks to Tom and Support for everything you have done this year.

tford

Graham,

You can choose whether rules are applied at import time or not. It's described in the manual.

Secondly, if you have a process that works today you could execute that process at the conclusion of the import process.

Tom

ghilton

Hi Tom

Thanks for the reply

I cannot see how to put on the rule at import going through the manual!

I have used this process from the rule earlier in the threed
IF EXISTS Contacts WHERE (Contacts.EmailAddress = ThisContacts.EmailAddress AND Contacts.ID <> ThisContacts.ID) Then
ThisContacts.Duplicate = 'Yes'

what I have tried to do is put the contacts into context then run the above.
As a test I have loads of duplicates in the test system but the process above only found 1?

Process 1
DupRecContext
Find Contacts Where Contacts.Duplicate='Yes'
Process 2
DupFlag
IF EXISTS Contacts WHERE (Contacts.EmailAddress = ThisContacts.EmailAddress AND Contacts.ID <> ThisContacts.ID) Then
ThisContacts.Duplicate = 'Yes'

Like I said on the import side of thinks I cannot see for looking where / How to apply rules on imports.

ghilton

ooops sorry my mistake process one reads -

Find ALL Contacts and not what I said in the last post. hey its almost christmas it has been a long year!

😃

tford

Graham,

1) place this rule in the Contacts BO --- not in a process::
IF EXISTS Contacts WHERE (Contacts.EmailAddress = ThisContacts.EmailAddress AND Contacts.ID <> ThisContacts.ID) Then
ThisContacts.Duplicate = 'Yes'

2) look up the term "WITH VALIDATION" in the User Guide & use it in the import

3) execute your process to find all duplicates

ghilton

Tom,

Thanks

1) This is already completed
2) Will do this
3)Will try this too

Thanks and I will let you know the out come.

Cheers Tom

ghilton

Cool this works a treat, how the hell did I miss this trick!

Thanks as always Tom

:lol: