function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Pedro ArgentiPedro Argenti 

Help to bulkify Apex code looking up country/state names in a custom setting

Hi,

I wrote an Apex trigger that looks up country, state and region values inserted in Leads on a custom setting containing a canonical list and replaces the entered values with the canonical values.

I am aware that when you match using field "name" of an entry in your custom setting, you don't need SOQL. The problem is that certain country names are too long to fit in the character limit. That obliges the use of SOQL. I need help to bulkify this one as I never bulkified a trigger before.

Thanks in advance!

Here is my trigger:

trigger ProperCountryStateRegionLead on lead (before insert, before update) {
for(lead lead : Trigger.new){

// checks if country field is empty
if(lead.Country != null){

// creates a variable with the country value inserted by the user
Set<String> inputcountry = new Set<String>();

// populates the variable with a lower case version of the country value inserted by the user
inputcountry.add(lead.Country.toLowerCase());

// queries via SOQL a custom settings list containing all variations of country names and returns the good data integrity key
List<CountryDataMatch__c> goodcountry =[SELECT DataIntegrityKey__c FROM CountryDataMatch__c WHERE Alternative__c IN: inputcountry];

// checks if the list goodcountry is empty, if it is it's because the inserted value is not among the country name variations 
if (goodcountry.isEmpty() == false){

// looks for the good country name in another custom settings list using the data integrity key
CountryDataIntegrity__c valuecountry = CountryDataIntegrity__c.getValues(goodcountry[0].DataIntegrityKey__c);

// checks if the good country name value has been found
if(valuecountry != null){

// replaces the inserted country value with the good country name
lead.Country = valuecountry.Value__c;

// inserts ISO 3166-1 alpha2 country code in country code custom field
lead.Country_Code__c = goodcountry[0].DataIntegrityKey__c;

}
}
}
}

for(lead lead : Trigger.new){

// checks if state field is empty
if(lead.state != null){

// creates a variable with the state value inserted by the user
Set<String> inputstate = new Set<String>();

// populates the variable with a lower case version of the state value inserted by the user
inputstate.add(lead.state.toLowerCase());

// queries via SOQL a custom settings list containing all variations of state names and returns the good data integrity key
List<StateDataMatch__c> goodstate =[SELECT DataIntegrityKey__c FROM StateDataMatch__c WHERE Alternative__c IN: inputstate AND Country_Code__c =: lead.Country_Code__c];

// checks if the list goodstate is empty, if it is it's because the inserted value is not among the state name variations 
if (goodstate.isEmpty() == false){

// creates a variable with the good data integrity key
string goodstatestring = goodstate[0].DataIntegrityKey__c;

// queries for the good state name via SOQL a custom settings list containing all data integrity keys matching with the respective country
List<StateDataIntegrity__c> valuestate = [SELECT Value__c FROM StateDataIntegrity__c WHERE Name =: goodstatestring]; 

// checks if valuestate list is empty
if (valuestate.isEmpty() == false){

// creates a variable with the good state name
string valuestatestring = valuestate[0].Value__c;

// checks if the good country name value has been found
if(valuestate != null){

// replaces the inserted country value with the good country name
lead.state = valuestatestring;

}
}
}
}
}
for(lead lead : Trigger.new){

//maps custom settings list containing the commercial regions
map<String, CountryRegion__c> regions = CountryRegion__c.getAll();

// sets variable with country name
 string countryforregion = lead.Country_Code__c;

// checks if country field is empty
    if(countryforregion == null){
    
// if country field empty, set commercial region field to null    
    lead.Commercial_Region__c = null;
    }
    
// if country field not empty, try to retrieve region on custom settings list   
    else if(countryforregion != null) {    
    CountryRegion__c matchcountryregion = regions.get(countryforregion);

// if region retrieved, set region to commercial region field
    if(matchcountryregion != null){
    lead.Commercial_Region__c = CountryRegion__c.getInstance(lead.Country_Code__c).Commercial_Region__c;
    }
    
// if failed, set commercial region field to null    
    else {lead.Commercial_Region__c = null;
    }
}    
}
    for(lead lead : Trigger.new){

//maps custom settings list containing the commercial regions
map<String, CountryRegion__c> regions = CountryRegion__c.getAll();

// sets variable with country name
 string countryforregion = lead.Country_Code__c;

// checks if country field is empty
    if(countryforregion == null){
    
// if country field empty, set commercial region field to null    
    lead.Geo_Region__c = null;
    }
    
// if country field not empty, try to retrieve region on custom settings list   
    else if(countryforregion != null) {    
    CountryRegion__c matchcountryregion = regions.get(countryforregion);

// if region retrieved, set region to commercial region field
    if(matchcountryregion != null){
    lead.Geo_Region__c = CountryRegion__c.getInstance(lead.Country_Code__c).Geo_Region__c;
    }
    
// if failed, set commercial region field to null    
    else {lead.Geo_Region__c = null;
    }
}    
}
     for(lead lead : Trigger.new){

//maps custom settings list containing the commercial regions
map<String, CountryRegion__c> regions = CountryRegion__c.getAll();

// sets variable with country name
 string countryforregion = lead.Country_Code__c;

// checks if country field is empty
    if(countryforregion == null){
    
// if country field empty, set commercial region field to null    
    lead.Geo_Subregion__c = null;
    }
    
// if country field not empty, try to retrieve region on custom settings list   
    else if(countryforregion != null) {    
    CountryRegion__c matchcountryregion = regions.get(countryforregion);

// if region retrieved, set region to commercial region field
    if(matchcountryregion != null){
    lead.Geo_Subregion__c = CountryRegion__c.getInstance(lead.Country_Code__c).Geo_Subregion__c;
    }
    
// if failed, set commercial region field to null    
    else {lead.Geo_Subregion__c = null;
    }
}    
}
}

