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
Manju053Manju053 

How to Bulkify the SOQL queries the following trigger

Hello Everyone

I am new to the Apex development, i have Developed a Trigger where a custom field is updated based on the Formula field(Region), i have designed a trigger and its working perfectly, but the thing is i have written 5 seperate SOQL queries, so when i save a record it queries 5 times, How do i bulkify the trigger, so that it should query only once

Any Help will be Appricated

trigger Increment on Lead (before insert,Before Update) {
    if(trigger.isBefore && (trigger.isInsert || trigger.isUpdate || Trigger.IsUnDelete))  {
        List<Lead> leadList = [Select Id,PraticeLWC__Lead_No_Region_IN__c From Lead Where PraticeLWC__Region__c = 'India'];
        For(Lead l : trigger.New ) {
            if(l.PraticeLWC__Region__c == 'India') {
                if(leadList.size() > 0){
                    l.PraticeLWC__Lead_No_Region_IN__c = leadList.size()+0;
                } else {
                    l.PraticeLWC__Lead_No_Region_IN__c = 1;
                }
            }
        }
        List<Lead> leadListt = [Select Id,PraticeLWC__Lead_No_Region_USA__c From Lead Where PraticeLWC__Region__c = 'US'];
        For(Lead m : trigger.New) {
            if(m.PraticeLWC__Region__c == 'US') {
                if(leadListt.size() > 0){
                    m.PraticeLWC__Lead_No_Region_USA__c = leadListt.size()+0;   
                } else {
                    m.PraticeLWC__Lead_No_Region_USA__c = 1;
                }
            }
        } 
        List<Lead> leadListm = [Select Id,PraticeLWC__Lead_No_Region_EU__c From Lead Where PraticeLWC__Region__c = 'EU'];
        For(Lead n : trigger.New) {
            if(n.PraticeLWC__Region__c == 'EU') {
                if(leadListm.size() > 0){
                    n.PraticeLWC__Lead_No_Region_EU__c = leadListm.size()+0;   
                } else {
                    n.PraticeLWC__Lead_No_Region_EU__c = 1;
                }
            }
        } 
        List<Lead> leadListo = [Select Id,PraticeLWC__Lead_No_Region_SEA__c From Lead Where PraticeLWC__Region__c = 'SEA'];
        For(Lead o : trigger.New) {
            if(o.PraticeLWC__Region__c == 'SEA') {
                if(leadListo.size() > 0){
                    o.PraticeLWC__Lead_No_Region_SEA__c = leadListo.size()+0;   
                } else {
                    o.PraticeLWC__Lead_No_Region_SEA__c = 1;
                }
            }
        }
        List<Lead> leadListp = [Select Id,PraticeLWC__Lead_No_Region_ME__c From Lead Where PraticeLWC__Region__c = 'ME'];
        For(Lead p : trigger.New) {
            if(p.PraticeLWC__Region__c == 'ME') {
                if(leadListp.size() > 0){
                    p.PraticeLWC__Lead_No_Region_ME__c = leadListp.size()+0;   
                } else {
                    p.PraticeLWC__Lead_No_Region_ME__c = 1;
                }
            }
        } 
        
    }                   
}
Best Answer chosen by Manju053
Uttpal_ChandraUttpal_Chandra

I forgot to replace variable here is the updated code
trigger Increment on Lead (before insert,Before Update) 
{
    if(trigger.isBefore && (trigger.isInsert || trigger.isUpdate || Trigger.IsUnDelete)) 
	{
        List<Lead> leadList = [Select Id,PraticeLWC__Lead_No_Region_IN__c From Lead Where PraticeLWC__Region__c = Trigger.new[0].PraticeLWC__Region__c];
        For(Lead l : trigger.New ) 
		{
            if(l.PraticeLWC__Region__c == 'India'){
                if(leadList.size() > 0){
                    l.PraticeLWC__Lead_No_Region_IN__c = leadList.size()+0;
                } else {
                    l.PraticeLWC__Lead_No_Region_IN__c = 1;
                }
            }
            else if(l.PraticeLWC__Region__c == 'US') {
                if(leadList.size() > 0){
                    l.PraticeLWC__Lead_No_Region_USA__c = leadList.size()+0;   
                } else {
                    l.PraticeLWC__Lead_No_Region_USA__c = 1;
                }
            }
            else if(l.PraticeLWC__Region__c == 'EU') {
                if(leadList.size() > 0){
                    l.PraticeLWC__Lead_No_Region_EU__c = leadList.size()+0;   
                } else 
				{
                    l.PraticeLWC__Lead_No_Region_EU__c = 1;
                }
            }
            else if(l.PraticeLWC__Region__c == 'SEA') {
                if(leadList.size() > 0){
                    l.PraticeLWC__Lead_No_Region_SEA__c = leadList.size()+0;   
                } else {
                    l.PraticeLWC__Lead_No_Region_SEA__c = 1;
                }
            }
            else if(l.PraticeLWC__Region__c == 'ME') {
                if(leadList.size() > 0){
                    l.PraticeLWC__Lead_No_Region_ME__c = leadList.size()+0;   
                } else {
                    l.PraticeLWC__Lead_No_Region_ME__c = 1;
                }
            }
		}
    }                   
}

 

All Answers

Uttpal_ChandraUttpal_Chandra
Hi Tanvi,

Try this code and let me know if this is working or not.
trigger Increment on Lead (before insert,Before Update) 
{
    if(trigger.isBefore && (trigger.isInsert || trigger.isUpdate || Trigger.IsUnDelete)) 
	{
        List<Lead> leadList = [Select Id,PraticeLWC__Lead_No_Region_IN__c From Lead Where PraticeLWC__Region__c = Trigger.new[0].PraticeLWC__Region__c];
        For(Lead l : trigger.New ) 
		{
            if(l.PraticeLWC__Region__c == 'India'){
                if(leadList.size() > 0){
                    l.PraticeLWC__Lead_No_Region_IN__c = leadList.size()+0;
                } else {
                    l.PraticeLWC__Lead_No_Region_IN__c = 1;
                }
            }
            else if(m.PraticeLWC__Region__c == 'US') {
                if(leadList.size() > 0){
                    m.PraticeLWC__Lead_No_Region_USA__c = leadList.size()+0;   
                } else {
                    m.PraticeLWC__Lead_No_Region_USA__c = 1;
                }
            }
            else if(n.PraticeLWC__Region__c == 'EU') {
                if(leadList.size() > 0){
                    n.PraticeLWC__Lead_No_Region_EU__c = leadList.size()+0;   
                } else 
				{
                    n.PraticeLWC__Lead_No_Region_EU__c = 1;
                }
            }
            else if(o.PraticeLWC__Region__c == 'SEA') {
                if(leadList.size() > 0){
                    o.PraticeLWC__Lead_No_Region_SEA__c = leadList.size()+0;   
                } else {
                    o.PraticeLWC__Lead_No_Region_SEA__c = 1;
                }
            }
            else if(p.PraticeLWC__Region__c == 'ME') {
                if(leadList.size() > 0){
                    p.PraticeLWC__Lead_No_Region_ME__c = leadList.size()+0;   
                } else {
                    p.PraticeLWC__Lead_No_Region_ME__c = 1;
                }
            }
		}
    }                   
}

Regards,
Uttpal Chandra
Manju053Manju053
@Uttpal

I am getting Variable does not exist errors, 

User-added image

 
Uttpal_ChandraUttpal_Chandra

I forgot to replace variable here is the updated code
trigger Increment on Lead (before insert,Before Update) 
{
    if(trigger.isBefore && (trigger.isInsert || trigger.isUpdate || Trigger.IsUnDelete)) 
	{
        List<Lead> leadList = [Select Id,PraticeLWC__Lead_No_Region_IN__c From Lead Where PraticeLWC__Region__c = Trigger.new[0].PraticeLWC__Region__c];
        For(Lead l : trigger.New ) 
		{
            if(l.PraticeLWC__Region__c == 'India'){
                if(leadList.size() > 0){
                    l.PraticeLWC__Lead_No_Region_IN__c = leadList.size()+0;
                } else {
                    l.PraticeLWC__Lead_No_Region_IN__c = 1;
                }
            }
            else if(l.PraticeLWC__Region__c == 'US') {
                if(leadList.size() > 0){
                    l.PraticeLWC__Lead_No_Region_USA__c = leadList.size()+0;   
                } else {
                    l.PraticeLWC__Lead_No_Region_USA__c = 1;
                }
            }
            else if(l.PraticeLWC__Region__c == 'EU') {
                if(leadList.size() > 0){
                    l.PraticeLWC__Lead_No_Region_EU__c = leadList.size()+0;   
                } else 
				{
                    l.PraticeLWC__Lead_No_Region_EU__c = 1;
                }
            }
            else if(l.PraticeLWC__Region__c == 'SEA') {
                if(leadList.size() > 0){
                    l.PraticeLWC__Lead_No_Region_SEA__c = leadList.size()+0;   
                } else {
                    l.PraticeLWC__Lead_No_Region_SEA__c = 1;
                }
            }
            else if(l.PraticeLWC__Region__c == 'ME') {
                if(leadList.size() > 0){
                    l.PraticeLWC__Lead_No_Region_ME__c = leadList.size()+0;   
                } else {
                    l.PraticeLWC__Lead_No_Region_ME__c = 1;
                }
            }
		}
    }                   
}

 
This was selected as the best answer
Manju053Manju053
@Uttpal

sorry for mentioning lately, but PraticeLWC__Region__c is an formula field, 

 

in the 5th line i am getting an error

User-added image

Instead i have changed to 

List<Lead> leadList = [Select Id,PraticeLWC__Lead_No_Region_IN__c,PraticeLWC__Lead_No_Region_USA__c,PraticeLWC__Lead_No_Region_EU__c, PraticeLWC__Region__c,PraticeLWC__Lead_No_Region_SEA__c,PraticeLWC__Lead_No_Region_ME__c From Lead WHERE PraticeLWC__Region__c = 'India' OR PraticeLWC__Region__c = 'US' OR  PraticeLWC__Region__c = 'EU' OR PraticeLWC__Region__c = 'SEA' OR PraticeLWC__Region__c = 'ME'];
 
Manju053Manju053
@Uttpal

So when i update India, it should increment the value in PraticeLWC__Lead_No_Region_IN__c field, when the Formula is Updated Germany the Custom Field PraticeLWC__Lead_No_Region_EU__c

PraticeLWC__Region__c is an formula field



CASE
(Country, "United States", "US",
"Canada", "US",
"USA", "US",
"US", "US",
"Brazil", "EU",
"Guyana", "SA",
"Paraguay", "SA",
"Peru", "SA",
"Uruguay", "SA",
"Venezuela", "SA",
"Germany", "EU",
"Deutschland", "EU",
"United Kingdom", "EU",
"France", "EU",
"Italy", "EU",
"Spain", "EU",
"Ukraine", "EU",
"Poland", "EU",
"Romania", "EU",
"Netherlands", "EU",
"Belgium", "EU",
"Greece", "EU",
"Czech Republic", "EU",
"Portugal", "EU",
"Sweden", "EU",
"Hungary", "EU",
"Belarus", "EU",
"Serbia", "EU",
"Austria", "EU",
"Switzerland", "EU",
"Bulgaria", "EU",
"Denmark", "EU",
"Finland", "EU",
"Slovakia", "EU",
"Norway", "EU",
"Ireland", "EU",
"Russia", "EU",
"Albania", "EU",
"Georgia", "EU",
"Latvia", "EU",
"Lithuania", "EU",
"Malta", "EU",
"Croatia", "EU",
"Polska", "EU",
"San Marino", "EU",
"Bosnia and Herzegovina", "EU",
"Sweden", "EU",
"Bahrain", "ME",
"Cyprus", "ME",
"Egypt", "ME",
"Iran", "ME",
"Iraq", "ME",
"Israel", "ME",
"Jordan", "ME",
"Kuwait", "ME",
"Lebanon", "ME",
"Oman", "ME",
"Qatar", "ME",
"Saudi Arabia", "ME",
"Turkey", "ME",
"United Arab Emirates", "ME",
"UAE", "ME",
"Yemen", "ME",
"India", "India",
"Indonesia", "SEA2",
"Malaysia", "SEA2",
"Philippines", "SEA1",
"Singapore", "SEA1",
"Thailand", "SEA1",
"Vietnam", "SEA1",
"Taiwan", "SEA1",
"Hong Kong", "SEA1",
"Cambodia", "SEA1",
"China", "SEA1",
"Japan", "SEA1",
"Australia", "SEA1",
"ROW")

 
Uttpal_ChandraUttpal_Chandra
Hi Tanvi,

You just need to add  colon(:)  which i missed. Kindly update query like this

List<Lead> leadList = [Select Id,PraticeLWC__Lead_No_Region_IN__c From Lead Where PraticeLWC__Region__c = :   Trigger.new[0].PraticeLWC__Region__c];
Manju053Manju053
@Uttpal

Awsome its working!! thanks Uttpal