function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Dips78Dips78 

Formula field on Campaign Member object

Hi Folks,

 

I have a custom field (Score) on both my contact and lead objects that I want to includein campaign member reports. Since the report interface does not allow me include custom fields, I need to create a custom formula field (let's call it CMScore) on the Camapign Member object that pulls this data from the lead/ contact field. Can someone explain to me what syntax to use for the formula- i'm a newbee and nto familiar with using formulae.

 

This is essentially what it needs to do:

 

CMScore=

Lead.Score if CampaignMember is a Lead

Contact.Score if CamapignMember is a Contact

 

Any help is greatly appreciated!

 

Thanks!

 

Best Answer chosen by Admin (Salesforce Developers) 
Shannon HaleShannon Hale

@ashishkr is correct:

 

IF(
  ISBLANK(ContactId), /* Check whether there's a ContactId value */
  Lead.Score__c,      /* No ContactId: it's a Lead, so get Lead score */
  Contact.Score__c    /* ContactId: it's a Contact, so get Contact score */
)

 

The one drawback to this approach is that it counts twice toward the unique relationships per object limit.

All Answers

Jeff MayJeff May

You will need to use a trigger for this, since the CampaignMember object only has the ID of the Contact/Lead, not the related record.  The easiest trigger would be to create 2 Lookup fields on the CampaignMember, 1 called RelatedContact, the other called RelatedLead.  The trigger will just set the ContactID into the RelatedContact field, and LeadID to the RelatedLead field.  Then, you'll have related records on the Campaign Member object and you can write a formula like the following:

 

RelatedContact__r.Score__c
ashishkrashishkr

You can use cross-object formula for this:

 

if( NOT( ISBLANK( ContactId ) ), Contact.Email , Lead.Email )

 

Make sure you select treat blank field as blank while creating this formula.

 

This formula has the reasonable assumption that a CampaignMember will either be a Lead or a Contact.

Instead of the Email, you can use your custom score field as Contact.Score__c.
Shannon HaleShannon Hale

@ashishkr is correct:

 

IF(
  ISBLANK(ContactId), /* Check whether there's a ContactId value */
  Lead.Score__c,      /* No ContactId: it's a Lead, so get Lead score */
  Contact.Score__c    /* ContactId: it's a Contact, so get Contact score */
)

 

The one drawback to this approach is that it counts twice toward the unique relationships per object limit.

This was selected as the best answer
ashishkrashishkr
Kudos for bringing out the "unique relationships per object limit" into light.
Dips78Dips78

Thank you all for your responses! I was able to create the field and get my report working. This is my first time using the discussion boards and it has been a great experience. I'm sure I'll be posting more questions soon!:)
Shannon HaleShannon Hale

Glad we could help! Please be sure to "Accept as Solution" the answer that helped you, so that others can see that the question was resolved and may benefit. It's also nice to give kudos if someone really helped you out.
TVPPAMarketingTVPPAMarketing
Hello! I have a similar question. My accounts include a "Type" field with particular drop-down options. I already have a lookup formula in contacts that looks at and populates a field using whatever was in teh accounts field. I need a formula within my Campaign Members fields that looks up this value from, I assume, the contact. I can't seem to figure it out. If the contact is there, I need it to populate the field based on the matching account/contact field. If there's no match, it should remain empty.

I've played with the formula Shannon provided but can't seem to figure it out. Any help?
viswanadham Aviswanadham A
HI

this condtion is also working .

text(Lead.picklistvalue)<>text(Contact.picklistvalue)

thanks
VIswa
Michelle McMillianMichelle McMillian
Hello. I have a similar question as the original. I have a custom field (HubSpot Lead Score) on both contact and lead objects that I want to include in campaign member reports. I created a Campaign Member Custom Field (lHubSpot Score), but it only pulls in the info from the first part of the sequence - either from the contact or lead, depending on which I list first in the formula. How can it get it to pull from either the contact or the lead?
 
This is what I am using:
if( NOT( ISBLANK( Email ) ), Text (Contact.Hubspot_Lead_Score__c) , Text (Lead.Hubspot_Lead_Score__c )) - this pulls the info from the contact but not the lead - or-
if( NOT( ISBLANK( Email ) ), Text (Lead.Hubspot_Lead_Score__c) , Text (Contact.Hubspot_Lead_Score__c )) - this pulls it from the lead but not the contact

Thank you!