0

I am getting an error while performing data loader operations on the Leads object as I get this error:

CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY:LeadTrigger: System.LimitException: Too many SOQL queries: 101

This is being caused by the Leads Trigger present in our system and here is the code as follows. Please let me know on how to solve this issue as I do not have any idea on apex programming.

Lead Trigger

 trigger LeadTrigger on Lead (
  before insert, after insert, 
  before update, after update, 
  before delete, after delete) {

  if (Trigger.isBefore) {
    if (Trigger.isInsert) {
      // Call class logic here!            
        LeadDuplicateManagementSystem.checkFullLeadDuplicates(trigger.new);
        LeadDuplicateManagementSystem.possibleLeadMatch(trigger.new);
    } 
    if (Trigger.isUpdate) {
      // Call class logic here!
    }
    if (Trigger.isDelete) {
      // Call class logic here!
    }
  }

  if (Trigger.IsAfter) {
    if (Trigger.isInsert) {
      // Call class logic here!
    } 
    if (Trigger.isUpdate) {
      // Call class logic here!
    }
    if (Trigger.isDelete) {
      // Call class logic here!
    }
  }
}

Apex class: LeadDuplicateManagementSystem

    public class LeadDuplicateManagementSystem
{
    /*
    Check full duplicates
    */   
    public static void checkFullLeadDuplicates(list<Lead> newLeads)
    {
        for (Lead newLead : newLeads)
        {
            List<Lead> existLeads = [SELECT Id
                                      FROM Lead
                                      WHERE Email = :newLead.Email
                                          AND Phone = :newLead.Phone
                                          AND FirstNameLastNameCompany__c = :newLead.FirstNameLastNameCompany__c
                                          AND Time_In_Business__c = :newLead.Time_In_Business__c
                                          AND Monthly_Gross_Sales__c = :newLead.Monthly_Gross_Sales__c
                                          AND Business_Type_Industry__c = :newLead.Business_Type_Industry__c
                                          AND Amount_Requested__c = :newLead.Amount_Requested__c
                                          AND Loan_Purpose_Targeted__c = :newLead.Loan_Purpose_Targeted__c
                                          AND Id != :newLead.Id
                                  limit 1];

            if (!existLeads.isEmpty())
            {
                newLead.addError('A lead with this parameters already exists.');
            }
        }
    }
    /*
    MATCHING CRITERIA:
    #1 - Email EXACT MATCH
    BypassLeadDuplicateManagementSystem__c
    */
    public static void possibleLeadMatch(list<Lead> newLeads)
    {
        //leadEmails is to create the collection of Emails to query against.
        Set<String> leadIds = new Set<String>();
        Set<String> leadEmails = new Set<String>();

        //loop through Lead(s) in trigger and determine if a match was found.       
        for (Lead l : newLeads)
        {
            System.debug('**Lead Added --- Email: ' + l.Email);
            leadIds.add(l.Id);
            System.debug('**Found Id.  Added to Collection leadIds');
            if (l.Email != NULL)
            {
                leadEmails.add(l.Email);
                System.debug('**Found Email.  Added to Collection leadEmails');
            }
        }

        //Find Leads that match Lead(s) passed into Method
        List<Lead> duplicateLeadMatch = [SELECT Id, FirstNameLastNameCompany__c, Email, Phone, Status, StatusforDuplicateNotAllowed__c
                                          FROM Lead
                                          WHERE Email IN: leadEmails
                                                  AND Id NOT IN: leadIds];
        System.debug('**duplicateLeadMatch Size:  ' + duplicateLeadMatch.size());

        //Find Duplicate Management Queue
        string duplicateManagementQueueId =  [SELECT Id
                                                FROM Group
                                                WHERE DeveloperName = 'Duplicate_Management' limit 1].Id;
        System.debug('**duplicateManagementQueueId :  ' + duplicateManagementQueueId);

        //Running User's ID
        String currentUserId = UserInfo.getUserId();
        Boolean bypassLeadDuplicateManagementSystem =  [SELECT Id, BypassLeadDuplicateManagementSystem__c
                                                        FROM User
                                                        WHERE Id = :currentUserId
                                                        limit 1].BypassLeadDuplicateManagementSystem__c;
        System.debug('**bypassLeadDuplicateManagementSystem :  ' + bypassLeadDuplicateManagementSystem);

        //Create Map of Leads that are Allowed
        Map<String, Lead> duplicateAllowed = new Map<String, Lead>();
        //Create Map of Leads that are Not Allowed
        Map<String, Lead> duplicateNotAllowed = new Map<String, Lead>();

        for (Lead l : duplicateLeadMatch)
        {
            //Add Lead to Allowed Map
            if (l.StatusforDuplicateNotAllowed__c == FALSE)
            {
                duplicateAllowed.put(l.Email, l);
                System.debug('**Add Lead to Allowed Map');
            }

            //Add Lead to Not Allowed Map
            else if (l.StatusforDuplicateNotAllowed__c == TRUE)
            {
                duplicateNotAllowed.put(l.Email, l);
                System.debug('**Add Lead to Not Allowed Map');
            }
        }

        //Find Match(s) & Update Record(s)
        for (Lead newLead : newLeads)
        {
            System.debug('**Entering Matching and Updating');
            if (newLead.Email != null)
            {
                Lead newLeadDuplicateNotAllowedEmail = duplicateNotAllowed.get(newLead.Email);
                Lead newLeadDuplicateAllowedEmail = duplicateAllowed.get(newLead.Email);
                System.debug('**newLeadDuplicateNotAllowedEmail:  ' + newLeadDuplicateNotAllowedEmail);
                System.debug('**newLeadDuplicateAllowedEmail:  ' + newLeadDuplicateAllowedEmail);

                //First check to see if Duplicate is Not Allowed
                if (newLeadDuplicateNotAllowedEmail != null)
                {
                    //Before Not Allowing the Duplicate, check to see if has bypass permission
                    if (bypassLeadDuplicateManagementSystem == TRUE)
                    {
                        newLead.DuplicateAllowed__c = TRUE;
                        System.debug('**MATCH Allowed via bypassLeadDuplicateManagementSystem');
                    }
                    else
                    {
                        System.debug('**MATCH Not Allowed');
                        newLead.OwnerId = duplicateManagementQueueId;
                    }
                }

                //If No Not Allowed match, check to see if Duplicate is Allowed
                if (newLeadDuplicateAllowedEmail != null)
                {
                    newLead.DuplicateAllowed__c = TRUE;
                    System.debug('**MATCH Allowed');
                }
            }
        }
    }
}

Apex Test Class:LeadDuplicateManagementSystemTest

 @isTest 
public class LeadDuplicateManagementSystemTest {
    static testMethod void testDuplicateManagementSystem() {
    String currentUserId = UserInfo.getUserId();

        Boolean bypassLeadDuplicateManagementSystem =  [SELECT Id, BypassLeadDuplicateManagementSystem__c
                                                       FROM User 
                                                       WHERE Id = :currentUserId 
                                                       limit 1].BypassLeadDuplicateManagementSystem__c;                             

        //Create Lead that we will test Allowed Matches with
        Lead newLeadAllowed       = new Lead();
        newLeadAllowed.FirstName  = 'Justin';
        newLeadAllowed.LastName   = 'Grimm';
        newLeadAllowed.Company    = 'Chicago Cubs';
        newLeadAllowed.Email      = 'justin.grimm@cubs.com';
        newLeadAllowed.Phone      = '678-678-9876';
        newLeadAllowed.Status     = 'Unqualified';
        insert newLeadAllowed;

        //New Lead Allowed - Match is on Email
        Lead duplicateLeadAllowedEmail      = new Lead();
        duplicateLeadAllowedEmail.FirstName = 'Anthony';
        duplicateLeadAllowedEmail.LastName  = 'Rizzo';
        duplicateLeadAllowedEmail.Company   = 'Chicago Cubs';
        duplicateLeadAllowedEmail.Email     = 'justin.grimm@cubs.com';
        duplicateLeadAllowedEmail.Phone     = '777-970-5678';
        duplicateLeadAllowedEmail.Status    = 'New';
        insert duplicateLeadAllowedEmail;       

        //New Lead Allowed - Match is on Phone
        Lead duplicateLeadAllowedPhone      = new Lead();
        duplicateLeadAllowedPhone.FirstName = 'Javier';
        duplicateLeadAllowedPhone.LastName  = 'Biaz';
        duplicateLeadAllowedPhone.Company   = 'Chicago Cubs';
        duplicateLeadAllowedPhone.Email     = 'javier.biaz@cubs.com';
        duplicateLeadAllowedPhone.Phone     = '678-678-9876';
        duplicateLeadAllowedPhone.Status    = 'New';
        insert duplicateLeadAllowedPhone;    

        //New Lead Allowed - Match is on First Name, Last Name, Company
        Lead duplicateLeadAllowedFLC      = new Lead();
        duplicateLeadAllowedFLC.FirstName = 'Justin';
        duplicateLeadAllowedFLC.LastName  = 'Grimm';
        duplicateLeadAllowedFLC.Company   = 'Chicago Cubs';
        duplicateLeadAllowedFLC.Email     = 'willson.contreras@cubs.com';
        duplicateLeadAllowedFLC.Phone     = '712-933-5333';
        duplicateLeadAllowedFLC.Status    = 'New';
        insert duplicateLeadAllowedFLC;    


        //Create Lead that we will test Not Allowed Matches with
        Lead newLeadNotAllowed       = new Lead();
        newLeadNotAllowed.FirstName  = 'Kris';
        newLeadNotAllowed.LastName   = 'Bryant';
        newLeadNotAllowed.Company    = 'Chicago Cubs';
        newLeadNotAllowed.Email      = 'kris.bryant@cubs.com';
        newLeadNotAllowed.Phone      = '678-234-5678';
        newLeadNotAllowed.Status     = 'New';
        insert newLeadNotAllowed;

        //New Lead Not Allowed - Match is on Email
        Lead duplicateLeadNotAllowedEmail      = new Lead();
        duplicateLeadNotAllowedEmail.FirstName = 'Ben';
        duplicateLeadNotAllowedEmail.LastName  = 'Zobrist';
        duplicateLeadNotAllowedEmail.Company   = 'Chicago Cubs';
        duplicateLeadNotAllowedEmail.Email     = 'kris.bryant@cubs.com';
        duplicateLeadNotAllowedEmail.Phone     = '711-922-5444';
        duplicateLeadNotAllowedEmail.Status    = 'New';
        insert duplicateLeadNotAllowedEmail;       

        //New Lead Not Allowed - Match is on Phone
        Lead duplicateLeadNotAllowedPhone      = new Lead();
        duplicateLeadNotAllowedPhone.FirstName = 'Jason';
        duplicateLeadNotAllowedPhone.LastName  = 'Heyward';
        duplicateLeadNotAllowedPhone.Company   = 'Chicago Cubs';
        duplicateLeadNotAllowedPhone.Email     = 'jason.heyward@cubs.com';
        duplicateLeadNotAllowedPhone.Phone     = '678-234-5678';
        duplicateLeadNotAllowedPhone.Status    = 'New';
        insert duplicateLeadNotAllowedPhone;    

        //New Lead Not Allowed - Match is on First Name, Last Name, Company
        Lead duplicateLeadNotAllowedFLC       = new Lead();
        duplicateLeadNotAllowedFLC.FirstName  = 'Kris';
        duplicateLeadNotAllowedFLC.LastName   = 'Bryant';
        duplicateLeadNotAllowedFLC.Company    = 'Chicago Cubs';
        duplicateLeadNotAllowedFLC.Email      = 'kyle.schwarber@cubs.com';
        duplicateLeadNotAllowedFLC.Phone      = '770-970-5678';
        duplicateLeadNotAllowedFLC.Status     = 'New';
        insert duplicateLeadNotAllowedFLC;    


        //Query the records created and the QueueID for Asserts

        string duplicateManagementQueueId               = [SELECT Id 
                                                          FROM Group 
                                                          WHERE DeveloperName = 'Duplicate_Management' limit 1].Id;

        Lead newLeadAllowedQuery                        = [SELECT Id, Email, FirstName, LastName, Status, Phone, OwnerId, DuplicateAllowed__c
                                                          FROM Lead 
                                                          WHERE Id = :newLeadNotAllowed.Id
                                                          LIMIT 1];

        Lead duplicateLeadAllowedEmailQuery             = [SELECT Id, Email, FirstName, LastName, Status, Phone, OwnerId, DuplicateAllowed__c
                                                          FROM Lead 
                                                          WHERE Id = :duplicateLeadAllowedEmail.Id
                                                          LIMIT 1];

        Lead duplicateLeadAllowedPhoneQuery             = [SELECT Id, Email, FirstName, LastName, Status, Phone, OwnerId, DuplicateAllowed__c
                                                          FROM Lead 
                                                          WHERE Id = :duplicateLeadAllowedPhone.Id
                                                          LIMIT 1];

        Lead duplicateLeadAllowedFLCQuery               = [SELECT Id, Email, FirstName, LastName, Status, Phone, OwnerId, DuplicateAllowed__c
                                                          FROM Lead 
                                                          WHERE Id = :duplicateLeadAllowedFLC.Id
                                                          LIMIT 1];

        Lead newLeadNotAllowedQuery                     = [SELECT Id, Email, FirstName, LastName, Status, Phone, OwnerId, DuplicateAllowed__c
                                                          FROM Lead 
                                                          WHERE Id = :newLeadAllowed.Id
                                                          LIMIT 1];

        Lead duplicateLeadNotAllowedEmailQuery          = [SELECT Id, Email, FirstName, LastName, Status, Phone, OwnerId, DuplicateAllowed__c
                                                          FROM Lead 
                                                          WHERE Id = :duplicateLeadNotAllowedEmail.Id
                                                          LIMIT 1];

        Lead duplicateLeadNotAllowedPhoneQuery          = [SELECT Id, Email, FirstName, LastName, Status, Phone, OwnerId, DuplicateAllowed__c
                                                          FROM Lead 
                                                          WHERE Id = :duplicateLeadNotAllowedPhone.Id
                                                          LIMIT 1];

        Lead duplicateLeadNotAllowedFLCQuery            = [SELECT Id, Email, FirstName, LastName, Status, Phone, OwnerId, DuplicateAllowed__c
                                                          FROM Lead 
                                                          WHERE Id = :duplicateLeadNotAllowedFLC.Id
                                                          LIMIT 1];

        //Validate that DuplicateAllowed is FALSE on First Lead Inserted
        System.assertEquals(FALSE, newLeadAllowedQuery.DuplicateAllowed__c);
        //Validate that OwnerId is not Duplicate Management Queue
        System.assertNotEquals(newLeadAllowedQuery.OwnerId, duplicateManagementQueueId);

        //Validate that DuplicateAllowed is TRUE on Allowed (Email) Lead Inserted
        System.assertEquals(TRUE, duplicateLeadAllowedEmailQuery.DuplicateAllowed__c);
        //Validate that OwnerId is Queue
        System.assertNotEquals(duplicateLeadAllowedEmailQuery.OwnerId, duplicateManagementQueueId);

        //Validate that DuplicateAllowed is FALSE on Allowed (Phone) Lead Inserted
        System.assertEquals(FALSE, duplicateLeadAllowedPhoneQuery.DuplicateAllowed__c);
        //Validate that OwnerId is Queue
        System.assertNotEquals(duplicateLeadAllowedPhoneQuery.OwnerId, duplicateManagementQueueId);

        //Validate that DuplicateAllowed is FALSE on Allowed (First, Last, Company) Lead Inserted
        System.assertEquals(FALSE, duplicateLeadAllowedFLCQuery.DuplicateAllowed__c);
        //Validate that OwnerId is Queue
        System.assertNotEquals(duplicateLeadAllowedFLCQuery.OwnerId, duplicateManagementQueueId);

        //If has Bypass, test that Bypass working correctly
        if(bypassLeadDuplicateManagementSystem == TRUE) {

        //Validate that DuplicateAllowed is FALSE on Lead Inserted
        System.assertEquals(FALSE, newLeadNotAllowedQuery.DuplicateAllowed__c);
        //Validate that OwnerId is CreatedById/RunningUser
        System.assertNotEquals(newLeadNotAllowedQuery.OwnerId, duplicateManagementQueueId);

        //Validate that DuplicateAllowed is FALSE on Not Allowed (Email) Lead Inserted
        System.assertEquals(TRUE, duplicateLeadNotAllowedEmailQuery.DuplicateAllowed__c);
        //Validate that OwnerId is not Duplicate Management Queue
        System.assertNotEquals(duplicateLeadNotAllowedEmailQuery.OwnerId, duplicateManagementQueueId);

        //Validate that DuplicateAllowed is FALSE on Not Allowed (Phone) Lead Inserted
        System.assertEquals(TRUE, duplicateLeadNotAllowedPhoneQuery.DuplicateAllowed__c);
        //Validate that OwnerId is not Duplicate Management Queue
        System.assertNotEquals(duplicateLeadNotAllowedPhoneQuery.OwnerId, duplicateManagementQueueId);

        //Validate that DuplicateAllowed is FALSE on Not Allowed (First, Last, Company) Lead Inserted
        System.assertEquals(TRUE, duplicateLeadNotAllowedFLCQuery.DuplicateAllowed__c);
        //Validate that OwnerId is not Duplicate Management Queue
        System.assertNotEquals(duplicateLeadNotAllowedFLCQuery.OwnerId, duplicateManagementQueueId);
        }

        //If no Bypass, test that rejection is working correctly.
        else {
        //Validate that DuplicateAllowed is FALSE on Lead Inserted
        System.assertEquals(FALSE, newLeadNotAllowedQuery.DuplicateAllowed__c);
        //Validate that OwnerId is CreatedById/RunningUser
        System.assertNotEquals(newLeadNotAllowedQuery.OwnerId, duplicateManagementQueueId);

        //Validate that DuplicateAllowed is FALSE on Not Allowed (Email) Lead Inserted
        System.assertEquals(FALSE, duplicateLeadNotAllowedEmailQuery.DuplicateAllowed__c);
        //Validate that OwnerId is not Duplicate Management Queue
        System.assertEquals(duplicateLeadNotAllowedEmailQuery.OwnerId, duplicateManagementQueueId);

        //Validate that DuplicateAllowed is FALSE on Not Allowed (Phone) Lead Inserted
        System.assertEquals(FALSE, duplicateLeadNotAllowedPhoneQuery.DuplicateAllowed__c);
        //Validate that OwnerId is in Duplicate Management Queue
        System.assertNotEquals(duplicateLeadNotAllowedPhoneQuery.OwnerId, duplicateManagementQueueId);

        //Validate that DuplicateAllowed is FALSE on Not Allowed (First, Last, Company) Lead Inserted
        System.assertEquals(FALSE, duplicateLeadNotAllowedFLCQuery.DuplicateAllowed__c);
        //Validate that OwnerId is in Duplicate Management Queue
        System.assertNotEquals(duplicateLeadNotAllowedFLCQuery.OwnerId, duplicateManagementQueueId);
        }       
    }

}
  • The community here is not likely to help you fix this trigger wholesale. However, one quick and dirty fix is to lower the batch size on your data loader tool (perhaps as far as 1 record per transaction if necessary). – Adrian Larson Jan 2 at 16:56
4

checkFullLeadDuplicates has a query inside a loop. The easiest way to fix this is to change the code to an aggregate-query-update pattern. In your case, it looks like this:

Set<String> phones = new Set<String>();
Set<String> emails = new Set<String>();
// one set per criteria ...
Set<Lead> dupes = new Set<Lead>(); // existing records
for(Lead record: newLeads) {
  phones.add(record.Phone);
  emails.add(record.Email);
  // also rest of the fields
}
for(Lead record: [select phone, email, ...
                  from lead
                  where email = :emails and phones = :phones and ...]) {
  dupes.add(new Lead(Phone=record.Phone, Email=record.Email, ...));
}
for(Lead record: newLeads) {
  Lead key = new Lead(Phone=record.Phone, Email=record.Email, ...);
  if(dupes.contains(key)) {
    record.addError('A lead with this parameters already exists.');
  }
}

Note that you need to "fill in the blanks"; your code is going to be closer to about 50 lines worth of work. There are similar questions like this here on SFSE that have decent answers, so you might also want to do a search to see similar results.


There are ways to do this through configuration, so you might also investigate Duplicate Management as an alternative to writing a complicated trigger; it would be far easier to maintain and would not have the problems the existing trigger has.

  • Thank you for the answer and I will look around here for similar posts. – Srivatsa Jan 2 at 17:25
