function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
LloydSilverLloydSilver 

Apex Error On Class

The purpose of this class is to update a custom field on the Contact record of contacts associated with an opportunity via Opportunity Contact Role, when the Opportunity is a specific stage (Prospecting or Qualification).

 

Currently, when an Opportunity is created or updated I get an error:

 

Error: Invalid Data. 
Review all error messages below to correct your data.
Apex trigger OpportunityAgentStatus caused an unexpected exception, contact your administrator: OpportunityAgentStatus: execution of AfterUpdate caused by: System.QueryException: List has no rows for assignment to SObject: Class.OpportunityAgentStageProspect.prospect: line 15, column 1

 

Obviously something is wrong with how I'm pulling contacts but I can't figure it out.

 

I'd appreciate the help.

 

Thanks.

public class OpportunityAgentStageProspect {

	public static void prospect(Opportunity[] agentopps) {
		
		for (Opportunity opp :agentopps){
			
			Opportunity opp2 = [select ID, StageName from Opportunity where ID = :opp.Id];
			
			if (opp2.StageName == 'Prospecting' || opp2.StageName == 'Qualification') {
			
				OpportunityContactRole[] contacts = [select ID from OpportunityContactRole where OpportunityID = :opp2.Id];
			
					for (OpportunityContactRole con :contacts){
					
						Contact c = [select ID, Agent_Status__c from Contact where ID = :con.Id];
					
						c.Agent_Status__c = 'Prospect';
						update c;
				}
			
			}
		}
		
	}

}

 

 

Best Answer chosen by Admin (Salesforce Developers) 
liron169liron169
My mistake, it should be:

Contact c = [select ID, Agent_Status__c from Contact where ID = :con.ContactID];

All Answers

liron169liron169

Hello,

 

You're retrieving OpportunityContactRole, and than searching by the id of OpportunityContactRole in the Contact table, so abiouveisly it won't find any Contact.


I assume it should be something like:

OpportunityContactRole[] oppContact = [select ContactID, id from OpportunityContactRole where OpportunityID = :opp2.Id];
            
                    for (OpportunityContactRole con :oppContact){
                    
                        Contact c = [select ID, Agent_Status__c from Contact where ID = :oppContact.ContactId];
                    
                        c.Agent_Status__c = 'Prospect';
                        update c;
                }

 

 

In addition you have 2 SQL + 1 DML inside a loop.

It's  not best practive, and likely to failed on limitation exception if you will update in bulk.



 

 

LloydSilverLloydSilver

I made the changes but got another error:

 

Initial term of field expression must be a concrete SObject: LIST<OpportunityContactRole>, line# = 15, column# = 72, id = null, fullname = OpportunityAgentStageProspect
(2) filename = package.xml, result = SUCCESS, affect = changed, id = null, fullname = package.xml

 

Here is the udpated code:

 

public class OpportunityAgentStageProspect {

	public static void prospect(Opportunity[] agentopps) {
		
		for (Opportunity opp :agentopps){
			
			Opportunity opp2 = [select ID, StageName from Opportunity where ID = :opp.Id];
			
			if (opp2.StageName == 'Prospecting' || opp2.StageName == 'Qualification') {
			
				OpportunityContactRole[] oppcontact = [select ContactID, ID from OpportunityContactRole where OpportunityID = :opp2.Id];
			
					for (OpportunityContactRole con :oppcontact){
					
						Contact c = [select ID, Agent_Status__c from Contact where ID = :oppcontact.ContactID];
					
						c.Agent_Status__c = 'Prospect';
						update c;
				}
			
			}
		}
		
	}

}

 

liron169liron169
My mistake, it should be:

Contact c = [select ID, Agent_Status__c from Contact where ID = :con.ContactID];
This was selected as the best answer
LloydSilverLloydSilver

Awesome. Thanks much for the help. 

LloydSilverLloydSilver

I updated the code to handle bulk usage.

 

public class OpportunityAgentStageProspect {

	public static void prospect(Opportunity[] agentopps) {
		
		List<Id> contactIds = new List<Id>{};
		List<Contact> consToUpdate = new List<Contact>{};
		
		for (Opportunity opp :agentopps){
			
			if (opp.StageName == 'Prospecting' || opp.StageName == 'Qualification')
				for(OpportunityContactRole ocr: [select ID, ContactID from OpportunityContactRole where OpportunityID = :opp.Id]){
					contactIds.add(ocr.ContactID);
				}
				
			for(Contact c: [select ID, Agent_Status__c from Contact where ID =:contactIds]){
					c.Agent_Status__c = 'Prospect';
					consToUpdate.add(c);
			}
				
		}
		
		if(!consToUpdate.isEmpty())
			update consToUpdate;
			
	}
	
}

 

liron169liron169

You are still using SQL inside the loop - for each Opportunity, you are queting the OpportunityContactRole and the Contact

 

try this

 

public class OpportunityAgentStageProspect {

	public static void prospect(Opportunity[] agentopps) {
		
		List<Id> contactIds = new List<Id>{};
		List<Contact> consToUpdate = new List<Contact>{};

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

		for (Opportunity opp :agentopps){
			if (opp.StageName == 'Prospecting' || opp.StageName == 'Qualification')
				oppIdLst.add(opp.Id);
		}

		if(!oppIdLst.isEmpty()){
			for(OpportunityContactRole ocr: [	select ID, ContactID 
								from OpportunityContactRole 
								where OpportunityID IN :oppIdLst]){
				contactIds.add(ocr.ContactID);
			}

			for(Contact c: [	select ID, Agent_Status__c 
						from Contact 
						where ID =:contactIds]){
				c.Agent_Status__c = 'Prospect';
				consToUpdate.add(c);
			}
		}


		
		if(!consToUpdate.isEmpty())
			update consToUpdate;
			
	}
	
}

 

LloydSilverLloydSilver

Thanks. I've been struggline with another class and failing in a bulk update, and this helps explain what I need to do very clearly.