2

Goal: 'Bulkify' the existing Apex trigger used for counting how many dupes a Lead had, and inserting the Contact Id of the Contact record (if more than one, the first in the list) that matched the email address of the Lead.

Here's a snippet of the code I want to bulkify:

for (Lead l : Trigger.new) {
        //Make sure our lead has an email address
        if (l.Email != null) {
            //Find a dupe
            String leadEmail = l.Email;
            List<Lead> dupeLeads = [SELECT Id FROM Lead WHERE Email = :leadEmail];

            //If a dupe is found, update a field
            if (dupeLeads.size() > 0) {
                l.Dupe_Lead_Counter__c = dupeLeads.size();
            } else {
                //0 would designate a true unique
                l.Dupe_Lead_Counter__c = 0;
            }

So, this is clearly violating the mandate of 'No SOQL queries in For() loops.'

Reading the various doucmentation and tutorials, it looks as though the pattern needs to be a List of the sObjects we want to query from (all Leads with email values in this case) and then use mapping to somehow map the lists of dupe Leads (by email) with the master Lead email list.

How do I even group duplicate emails into a List without creating a For() loop/SOQL query in the first place?

This is what I have so far:

Set<String> leadsWithEmails = new Set<String>();
    for (Lead l : Trigger.new) {
        if(l.email != null) {
            leadsWithEmails.add(l.Email);
        }
    }
    //Query for all Leads
    List<Lead> potentialDupeLeads = [SELECT Id,Email FROM Lead
                                    WHERE Email IN :leadsWithEmails];

    //Step 3: Make a map that lets you search for Leads by Email
    Map<String, Lead> emailToLeadMap = new Map<String, Lead>();
    for (Lead l : potentialDupeLeads) {
        emailToLeadMap.put(l.Email, l);
    }
  • 1
    @AdrianLarson testing it thanks! Btw, when you created the Set<String> lead Emails, why is there a z in new Set<z String>(); – GPP Jul 3 '16 at 16:27
  • 1
    Mobile formatting ftw. – Adrian Larson Jul 3 '16 at 17:29
  • @AdrianLarson just clarifying-- is that z intentional? – GPP Jul 3 '16 at 20:48
  • @AdrianLarson good stuff, I am encountering an error: Invalid foreign key relationship this statement is tripping it up: record.Lead.Dupe_Lead_Counter__c = count; – GPP Jul 4 '16 at 3:41
  • 1
    Did the updated answer resolve? – Adrian Larson Jul 12 '16 at 14:25
4

You can use aggregate queries for this.

Set<String> leadEmails = new Set<String>();
for (Lead record : trigger.new)
    leadEmails.add(record.Email);

Map<String, Integer> emailCounts = new Map<String, Integer>();
for (AggregateResult aggregate : [
    SELECT Email, count(Id) records
    FROM Lead WHERE Email IN :leadEmails
    GROUP BY Email
])
    emailCounts.put((String)aggregate.get('Email'), (Integer)aggregate.get('records'));

for (Lead record : trigger.new)
{
    Integer count = emailCounts.containsKey(record.Email) ?
        emailCounts.get(record.Email) : 0;
    record.Dupe_Lead_Counter__c = count;
}
  • thanks! Sorry for belated reply, just to be clear-- you created this code to augment my starting point, or is there original code I have that needs to be stripped? Trying to track with your additions – GPP Jun 13 '16 at 23:24
  • 1
    @GPP This would replace what you have entirely. Note that the counts above do not include the leads from the transaction itself, which you probably also want to count. Not that you are updating those matching records... – Adrian Larson Jun 13 '16 at 23:30
  • thanks for the quick reply (pretty sure that's a world record!) So I would effectively wrap this snippet of code in my Lead Trigger code, correct? 'trigger CountDupeLeads on Lead (before insert, before update) {}' – GPP Jun 13 '16 at 23:33
  • 1
    @GPP Yeah you would wrap it in that. FYI you can in-line format code with ` characters. – Adrian Larson Jun 13 '16 at 23:44
  • 1
    Oh yeah I can add that but just build a set. – Adrian Larson Jun 14 '16 at 0:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.