4

This trigger is guaranteed to fail for any batch size greater than or equal to 100, for this reason right here:

    for (Lead newLead : newLeads)
    {
        List<Lead> existLeads = [SELECT Id ... FROM Lead ...];

This class runs one SOQL query per Lead.

Then LeadDuplicateManagementSystem.possibleLeadMatch() runs at least 3 more total queries. If you have other code running on Lead trigger events, even more SOQL may be consumed.

The 100-SOQL limit is transaction-wide, meaning that you will be able to run an absolute maximum of 97 Leads through in a given batch, and possibly fewer. This cannot be fixed without completely re-engineering this poorly-designed duplicate management system. It's tricky to handle duplicate management well within platform limits within a trigger. Your use case does appear fairly amenable to using Salesforce's native duplicate management functionality, but I don't think fixing your use case is something we can fully resolve in the scope of a StackExchange answer.

As Adrian mentioned in a comment, your only easy fix is to dramatically reduce your batch size. I would try at 60 or 70 and see if that works, reducing it further from there if you continue to encounter failures.

  • 1
    Might also recomment OOTB Duplicate Management. – Adrian Larson Jan 2 at 17:03
  • Thank you and I will try the suggested workaround of reducing the data loader batch size and trying, although it will take me quite some time as I have 26,000 records to upload. – Srivatsa Jan 2 at 17:23

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.