+ Start a Discussion
Merry SMerry S 

Trigger on OP to update a field that counts how many contact roles have a specific role

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.

I got some help on the stackexchange - but I am running into an issue... I am getting this error: Invalid field OpportunityId for SObject AggregateResult on this line: Opportunity opps = oppsToCheckMap.get(aggCount.OpportunityId);

Here is the trigger..

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.
        if(aggCount.expr0 == 0) {
            Opportunity opp = oppsToCheckMap.get(aggCount.OpportunityId);
            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 = aggCount.expr0;

Offshore Freelance ConsultantOffshore Freelance Consultant

Since the trigger is written on Before Insert, OpportunityId, will not be generated before insert.

Suggest to try the following.

Do not use adderror and do validation in trigger itself.

Instead, set the count field (custom field) in opportunity with the count of opportunity role satisfying the criteria.

Then write a validation rule, to check for that custom field count to greater than 0 to allow saving.

since validation rules fire after trigger before insert, before update, you should be covered.

Hope this helps!