3

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;
    }
    }
}
2
  • 3
    one issue i see right off the bat is that before inserts dont have Ids yet.. Jan 30, 2014 at 18:59
  • 1
    SO your in your map 'Trigger.new[i].id' wouldn't work since there is no 'Id' on 'Trigger.new[1]' Jan 30, 2014 at 19:22

3 Answers 3

5

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;
    }
}
11
  • I updated the code.. now I am getting this error: Invalid field OpportunityId for SObject AggregateResult. I tried to work around it to no avail. Jan 31, 2014 at 17:43
  • The OpportunityId should be selected along with the count in the prior SOQL query with the group by clause. I'll try the query out again. Feb 1, 2014 at 18:51
  • I tried the following query in web workbench: select OpportunityId, count(Id) from OpportunityContactRole group by OpportunityId. It returned the OpportunityId along with the count. Which line in the example code is giving you the error? Feb 1, 2014 at 18:54
  • This line.. Opportunity opps = oppsToCheckMap.get(aggCount.OpportunityId); Feb 3, 2014 at 18:21
  • I tried that query too, and got the same thing you did, however, I still get the error on the line mentioned. Feb 10, 2014 at 18:25
1
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

3
  • I don't believe you can currently create a trigger on OpportunityContactRole. See Ideas:Allow APEX Trigger on OpportunityContactRole Jan 30, 2014 at 19:47
  • In that case to after Insert, after update on opportunity. Jan 30, 2014 at 19:48
  • 1
    @Birthus - because I just don't know what I am doing half of the time. I take what I think I know, apply it and then muddle through from there. Someday, I hope to be a real boy. :) Jan 30, 2014 at 21:44
0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .