+ Start a Discussion
Travis WrightTravis Wright 

System.SObjectException: SObject row was retrieved via SOQL without querying the requested field: OpportunityLineItem.OpportunityId

I am not sure how to fix this, Any help would be great. 

trigger Projectrollup on Opportunity (after insert, after update)
{
  Map<Id,Opportunity> oppIds = new Map<Id,Opportunity>();
  List<Opportunity> updateList = new List<Opportunity>();
 
  for(Opportunity o : Trigger.new)
  {
      oppIds.put(o.id,o);
                                        
  }
 
  OpportunityLineItem[] OLI = [Select UnitPrice, Quantity, PricebookEntry.Product2Id, TotalPrice,PricebookEntry.Product2.Name, Description,
                                Converted_to_Asset__c,Asset__c,PricebookEntry.Product2.Create_Asset__c, PricebookEntry.Product2.Sales_Order_Group__c
                               From OpportunityLineItem
                               where OpportunityId IN : oppIds.keySet()
                               And (PricebookEntry.Product2.Product_Reporting_Category__c = 'Services'
                               or PricebookEntry.Product2.Name LIKE '%Communications%')];
 
  for(OpportunityLineItem o : OLI)
  {
    if(o.PricebookEntry.Product2.Sales_Order_Group__c == 'Hotline Services' || o.PricebookEntry.Product2.Sales_Order_Group__c == 'Policy Management Services' || o.PricebookEntry.Product2.Sales_Order_Group__c == 'Incident Management Services')
    {
      Opportunity opp = new Opportunity(id= o.OpportunityId);
      opp.Implementation_Team__c = true;
      updateList.add(opp);
    }
    if(o.PricebookEntry.Product2.Sales_Order_Group__c == 'ReadyTraining Services')
    {
      Opportunity opp = new Opportunity(id=o.OpportunityId);
      opp.Training_Team__c = true;
      updateList.add(opp);
    }
   }
 
  if(updateList.size()>0)
  {
    update updateList;
  }
}
 
sandeep sankhlasandeep sankhla
Hey,

You can add OpportunityId in query while query..you have not queried this fields and you are using so ypu are getting this error..

Thanks,
Sandeep
sandeep sankhlasandeep sankhla
Hi,

You can simply replace your query with below query
OpportunityLineItem[] OLI = [Select UnitPrice,OpportunityId ,  Quantity, PricebookEntry.Product2Id, TotalPrice,PricebookEntry.Product2.Name, Description,
                                Converted_to_Asset__c,Asset__c,PricebookEntry.Product2.Create_Asset__c, PricebookEntry.Product2.Sales_Order_Group__c
                               From OpportunityLineItem
                               where OpportunityId IN : oppIds.keySet()
                               And (PricebookEntry.Product2.Product_Reporting_Category__c = 'Services'
                               or PricebookEntry.Product2.Name LIKE '%Communications%')];

Please check and let me know if it helps..To avoid these kind of error you should always query all the fields which we are using or reffering in code ....

Thanks,
Sandeep
SarfarajSarfaraj
Hi Travis,

As Sandeep already has mentioned, you have to query the field OpportunityId to fix the error. Apart from that you have the move the update operation to a future operation. As you are trying to update the same records in an after trigger, the records will be locked for update in a synchronous operation. Use the following code,
Trigger,
trigger Projectrollup on Opportunity (after insert, after update)
{
	Map<Id,Opportunity> oppIds = new Map<Id,Opportunity>();
	List<Opportunity> updateList = new List<Opportunity>();
	List<Id> oppImplementationTeamList = new List<Id>();
	List<Id> oppTrainingTeamList = new List<Id>();
	for(Opportunity o : Trigger.new)
	{
		oppIds.put(o.id,o);
	}
 
  OpportunityLineItem[] OLI = [Select UnitPrice, Quantity, PricebookEntry.Product2Id, TotalPrice,PricebookEntry.Product2.Name, Description,
                                Converted_to_Asset__c,Asset__c,PricebookEntry.Product2.Create_Asset__c, PricebookEntry.Product2.Sales_Order_Group__c, OpportunityId
                               From OpportunityLineItem
                               where OpportunityId IN : oppIds.keySet()
                               And (PricebookEntry.Product2.Product_Reporting_Category__c = 'Services'
                               or PricebookEntry.Product2.Name LIKE '%Communications%')];
 
	for(OpportunityLineItem o : OLI)
	{
		if(o.PricebookEntry.Product2.Sales_Order_Group__c == 'Hotline Services' || o.PricebookEntry.Product2.Sales_Order_Group__c == 'Policy Management Services' || o.PricebookEntry.Product2.Sales_Order_Group__c == 'Incident Management Services')
		{
			oppImplementationTeamList.add(o.OpportunityId);
		}
		if(o.PricebookEntry.Product2.Sales_Order_Group__c == 'ReadyTraining Services')
		{
			oppTrainingTeamList.add(o.OpportunityId);
		}
	}
	if(oppImplementationTeamList.size() > 0 || oppTrainingTeamList.size() > 0)
	OpportunityTriggerHandler.updateOpportunities(oppImplementationTeamList, oppTrainingTeamList);
}

