+ Start a Discussion
hgeorgehgeorge 

Counting original lead in Opportunity

Hi,

I am trying to display the total number of original leads in Opportunities. Is it possible to create a customer field in Opportunities that counts the total number of original leads for the opportunities (represented by a picklist filed in Lead object)?

 

Thanks in advance,

George

Pradeep_NavatarPradeep_Navatar

Business requirement is not very clear. Can you elaborate more?

hgeorgehgeorge

My mistake, the question was about the Campaign object and not about Opportunity, Sorry...let me repeat again.

 

I have many leads for a campaign, each Lead has a custom field identifying the original campaign for which the lead is associated with. This is a picklist field that I have to enter the value (Campaign name) manually. Once I do this, I am able to see such original leads in the campaign. This is is subset of the total leads for the campaign.

 

Now I like to have a custom field in the Campaign object that prints the number of such original leads associated with the campaign object. I tried using the Formula & Rollup fields, but I could not find a way to count the original leads.

 

Thanks

George

Pradeep_NavatarPradeep_Navatar

I think, this can be achieved by writing a trigger. You can update the custom field (to count the original leads)  whenever a new lead is created for the campaign.

jkucerajkucera

Pradeep's correct - you'd need to use a trigger for this.  Here's code very similar to what you'd need where it counts the # campaigns on Lead.

 

Instead, you can swap Campaign for Lead in the triggers and filter by your custom field to get a count of leads generated by a campaign.

 

https://login.salesforce.com/?startURL=%2Fpackaging%2FinstallPackage.apexp%3Fp0%3D04t30000000jDgR
JabzJabz

We are looking for the same information as well.

 

I dont have admin access to look at the trigger. Can you pl. cut and paste it in the forum for me to get an understanding? I will then work with my admin to get it created/installed.

 

Thanks.

 

jkucerajkucera

 

trigger fakeLeadContactRSFIncrement on CampaignMember (before update) {
//This trigger assumes you have a custom field called Campaign Count of type Number on both lead & contact which will store the value

    Map<Id, Long> leadCounts=new Map<Id, Long>();
    Set<Id> leadIds=new Set<Id>();
    Map<Id, Long> contactCounts=new Map<Id, Long>();
    Set<Id> contactIds=new Set<Id>();

    AggregateResult[] contactResults=[SELECT ContactId, COUNT(Id)recordCount FROM CampaignMember WHERE Id IN :Trigger.new AND ContactId !=Null GROUP BY ContactId ];
    AggregateResult[] leadResults=[SELECT leadId, COUNT(Id)recordCount FROM CampaignMember WHERE Id IN :Trigger.new AND leadId !=Null GROUP BY LeadId ];

    For(AggregateResult contactResult:contactResults){
        contactCounts.put(String.valueOf(contactResult.get('ContactId')),Long.valueOf(String.valueOf(contactResult.get('recordCount'))));
        contactIds.add(String.valueOf(contactResult.get('ContactId')));
    }//for    
    
    For(AggregateResult leadResult:leadResults){
        leadCounts.put(String.valueOf(leadResult.get('LeadId')),Long.valueOf(String.valueOf(leadResult.get('recordCount'))));
        leadIds.add(String.valueOf(leadResult.get('LeadId')));
    }//for

    List<Contact> contacts=[SELECT Id, Campaign_Count__c FROM Contact WHERE Id IN: contactIds];
    List<Lead> leads=[SELECT Id, Campaign_Count__c FROM Lead WHERE Id IN: leadIds];

    for (Contact c:contacts){
        if (c.Campaign_Count__c==null) c.Campaign_Count__c=0;
        c.Campaign_Count__c+=contactCounts.get(c.Id);
    }    

    for (Lead l:leads){
        if (l.Campaign_Count__c==null) l.Campaign_Count__c=0;
        system.debug('Lead campaign count before: '+l.Campaign_Count__c);
        l.Campaign_Count__c+=leadCounts.get(l.Id);
        system.debug('Lead campaign count after: '+l.Campaign_Count__c);
    }    

    try{
        update contacts;
    }catch(Exception e){
        system.debug('Contacts could not be updated with new Campaign Count: '+e);
    }//try

    try{
        update leads;
    }catch(Exception e){
        system.debug('Leads could not be updated with new Campaign Count: '+e);
    }//try

}//trigger

 

trigger fakeLeadContactRSFDecrement on CampaignMember (before delete) {
//This trigger assumes you have a custom field called Campaign Count of type Number on both lead & contact which will store the value

    Map<Id, Long> leadCounts=new Map<Id, Long>();
    Set<Id> leadIds=new Set<Id>();
    Map<Id, Long> contactCounts=new Map<Id, Long>();
    Set<Id> contactIds=new Set<Id>();

    AggregateResult[] contactResults=[SELECT ContactId, COUNT(Id)recordCount FROM CampaignMember WHERE Id IN :Trigger.old AND ContactId !=Null GROUP BY ContactId ];
    AggregateResult[] leadResults=[SELECT leadId, COUNT(Id)recordCount FROM CampaignMember WHERE Id IN :Trigger.old AND leadId !=Null GROUP BY LeadId ];

    For(AggregateResult contactResult:contactResults){
        contactCounts.put(String.valueOf(contactResult.get('ContactId')),Long.valueOf(String.valueOf(contactResult.get('recordCount'))));
        contactIds.add(String.valueOf(contactResult.get('ContactId')));
    }//for    
    
    For(AggregateResult leadResult:leadResults){
        leadCounts.put(String.valueOf(leadResult.get('LeadId')),Long.valueOf(String.valueOf(leadResult.get('recordCount'))));
        leadIds.add(String.valueOf(leadResult.get('LeadId')));
    }//for

    List<Contact> contacts=[SELECT Id, Campaign_Count__c FROM Contact WHERE Id IN: contactIds];
    List<Lead> leads=[SELECT Id, Campaign_Count__c FROM Lead WHERE Id IN: leadIds];

    for (Contact c:contacts){
        if (c.Campaign_Count__c==null) c.Campaign_Count__c=0;
        c.Campaign_Count__c-=contactCounts.get(c.Id);
    }    

    for (Lead l:leads){
        if (l.Campaign_Count__c==null) l.Campaign_Count__c=0;
        system.debug('Lead campaign count before: '+l.Campaign_Count__c);
        l.Campaign_Count__c-=leadCounts.get(l.Id);
        system.debug('Lead campaign count after: '+l.Campaign_Count__c);
    }    

    try{
        update contacts;
    }catch(Exception e){
        system.debug('Contacts could not be updated with new Campaign Count: '+e);
    }//try

    try{
        update leads;
    }catch(Exception e){
        system.debug('Leads could not be updated with new Campaign Count: '+e);
    }//try

}//trigger