+ Start a Discussion
Vetriselvan ManoharanVetriselvan Manoharan 

Avoid duplicates in list

Hi,

My trigger clones an opportunity when it is set to closed won or when the account program status is set to on Program. It works fine, but my problem is in data loader bulk update,  I am adding the values in list and inserting outside the loop. what it does is adding all opportunities to all the account all the accounts. All the opportunities in the list is inserted in all account for update made. How to associate an opportunity to corresponding account. Below is my code:

Opportunity[] oppsToClone = new Opportunity[]{};
List<Opportunity> opptsToInsert = new List<Opportunity>();
List<Opportunity> oppMap = new List<Opportunity>();
Set<Opportunity> oppSet = new Set<Opportunity>();  

for (Opportunity acc:record) 
        {
    Validatios takes place;
     And valid records added to 
    oppsToClone.add(acc);
    if (oppsToClone.size() > 0 )
                        { 
                            for (Opportunity record1:oppsToClone)
                            {
                                Opportunity theClone = record1.clone(false,false); 
                  Values are assigned
                  and inserted in list
                    opptsToInsert.add(theClone); 
             }
         }
        }
if(opptsToInsert.size() > 0) 
{
    insert opptsToInsert;
}
Best Answer chosen by Vetriselvan Manoharan
Swayam  AroraSwayam Arora
Replace the above queries and try again

AggregateResult[] shippingCounts = [SELECT Count(Id) c, OpportunityId FROM OpportunityLineItem WHERE OpportunityId in :ids and (pricebookEntry.product2.ProductCode = 'FedEx') GROUP BY OpportunityId];
AggregateResult[] discountAmounts = [SELECT Count(Id) c, OpportunityId FROM OpportunityLineItem WHERE OpportunityId in :ids and (pricebookEntry.product2.ProductCode = 'DiscountAmount') GROUP BY OpportunityId];

All Answers

Swayam  AroraSwayam Arora
What your code is doing is for each oppsToClone it is running the inner loop again and again creating unnecessary Cloned Opportunities. Extract the "inner if and for" from the "outer for" loop.

Opportunity[] oppsToClone = new Opportunity[]{};
List<Opportunity> opptsToInsert = new List<Opportunity>();
List<Opportunity> oppMap = new List<Opportunity>();
Set<Opportunity> oppSet = new Set<Opportunity>();  

for (Opportunity acc:record) 
{
    Validatios takes place;
     And valid records added to 
    oppsToClone.add(acc);
}
if (oppsToClone.size() > 0 )

     for (Opportunity record1:oppsToClone)
     {
          Opportunity theClone = record1.clone(false,false); 
         
          Values are assigned and inserted in list
         
          opptsToInsert.add(theClone); 
     }
 }
       
if(opptsToInsert.size() > 0) 
{
    insert opptsToInsert;
}
Vetriselvan ManoharanVetriselvan Manoharan
Hi Swayam,

Thanks a lot that worked. I have some other issues in bulkifying the code. Will get to you back if needed.

Regards,
Vetri
Swayam  AroraSwayam Arora
Sure Vetri, Please mark the answer as Best Answer if the Answer really helped you.
Vetriselvan ManoharanVetriselvan Manoharan
Hi Swayam,

Below is the code
            insert opptsToInsert;
            for(Opportunity opps : opptsToInsert)
            {
                allAccIds.add(opps.AccountId);
            }

            OpportunityLineItem newOpptLineItem = new OpportunityLineItem(); 
            List<Opportunity> opps = [Select Id, Account.Program_Type__c, SubTotal__c from Opportunity where Id in :opptsToInsert];
            for(Opportunity opptInserted : opps) 
            { 
                PricebookEntry pbe1 = [SELECT Id,UnitPrice FROM PricebookEntry WHERE Name = :opptInserted.Account.Program_Type__c and PRICEBOOK2ID='01sd0000000YUpOAAW' and IsActive = true];   
                newOpptLineItem.OpportunityId = opptInserted.Id; 
                newOpptLineItem.Quantity = 1; 
                newOpptLineItem.UnitPrice = opptInserted.SubTotal__c;
                newOpptLineItem.PricebookEntryId = pbe1.Id;
                oppLineMap.add(newOpptLineItem);  
            }
            oppLineSet.addAll(oppLineMap);
            opptLineItemsToInsert.addAll(oppLineSet); 
            insert(opptLineItemsToInsert); 

