+ Start a Discussion
LightsLights 

Trigger to auto add another product once one product is added to the opportunity only works in the Developer Sandbox.

Hello friends.

I'm having issue with this trigger in the Opportunity Products. It works fine in the Developer Sandbox, but it doesn't work in the Partial Sandbox or Full Sandbox... so I doubt that it will work in the production. Can you guys help me out? I tried everything...

I have the following trigger:

trigger AutoAddPF00001 on OpportunityLineItem (after insert, after update)
{
    List<OpportunityLineItem> oliList = new List<OpportunityLineItem>();

    List<String> lstProductCodes = new List<String>();

    for(OpportunityLineItem optLineItem: Trigger.new)
    {
        if(optLineItem.ProductCode == '00001a')
        {
            lstProductCodes.add(optLineItem.ProductCode);
        }
    }

    if(lstProductCodes.size()>0)
    {
        System.debug('lstProductCodes=' + lstProductCodes);

        //retrieve the values based on Product list
        List<OpportunityLineItem> lstOpptyLineItems = [SELECT OpportunityId, Opportunity.Pricebook2Id, Name, ProductCode , PricebookEntryId, Quantity, UnitPrice
                                                        FROM    OpportunityLineItem
                                                        WHERE ProductCode IN:lstProductCodes];

        //create a map which contains Product Name and OpportunityLineItem
        Map<String, OpportunityLineItem> mapOpptyLineItem = new Map<String, OpportunityLineItem>();
        for(OpportunityLineItem item:lstOpptyLineItems)
        {
            mapOpptyLineItem.put(item.ProductCode, item);
        }


        Id pbkId = lstOpptyLineItems[0].Opportunity.Pricebook2Id;

        //retrieve PriceBookEntry of the Product B, this is most important
        PricebookEntry pbeProduct2 = [SELECT Id, Pricebook2Id, UnitPrice, Name, Product_Fee_Percentage__c 
                                        FROM PricebookEntry
                                        WHERE Name ='Product Fee'
                                        AND Pricebook2Id  IN (SELECT Id FROM PriceBook2 WHERE Id =:pbkId) LIMIT 1];

        //retrieve Product A item from the map.        
        OpportunityLineItem itemProductA = mapOpptyLineItem.get('00001a');
        System.debug('itemProductA= ' + itemProductA);

        if(itemProductA != null)
        {
            if(Trigger.isInsert)
            {
            //now assign Product A items as required, you can retrieve the amount from Product A
            oliList.add(new OpportunityLineItem(
                OpportunityId = itemProductA.OpportunityId,
                PricebookEntryId = pbeProduct2.Id,
                Quantity = itemProductA.Quantity,
                UnitPrice = itemProductA.UnitPrice * pbeProduct2.Product_Fee_Percentage__c * 0.01 )
              );
            System.debug('oliList=' + oliList);
            insert oliList;
        }
        else if (Trigger.isUpdate)
            {
                //if you need to update PriceBookEntry of Product B
                pbeProduct2.UnitPrice = itemProductA.UnitPrice * pbeProduct2.Product_Fee_Percentage__c * 0.01 ;
                update pbeProduct2;

                //if you need to update OpportunityLineItem of Product B
                OpportunityLineItem optLIProductB = [SELECT OpportunityId, Opportunity.Pricebook2Id,
                                                        Name, ProductCode , PricebookEntryId,
                                                        Quantity, UnitPrice
                                                        FROM    OpportunityLineItem
                                                        WHERE ProductCode = '00001b'];

                optLIProductB.Quantity = mapOpptyLineItem.get('00001a').Quantity;
                optLIProductB.UnitPrice = mapOpptyLineItem.get('00001a').UnitPrice * pbeProduct2.Product_Fee_Percentage__c * 0.01 ;

                update optLIProductB;
            }
    }
}
}

I'm getting the following error:
Apex trigger AutoAddPF00001 caused an unexpected exception, contact your administrator: AutoAddPF00001: execution of AfterInsert caused by: System.QueryException: List has no rows for assignment to SObject: Trigger.AutoAddPF00001: line 35, column 1

