Opportunity Renewal Process

Hello developers of the Salesforce World!

I need some help (probably more than I realize). I am trying to create a trigger that will carry over products to a custom object when an opportunity is closed won. I have this part working. However, those products need to be updated (renewed) with a similar purchase (same account, new opp, but same products). This part I cannot get to work. I keep getting List has more than 1 row for assignment as an error and I do not know how to resolve it. Any assistance would be amazing! Thanks!

trigger OpportunityWon2 on Opportunity (after update, after insert) {
  for (Opportunity o : Trigger.New) {
        if (o.isWon != (Trigger.oldMap != null ? Trigger.oldMap.get(o.Id).isWon : false) && o.isWon)
        {
      
      boolean okee = false;
      if (Trigger.isUpdate) {    
        // Check if there already is a SPA, if true then skip
        Integer renewcnt = [SELECT count() FROM Renewal_Summary__c WHERE Account__c = :o.AccountId];
        if (renewcnt == 0)
          okee = true;

            List<OpportunityLineItem> prods = [SELECT id, PricebookEntryId,Product_Family__c, TotalPrice, Discount, UnitPrice, Quantity, name FROM OpportunityLineItem WHERE OpportunityId = :o.id AND Product_Family__c=:'Maintenance Contracts' ];
            List<OpportunityLineItem> prods2 = [SELECT id, PricebookEntryId,Product_Family__c, TotalPrice, Discount, UnitPrice, Quantity, name FROM OpportunityLineItem WHERE OpportunityId = :o.id AND Product_Family__c!='Maintenance Contracts' ];
            
            for(OpportunityLineItem prod : prods )
            {
            
            Id pId = [SELECT id, Product2Id FROM PriceBookEntry WHERE id = :prod.PricebookEntryId][0].Product2Id;
            Product2 p = [SELECT id, name, Family, Description, Is_Asset__c, Maintenance_Cycle__c, End_Of_Life_Date__c FROM Product2 WHERE id = :pId];
            
            if (Trigger.isInsert || (Trigger.isUpdate && okee)) {  
           
            Renewal_Summary__c rs = new Renewal_Summary__c(Account__c = o.AccountId); 
           
            rs.Reseller__c = o.Reseller__r.Id;
            rs.Type__c = 'Maintenance Contracts';
            rs.Status__c = 'Active';
            rs.Product__c=pId;           
            insert rs;
            
            for(OpportunityLineItem addprod : prods2 )
            {
            
            Id pId2 = [SELECT id, Product2Id,UnitPrice FROM PriceBookEntry WHERE id = :addprod.PricebookEntryId][0].Product2Id;
            Product2 p2 = [SELECT id, name, Family, Description, Is_Asset__c, Maintenance_Cycle__c, End_Of_Life_Date__c FROM Product2 WHERE id = :pId2];
            
           
            
           Renewal_Line_Item__c RLI = new Renewal_Line_Item__c(Renewal_Summary__c=rs.Id);
           RLI.Opportunity__c=o.Id;
           RLI.Price__c=addprod.UnitPrice;
           RLI.Quantity__c=addprod.Quantity;
           RLI.Renewal_Date__c=o.CloseDate+365;
           RLI.Purchase_Date__c=o.CloseDate;
           RLI.Product__c=pID2;
           RLI.Total_Price__c=addprod.TotalPrice;
           
           insert RLI;
           }
           }
        
        Else{
        
        List<OpportunityLineItem> prodsold = [SELECT id, PricebookEntryId,Product_Family__c, TotalPrice, Discount, UnitPrice, Quantity, name FROM OpportunityLineItem WHERE OpportunityId = :o.id AND Product_Family__c=:'Maintenance Contracts' ];
            List<OpportunityLineItem> prods2old = [SELECT id, PricebookEntryId,Product_Family__c, TotalPrice, Discount, UnitPrice, Quantity, name FROM OpportunityLineItem WHERE OpportunityId = :o.id AND Product_Family__c!='Maintenance Contracts' ];
            
            for(OpportunityLineItem prod2old : prodsold )
            {
            
           // Id pId = [SELECT id, Product2Id FROM PriceBookEntry WHERE id = :prodold.PricebookEntryId][0].Product2Id;
            //Product2 p = [SELECT id, name, Family, Description, Is_Asset__c, Maintenance_Cycle__c, End_Of_Life_Date__c FROM Product2 WHERE id = :pId];
            
            Id pIdold = [SELECT id, Product2Id,UnitPrice FROM PriceBookEntry WHERE id = :prod2old.PricebookEntryId][0].Product2Id;
            Product2 p2old = [SELECT id, name, Family, Description, Is_Asset__c, Maintenance_Cycle__c, End_Of_Life_Date__c FROM Product2 WHERE id = :pIdold];
        
            if (Trigger.isInsert || (Trigger.isUpdate)) { 
        
            
        
           Renewal_Summary__c rsold= [select Id,Name,Product__c,Account__c from Renewal_Summary__c where Account__c=:o.AccountId];
           Renewal_Line_Item__c RLIold =[select Id,Product__c,Opportunity__c,Price__c,Quantity__c, Renewal_Summary__c, Renewal_Date__c,Purchase_Date__c,Total_Price__c from Renewal_Line_Item__c where Renewal_Summary__c =:rsold.Id];
           
           
           
           RLIold.Opportunity__c=o.Id;
           RLIold.Price__c=prod2old.UnitPrice;
           RLIold.Quantity__c=prod2old.Quantity;
           RLIold.Renewal_Date__c=o.CloseDate+365;
           RLIold.Purchase_Date__c=o.CloseDate;
           RLIold.Product__c=pIDold;
           RLIold.Total_Price__c=prod2old.TotalPrice;
           
           update RLIold;
    }
}}}}}}}

