Trigger that counts how many contact roles have a specific role on an opportunity and updates a field on the opportunity

Question

I am trying to get a trigger on the opportunity that will look at the contact roles, check to see if there is a contact role with the "role" that equals "fulfillment" and then return the number of contacts that meet the criteria. Basically I just need to know that the Opp has a contact role added and that it has the "role" of "fulfillment". I thought the counting would work so that I could create a pop-up window that alerts the users that a fulfillment contact needs to be added. And once past a certain stage a validation that will not let you move to a "win" without that contact role defined.

Below is my trigger, however it does not update the count. Any pointers? Is there a better more streamline way to accomplish this? Thanks!

trigger fulfillmentcontactcreatedopp on Opportunity (before delete, before insert) {


String Role;
Integer iCount;

Map<String, Opportunity> oppty_con = new Map<String, Opportunity>();//check if the contact role is needed and add it to the oppty_con map
for (Integer i = 0; i < Trigger.new.size(); i++) 
{
        oppty_con.put(Trigger.new[i].id,
        Trigger.new[i]);      
}
Role = 'Fulfillment'; 
for (List<OpportunityContactRole> oppcntctrle :[select OpportunityId from OpportunityContactRole where (Role = 'Fulfillment')])
{
 if (oppcntctrle .Size() <0)
 {
 Role = 'Fulfillment';     
 }
}
iCount = 0;
for (List<OpportunityContactRole> oppcntctrle2 : [select OpportunityId from OpportunityContactRole where (OpportunityContactRole.OpportunityId in :oppty_con.keySet())])//Query for Contact Roles
{    
 if (oppcntctrle2 .Size()>0)
 {
 iCount= oppcntctrle2 .Size();     
 }
}
for (Opportunity Oppty : system.trigger.new) //Check if  roles exist in the map or contact role isn't required 
{
Oppty.Number_of_Fulfillment_Contacts__c = iCount;

}
}

Here is the newly updated code... notice I had to change the last line..opportunity.Number_of_Fulfillment_Contacts__c = aggCount.get('expr0'); and now I get the error that Expression cannot be assigned.

trigger fulfillmentcontactcreatedopp on Opportunity (before insert, before update) {
     Integer Total;
    // Only need to enforce rule if going into the "Win" stage

    Map<Id, Opportunity> oppsToCheckMap = new Map<Id, Opportunity>();

    for(Opportunity opp : trigger.new) {
        // Add the opp Id to a Map(Id to Opp) if it is an insert trigger with a won stage 
        // or if it is an update changing into a won stage (use trigger.oldMap to find the value before the change.
        if(opp.StageName == '09-Win') { // <== Your "Won" stage name here.
            if(trigger.isInsert || 
               trigger.isUpdate && trigger.oldMap.get(opp.Id).StageName != opp.StageName)
                oppsToCheckMap.put(opp.Id, opp);
            }}




    // For each Opportunity in the map keyset get the count of the
    // OpportunityContactRole with role = fulfillment.
    List<AggregateResult> result = [
             select OpportunityId, count(Id)
             from OpportunityContactRole
             where (Role = 'Fulfillment') and
                    OpportunityId in :oppsToCheckMap.keySet() group by OpportunityId];


   // If the count is zero use addError to prevent the insert/update from committing.
    for(AggregateResult aggCount : result) {
        // Here expr0 should be the count(Id) result. 
        // I.e. How many Fulfillment Contact Roles each Opportunity has.
        if(aggCount.get('expr0') == 0) {
            Id oppId = (Id)aggCount.get('OpportunityId');
            Opportunity opp = oppsToCheckMap.get(oppId);
            opp.StageName.addError('Requires Fulfillment Contact Role to be Won');
        }

        // If you don't want to use addError you can also update the count field here
        // The problem is that this won't update the count when the
        // OpportunityContactRole records change.
        opportunity.Number_of_Fulfillment_Contacts__c = aggCount.get('expr0');
    }
}

Update -- working code to get a count of fulfillment contact roles on the opp above a certain stage.