Amit_Amit_
Hi Pedro, please find the below code. this will help you to understand how to bulky trigger :

trigger ProperCountryStateRegionLead on lead (before insert, before update) {

// creates a variable with the country value inserted by the user
Set<String> inputcountry = new Set<String>();
//maps custom settings list containing the commercial regions
map<String, CountryRegion__c> regions = CountryRegion__c.getAll();
for(lead lead : Trigger.new){

// checks if country field is empty
if(lead.Country != null){

  // populates the variable with a lower case version of the country value inserted by the user
  inputcountry.add(lead.Country.toLowerCase());

}
}
// queries via SOQL a custom settings list containing all variations of country names and returns the good data integrity key
List<CountryDataMatch__c> goodcountry =[SELECT DataIntegrityKey__c FROM CountryDataMatch__c WHERE Alternative__c IN: inputcountry];


// checks if the list goodcountry is empty, if it is it's because the inserted value is not among the country name variations
if (goodcountry.isEmpty() == false){

// looks for the good country name in another custom settings list using the data integrity key
CountryDataIntegrity__c valuecountry = CountryDataIntegrity__c.getValues(goodcountry[0].DataIntegrityKey__c);

  // checks if the good country name value has been found
  if(valuecountry != null){

   // replaces the inserted country value with the good country name
   lead.Country = valuecountry.Value__c;

   // inserts ISO 3166-1 alpha2 country code in country code custom field
   lead.Country_Code__c = goodcountry[0].DataIntegrityKey__c;

  }
}

you can reffer this code and try to use only one forloop of (Lead lead : trigger) I can see you are using this loop thrice in your code try to add all you loggic with the same loop using if-else condition, you can keep it like that also but it take more time to excute and might hit saleforce execution ltime limit in case of bulk operation.

the basic idead of bulkyfing trigger is to get your required info like Id,name...etc in set or list or map and bring SOQL out of for loop.
Pedro ArgentiPedro Argenti
Hi Amit!

Thanks for your input! Now I got it working.

Here's the result:

trigger CountryProperLeadBulk on Lead (before insert, before update) {

   
List<CountryDataMatch__c> countrydatamatch = CountryDataMatch__c.getAll().values();
Map<string,string> countrydatamatchmap = new map<string,string>();

    for(CountryDataMatch__c countryentry:countrydatamatch){
        countrydatamatchmap.put(countryentry.alternative__c,countryentry.dataintegritykey__c);
    }
    
List<CountryDataIntegrity__c> countrydataintegrity = CountryDataIntegrity__c.getAll().values();
Map<string,string> countrydataintegritymap = new map<string,string>();
    
    for(CountryDataIntegrity__c countryentry:countrydataintegrity){
        countrydataintegritymap.put(countryentry.name,countryentry.value__c);
    }
    
Set<String> countrycode = new Set<String>();
    
    for(Lead Lead:trigger.new){
        if(countrydatamatchmap.get(Lead.country.toLowerCase()) != null){
            Lead.country = countrydataintegritymap.get(countrydatamatchmap.get(Lead.country.toLowerCase()));
            Lead.country_code__c = countrydatamatchmap.get(Lead.country.toLowerCase());
            countrycode.add(countrydatamatchmap.get(Lead.country.toLowerCase()));
        }
    }
    
List<StateDataMatch__c> statedatamatch = [SELECT alternative__c, dataintegritykey__c FROM StateDataMatch__c WHERE country_code__c=:countrycode];
Map<string,string> statedatamatchmap = new map<string,string>();

    for(StateDataMatch__c countryentry:statedatamatch){
        statedatamatchmap.put(countryentry.alternative__c,countryentry.dataintegritykey__c);
    }
    
List<StateDataIntegrity__c> statedataintegrity = StateDataIntegrity__c.getAll().values();
Map<string,string> statedataintegritymap = new map<string,string>(); 
    
	 for(StateDataIntegrity__c countryentry:statedataintegrity){
        statedataintegritymap.put(countryentry.name,countryentry.value__c);
    }
    
map<String, CountryRegion__c> regions = CountryRegion__c.getAll();
    
    
    for(Lead Lead:trigger.new){
        if(statedatamatchmap.get(Lead.state.toLowerCase()) != null){
            Lead.state = statedataintegritymap.get(statedatamatchmap.get(Lead.state.toLowerCase()));
        }
        if(regions.get(Lead.country_code__c) !=null){
            Lead.Commercial_Region__c = CountryRegion__c.getInstance(lead.Country_Code__c).Commercial_Region__c;
            Lead.Geo_Region__c = CountryRegion__c.getInstance(lead.Country_Code__c).Geo_Region__c;
            Lead.Geo_Subregion__c = CountryRegion__c.getInstance(lead.Country_Code__c).Geo_Subregion__c;
        }

    }
    
}