How to avoid duplicate Records in Sobject list during Bulk Insert in After Insert Trigger

Question

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

Answer 1

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.