+ Start a Discussion
Michael CovertMichael Covert 

SOQL query for OpportunityTeamMember returning zero rows

Hello,

I'm having an issue with an Opportunity trigger that I'm writing, the trigger will change a User lookup field called ResponsibleParty to someone on the Opportunity Team when the stage changes. This trigger does work in the dev org, but I can't get it to work with a test class. Below is the Trigger and test class, it is failing when I perform the SOQL query on OpportunityTeamMember, it will return zero rows. Also I can take the test class code and put it right into the execute Annonymous Code windows and it all works.

FYI I'm fairly new to APEX and I'm working on the Bulkifing. 

Trigger -----

trigger Responsible_Party_Change on Opportunity (after update) {
    for (Opportunity opp : Trigger.new) {

        Opportunity oldOpp = Trigger.oldMap.get(opp.Id);
        //Getting the UserID for Opp Team Member with "Partner" Role
        OpportunityTeamMember partner = [SELECT UserId
                            FROM OpportunityTeamMember
                            WHERE OpportunityId = :opp.Id
                            AND TeamMemberRole = 'Partner'
                            LIMIT 1];

       //Getting the UserID for Opp Team Member with "Setup" Role
        OpportunityTeamMember setup = [SELECT UserId
                            FROM OpportunityTeamMember
                            WHERE OpportunityId = :opp.Id
                            AND TeamMemberRole = 'Setup'
                            LIMIT 1];

        //Getting the UserID for Opp Team Member with "Processor" Role
        OpportunityTeamMember processor = [SELECT UserId
                            FROM OpportunityTeamMember
                            WHERE OpportunityId = :opp.Id
                            AND TeamMemberRole = 'Processor'
                            LIMIT 1];

        //IF the Stage is Partner then set Responsible Party to Partner
        if (!oldOpp.StageName.equals('Partner') && opp.StageName.equals('Partner'))
        {
                opp.Responsible_Party__c = partner.UserId;
        }
}

}

Test Class -----

@isTest
private class ResponsiblePartyChangeTest {
    
    @isTest static void StageChangeTest() {
        Opportunity opp = new Opportunity();
        opp.CloseDate = Date.Today();
        opp.OwnerId = UserInfo.getUserId();
        opp.Name = 'Test Opp';
        opp.StageName = 'Officer';
        Insert(opp);

        opp.StageName = 'Partner';
        Update(opp);
    }
    
}


Any help would be greatly Appreciated!!

 
Best Answer chosen by Michael Covert
Amit Chaudhary 8Amit Chaudhary 8
Hi Michael Covert,

Please follow below Trigger Best Practice
1) NO SOQL inside the loop
2) NO DML inside the loop
3) Trigger should be on before update

Try to update your code like below
trigger Responsible_Party_Change on Opportunity (before update) {

	Set<Id> setOppId = new Set<ID>();
    for (Opportunity opp : Trigger.new) 
	{
		setOppId.add(opp.id);
	}
	
	List<OpportunityTeamMember> lstOTM = [ SELECT UserId,OpportunityId,TeamMemberRole
                            FROM OpportunityTeamMember
                            WHERE OpportunityId = :setOppId ];

	Map<String,OpportunityTeamMember> mapOppWiseOTMPartner = new Map<String,OpportunityTeamMember>();
	Map<String,OpportunityTeamMember> mapOppWiseOTMProcessor = new Map<String,OpportunityTeamMember>();
	Map<String,OpportunityTeamMember> mapOppWiseOTMSetup = new Map<String,OpportunityTeamMember>();
	
	for(OpportunityTeamMember otm : lstOTM )
	{
		if(otm.TeamMemberRole =='Partner')
		{
			mapOppWiseOTMPartner.put(otm.OpportunityId,OpportunityTeamMember);
		}
		else if( otm.TeamMemberRole =='Setup' )
		{
			mapOppWiseOTMSetup.put(otm.OpportunityId,OpportunityTeamMember);
		}
		else if ( otm.TeamMemberRole =='Processor' )
		{
			mapOppWiseOTMProcessor.put(otm.OpportunityId,OpportunityTeamMember);
		}
	}
	
    for (Opportunity opp : Trigger.new) 
	{
        Opportunity oldOpp = Trigger.oldMap.get(opp.Id);
        if (!oldOpp.StageName.equals('Partner') && opp.StageName.equals('Partner') && mapOppWiseOTMPartner.containsKey(opp.id) )
        {
                opp.Responsible_Party__c = mapOppWiseOTMPartner.get(opp.id).UserId;
        }
	}
}

Your test class should be like below
@isTest
private class ResponsiblePartyChangeTest {
    
    @isTest static void StageChangeTest() 
	{
		Account acc = new Account();
		acc.name ='Test';
		insert acc;

		Contact cont = new Contact();
		cont.LastName ='Test';
		cont.FirstName ='TEst';
		cont.accountid = acc.id;
		insert cont;
		
        Opportunity opp = new Opportunity();
		opp.accountid = acc.id;
        opp.CloseDate = Date.Today();
        opp.OwnerId = UserInfo.getUserId();
        opp.Name = 'Test Opp';
        opp.StageName = 'Officer';
        Insert(opp);
		
		OpportunityTeamMember newOTM = new OpportunityTeamMember();
		newOTM.OpportunityId = opp.id;
		newOTM.TeamMemberRole  = 'Partner';
		newOTM.UserId  = UserInfo.getUserId() ;
		insert newOTM;
		
        opp.StageName = 'Partner';
        Update(opp);
    }
    
}

Let us know if this will help you

 

All Answers

Amit Chaudhary 8Amit Chaudhary 8
Hi Michael Covert,

Please follow below Trigger Best Practice
1) NO SOQL inside the loop
2) NO DML inside the loop
3) Trigger should be on before update

Try to update your code like below
trigger Responsible_Party_Change on Opportunity (before update) {

	Set<Id> setOppId = new Set<ID>();
    for (Opportunity opp : Trigger.new) 
	{
		setOppId.add(opp.id);
	}
	
	List<OpportunityTeamMember> lstOTM = [ SELECT UserId,OpportunityId,TeamMemberRole
                            FROM OpportunityTeamMember
                            WHERE OpportunityId = :setOppId ];

	Map<String,OpportunityTeamMember> mapOppWiseOTMPartner = new Map<String,OpportunityTeamMember>();
	Map<String,OpportunityTeamMember> mapOppWiseOTMProcessor = new Map<String,OpportunityTeamMember>();
	Map<String,OpportunityTeamMember> mapOppWiseOTMSetup = new Map<String,OpportunityTeamMember>();
	
	for(OpportunityTeamMember otm : lstOTM )
	{
		if(otm.TeamMemberRole =='Partner')
		{
			mapOppWiseOTMPartner.put(otm.OpportunityId,OpportunityTeamMember);
		}
		else if( otm.TeamMemberRole =='Setup' )
		{
			mapOppWiseOTMSetup.put(otm.OpportunityId,OpportunityTeamMember);
		}
		else if ( otm.TeamMemberRole =='Processor' )
		{
			mapOppWiseOTMProcessor.put(otm.OpportunityId,OpportunityTeamMember);
		}
	}
	
    for (Opportunity opp : Trigger.new) 
	{
        Opportunity oldOpp = Trigger.oldMap.get(opp.Id);
        if (!oldOpp.StageName.equals('Partner') && opp.StageName.equals('Partner') && mapOppWiseOTMPartner.containsKey(opp.id) )
        {
                opp.Responsible_Party__c = mapOppWiseOTMPartner.get(opp.id).UserId;
        }
	}
}

Your test class should be like below
@isTest
private class ResponsiblePartyChangeTest {
    
    @isTest static void StageChangeTest() 
	{
		Account acc = new Account();
		acc.name ='Test';
		insert acc;

		Contact cont = new Contact();
		cont.LastName ='Test';
		cont.FirstName ='TEst';
		cont.accountid = acc.id;
		insert cont;
		
        Opportunity opp = new Opportunity();
		opp.accountid = acc.id;
        opp.CloseDate = Date.Today();
        opp.OwnerId = UserInfo.getUserId();
        opp.Name = 'Test Opp';
        opp.StageName = 'Officer';
        Insert(opp);
		
		OpportunityTeamMember newOTM = new OpportunityTeamMember();
		newOTM.OpportunityId = opp.id;
		newOTM.TeamMemberRole  = 'Partner';
		newOTM.UserId  = UserInfo.getUserId() ;
		insert newOTM;
		
        opp.StageName = 'Partner';
        Update(opp);
    }
    
}

Let us know if this will help you

 
This was selected as the best answer
Michael CovertMichael Covert
This corrected the issue! Also I apprecite the tips and it helped so much being able to see what you meant with your code!