Line 35 is:
PricebookEntry pbeProduct2 = [SELECT Id, Pricebook2Id, UnitPrice, Name, Product_Fee_Percentage__c

I tried the solution given by the article below, but it causes more issues.
https://help.salesforce.com/articleView?id=000159853&type=1

So I used the following code with some help:​
List<PricebookEntry> pbeProduct2=new List<PricebookEntry>();
 pbeProduct2 = [SELECT Id, Pricebook2Id, UnitPrice, Name, Product_Fee_Percentage__c 
                            FROM PricebookEntry
                            WHERE Name ='Product Fee'
                            AND Pricebook2Id  IN (SELECT Id FROM PriceBook2 WHERE Id =:pbkId) LIMIT 1];
System.debug('pbeProduct2 '+pbeProduct2);

I get the following error if I try the solution given in the link above:
Error: Compile Error: Variable does not exist: Id at line 53 column 48

Line 53 is:
PricebookEntryId = pbeProduct2.Id,

Best Answer chosen by Lights
Gokula KrishnanGokula Krishnan
Hi Light,

I'm not sure, but check
1. whether the product is Active,
2. All old Product are included in the pricebook. 
3. Is the Currency of Opportunity is match with the price  in the pricebookentry(Eg: Product is cost with dollar, euro..etc).
4. In coding Part - If old product are satisfies the condition of your coding.  

Thanks,

All Answers

Gokula KrishnanGokula Krishnan
Hi Lights,

I couldn't able to understand the logic in your code.

But solution to this issue is:
Apex trigger AutoAddPF00001 caused an unexpected exception, contact your administrator: AutoAddPF00001: execution of AfterInsert caused by: System.QueryException: List has no rows for assignment to SObject: Trigger.AutoAddPF00001: line 35, column 1

Include the product  in all your pricebook.

Thanks,

If it helps you, please mark is as best answer, so it will be helpful for other developers.
LightsLights
The product is in the pricebook. The issue is something else. I was able to fix it by doing the following:
 
PricebookEntry pbeProduct2 = [SELECT Id, Pricebook2Id, UnitPrice, Name, ProductCode, Product_Fee_Percentage__c
                                                FROM PricebookEntry
                                                WHERE Name ='Product Fee'
                                                AND Pricebook2Id  IN (SELECT Id FROM PriceBook2WHERE Id ='01sA00000004lbRIAQ') LIMIT 1];

I'm not getting any errors now, but the 2nd product (Product Fee) is not being added to the opportunity automatically once the main product is added. I'm not sure why. It works fine in the sandbox...
Gokula KrishnanGokula Krishnan
Hi Light,

Check whether the Trigger is Active in Production and also try in production, whether all the system.debug statements are executing. In your code, you have  written IF condition in the  Line 9, 15 and 44, check it returns TRUE. Test with Developer console.

Thanks,
LightsLights

Thank you for the Gokula, but thats not it either. The issue is not with the code. It works in the production as well... I just tested I created a new product and it worked fine. Something wrong going on with the old products that we have. Just figured I would let you all know that the code works.

Gokula KrishnanGokula Krishnan
Hi Light,

I'm not sure, but check
1. whether the product is Active,
2. All old Product are included in the pricebook. 
3. Is the Currency of Opportunity is match with the price  in the pricebookentry(Eg: Product is cost with dollar, euro..etc).
4. In coding Part - If old product are satisfies the condition of your coding.  

Thanks,
This was selected as the best answer
Heather_HansonHeather_Hanson
Hello Lights, thank you for sharing your code!  I'm a beginner with triggers and have been using your post to help me, but got stuck.  I have someone helping me on StackExchange but I'm just getting more confused by the minute by his responses and hoping someone can help me here.

I'm trying to create a trigger that will look at the the OLI selected and determine if additional products need to be added and if so grab them and include them in the insert (with the same quantity as the OLI that was selected initially). The way I set up the logic for this is that every product has a field "lsrv_pk__c" (unique) and "add_for__c" (not unique).  If a product is a PRIMARY product in the bundle (meaning it is a product the user would select), it would have a value for lsrv_pk__c (because all products do) and the value for add_for__c would be 0. If add_for__c is 0, it would mean the trigger should look for related products. The way my trigger would locate the secondary products would be by comparing the values of "add_for__c" of the products in the pricebook to the value of the selected lsrv_pk__c and if it matches it would select those products to add at the same time.  

Here is the code I have.  It has allowed me to save, but when I test it, I'm not getting an error, but it is not working as expected either.  Looking for help!
 
trigger Product_Auto_Add on OpportunityLineItem (before insert) {
    
    List<OpportunityLineItem> oliList = new List<OpportunityLineItem>(); 
    
    List<String> lstProduct = new List<String>();
    
    for(OpportunityLineItem optLineItem: Trigger.new)
    {
        if(optLineItem.add_for__c == '0')
        {
            lstProduct.add(optLineItem.lsrv_pk__c);
        }
    }
    
    //retrieve values of Product Selected
    List<OpportunityLineItem> lstOpptyLineItems = [SELECT OpportunityId, Name, PricebookEntryId, Quantity, add_for__c, lsrv_pk__c, UnitPrice
                                                   FROM    OpportunityLineItem
                                                   WHERE   Id = :Trigger.new 
                                                   AND lsrv_pk__c IN:lstProduct];
    
    //create a map which contains Product lsrv_pk and OpportunityLineItem
    Map<String, OpportunityLineItem> mapOpptyLineItem = new Map<String, OpportunityLineItem>();
    for(OpportunityLineItem item:lstOpptyLineItems)
    {
        mapOpptyLineItem.put(item.lsrv_pk__c, item);
    }

    
    //retrieve PriceBookEntry of the Product B, this is most important
    PricebookEntry pbeProduct2 = [SELECT Id, Pricebook2Id, UnitPrice, Name, add_for__c
                                  FROM PricebookEntry 
                                  WHERE Pricebook2Id IN 
                                  (SELECT Id FROM PriceBook2 
                                   WHERE Name = 'Selectcom Telecom Pricing') 
                                  LIMIT 1];
    
    
    //retrieve Product A item from the map.         
    OpportunityLineItem itemProductA = mapOpptyLineItem.get('Product A');
    
	if (itemProductA != null && pbeProduct2!=null)
    
    
    if(itemProductA.lsrv_pk__c == pbeProduct2.add_for__c){
        
        
        oliList.add(new OpportunityLineItem(
            OpportunityId = itemProductA.OpportunityId, 
            PricebookEntryId = pbeProduct2.Id,
        	Quantity = itemProductA.Quantity,
        	UnitPrice = pbeProduct2.UnitPrice)
                   );
        
        insert oliList;
    }
}