1

Our org was configured such that Opps can only be created during Lead Conversion or from a Contact related list. This ensures each Opp has a Contact Role associated.

SDRs create an Event when they have qualified a prospect (sometimes on a Lead, sometimes on a Contact) and set a meeting. If it's on a Lead, once the Lead is converted with an Opp created, the Event automatically associates with the Opp.

If the Event was created on a Contact which was qualified and an Opp is later created off the Contact, the Event doesn't associate with the Opp.

Here's my trigger for associating Events to an Opp from Contacts associated as Contact Roles on the Opp.

trigger AddMQLEventstToOpp on Opportunity (after update) {

    for (Opportunity myOpp : Trigger.new) {
        OpportunityContactRole contactRole = [select ContactId from OpportunityContactRole where OpportunityId = :myOpp.Id];
        String cRoleId = contactRole.ContactId;

        List<Event> mqlEventsToAdd = new List <Event>();
        mqlEventsToAdd.add([select Id from Event where WhoId = :cRoleId and WhatId = null and Type = 'MQL']);
        for (Event mqlEvent : mqlEventsToAdd){
            mqlEvent.WhatId = myOpp.Id;
            update mqlEvent;
        }   
    }
}

I'm getting this error and not sure how to troubleshoot.

System.QueryException: List has no rows for assignment to SObject: External entry point

  • you can try out writing a trigger and post your code here in case of any issues – Santanu Boral Feb 2 '17 at 3:00
  • Here, you should give there first sort and if any issues are getting faced then post with specific issues. Community members not involved to execute your project – Santanu Boral Feb 2 '17 at 18:31
  • Seems to work well. One piece of logic I still can't figure out is in addition to checking to see if Events have a blank WhatId, I'd like to check if the WhatId isn't an Opportunity. Example would be if the Event's WhatID is an Account. Any ideas? Thanks. – Brooks Bruce Feb 9 '17 at 22:54
1

Woah! Never query or perform DML in a loop! You do two queries and an update within a loop in this trigger! And your update is within a further nested loop!

In addition to having no guarantee that these records will even exist, you're chewing through governor limits at an incredible rate. With this code in place, you will never be able to update more than 50 Opportunity records at a time.

The basic pattern is known as Query-Transform-Load. You do your transformations within your loop. Move everything else outside of it.

Query

// this first loop combines two common patterns that allow you to
// use a collection to move your query outside the loop

Set<Id> contactIds = new Set<Id>();
Map<Id, List<OpportunityContactRole>> ocrs = new Map<Id, List<OpportunityContactRole>>();
for (OpportunityContactRole ocr : [
    SELECT OpportunityId, ContactId FROM OpportunityContactRole
    WHERE OpportunityId IN :trigger.new
]){
    contactIds.add(ocr.ContactId);
    if (!ocrs.containsKey(ocr.OpportunityId))
        ocrs.put(ocr.OpportunityId, new List<OpportunityContactRole>());
    ocrs.get(ocr.OpportunityId).add(ocr);
}

// you may recognize this pattern
Map<Id, List<Event>> events = new Map<Id, List<Event>>();
for (Event record : [
    SELECT WhoId FROM Event
    WHERE WhoId IN :contactIds
    AND WhatId = null
    AND Type = 'MQL'
]){
    if (!events.containsKey(record.whoId))
        events.put(record.whoId, new List<Event>());
    events.get(record.whoId).add(event);
}

Transform

Now the key (pun intended) is simply to extract your data from these collections.

for (Opportunity record : trigger.new)
{
    if (!ocrs.containsKey(record.Id)) continue;
    for (OpportunityContactRole ocr : ocrs.get(record.Id))
    {
        if (!events.containsKey(ocr.ContactId)) continue;
        for (Event event : events.get(ocr.ContactId))
            event.WhatId = record.Id;
    }
}

Load

Now you can just flatten your events collection and make one update call. Now this entire trigger consumes just two queries and one DML Statement.

List<Event> eventsToUpdate = new List<Event>();
for (List<Event> contactSpecificEvents : events.values())
{
    eventsToUpdate.addAll(contactSpecificEvents);
}
update eventsToUpdate;

That's the basic idea to bulkification. You should also have a read of General trigger bulkification - best practices.

|improve this answer|||||
  • Thank you so much Adrian! Will test this out and let you know. – Brooks Bruce Feb 10 '17 at 19:34
  • 1
    Just a note: this is how you would take the logic you have, and make it more safe to perform. This logic doesn't really make sense in an update trigger on Opportunity though. It would make a lot more sense in an insert/update trigger on Event. – Adrian Larson Feb 10 '17 at 19:35
  • 1
    @BrooksBruce Please ask a new question to address that issue. Feel free to link back to this one if it helps provide context. – Adrian Larson Feb 13 '17 at 21:58
  • Your point about this making more sense on an Event as opposed to an Opp - these Events are created by an SDR before an Opp is created. After the Event takes place, an AE will choose to make an Opp or not based on how the meeting went. If he/she makes the Opp from the Contact (as we've configured Salesforce to only allow) this code associates any MQL Events to the Opp. That's how I've been thinking about it. – Brooks Bruce Feb 13 '17 at 21:59
  • This works great! Even if I add another Contact Role to an Opp which already has a Contact Role, after I update the Opp the most recent Contact Role's Event is associated to the Opp, too. Thank you @AdrianLarson! – Brooks Bruce Feb 14 '17 at 0:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.