Future handler class,
public class OpportunityTriggerHandler {
	@future
    public static void updateOpportunities(List<Id> oppImplementationTeamList, List<Id> oppTrainingTeamList)
    {
        List<Opportunity> updateList = new List<Opportunity>();
		for(Id oppId : oppImplementationTeamList)
		{
			Opportunity opp = new Opportunity(id = oppId);
			opp.Implementation_Team__c = true;
			updateList.add(opp);
		}
		
		for(Id oppId : oppTrainingTeamList)
		{
			Opportunity opp = new Opportunity(id = oppId);
			opp.Training_Team__c = true;
			updateList.add(opp);
		}
		
		if(updateList.size()>0)
		{
			update updateList;
		}
    }
}

First create the class OpportunityTriggerHandler and then modify the trigger.

--Akram
Travis WrightTravis Wright
@Akram

I have a couple questions as I have followed the suggestions but I am not seeing the update happen. Following the record I see if pass through the trigger and add the record to the list but I am not sure if the Class is firing as I don't see any update. Any way to determine if this is happening?

Also on a side note this is just the base and I have to expand it to include a couple different product lines. One being any product that Contains Communication within the name. I have done some research and just can't grasp the Contains formula within APEX. Can you please provide an example of this?

Thanks 
Travis
SarfarajSarfaraj
Yeah I missed a small part. Please enclose the entire code in the trigger within this if block, if(!System.isFuture()) { } For debugging you have to use debug logs. You will find a new log file for the future invocation. Let me know if you need any guidance on how to use this. Is it possible for you to elaborate a bit more on the product lines. Then it will be more convenient for me to come up with some more specific example.
Travis WrightTravis Wright
@akram

Sure we are getting ready to start transferring closed won opportunities to our implementations team that uses a different system. The problem is that an opporutunity needs to create multiple projects based of the implementation service that are selected. In the code you see the IF statements that help me identify which devision in implementations needs a project created. The code only reflexs 2 of the 4 teams. Implementations which handles the following, 

if(o.PricebookEntry.Product2.Sales_Order_Group__c == 'Hotline Services' || o.PricebookEntry.Product2.Sales_Order_Group__c == 'Policy Management Services' || o.PricebookEntry.Product2.Sales_Order_Group__c =='Incident Management Services')

Training which handles the following, 
if(o.PricebookEntry.Product2.Sales_Order_Group__c == 'ReadyTraining Services')

Communications which should be,

