Validation Rule Formula: Specific Opportunity Contact Role required - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Max CalderonMax Calderon 

Validation Rule Formula: Specific Opportunity Contact Role required

Hey Success

Trying to require a specific Opportunity Contact Role ("Billing Contact") before an Opportunity can be moved to Stage: Closed Won.

Here's what I have so far:
  - Validation rule with formula:
AND(ISPICKVAL(StageName, "Closed Won"),NOT(ISPICKVAL(Opportunity_Contact__r.Primary_Role__c, "Billing Contact")))

What's strange here is this looks up the [Contact].Primary Role from the contact object - not the [Opportunity Contact].Role in the Opportunity Contact area, and I can't seem to find a way to lookup the [Opportunity Contact].Role in a formula.

Screenshots for what I'm talking about:
Trying to look up this role

User-added image

Somehow only finding (but still unsuccessfully looking up) this Primary Role
User-added image

The validation correctly stops the opp from changing stage, but adjusting [Contact].Primary Role or [Opportunity Contact].Role does not seem to validate. 

What am I missing?

Looking at a few related posts, there seem to be apps looking for primary contact, and I assume there's always a wya to do this through Apex.. but this seems like something a formula could do. 

Related posts:

Best Answer chosen by Max Calderon
Patricio PenaherreraPatricio Penaherrera
No problem at all.  And, apologies for the delay!  See the sample code below.  This is a static method that you can place in a utility class separate from your Opportunity Trigger.  You can pass in some arguments, such as the Contact Role you are checking for, and the message you want displayed to the user if the Contact Role is not found.  In this way, the method is a little more reusable in case your requirements change in the future.

In your Opportunity trigger, you would first build a map of all Opportunities that have changed to the "Closed Won" stage as a part of the current transaction.  Using that map, you can call this method below from your utility class and pass in the list of Opportunity Ids, the Opportunities themselves, as well as the Contact Role and the Error Message text.  The method will do the rest!

I hope this is helpful!  If so, please like and mark this answer as the best answer at your convenience, and as always, let us know if you need anything else! :)
 
public static void enforceContactRoleValidation(List<Opportunity> opps, Set<Id> oppIds, String contactRole, String message){
        //Query for the OpportunityContactRole records related to the Opportunity Ids that were passed into the method
        List<OpportunityContactRole> ocrs = [SELECT OpportunityId, Role FROM OpportunityContactRole WHERE OpportunityId in: oppIds AND Role = :contactRole];
        //Build a Map where Opportunity Ids will be the keys, and OpportunityContactRole records will be the values
        Map<Id,OpportunityContactRole> ocrMap = new Map<Id,OpportunityContactRole>();
        for(OpportunityContactRole ocr: ocrs){
            ocrMap.put(ocr.OpportunityId, ocr);
        }
        //Now, perform our check
        for(Opportunity o: opps){
            if(!ocrMap.containsKey(o.Id)){
                o.addError(message);
            }
        }
    }

 

All Answers

Nitish SinghalNitish Singhal
Hi Max,

Can you tell me how many Opportunity contacts you can have on one Opportunity record. Because that's a parent child relationship Opportunity (Parent) --> Opp Contact Roles(Child) .

In a formula, you can't refer to something that sits on child record, because there can be many child records at once, and formula won't be able to decide, which child record to consider in calculation.

So, what you can do with some apex logic, that as soon as some type of Opp contact role is added on opportunity, some checkbox gets ticked. And then you can use that checkbox in your formula to know if that type of contact role has been added on opportunity or not.

Does that make sense to you?

Thanks
Nitish
Patricio PenaherreraPatricio Penaherrera
Hi Max,

I agree with Nitish that you'll need some custom Apex code to achieve this requirement.  However, it is not possible today to write an Apex Trigger on the Opportunity Contact Role object, as it is not a first-class object in Salesforce.  What this means is that you won't be able to update a field on your Opportunity record whenever an Opportunity Contact Role of "Billing Contact" is related to your Opportunity, and therefore you won't have a field you can reference in your validation rule to drive your logic.

Instead, you'll need an Apex Trigger on the Opportunity object that checks the Opportunity Contacts (OpportunityContactRole records) related to any Opportunities that have been updated to a Stage of "Closed Won" to see if a Billing Contact has been added.  If yes, do nothing, but if not, add an error to the Opportunity, which in turn blocks the update from taking place.
Patricio PenaherreraPatricio Penaherrera
On a related note, if you are interested, you can vote on the idea below, which is a request to make the Opportunity Contact Role object a first class object, which would allow you to meet this requirement without any code.  It looks like it is under Product Review at Salesforce, so hopefully we'll see this become a reality soon!

https://success.salesforce.com/ideaView?id=08730000000DpQGAA0
Max CalderonMax Calderon
Wow! Thanks for the fast responses. Huge bummer - this seems like it should be standard enough. I'll definitely look at apex solutions.. anyone have a good starting point?
Patricio PenaherreraPatricio Penaherrera
No problem at all.  And, apologies for the delay!  See the sample code below.  This is a static method that you can place in a utility class separate from your Opportunity Trigger.  You can pass in some arguments, such as the Contact Role you are checking for, and the message you want displayed to the user if the Contact Role is not found.  In this way, the method is a little more reusable in case your requirements change in the future.

In your Opportunity trigger, you would first build a map of all Opportunities that have changed to the "Closed Won" stage as a part of the current transaction.  Using that map, you can call this method below from your utility class and pass in the list of Opportunity Ids, the Opportunities themselves, as well as the Contact Role and the Error Message text.  The method will do the rest!

I hope this is helpful!  If so, please like and mark this answer as the best answer at your convenience, and as always, let us know if you need anything else! :)
 
public static void enforceContactRoleValidation(List<Opportunity> opps, Set<Id> oppIds, String contactRole, String message){
        //Query for the OpportunityContactRole records related to the Opportunity Ids that were passed into the method
        List<OpportunityContactRole> ocrs = [SELECT OpportunityId, Role FROM OpportunityContactRole WHERE OpportunityId in: oppIds AND Role = :contactRole];
        //Build a Map where Opportunity Ids will be the keys, and OpportunityContactRole records will be the values
        Map<Id,OpportunityContactRole> ocrMap = new Map<Id,OpportunityContactRole>();
        for(OpportunityContactRole ocr: ocrs){
            ocrMap.put(ocr.OpportunityId, ocr);
        }
        //Now, perform our check
        for(Opportunity o: opps){
            if(!ocrMap.containsKey(o.Id)){
                o.addError(message);
            }
        }
    }

 
This was selected as the best answer
Melanie HarrisonMelanie Harrison
I created a flow to add the Primary Contact as a lookup on the opportunity record.
- here's a great article on how to do that.
https://judisohn.com/2015/04/06/using-salesforce-process-builder-flow-with-opportunity-contact-roles/
If you implement that process then you should be able to validate easily enough. 
Harold CarlsonHarold Carlson
A free app was just released in the App Exchange: https://appexchange.salesforce.com/appxListingDetail?listingId=a0N3A00000EJfoWUAT  I tried it out and it works great for me.  Much easier than implementing a Flow/Process Builder and you don't have to edit the opp for it to take effect.
Jay KilianJay Kilian
Note, Winter 20 seems to address this by making Opportunity Contact Roles more first-class:

https://releasenotes.docs.salesforce.com/en-us/winter20/release-notes/rn_sales_opportunity_contact_roles_customize.htm