trigger fulfillmentcontactcreatedopp on Opportunity (before insert, before update) {

    // Only need to enforce rule if going into the "Win" stage

    Map<Id, Opportunity> oppsToCheckMap = new Map<Id, Opportunity>();

    for(Opportunity opp : trigger.new) {
        // Add the opp Id to a Map(Id to Opp) if it is an insert trigger with a won stage 
        // or if it is an update changing into a won stage (use trigger.oldMap to find the value before the change.
        if(opp.Stage_Calc__c >= 5 ){ // <== Your "Won" stage name here.

            if(trigger.isInsert || 
               trigger.isUpdate && trigger.oldMap.get(opp.Id).StageName != opp.StageName)
                oppsToCheckMap.put(opp.Id, opp);
            }




    // For each Opportunity in the map keyset get the count of the
    // OpportunityContactRole with role = fulfillment.
    List<AggregateResult> result = [
             select OpportunityId, count(Id)
             from OpportunityContactRole
             where (Role = 'Fulfillment') and
                    OpportunityId in :oppsToCheckMap.keySet() group by OpportunityId];


   // If the count is zero use addError to prevent the insert/update from committing.
    for(AggregateResult aggCount : result) {
        // Here expr0 should be the count(Id) result. 
        // I.e. How many Fulfillment Contact Roles each Opportunity has.
        if(aggCount.get('expr0') == 0) {

            Id oppId = (Id)aggCount.get('OpportunityId');
            Opportunity oppo = oppsToCheckMap.get(oppId);
            //oppo.StageName.addError('Requires Fulfillment Contact Role to be 05-Short List - Vendor Assessement');
        }
       Integer fulcount = Integer.valueOf(aggCount.get('expr0'));
        // If you don't want to use addError you can also update the count field here
        // The problem is that this won't update the count when the
        // OpportunityContactRole records change.
        opp.Number_of_Fulfillment_Contacts__c = fulcount;
    }
    }
}

Answer 1

Rather than try and populate a field with the count, detect when the Opportunity is changing into the won stage and then enforce the check then. You can use addError to stop the update occurring.

I've mocked out an example trigger below. This was done by hand, so it is probably full of typos and things that might not compile. Hopefully it demonstrates the idea.

I've extended the example to update the count field as you originally wanted. The issue here is that this field will only be set when the Opportunity is changed. It won't automatically update with changes to the OpportunityContactRole records. Unfortunately you can't put a trigger on them. Ref. Ideas:Allow APEX Trigger on OpportunityContactRole.

trigger fulfillmentcontactcreatedopp on Opportunity (before insert, before update) {

    // Only need to enforce rule if going into the "Win" stage

    Map<Id, Opportunity> oppsToCheckMap = new Map<Id, Opportunity>();

    for(Opportunity opp : trigger.new) {
        // Add the opp Id to a Map(Id to Opp) if it is an insert trigger with a won stage 
        // or if it is an update changing into a won stage (use trigger.oldMap to find the value before the change.
        if(opp.Stage == 'Closed Won') { // <== Your "Won" stage name here.
            if(trigger.isInsert || 
               trigger.isUpdate && trigger.oldMap.get(opp.Id).Stage != opp.Stage)
                oppsToCheckMap.put(opp.Id, opp);
            }
        }            

    }

    // For each Opportunity in the map keyset get the count of the
    // OpportunityContactRole with role = fulfillment.
    List<AggregateResult> result = [
             select OpportunityId, count(Id)
             from OpportunityContactRole
             where (Role = 'Fulfillment') and
                    OpportuntiyId in :oppsToCheckMap.keySet() group by OpportunityId];

    // If the count is zero use addError to prevent the insert/update from committing.
    for(AggregateResult aggCount : result) {
        // Here expr0 should be the count(Id) result. 
        // I.e. How many Fulfillment Contact Roles each Opportunity has.
        integer roleCount = (integer)aggCount.get('expr0');
        if(roleCount == 0) {
            Id oppId = (Id)aggCount.get('OpportunityId');
            Opportunity opp = oppsToCheckMap.get(oppId);
            opp.Stage.addError('Requires Fulfillment Contact Role to be Won');
        }

        // If you don't want to use addError you can also update the count field here
        // The problem is that this won't update the count when the
        // OpportunityContactRole records change.
        opp.Number_of_Fulfillment_Contacts__c = roleCount;
    }
}

Answer 2

if (oppcntctrle .Size() <0)

what the point of this? Use isEmpty()

Also why do you need 2 queries to OpportunityContactRole? Remember there is only 100 queryies - use them wisely ;)

But the solution to this is as someone said in comment : This trigger is before insert and before delete. Before insert there are no Id for opportunity. And before delete, well there is no trigger.new

I dont see point of this code. You cant have child until parent is inserted. Plese correct me if I am wrong.

So I think you should move logic to after Insert trigger on OpportunityContactRole

Answer 3

In addition to the good answer from Daniel, I want to add another architecture suggestion. Since OpportunityContactRole cannot fire a trigger, your rollup field has the potential to become inaccurate. Anytime a user adds or removes a contact role for example, without updating the oppty, your field is wrong.

You could address this in one of a few ways:

• Create a batch rollup in addition to your trigger, that runs nightly. The same code can be used from the trigger and batch to calculate the rollup.
• Create an custom VF page for Opportunity and/or Contact Role creation. This would allow you to control the logic for when the rollup is triggered
• Don't use OpportunityContactRole, create your own custom junction object.

I would advise one of these alternatives in addition to or in lieu of your trigger.