I’ve been working on a fun problem for Force-Friend Nick (Hi Nick!).

The requirement:

• When an Opportunity is closed, workflow sends an email to the Accounts people (who don’t have a Salesforce login)
• The email is to include address details of the Primary Contact associated with the Opportunity

Some relevant information:

• Contacts are linked to Opportunity objects via the OpportunityContactRole object, which contains an OpportunityId, a ContactId and an IsPrimary flag (plus various other fields)
• Cross-object formulas can’t span the Opportunity-Contact relationship

The Solution

There are several ways this could be implemented:

• Move the email generation from workflow to Apex, which would give full access to the object model (but lose the easy user interface)
• Create a Trigger on OpportunityContactRole to copy information onto the Opportunity (unfortunately, we discovered that you can’t create a Trigger on this object!)
• Create a Trigger on Opportunity to ‘pull’ the information from the Primary Contact onto the Opportunity record

We decided to go with the Trigger on Opportunity. The information is only wanted when the Opportunity is closed, so it means we could write the Trigger to fire on closure of the Opportunity. It then looks for the Primary Contact (as flagged by the IsPrimary field) and copies it to a Custom Field (Primary_Contact__C) on the Opportunity object, which can then be used by cross-formula functions within workflow.

Here’s the code for the trigger:

trigger SetPrimaryContact on Opportunity (before update) {

   for (Opportunity o : Trigger.new) {
     // Check if the Opportunity just closed
     if (o.IsClosed && !Trigger.oldMap.get(o.id).IsClosed) {
       OpportunityContactRole contactRole =
            [select ContactID from OpportunityContactRole where IsPrimary = true and OpportunityId = :o.id];
       if (contactRole != null) {
         o.Primary_Contact__c = contactRole.ContactID;
       }
     }
   }
 }

Simple result, but took a bit of thinking to create it.

The only funny-looking code is !Trigger.oldMap.get(o.id).IsClosed, which retrieves the old value of the IsClosed field to see that it really changed. I use oldMap because I read somewhere that you should not rely on Trigger.old and Trigger.new being perfectly aligned (that is, for example, Trigger.old[4] and Trigger.new[4] do not necessarily refer to the same fields). However, I can’t find that reference anymore, so feel free to tell me if I’m right or wrong!

The Bottom Line

• Not every object permits a Trigger
• Cross-object formulas also have their limitations
• Sometimes a bit of Apex trickery saves having to buy licenses for extra users!