function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
JamuraiJamurai 

Moving SOQL query outside For statement for Lead Convert Trigger into existing Account

This trigger I'm writing works well for individual records, but I know it's not going to work for bulk uploads/updates because I put 3 SOQL Queries inside the For Loop (o.0)

 

Can someone please advise me how to do this without putting the queries inside the loop? 

 

I'm trying to convert leads into existing Accounts (if there is a corresponding Account to conver them into)

 

trigger myLeadConvertTrigger on Lead(after insert, after update) {
	//Create an empty list of Leads to convert
    Database.LeadConvert[] leadsToConvert = new Database.LeadConvert[]{};
    //Find a Lead Status for Leads converted - note that this will take 1 value and only the first one that it finds
    LeadStatus convertStatus = [SELECT Id, MasterLabel FROM LeadStatus WHERE IsConverted=true LIMIT 1];
    
    //For Lead being updated or inserted
    for(Lead l : Trigger.new) {
        
        // if statement logic: Lead is Lead Source 'Sign Up' and was Invited and isn't converted yet
        if(l.LeadSource == 'Sign Up' && l.Was_Invited__c=='TRUE' && !l.isConverted) {
			
            //Create a list of Accounts where the Lead Company spelling matches the Account Name
    		List<Account> accountList = [SELECT Id, Name FROM Account WHERE Name = :l.Company LIMIT 1];//Need to figure out if there's a way to take this SOQL out of the for loop
             
            //Query AccountID via Invited to app 
            List<Apps__c> accountIdViaApp = [SELECT Account_Temp__c FROM Apps__c WHERE CritterAppID__c =:l.Invited_to_app__c LIMIT 1];
            
            //Query Account ID via Added by
            List<Contact> accountIdViaAddedBy = [SELECT AccountId FROM Contact WHERE CritterUserID__c =:l.Added_by__c LIMIT 1];
            
            //Create a new Lead Convert Record
            Database.LeadConvert lc = new database.LeadConvert();
            lc.setLeadId(l.id);
            lc.setConvertedStatus(convertStatus.MasterLabel); //set it to the status we retrieved
            lc.setDoNotCreateOpportunity(True); //do not create an opp
            
            //if accountIdViaApp is not empty
            if(!accountIdViaApp.isempty()){lc.setAccountId(accountIdViaApp[0].Account_Temp__c);}
                
            //else if accountIdViaAddedBy is not empty
            else if(!accountIdViaAddedBy.isempty()){lc.setAccountId(accountIdViaAddedBy[0].AccountId);}
            
            //else if accountList is not empty, set the Account Id
            else if(!accountList.isempty()){lc.setAccountId(accountList[0].Id);}
                        
            //add lc to the leadsToConvert list
            leadsToConvert.add(lc);
        }
    }
	//if the leadsToConvert list is not empty
    if(!leadsToConvert.isEmpty()) {
        //convert those Leads
        Database.convertLead(leadsToConvert );
    }
}
 

 

 

Best Answer chosen by Admin (Salesforce Developers) 
JamuraiJamurai

JayNic, THANK YOU SO MUCH!!!

 

I made a few minor adjustments (namely added the LeadConvert to the LeadsToConvert list).  This gives me the direction I needed in order to figure out the other 2 ways of  converting the lead into the correct existing Account.

 

Here's the updated version:

 

