1

I'm new to trigger and trying to write one that will create custom object Existing_Service__c based on OpportunityLineItem when Oppty stage is Won - All Approved.

But I've facing a few issues: - How do I check that a similar record in Existing_Service object isn't exist before creation - One of the custom field needs to capture the Contact role, having problem with this

Below is the trigger I've written. Anyone can shed some light on this? Really appreciate your help.

trigger CreateServices on Opportunity (after update) {

    List<Existing_Service__c> ServiceList = new List<Existing_Service__c>();
    List<Opportunitylineitem> opptylineitemList = new List<Opportunitylineitem>();
    List<Opportunity> opptyList = new List<Opportunity>();
    List<OpportunityContactRole> contFinRole = new List<OpportunityContactRole>();
    List<OpportunityContactRole> contBizRole = new List<OpportunityContactRole>();

    for(Opportunity opp :trigger.new) {
     if(opp.Stagename=='Won - All Approved') {       
        opptylineitemList = [select id, PricebookEntry.Product2.Name, PricebookEntry.Product2.Key_Service_Category__c,
                            PricebookEntry.Product2.id,PricebookEntry.Product2.Billing_Code__c, Fee_Type__c, Frequency__c,  
                            Service_Start_Date__c,Auto_Bill_Commencement_Date__c
                            from Opportunitylineitem where Opportunityid =: opp.id];
        opptyList  = [Select Accountid from Opportunity where Id =: opp.id];
        contFinRole = [select ContactId from OpportunityContactRole where opportunityid =:opp.id and OpportunityContactRole.Role = 'Finance Contact'];
        contBizRole = [select ContactId from OpportunityContactRole where opportunityid =:opp.id and OpportunityContactRole.Role = 'Business Contact'];

        for(Opportunitylineitem Opplist :opptylineitemList ) {
            Existing_Service__c svc = new Existing_Service__c();
            svc.Company__c = opptyList[0].AccountId;
            svc.Opportunity__c = opptyList[0].Id;
            svc.Service__c = opplist.PricebookEntry.Product2.Id;
            svc.Service_Name__c = opplist.PricebookEntry.Product2.Name;
            svc.Key_Service_Category__c = opplist.PricebookEntry.Product2.Key_Service_Category__c;
            svc.Fee_Type__c = opplist.Fee_Type__c;
            svc.Frequency__c = opplist.Frequency__c;
            svc.Billing_Code__c = opplist.PricebookEntry.Product2.Billing_Code__c;
            svc.Service_Start_Date__c = opplist.Service_Start_Date__c;
            svc.Auto_Bill_Commencement_Date__c = opplist.Auto_Bill_Commencement_Date__c;
            svc.Finance_Contact__c = contFinRole.ContactId;
            svc.Business_Contact__c = contBizRole.ContactId;
            ServiceList.add(svc);
        }
        insert ServiceList;
      } 
    }       
}
  • You might quickly hit governor limits issue as you have SOQL queries and DML statements within a for loop. You might want to take a look at salesforce.stackexchange.com/questions/47469/… and developer.salesforce.com/page/… for bulkifying your code. In your existing service is there any reference to oli records? – Prady Nov 24 '14 at 13:36
  • Welcome to SF.SE Esther. What's the name of the previous Stage Name and does your workflow allow Opportunities to be reset to a prior Stage Name? If that's not permitted, your issue is easy to solve as there'd be no 2 records based on the same Opp.Id if created when the stage name changes. Much of what you're querying for is already in Trigger.new or Trigger.old. You may be able to combine the rest into a single query once you have all the Opp Ids. Just edit your post (bottom LH corner) to update it with the answers to this question. – crmprogdev Nov 24 '14 at 14:51
  • Prady, there isn't a link to oli. Yes, is better to include in. Not fluent with DM stuff and bulkifying. Will need to find out more. – Esther Nov 24 '14 at 16:03
  • Crmprohdev, the previous stage name is Won - BU Approved. Both stagename is updated by workflow rule only after approval. And oppty is lock after approval – Esther Nov 24 '14 at 16:06
0

You need to bulkify the trigger - avoid having SOQL queries and dml statements inside for loop. I think your code should look something like this:

trigger CreateServices on Opportunity (after update) {

List<Existing_Service__c> ServiceList = new List<Existing_Service__c>();
List<Opportunitylineitem> opptylineitemList = new List<Opportunitylineitem>();
List<Opportunity> opptyList = new List<Opportunity>();
List<OpportunityContactRole> contFinRole = new List<OpportunityContactRole>();
List<OpportunityContactRole> contBizRole = new List<OpportunityContactRole>();
Map<Id,Id> oppIdConId = new Map<Id,Id>();

Map<Id, Opportunity> oppMap = new Map<Id, Opportunity>(Trigger.new);
Map<Id, Id> oppAccIdMap = new Map<Id, Id>();
Map<Id,Id> oppConFinIdMap = new Map<Id, Id>();
Map<Id,Id> oppConBizIdMap = new Map<Id, Id>();
Set<Id> oppIds = new Set<Id>();
oppIds = oppMap.keySet();

opptylineitemList = [select id, AccountId, PricebookEntry.Product2.Name, PricebookEntry.Product2.Key_Service_Category__c,
                        PricebookEntry.Product2.id,PricebookEntry.Product2.Billing_Code__c, Fee_Type__c, Frequency__c,  
                        Service_Start_Date__c,Auto_Bill_Commencement_Date__c
                        from Opportunitylineitem where Opportunityid in :oppIds];

for(Opportunitylineitem opp:opptylineitemList)
{
    oppAccIdMap.put(opp.Id,opp.AccountId);
}

contFinRole = [select ContactId,opportunityid from OpportunityContactRole where opportunityid in :oppIds and OpportunityContactRole.Role = 'Finance Contact' ];
contBizRole = [select ContactId,opportunityid from OpportunityContactRole where opportunityid in :oppIds and OpportunityContactRole.Role = 'Business Contact'];

for(OpportunityContactRole oppConRole:contFinRole)
{
    oppConFinIdMap.put(oppConRole.opportunityid,oppConRole.ContactId);      
}

for(OpportunityContactRole oppConRole:contBizRole)
{
    oppConBizIdMap.put(oppConRole.opportunityid,oppConRole.ContactId);
}

for(Opportunity opp :trigger.new) {
 if(opp.Stagename=='Won - All Approved') {       

    //opptyList  = [Select Accountid from Opportunity where Id =: opp.id];
    //contFinRole = [select ContactId from OpportunityContactRole where opportunityid =:opp.id and OpportunityContactRole.Role = 'Finance Contact'];
    //contBizRole = [select ContactId from OpportunityContactRole where opportunityid =:opp.id and OpportunityContactRole.Role = 'Business Contact'];

    for(Opportunitylineitem Opplist :opptylineitemList ) {
        Existing_Service__c svc = new Existing_Service__c();
        svc.Company__c = oppAccIdMap.get(Opplist.Id);
        svc.Opportunity__c = Opplist.Id;
        svc.Service__c = opplist.PricebookEntry.Product2.Id;
        svc.Service_Name__c = opplist.PricebookEntry.Product2.Name;
        svc.Key_Service_Category__c = opplist.PricebookEntry.Product2.Key_Service_Category__c;
        svc.Fee_Type__c = opplist.Fee_Type__c;
        svc.Frequency__c = opplist.Frequency__c;
        svc.Billing_Code__c = opplist.PricebookEntry.Product2.Billing_Code__c;
        svc.Service_Start_Date__c = opplist.Service_Start_Date__c;
        svc.Auto_Bill_Commencement_Date__c = opplist.Auto_Bill_Commencement_Date__c;
        if(oppConFinIdMap.get(opp.Id) != '' && oppConFinIdMap.get(opp.Id) != null)
            svc.Finance_Contact__c = oppConFinIdMap.get(opp.Id);
        if(oppConBizIdMap.get(opp.Id) != '' && oppConFinIdMap.get(opp.Id) != null)
            svc.Business_Contact__c = oppConBizIdMap.get(opp.Id);
        ServiceList.add(svc);
    }

  } 
} 
insert ServiceList;      
}

For checking if Existing_Service already exists, check for a combination of fields that will give you a unique value.

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.