+ Start a Discussion
Mike SummittMike Summitt 

Detect duplicate emails between lead and contact.

I have the following code in the dev console, but it doesn't compile because it doesn't like my attempts to cast the aggregateresult into something I can evaluate (numerous other formats tried).  Because of what it's doing, I can't use "HAVING" - if an email appears only once in lead and once in contact, I need to know that also.  How can the 2 evaluation statements be changed so that they compile and execute?

Map<String, AggregateResult> LeadsByEmail = new Map<String, AggregateResult>([SELECT Email Id, Count_distinct(Id) Cnt
                                                                              FROM Lead
                                                                              WHERE IsConverted != true
                                                                              GROUP BY Email]);
Map<String, AggregateResult> ContactsByEmail = new Map<String, AggregateResult>([SELECT Email Id, Count_distinct(Id) Cnt
                                                                                 FROM Contact
                                                                                 GROUP BY Email]);
for (AggregateResult arl : LeadsByEmail) {
    if (LeadsByEmail.get(arl.Id) > (AggregateResult)'1') {
        system.debug('Duplicate email - '+ arl.Id);
    }
    if (ContactsByEmail.containsKey(arl.Id)) {
        system.debug('Duplicate email - ' + arl.Id);
    }
}
for (AggregateResult arc : ContactsByEmail) {
    if (ContactsByEmail.get(arc.Id) > (AggregateResult)'1') {
        system.debug('Duplicate email - '+ arc.Id);
    }
    if (LeadsByEmail.containsKey(arc.Id)) {
        system.debug('Duplicate email - ' + arc.Id);
    }
}
Best Answer chosen by Mike Summitt
R Z KhanR Z Khan
AggregateResult[] LeadsByEmail = [SELECT Email,count(Id) cnt
                                                                              FROM Lead
                                                                              GROUP BY Email];
AggregateResult[] ContactsByEmail = [SELECT Email, count(Id) Cnt
                                                                                 FROM Contact
                                                                                 GROUP BY Email];

Set<String> leadEmails = new Set<String>();

for (AggregateResult arl : LeadsByEmail) {
    if ((Integer)arl.get('cnt') > 1) {
        system.debug('Duplicate email - '+ arl);
    }
    leadEmails.add((String)arl.get('Email'));
}
for (AggregateResult arc : ContactsByEmail) {
   if ((Integer)arc.get('cnt') > 1) {
        system.debug('Duplicate email - '+ arc);
    }
    if (leadEmails.contains((String)arc.get('Email'))) {
        system.debug('Duplicate email - ' + arc);
    }
}
Hi Mike,

try the code above. It will tell you if there are duplicates while going through the contacts

Let me knwo if it helps

All Answers

R Z KhanR Z Khan
Hi Mike,

What are you trying to do in this line?
(LeadsByEmail.get(arl.Id) > (AggregateResult)'1')
Mike SummittMike Summitt
The intent was to find out if the email occurs more than once in the lead objects.
R Z KhanR Z Khan
AggregateResult[] LeadsByEmail = [SELECT Email,count(Id) cnt
                                                                              FROM Lead
                                                                              GROUP BY Email];
AggregateResult[] ContactsByEmail = [SELECT Email, count(Id) Cnt
                                                                                 FROM Contact
                                                                                 GROUP BY Email];

Set<String> leadEmails = new Set<String>();

for (AggregateResult arl : LeadsByEmail) {
    if ((Integer)arl.get('cnt') > 1) {
        system.debug('Duplicate email - '+ arl);
    }
    leadEmails.add((String)arl.get('Email'));
}
for (AggregateResult arc : ContactsByEmail) {
   if ((Integer)arc.get('cnt') > 1) {
        system.debug('Duplicate email - '+ arc);
    }
    if (leadEmails.contains((String)arc.get('Email'))) {
        system.debug('Duplicate email - ' + arc);
    }
}
Hi Mike,

try the code above. It will tell you if there are duplicates while going through the contacts

Let me knwo if it helps
This was selected as the best answer
Mike SummittMike Summitt
Thanks!  Simpler than mine too.