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;
    }
}    
}
}

Hello,

I need to create sharing rules for opportunities using the account country as a criterion. I tried doing that by simply creating a new field and using formula to populate the field with the account country, but sharing rules can't use fields with formulas as criteria.

A possible alternative is to populate my custom text field and populate it with an Apex trigger. I have created a trigger which does exactly what I need and now I need to deploy it. Since I have limited experience in Apex, I need some help to create the test required for this field.

Thanks for your help!

trigger AccountCountry on Opportunity (before insert, before update){
Set<Id> ownerIds = new Set<Id>();
Set<Id> AccountIds = new Set<Id>();
for (Opportunity op : Trigger.new) {  
ownerIds.add(op.OwnerId);  
accountIds.add(op.AccountId);
}

Map<Id, Account> accountMap = new Map<Id, Account>([SELECT BillingCountry FROM Account WHERE Id IN :accountIds]);
String AccountCountryName;
for (Opportunity op : Trigger.new) { 

// add the BillingCountry 
AccountCountryName = accountMap.get(op.AccountId).BillingCountry; 

// set the name :-) 

op.Account_Country__c = AccountCountryName;}     

static testMethod void testAccountCountry() {

??

}

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;
    }
}    
}
}