Based on the Program Type in Account, I need to add line Item. But it is inserting line item for first opportunity cloned but not for the next record. Can you please help?? All I want to move the Select statement outside the loop. Is that possible? Thanks in advance
Swayam  AroraSwayam Arora
Hi Vetri,

Below is the required code. I couldn't test the code so let me know if there is any syntax error.
One suggestion, don't use hardcoded id for Pricebook2Id as Ids change from one instance to another, so this code will break if you migrate it to some other org.

insert opptsToInsert;
            for(Opportunity opps : opptsToInsert)
            {
                allAccIds.add(opps.AccountId);
            }

            OpportunityLineItem newOpptLineItem;
            List<Opportunity> opps = [Select Id, Account.Program_Type__c, SubTotal__c from Opportunity where Id in :opptsToInsert];
            Set<String> programTypes = new Set<String>();
            for(Opportunity opp : opps) {
                programTypes.add(opp.Account.Program_Type__c);
            }
            List<PricebookEntry> listPbe = [SELECT Id,UnitPrice FROM PricebookEntry WHERE Name in :programTypes and PRICEBOOK2ID='01sd0000000YUpOAAW' and IsActive = true];
            for(Opportunity opptInserted : opps)
            {
                for(PricebookEntry pbe : listPbe) {
                    if(opptInserted.Account.Program_Type__c == pbe.Name) {
                        newOpptLineItem = new OpportunityLineItem();
                        newOpptLineItem.OpportunityId = opptInserted.Id;
                        newOpptLineItem.Quantity = 1;
                        newOpptLineItem.UnitPrice = opptInserted.SubTotal__c;
                        newOpptLineItem.PricebookEntryId = pbe1.Id;
                        //oppLineMap.add(newOpptLineItem);
                        opptLineItemsToInsert.add(newOpptLineItem);
                    }
                }
                 
            }
            //oppLineSet.addAll(oppLineMap);
            //opptLineItemsToInsert.addAll(oppLineSet);
            insert opptLineItemsToInsert;
Vetriselvan ManoharanVetriselvan Manoharan
Hi Swayam,

Thanks a lot. you are awesome. I am new to APEX development and learned a lot.

Regards,
Vetri
Swayam  AroraSwayam Arora
Happy to know that Vetri.
Vetriselvan ManoharanVetriselvan Manoharan
Hi Swayam,

I have almost bulkfied all my code. Here is some scenario. Below is the code

Opportunity[] oppsToClone = new Opportunity[]{};
List<Opportunity> opptsToInsert = new List<Opportunity>();
List<Opportunity> oppMap = new List<Opportunity>();
Set<Opportunity> oppSet = new Set<Opportunity>();  
Set<ID> ids = opportunityNewMap.keySet();
for (Opportunity acc:record) 
        {
     Integer ShippingCount = [SELECT Count() FROM OpportunityLineItem WHERE OpportunityId = :ids and (pricebookEntry.product2.ProductCode = 'FedEx')];
     Integer DiscountCount = [SELECT Count() FROM OpportunityLineItem WHERE OpportunityId = :ids and (pricebookEntry.product2.ProductCode = 'DiscountAmount')];
    Validatios takes place;
     And valid records added to 
    oppsToClone.add(acc);
    if (oppsToClone.size() > 0 )
                        { 
                            for (Opportunity record1:oppsToClone)
                            {
                                Opportunity theClone = record1.clone(false,false); 
                  Values are assigned
                  and inserted in list
                    opptsToInsert.add(theClone); 
             }
         }
        }
if(opptsToInsert.size() > 0) 
{
    insert opptsToInsert;
}

Nothing new, you can see couple integer field selected inside the for loops. I need this for validation purpose. I want to move these soql outside for loop and check validation inside for loops. Can you see it?

Thanks in advance
Swayam  AroraSwayam Arora

Opportunity[] oppsToClone = new Opportunity[]{};
List<Opportunity> opptsToInsert = new List<Opportunity>();
List<Opportunity> oppMap = new List<Opportunity>();
Set<Opportunity> oppSet = new Set<Opportunity>();  
Set<ID> ids = opportunityNewMap.keySet();


AggregateResult[] shippingCounts = [SELECT Count(OpportunityId) c, OpportunityId FROM OpportunityLineItem WHERE OpportunityId in :ids and (pricebookEntry.product2.ProductCode = 'FedEx') GROUP BY OpportunityId];
AggregateResult[] discountAmounts = [SELECT Count(OpportunityId) c, OpportunityId FROM OpportunityLineItem WHERE OpportunityId in :ids and (pricebookEntry.product2.ProductCode = 'DiscountAmount') GROUP BY OpportunityId];