if(o.PricebookEntry.Product2.Name Contains "Communications"

Than professional services which is going to be handled by 

if(o.PricebookEntry.Product2.Sales_Order_Group__c == 'Professional Services')

I am not sure how to handle the Contains Statement within the Communication If Statement. 


Also I am not sure if I am putting the 
if(!System.isFuture()){} in the right spot can you show me?
 
SarfarajSarfaraj
Sure. First of all this is how you have to put the isFuture check,
trigger Projectrollup on Opportunity (after insert, after update)
{
	if(!System.isFuture())
	{
		Map<Id,Opportunity> oppIds = new Map<Id,Opportunity>();
		List<Opportunity> updateList = new List<Opportunity>();
		List<Id> oppImplementationTeamList = new List<Id>();
		List<Id> oppTrainingTeamList = new List<Id>();
		for(Opportunity o : Trigger.new)
		{
			oppIds.put(o.id,o);
		}
 
		OpportunityLineItem[] OLI = [Select UnitPrice, Quantity, PricebookEntry.Product2Id, TotalPrice,PricebookEntry.Product2.Name, Description,
		Converted_to_Asset__c,Asset__c,PricebookEntry.Product2.Create_Asset__c, PricebookEntry.Product2.Sales_Order_Group__c, OpportunityId From
		OpportunityLineItem where OpportunityId IN : oppIds.keySet() And (PricebookEntry.Product2.Product_Reporting_Category__c = 'Services' or
		PricebookEntry.Product2.Name LIKE '%Communications%')];
 
		for(OpportunityLineItem o : OLI)
		{
			if(o.PricebookEntry.Product2.Sales_Order_Group__c == 'Hotline Services' || o.PricebookEntry.Product2.Sales_Order_Group__c == 'Policy Management Services' || o.PricebookEntry.Product2.Sales_Order_Group__c == 'Incident Management Services')
			{
				oppImplementationTeamList.add(o.OpportunityId);
			}
			if(o.PricebookEntry.Product2.Sales_Order_Group__c == 'ReadyTraining Services')
			{
				oppTrainingTeamList.add(o.OpportunityId);
			}
		}
		if(oppImplementationTeamList.size() > 0 || oppTrainingTeamList.size() > 0)
			OpportunityTriggerHandler.updateOpportunities(oppImplementationTeamList, oppTrainingTeamList);
	}
}

We need this if statement to avoid infinite stack of recursive call. Our update statement in the future handler will invoke the trigger again, so we have to make sure the trigger does not calls the future method for the second time.

If I understood your other requirement correctly you need to put two more if blocks inside the for loop on OLI. And you need help with the third if block. This is how we check contains in Apex,
 
if(o.PricebookEntry.Product2.Name.contains('Communications'))

Please note that this checks in case sensitive manner, i.e. 'communications' and 'Communications' are different. If you want case insensitive check use this,
if(o.PricebookEntry.Product2.Name.toUpperCase().contains('COMMUNICATIONS'))
Let me know if this solves your requirement.

--Akram
Travis WrightTravis Wright
Thanks I will give it a try and let you know. 
Travis WrightTravis Wright
@Akram

OK So after testing this I am getting a duplicateId in list error. I think this is because there are 3 different products that fall in the first list for implmentations. Is there a limit function that I can use to limit it to 1, So if it finds 1 product that matchs it just adds the ID and moves on to the next List?

Or do I have to write and limit Queries? I would prefer not to write multiple Queries due to limits.
SarfarajSarfaraj
Travis,

You have to make use of Set and Map. I have modified the code,
Trigger,
trigger Projectrollup on Opportunity (after insert, after update)
{
	if(!System.isFuture())
	{
		Map<Id,Opportunity> oppIds = new Map<Id,Opportunity>();
		List<Opportunity> updateList = new List<Opportunity>();
		Set<Id> oppImplementationTeamList = new Set<Id>();
		Set<Id> oppTrainingTeamList = new Set<Id>();
		
		for(Opportunity o : Trigger.new)
		{
			oppIds.put(o.id, o);
		}
 
		OpportunityLineItem[] OLI = [Select UnitPrice, Quantity, PricebookEntry.Product2Id, TotalPrice,PricebookEntry.Product2.Name, Description,
		Converted_to_Asset__c,Asset__c,PricebookEntry.Product2.Create_Asset__c, PricebookEntry.Product2.Sales_Order_Group__c, OpportunityId From
		OpportunityLineItem where OpportunityId IN : oppIds.keySet() And (PricebookEntry.Product2.Product_Reporting_Category__c = 'Services' or
		PricebookEntry.Product2.Name LIKE '%Communications%')];
 
		for(OpportunityLineItem o : OLI)
		{
			if(o.PricebookEntry.Product2.Sales_Order_Group__c == 'Hotline Services' || o.PricebookEntry.Product2.Sales_Order_Group__c == 'Policy Management Services' || o.PricebookEntry.Product2.Sales_Order_Group__c == 'Incident Management Services')
			{
				oppImplementationTeamList.add(o.OpportunityId);
			}
			if(o.PricebookEntry.Product2.Sales_Order_Group__c == 'ReadyTraining Services')
			{
				oppTrainingTeamList.add(o.OpportunityId);
			}
		}
		
		if(oppImplementationTeamList.size() > 0 || oppTrainingTeamList.size() > 0)
			OpportunityTriggerHandler.updateOpportunities(oppImplementationTeamList, oppTrainingTeamList);
	}
}

Apex Class,
public class OpportunityTriggerHandler {
	@future
    public static void updateOpportunities(Set<Id> oppImplementationTeamList, Set<Id> oppTrainingTeamList)
    {
        List<Opportunity> updateList = new List<Opportunity>();
		Map<Id, Opportunity> updateMap = new Map<Id, Opportunity>();
		
		for(Id oppId : oppImplementationTeamList)
		{
			Opportunity opp = updateMap.get(oppId);
			if(opp == null)
			{
				opp = new Opportunity(id = oppId);
				updateList.add(opp);
				updateMap.put(oppId, opp);
			}
			opp.Implementation_Team__c = true;
		}
		
		for(Id oppId : oppTrainingTeamList)
		{
			Opportunity opp = updateMap.get(oppId);
			if(opp == null)
			{
				opp = new Opportunity(id = oppId);
				updateList.add(opp);
				updateMap.put(oppId, opp);
			}
			opp.Training_Team__c = true;
		}
		
		if(updateList.size()>0)
		{
			update updateList;
		}
    }
}

--Akram
Travis WrightTravis Wright
@Akram

Thanks for all the help. I have never used an @Future class so it something nice to know. I have noticed that this doesn't update if they remove the product that has caused the update. IE, If they were going to sell training but then updated the OLI by removing the Training Product it doesn't update the Checkbox to false. 

Would it be best to add this to the Future task by writing it as an Else Statement or would it be best to make these fields Null in the trigger and let the class update them. I am afraid that if I put it in the trigger I might cause some issues down the road but in the future task I am not sure how to add it as I don't understand the If Statement that you have writen. I guess this is due to the lack of APEX knowledge that I have. 
SarfarajSarfaraj
Are you deleting the associated OLI or just editing the record to remove the lookup to product field?
Travis WrightTravis Wright
@Akram

The Sales Reps would be deleting the OLI. 
SarfarajSarfaraj
In that case I would suggest you to do the entire operation in a trigger on Opportunity Product instead of Opportunity. And you don't need any future operation for this. Use the following code,
trigger Projectrollup on OpportunityLineItem (after insert, after update, before delete)
{
	Set oppIdSet = new Set();

	if(Trigger.isAfter)
		for(OpportunityLineItem oli : Trigger.new)
			oppIdSet.add(oli.OpportunityId);
	if(Trigger.isBefore)
		for(OpportunityLineItem oli : Trigger.old)
			oppIdSet.add(oli.OpportunityId);
	
	Map oppOldMap = new Map([Select Id, Implementation_Team__c, Training_Team__c From Opportunity Where Id IN :oppIdSet]);

	Set oppImplementationTeamList = new Set();
	Set oppTrainingTeamList = new Set();
		
	List oliList = [Select UnitPrice, Quantity, PricebookEntry.Product2Id, TotalPrice,PricebookEntry.Product2.Name, Description,
		Converted_to_Asset__c,Asset__c,PricebookEntry.Product2.Create_Asset__c, PricebookEntry.Product2.Sales_Order_Group__c, OpportunityId From OpportunityLineItem where 
		OpportunityId IN : oppIdSet And (PricebookEntry.Product2.Product_Reporting_Category__c = 'Services' or PricebookEntry.Product2.Name LIKE '%Communications%')];

	for(OpportunityLineItem oli : oliList)
	{
		if(oli.PricebookEntry.Product2.Sales_Order_Group__c == 'Hotline Services' || oli.PricebookEntry.Product2.Sales_Order_Group__c == 'Policy Management Services' || oli.PricebookEntry.Product2.Sales_Order_Group__c == 'Incident Management Services')
		{
			oppImplementationTeamList.add(oli.OpportunityId);
		}
		if(oli.PricebookEntry.Product2.Sales_Order_Group__c == 'ReadyTraining Services')
		{
			oppTrainingTeamList.add(oli.OpportunityId);
		}
	}
	
	Map updateMap = new Map();
	
	for(Id oppId : oppIdSet)
	{
		Opportunity opp = new Opportunity(Id = oppId, Implementation_Team__c = false, Training_Team__c = false);
		updateMap.put(oppId, opp);
	}
	
	for(Id oppId : oppImplementationTeamList)
	{
		Opportunity opp = updateMap.get(oppId);
		opp.Implementation_Team__c = true;
	}
	
	for(Id oppId : oppTrainingTeamList)
	{
		Opportunity opp = updateMap.get(oppId);
		opp.Training_Team__c = true;
	}
	
	for(Opportunity oppOld : oppOldMap.values())
	{
		Opportunity oppNew = updateMap.get(oppOld.Id);
		if(oppNew.Implementation_Team__c == oppOld.Implementation_Team__c && oppNew.Training_Team__c == oppOld.Training_Team__c)
			updateMap.remove(oppOld.Id);
	}
	
	List updateList = updateMap.values();
	
	if(updateList.size()>0)
		update updateList;
}

--Akram
Travis WrightTravis Wright
@Akram  

Thanks I will give it a try and let you know, Thanks again for all the help.