trigger autoConvertLeads on Lead (after insert, after update) {
    //Create an empty list of Leads to convert
    Database.LeadConvert[] leadsToConvert = new Database.LeadConvert[]{};
    //Find a Lead Status for Leads converted - note that this will take 1 value and only the first one that it finds
    LeadStatus convertStatus = [SELECT Id, MasterLabel FROM LeadStatus WHERE IsConverted=true LIMIT 1];
    
    //Create a map of lead ids, to account names
    set<string> accountNames = new set<string>();

    list<Lead> leads = new list<lead>();
    //For Lead being updated or inserted
    for(Lead l : Trigger.new) {
        
        // if statement logic: Lead is Lead Source 'Sign Up' and was Invited and isn't converted yet
        if(l.LeadSource == 'Sign Up' && l.Was_Invited__c=='TRUE' && !l.isConverted) {
            leads.add(l);

            //Add the various identifiers to their maps to check against later
            accountNames.add(l.Company);
            //Create two more sets, and add the criteria you are checking against. It may be prudent to check for null values first! We know company is a required fields, but you will get strange results if you add 'null' to the sets!
        }
    }
    
    //If we have leads to convert:
    if(!leads.isEmpty()) {
        //Query for the various lists we need - Note that calling .values() on a map returns a list of all values in the whole map
        Account[] accounts = [SELECT Id, Name FROM Account WHERE Name IN :accountNames];
        
        //And for each of those lists, create a corresponding map to check against
        map<string, account> accountNames_map = new map<string,account>();
        for(account a : accounts) {
            accountNames_map.put(a.name,a);
        }
        
        
        //And for each lead, find if it has a match
        for(Lead l : leads) {
            //Create a new Lead Convert Record
            Database.LeadConvert lc = new database.LeadConvert();
            lc.setLeadId(l.id);
            lc.setConvertedStatus(convertStatus.MasterLabel); //set it to the status we retrieved
            lc.setDoNotCreateOpportunity(True); //do not create an opp
            
            if(accountNames_map.get(l.company) != null) { //Hey we have that company already!!!
                lc.setAccountId(accountNames_map.get(l.company).Id);
            }
            //add lc to the leadsToConvert list
            leadsToConvert.add(lc);
        }
    }

    //if the leadsToConvert list is not empty
    if(!leadsToConvert.isEmpty()) {
        //convert those Leads
        Database.convertLead(leadsToConvert);
    }
}

 

All Answers

JayNicJayNic

I've done it for one check, not all three, but you will see how it works.

 

You're on the right track, but you need to seperate out your processes:

1) Get all accounts from all leads matching your criteria ( "f(l.LeadSource == 'Sign Up' && l.Was_Invited__c=='TRUE' && !l.isConverted) " )

2) Create a map of all of those accounts by name so you can do a quick map.get(companyName) to check if it's null

3) Only then create your lead

 

You're trying to do all this at once

 

 

So here if my sample - not that I didn't test it, jsut wrote it out:

trigger myLeadConvertTrigger on Lead(after insert, after update) {
	//Create an empty list of Leads to convert
    Database.LeadConvert[] leadsToConvert = new Database.LeadConvert[]{};
    //Find a Lead Status for Leads converted - note that this will take 1 value and only the first one that it finds
    LeadStatus convertStatus = [SELECT Id, MasterLabel FROM LeadStatus WHERE IsConverted=true LIMIT 1];
    
	//Create a map of lead ids, to account names
	set<string> accountNames = new set<string>();

	list<Lead> leads = new list<lead>();
    //For Lead being updated or inserted
    for(Lead l : Trigger.new) {
        
        // if statement logic: Lead is Lead Source 'Sign Up' and was Invited and isn't converted yet
        if(l.LeadSource == 'Sign Up' && l.Was_Invited__c=='TRUE' && !l.isConverted) {
			leads.add(lead);

			//Add the various identifiers to their maps to check against later
			accountNames.add(l.Company);
			//Create two more sets, and add the criteria you are checking against. It may be prudent to check for null values first! We know company is a required fields, but you will get strange results if you add 'null' to the sets!
		}
	}
	
	//If we have leads to convert:
	if(!leads.isEmpty()) {
		//Query for the various lists we need - Note that calling .values() on a map returns a list of all values in the whole map
		Account[] accounts = [SELECT Id, Name FROM Account WHERE Name IN :accountNames];
		
		//And for each of those lists, create a corresponding map to check against
		map<string, account> accountNames_map = new map<string,account>();
		for(account a : accounts) {
			accountNames_map.put(a.name,a);
		}
		
		
		//And for each lead, find if it has a match
		for(Lead l : leads) {
			//Create a new Lead Convert Record
            Database.LeadConvert lc = new database.LeadConvert();
            lc.setLeadId(l.id);
            lc.setConvertedStatus(convertStatus.MasterLabel); //set it to the status we retrieved
            lc.setDoNotCreateOpportunity(True); //do not create an opp
			
			if(accountNames_map.get(l.company) != null) { //Hey we have that company already!!!
				lc.setAccountId(accountNames_map.get(l.company));
			}
		}
	}

	//if the leadsToConvert list is not empty
    if(!leadsToConvert.isEmpty()) {
        //convert those Leads
        Database.convertLead(leadsToConvert );
    }
}

 

So you really just need two more maps for each of those other queries

JayNicJayNic
Just thinking again.. Make SURE you do a null check before populating your sets: eg

if(l.Added_by__c != null && l.Added_by__c != '') {
critterUserIds.add(l.Added_by__c);
}
JayNicJayNic

One more thing... You might want to investigate the governor hits that you incurr when using the lead convert operation. I believe this operation is not batchable... But this is still a step in the right direction

JamuraiJamurai

JayNic, THANK YOU SO MUCH!!!

 

I made a few minor adjustments (namely added the LeadConvert to the LeadsToConvert list).  This gives me the direction I needed in order to figure out the other 2 ways of  converting the lead into the correct existing Account.

 

Here's the updated version:

 

trigger autoConvertLeads on Lead (after insert, after update) {
    //Create an empty list of Leads to convert
    Database.LeadConvert[] leadsToConvert = new Database.LeadConvert[]{};
    //Find a Lead Status for Leads converted - note that this will take 1 value and only the first one that it finds
    LeadStatus convertStatus = [SELECT Id, MasterLabel FROM LeadStatus WHERE IsConverted=true LIMIT 1];
    
    //Create a map of lead ids, to account names
    set<string> accountNames = new set<string>();

    list<Lead> leads = new list<lead>();
    //For Lead being updated or inserted
    for(Lead l : Trigger.new) {
        
        // if statement logic: Lead is Lead Source 'Sign Up' and was Invited and isn't converted yet
        if(l.LeadSource == 'Sign Up' && l.Was_Invited__c=='TRUE' && !l.isConverted) {
            leads.add(l);

            //Add the various identifiers to their maps to check against later
            accountNames.add(l.Company);
            //Create two more sets, and add the criteria you are checking against. It may be prudent to check for null values first! We know company is a required fields, but you will get strange results if you add 'null' to the sets!
        }
    }
    
    //If we have leads to convert:
    if(!leads.isEmpty()) {
        //Query for the various lists we need - Note that calling .values() on a map returns a list of all values in the whole map
        Account[] accounts = [SELECT Id, Name FROM Account WHERE Name IN :accountNames];
        
        //And for each of those lists, create a corresponding map to check against
        map<string, account> accountNames_map = new map<string,account>();
        for(account a : accounts) {
            accountNames_map.put(a.name,a);
        }
        
        
        //And for each lead, find if it has a match
        for(Lead l : leads) {
            //Create a new Lead Convert Record
            Database.LeadConvert lc = new database.LeadConvert();
            lc.setLeadId(l.id);
            lc.setConvertedStatus(convertStatus.MasterLabel); //set it to the status we retrieved
            lc.setDoNotCreateOpportunity(True); //do not create an opp
            
            if(accountNames_map.get(l.company) != null) { //Hey we have that company already!!!
                lc.setAccountId(accountNames_map.get(l.company).Id);
            }
            //add lc to the leadsToConvert list
            leadsToConvert.add(lc);
        }
    }

    //if the leadsToConvert list is not empty
    if(!leadsToConvert.isEmpty()) {
        //convert those Leads
        Database.convertLead(leadsToConvert);
    }
}

 

This was selected as the best answer