for (Opportunity acc:record) 
        {
         Integer ShippingCount = 0, DiscountAmount = 0;
         for(AggregateResult ar : shippingCounts) {
                     if(ar.get('OpportunityId') == acc.Id){
                                ShippingCount = ar.get('c');
                     }
         }
         for(AggregateResult ar : discountAmounts) {
                     if(ar.get('OpportunityId') == acc.Id){
                                DiscountAmount = ar.get('c');
                     }
         }
    
    Validatios takes place;
     And valid records added to 
    oppsToClone.add(acc);
    if (oppsToClone.size() > 0 )
                        { 
                            for (Opportunity record1:oppsToClone)
                            {
                                Opportunity theClone = record1.clone(false,false); 
                  Values are assigned
                  and inserted in list
                    opptsToInsert.add(theClone); 
             }
         }
        }
if(opptsToInsert.size() > 0) 
{
    insert opptsToInsert;
}

Please check the syntax of the Aggregate SOQL Queries, I am not sure about them.
Vetriselvan ManoharanVetriselvan Manoharan
Hi Swayam,

I am getting following error:

"Field must be grouped or aggregated: OpportunityId". So can't able to select opportunityId

Thanks,
Vetri
 
Swayam  AroraSwayam Arora
Replace the above queries and try again

AggregateResult[] shippingCounts = [SELECT Count(Id) c, OpportunityId FROM OpportunityLineItem WHERE OpportunityId in :ids and (pricebookEntry.product2.ProductCode = 'FedEx') GROUP BY OpportunityId];
AggregateResult[] discountAmounts = [SELECT Count(Id) c, OpportunityId FROM OpportunityLineItem WHERE OpportunityId in :ids and (pricebookEntry.product2.ProductCode = 'DiscountAmount') GROUP BY OpportunityId];
This was selected as the best answer
Vetriselvan ManoharanVetriselvan Manoharan
Hi Swayam,

Thanks it worked. Here are some of my doubts.

While updating data via data laoder and it will trigger the operation. If the 3rd record enters in custom validation condition, the update fails and the all the records displaying same validation message. Please clear it.

Thanks,
Vetriselvan M
Swayam  AroraSwayam Arora
Hi Vetri,

Please raise different Questions for different doubts so that it help others as well and solves the purpose of the forum. Create a new question and share with me the link here.

Regards,
Swayam Arora
Swayam  AroraSwayam Arora
Vetri, please share code as well.
Vetriselvan ManoharanVetriselvan Manoharan

Hi Swayam,

Below is the code:

Opportunity[] oppsToClone = new Opportunity[]{};
List<Opportunity> opptsToInsert = new List<Opportunity>();
List<Opportunity> oppMap = new List<Opportunity>();
Set<Opportunity> oppSet = new Set<Opportunity>();  

for (Opportunity acc:record) 
{
    Validatios takes place;
    if(acc.Auth_Payment_Status__c != opportunityOldMap.get(acc.Id).Auth_Payment_Status__c)
    {    
          if((acc.Account.Program_Status__c == 'On Hold' || acc.Account.Program_Status__c == 'Finished') && (acc.Auth_Payment_Status__c == 'Authorized' || acc.Auth_Payment_Status__c == 'Captured'))
         {
              acc.addError(' Opportunity cannot be set to Authorized/Captured since the Account Program Status is On Hold/Finished');
         }
     }
     if((discounttype == datespecified || discounttype == lifetime) &&  couponCode == null)
       {
             acc.addError(' Please provide coupon code for the discount type specified!');
        } 
     And valid records added to 
    oppsToClone.add(acc);
}
if (oppsToClone.size() > 0 )

     for (Opportunity record1:oppsToClone)
     {
          Opportunity theClone = record1.clone(false,false); 
         
          Values are assigned and inserted in list
         
          opptsToInsert.add(theClone); 
     }
 }
       
if(opptsToInsert.size() > 0) 
{
    insert opptsToInsert;
}

If any data in bulk update goes through custom validation, all the updates fails and same validation message displayed to all records. It is saying as "SObject doesn't allows errors". Please help on this.

Thanks,
Vetri
Vetriselvan ManoharanVetriselvan Manoharan
The update via data loader in made in account object and it affects the opportunity trigger which contains custom validation. Is it a problem?