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
sudha76sudha76 

Trigger for Duplicate check based on City, and Street.

Hi there,

 

I need to enhance this trigger so it can check duplicates based on the City name, Street Name and Account name.

 

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

trigger AccountCreateTrigger on Account (before insert) {
for (Account acc: Trigger.new)
     {Account [] accs = [SELECT Name FROM Account WHERE Name = :acc.Name LIMIT 1];
      if (accs.size() > 0 ) //if account already exists, block and show the information on the page
         {acc.addError('Account  \'' + accs[0].Name +  '\' already exists. Please use existing account or contact your Salesforce Administrator for assistance.'); }
     }
}

////////////////////////////////////////////

 

 

The above trigger only displays error message if the Account Name entered by User is the same to the Account Name already existed.

 

For example:-

 

a) Accucom - Baulkham Hills - is already existing.

 

b) Accucom Ltd. - Baulkham Hills - this value should not be allowed to save, but the user is able to save this record if the Account Name is slightly different.

 

How can i make sure that if the Site_City Name field is same and the Street Name, along with the Account Name then do not allow to save records.

 

Any recommendations???

 

TheIntegratorTheIntegrator

try this, note that I have bulkified your trigger, its a big no no to put queries inside a loop in Salesforce.

 

trigger AccountCreateTrigger on Account (before insert, before update) {
    List<String> accName = new List<String>();
    List<String> bCity = new List<String>();
    List<String> bStreet = new List<String>();
    List <Account> accs = new List<Account>();

    
    for (Account acc: Trigger.new){
        accName.add(acc.Name);
        bCity.add(acc.BillingCity);
        bStreet.add(acc.BillingStreet);
    }
    accs = [SELECT Name, BillingCity, BillingStreet FROM Account Where (Name IN: accName 
            OR (BillingCity IN : bCity AND BillingStreet IN : bStreet) )
            AND Id NOT IN : trigger.new];
    
    for(Account tacc: Trigger.new){
        for (Account acc: accs)
        {
            if (acc.Name.equals(tacc.Name)  ) //if account already exists, block and show the information on the page
             {tacc.addError('Account  \'' + acc.Name +  '\' already exists. Please use existing account or contact your Salesforce Administrator for assistance.'); }
            else if(acc.BillingCity.equals(tacc.BillingCity) && acc.BillingStreet.equals(tacc.BillingStreet)) 
             {tacc.addError('Account  with the same City & Street already exists. Please use existing account or contact your Salesforce Administrator for assistance.'); }
        }
    }
}

 

This code can be made more efficient by using maps, try and explore it if you could.

 
vishal@forcevishal@force

Hi,

 

yes, the above code will work for you!

 

However as he said, you can still optimize it. I have tried my bit of optimzation. Mainly to remove that for within a for (that can lead to too many scripts in case of a large bulk dml)

 

You can refer the below code too.

 

trigger AccountCreateTrigger on Account (before insert, before update) {
    Set<String> accName = new Set<String>();
    Set<String> bCity = new Set<String>();
    Set<String> bStreet = new Set<String>();

    // for existing records
    Set<String> existingAccName = new Set<String>();
    Set<String> existingCity = new Set<String>();
    Set<String> existingStreet = new Set<String>();
       
    for (Account acc: Trigger.new)
    {
        accName.add(acc.Name);
        bCity.add(acc.BillingCity);
        bStreet.add(acc.BillingStreet);
    }
    
    for(Account acc :[SELECT Name, BillingCity, BillingStreet FROM Account Where (Name IN: accName 
		      OR (BillingCity IN : bCity AND BillingStreet IN : bStreet) )
		      AND Id NOT IN : trigger.new])
    {
	existingAccName.add(acc.Name);
        existingCity.add(acc.BillingCity);
        existingStreet.add(acc.BillingStreet);
    }

    for(Account tacc: Trigger.new)
    {
	if(existingAccName.contains(tacc.Name)) //if account already exists, block and show the information on the page
		tacc.addError('Account  \'' + acc.Name +  '\' already exists. Please use existing account or contact your Salesforce Administrator for assistance.');
	
	else if(existingCity.contains(tacc.BillingCity) && existingStreet.contains(tacc.BillingStreet))
		tacc.addError('Account  with the same City & Street already exists. Please use existing account or contact your Salesforce Administrator for assistance.');	
    }
}

 Let me know if that helps :)
sudha76sudha76

thank you so much.

 

I am trying now and will inform back what really worked.

 

thanks.
sudha76sudha76

I have few questions -

 

a) Why should we NOT put any queries inside the FOR Loop? What really happens? This code has been sitting in our production org for like 3 years now and it was developed by an Admin.

I need to understand what are the effects of doing this? Why are we using the Query in this FOR statement then?

 

for(Account acc :[SELECT Name, BillingCity, BillingStreet FROM Account Where (Name IN: accName 
		      OR (BillingCity IN : bCity AND BillingStreet IN : bStreet) )
		      AND Id NOT IN : trigger.new])

 

b) In the first code the LIST is used ?whats the real difference between using these two operators?

 

c) This set of code, may also prevent SAVING the existing records if they are edited and it already exisits in the system.

 

// for existing records
    Set<String> existingAccName = new Set<String>();
    Set<String> existingCity = new Set<String>();
    Set<String> existingStreet = new Set<String>();

 

 

thanks for your time.

 
TheIntegratorTheIntegrator

a) We should not put query inside a for loop as there is a stringent limit on salesforce of 20 queries per trigger execution, so if you do a bulk upload of account, say 100 records, your original trigger would have failed.

 

the query in this for loop

for(Account acc :[SELECT Name, BillingCity, BillingStreet FROM Account Where (Name IN: accName 
		      OR (BillingCity IN : bCity AND BillingStreet IN : bStreet) )
		      AND Id NOT IN : trigger.new])

 is a more efficient way of doing this

List <Account> accs = new List<Account>();
accs = [SELECT Name, BillingCity, BillingStreet FROM Account Where (Name IN: accName 
            OR (BillingCity IN : bCity AND BillingStreet IN : bStreet) )
            AND Id NOT IN : trigger.new];
for (Account acc: accs){
}

 usually, when there is no updates to be performed in the queried data, you can directly put the query in the for statement

 

b) We use list mostly when records need to be iterated and updated, otherwise we can use set's as in the second code. Using set's or map's makes the code more efficient and reduces the line of code executed.

 

c) Yes, if the existing records need to be saved, you could either use list and then update the list, or use map's to fetch the records that need to be updated and then putting them in a list to later update. Using map's would save looping and hence recduce the lines of code executed.

 

Hope that's clarifies your doubts.