1

I have a after insert trigger on Account which inserts another object (Places) after checking Account Shipping fields with address fields of existing Places records. If nothing is found it inserts new Places records.

When I insert Accounts using Dataloader with few duplicates in Excel sheet, even though I have no duplicates existing (Database) in my Places object, it inserts duplicate Places due to Account duplicates in Excel sheet.

trigger InsertUniquePlaces on Account (after insert,after update) {

List<places__c> Places= new List<places__c>();
Map<Id,Account> Accounts = new Map<Id,account>();
Map<String, Id> recordTypeMap = new Map <String,Id>();


List<String> street = new List<String>();
List<String> city = new List<String>();
List<String> state = new List<String>();
List<String> zipCode = new List<String>();
List<String> country = new List<String>();
set<Id> accountIds = new set<Id>(); 


// After Insert
if(trigger.IsInsert&&trigger.IsAfter)
{

    accounts = Trigger.newMap;

    for(Account ac : accounts.Values())
    {


                street.add(ac.ShippingStreet);
                city.add(ac.ShippingCity);
                state.add(ac.ShippingState);
                zipCode.add(ac.ShippingPostalCode);
                country.add(ac.ShippingCountry);


    }

    List<Places__c> ExistingPlaces = new List<places__c>();

    ExistingPlaces=[SELECT ID,Name,Street__c,City__c,State__c,
    Zip__c,Country__c FROM Places__c WHERE Street__c IN : street and 
    City__c IN : city and  State__c IN : state and Zip__c IN : zipCode and Country__c IN : country limit 50000];

   if(ExistingPlaces.size()==0)
   {
    // If nothing Matched, Direct Insert a Loacation Record for accounts in Trigger.new
    for(Account acc : accounts.Values())
        {
                Places__c location = new Places__c(
                                    name = acc.Name,
                                    Street__c = acc.ShippingStreet,
                                    City__c = acc.ShippingCity,
                                    State__c = acc.ShippingState,
                                    Zip__c = acc.ShippingPostalCode,
                                    Country__c = acc.ShippingCountry,
                                    Location_Account__c=acc.id;

                                    );

            Places.add(location); 

        }


    }


 if(locations.size()>0 )
    {
            Database.Insert(Places);
            //Upsert(Places);
    }

Is there any way to avoid duplicate records with repeated shipping address (no more name comparsion) wise at below stage before hitting database

if(locations.size()>0 )
    {
            Database.Insert(Places);
            //Upsert(Places);
    }

I dont want to Insert duplicate places even though Inserted Accounts has Duplicates. Please any one suggest me. Thanks in advance

4

There are a couple of things I'd suggest you do.

First, I'd use sets for your collections below. There's no reason I can see to use lists:

List<String> street = new List<String>();
List<String> city = new List<String>();
List<String> state = new List<String>();
List<String> zipCode = new List<String>();
List<String> country = new List<String>();

You've mentioned that you know you have duplicate Accounts. You might want to consider looking at Account.Name to see if that helps you find those.

This part of your code seems fine:

   if(ExistingPlaces.size()==0)
   {
    // If nothing Matched, Direct Insert a Loacation Record for accounts in Trigger.new
    for(Account acc : accounts.Values()) 

It seems to me, that upserting values is likely what created your problem in the past to begin with on duplicate accounts based on what you've conveyed. What you have below, isn't even an upsert and would create more duplicate Places, so isn't what you want to do.

if(locations.size()>0 )
    {
            Database.Insert(Places);
            //Upsert(Places);
    }

Instead, I think you need to identify the match and remove it from the contents of your list to insert created from the Accounts in trigger.new.

if(locations.size()>0 )
    {
    list<Places__c>NwPlaces = new list<Places__c>();

    for(Account acc : accounts.Values())
        {
        for(Places__C ep : ExistingPlaces){

            if(!(ep.Street__c = acc.ShippingStreet &&
                 ep.Street__c = acc.ShippingStreet &&
                 ep.City__c = acc.ShippingCity &&
                 ep.State__c = acc.ShippingState &&
                 ep.Zip__c = acc.ShippingPostalCode &&
                 ep.Country__c = acc.ShippingCountry 
            )){
                Places__c location = new Places__c(
                                name = acc.Name,
                                Street__c = acc.ShippingStreet,
                                City__c = acc.ShippingCity,
                                State__c = acc.ShippingState,
                                Zip__c = acc.ShippingPostalCode,
                                Country__c = acc.ShippingCountry,
                                Location_Account__c=acc.id;

                                );

            NwPlaces.add(location); 

            }//end if

        }//end for(Places__C ep

    }//end for(Account acc

    Database.Insert(NwPlaces); 

}// end if(locations.size()>0

Here I've simply created a new list for insertion as opposed to removing them from your original list. Notice that I also didn't try to add criteria for checking matches on Account.Name or AccountId, but that would be an option you could explore at some time if you chose to.

| improve this answer | |
  • Were you also asking how to prevent duplicate accounts from being inserted into your database? Your question didn't seem to convey that. Are you using an external Id for your accounts? If they're unique, duplicate accounts will be inserted. If you map Name from your spreadsheet to Account.Name and the spelling is identical, only one account should be inserted with the 2nd one being reported as an error. Any trigger to find duplicates should be a BeforeInsert that also checks contents of trigger.new against itself if you're not de-duping it before inserting. – crmprogdev Aug 17 '15 at 13:06
  • Hello sir, Thanks for your help on this, Please suggest me any better snippet. Actually I need to check The Places Records in Data base(Existing records) and Current SpreadSheet data of Accounts(No Problem If Duplicates Records Inserted into Account) But ' Places ' Records (which are nothing but Shipping Address of Accounts) Should be Uinique. If already Places record Exists(Comparing shipping fields of Account) no need of Insert Again. hope you under stand my intension:) I am still stucked – SFDC_Igle Aug 17 '15 at 15:16
  • I just gave you the code to prevent inserting duplicate places records in my answer above. Did you not understand my answer? – crmprogdev Aug 17 '15 at 15:22
  • Simple enough. Notice the if condition. Using the && it requires the street, city, zip, state and country to match in order for the record to be considered a duplicate. The solution I've provided addresses your concern. A new acct address is only added to the list for insertion if ALL of those items are != to a record that's returned from your query. – crmprogdev Aug 17 '15 at 15:56
  • How so? The zip doesn't match so it won't show up as a duplicate. Read the code. – crmprogdev Aug 17 '15 at 16:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.