July 23, 2015
·
Answer
·
Like
0
·
Follow
1

Shashikant Sharma
You could use Limit 1 to reduce number of rows return to 1
Renewal_Summary__c rsold= [select Id,Name,Product__c,Account__c from Renewal_Summary__c where Account__c=:o.AccountId Limit 1]; 72 Renewal_Line_Item__c RLIold =[select Id,Product__c,Opportunity__c,Price__c,Quantity__c, Renewal_Summary__c, Renewal_Date__c,Purchase_Date__c,Total_Price__c from Renewal_Line_Item__c where Renewal_Summary__c =:rsold.Id Limit 1];
One thing that I noticed that your trigger is not bulkified and having SOQL in for loop, could failt for bulk operations.

July 23, 2015
·
Like
0
·
Dislike
0

Kevin Chiles 930
Thank you for the help on this! How could I bulkify this though to make it work better? Currently, I could have multiple line items on opps that need to update my custom object. I was wondering if using List<> would work better but I am not really the greatest in setting up the query. Any additional assistance is greatly appreciated!

July 23, 2015
·
Like
0
·
Dislike
0

Kevin Chiles 930
Alright,

Some edits to my original code. While this is now updating the rest of the line items (not just one like it used to) but if I have several different line items, it is updating all of the renewal lines to the same Product, even when they are different on the Opportunity. Not sure where I am going wrong on this:

trigger OpportunityWon2 on Opportunity (after update, after insert) { for (Opportunity o : Trigger.New) { if (o.isWon != (Trigger.oldMap != null ? Trigger.oldMap.get(o.Id).isWon : false) && o.isWon) { boolean okee = false; if (Trigger.isUpdate) { // Check if there already is a SPA, if true then skip Integer renewcnt = [SELECT count() FROM Renewal_Summary__c WHERE Account__c = :o.AccountId]; if (renewcnt == 0) okee = true; List<OpportunityLineItem> prods = [SELECT id, PricebookEntryId,Product_Family__c, TotalPrice, Discount, UnitPrice, Quantity, name FROM OpportunityLineItem WHERE OpportunityId = :o.id AND Product_Family__c=:'Maintenance Contracts' ]; List<OpportunityLineItem> prods2 = [SELECT id, PricebookEntryId,Product_Family__c, TotalPrice, Discount, UnitPrice, Quantity, name FROM OpportunityLineItem WHERE OpportunityId = :o.id AND Product_Family__c!='Maintenance Contracts' ]; for(OpportunityLineItem prod : prods ) { Id pId = [SELECT id, Product2Id FROM PriceBookEntry WHERE id = :prod.PricebookEntryId][0].Product2Id; Product2 p = [SELECT id, name, Family, Description, Is_Asset__c, Maintenance_Cycle__c, End_Of_Life_Date__c FROM Product2 WHERE id = :pId]; if (Trigger.isInsert || (Trigger.isUpdate && okee)) { Renewal_Summary__c rs = new Renewal_Summary__c(Account__c = o.AccountId); rs.Reseller__c = o.Reseller__r.Id; rs.Type__c = 'Maintenance Contracts'; rs.Status__c = 'Active'; rs.Product__c=pId; insert rs; for(OpportunityLineItem addprod : prods2 ) { Id pId2 = [SELECT id, Product2Id,UnitPrice FROM PriceBookEntry WHERE id = :addprod.PricebookEntryId][0].Product2Id; Product2 p2 = [SELECT id, name, Family, Description, Is_Asset__c, Maintenance_Cycle__c, End_Of_Life_Date__c FROM Product2 WHERE id = :pId2]; Renewal_Line_Item__c RLI = new Renewal_Line_Item__c(Renewal_Summary__c=rs.Id); RLI.Opportunity__c=o.Id; RLI.Price__c=addprod.UnitPrice; RLI.Quantity__c=addprod.Quantity; RLI.Renewal_Date__c=o.CloseDate+365; RLI.Purchase_Date__c=o.CloseDate; RLI.Product__c=pID2; RLI.Total_Price__c=addprod.TotalPrice; insert RLI; } } Else{ List<OpportunityLineItem> prodsold = [SELECT id, PricebookEntryId,Product_Family__c, TotalPrice, Discount, UnitPrice, Quantity, name FROM OpportunityLineItem WHERE OpportunityId = :o.id AND Product_Family__c=:'Maintenance Contracts' ]; List<OpportunityLineItem> prods2old = [SELECT id, PricebookEntryId,Product_Family__c, TotalPrice, Discount, UnitPrice, Quantity, name FROM OpportunityLineItem WHERE OpportunityId = :o.id AND Product_Family__c!='Maintenance Contracts' ]; for(OpportunityLineItem prod2old : prodsold ) { // Id pId = [SELECT id, Product2Id FROM PriceBookEntry WHERE id = :prodold.PricebookEntryId][0].Product2Id; //Product2 p = [SELECT id, name, Family, Description, Is_Asset__c, Maintenance_Cycle__c, End_Of_Life_Date__c FROM Product2 WHERE id = :pId]; Id pIdold = [SELECT id, Product2Id,UnitPrice FROM PriceBookEntry WHERE id = :prod2old.PricebookEntryId][0].Product2Id; Product2 p2old = [SELECT id, name, Family, Description, Is_Asset__c, Maintenance_Cycle__c, End_Of_Life_Date__c FROM Product2 WHERE id = :pIdold]; if (Trigger.isInsert || (Trigger.isUpdate)) { Renewal_Summary__c rsold= [select Id,Name,Product__c,Account__c from Renewal_Summary__c where Account__c=:o.AccountId Limit 1]; List<Renewal_Line_Item__c> renewals = new List<Renewal_Line_Item__c>(); for(Renewal_Line_Item__c RLIold :[select Id,Product__c,Opportunity__c,Price__c,Quantity__c, Renewal_Summary__c, Renewal_Date__c,Purchase_Date__c,Total_Price__c from Renewal_Line_Item__c where Renewal_Summary__c =:rsold.Id]){ RLIold.Opportunity__c=o.Id; RLIold.Price__c=prod2old.UnitPrice; RLIold.Quantity__c=prod2old.Quantity; RLIold.Renewal_Date__c=o.CloseDate+365; RLIold.Purchase_Date__c=o.CloseDate; RLIold.Product__c=pIDold; RLIold.Total_Price__c=prod2old.TotalPrice; renewals.add(RLIold); } update renewals; } }}}}}}}

July 24, 2015
·
Like
0
·
Dislike
0

Kevin Chiles 930

Getting closer. I am now updating all of the additional renewal line item records, but I am not able to get them to update individually. They are updating as a whole based on what looks like the first line item it pulls. I need to update all of the line items based on the id that they match up too. Here is the updated trigger:
trigger OpportunityWon2 on Opportunity (after update, after insert) { for (Opportunity o : Trigger.New) { if (o.isWon != (Trigger.oldMap != null ? Trigger.oldMap.get(o.Id).isWon : false) && o.isWon) { boolean okee = false; if (Trigger.isUpdate) { // Check if there already is a SPA, if true then skip Integer renewcnt = [SELECT count() FROM Renewal_Summary__c WHERE Account__c = :o.AccountId]; if (renewcnt == 0) okee = true; List<OpportunityLineItem> prods = [SELECT id, PricebookEntryId,Product_Family__c, TotalPrice, Discount, UnitPrice, Quantity, name FROM OpportunityLineItem WHERE OpportunityId = :o.id AND Product_Family__c=:'Maintenance Contracts' ]; List<OpportunityLineItem> prods2 = [SELECT id, PricebookEntryId,Product_Family__c, TotalPrice, Discount, UnitPrice, Quantity, name FROM OpportunityLineItem WHERE OpportunityId = :o.id AND Product_Family__c!='Maintenance Contracts' ]; for(OpportunityLineItem prod : prods ) { Id pId = [SELECT id, Product2Id FROM PriceBookEntry WHERE id = :prod.PricebookEntryId][0].Product2Id; Product2 p = [SELECT id, name, Family, Description, Is_Asset__c, Maintenance_Cycle__c, End_Of_Life_Date__c FROM Product2 WHERE id = :pId]; if (Trigger.isInsert || (Trigger.isUpdate && okee)) { Renewal_Summary__c rs = new Renewal_Summary__c(Account__c = o.AccountId); rs.Reseller__c = o.Reseller__r.Id; rs.Type__c = 'Maintenance Contracts'; rs.Status__c = 'Active'; rs.Product__c=pId; insert rs; for(OpportunityLineItem addprod : prods2 ) { Id pId2 = [SELECT id, Product2Id,UnitPrice FROM PriceBookEntry WHERE id = :addprod.PricebookEntryId][0].Product2Id; Product2 p2 = [SELECT id, name, Family, Description, Is_Asset__c, Maintenance_Cycle__c, End_Of_Life_Date__c FROM Product2 WHERE id = :pId2]; Renewal_Line_Item__c RLI = new Renewal_Line_Item__c(Renewal_Summary__c=rs.Id); RLI.Opportunity__c=o.Id; RLI.Price__c=addprod.UnitPrice; RLI.Quantity__c=addprod.Quantity; RLI.Renewal_Date__c=o.CloseDate+365; RLI.Purchase_Date__c=o.CloseDate; RLI.Product__c=pID2; RLI.Total_Price__c=addprod.TotalPrice; insert RLI; } } Else{ List<OpportunityLineItem> prodsold = [SELECT id, PricebookEntryId,Product_Family__c, TotalPrice, Discount, UnitPrice, Quantity, name FROM OpportunityLineItem WHERE OpportunityId = :o.id AND Product_Family__c!='Maintenance Contracts' ]; for(OpportunityLineItem prod2old : prodsold ) { //Id pIdold = [SELECT id, Product2Id,UnitPrice FROM PriceBookEntry WHERE id = :prod2old.PricebookEntryId].Product2Id; list<PricebookEntry> pIdold =[select id, Product2.Id, unitPrice From PricebookEntry where id =:prod2old.PricebookEntryId]; // list<Product2> p2old = [SELECT id, name, Family, Description, Is_Asset__c, Maintenance_Cycle__c, End_Of_Life_Date__c FROM Product2 WHERE id= :pIdold.Product2.Id]; //if (Trigger.isInsert || (Trigger.isUpdate) { Renewal_Summary__c rsold= [select Id,Name,Product__c,Account__c from Renewal_Summary__c where Account__c=:o.AccountId Limit 1]; List<Renewal_Line_Item__c> renewals = [select Id,Product__c,Opportunity__c,Price__c,Quantity__c, Renewal_Summary__c, Renewal_Date__c,Purchase_Date__c,Total_Price__c from Renewal_Line_Item__c where Renewal_Summary__c =:rsold.Id]; // for(Renewal_Line_Item__c RLIold :[select Id,Product__c,Opportunity__c,Price__c,Quantity__c, //Renewal_Summary__c, Renewal_Date__c,Purchase_Date__c,Total_Price__c //from Renewal_Line_Item__c where Renewal_Summary__c =:rsold.Id]){ for(Renewal_Line_Item__c RLIold : renewals){ RLIold.Opportunity__c=o.Id; RLIold.Price__c=prod2old.UnitPrice; RLIold.Quantity__c=prod2old.Quantity; RLIold.Renewal_Date__c=o.CloseDate+365; RLIold.Purchase_Date__c=o.CloseDate; //RLIold.Product__c=pIdold.Product2.Id; RLIold.Total_Price__c=prod2old.TotalPrice; //renewals.add(RLIold); } update renewals; } }}}}}}

July 27, 2015
·
Like
0
·
Dislike
0

You need to sign in to do that.

Need an account? Sign Up

Have an account? Sign In

Dismiss

Browse by Topic

Welcome to Support!

Show

sorted by

Opportunity Renewal Process

You need to sign in to do that.