+ Start a Discussion
Sean ClarkSean Clark 

Formula field isn't working as expected

Hi All, 

I have the following formula (text) field below. However it isn't working as i expect it to.

IF( 
Candidate__r.Company_Group_Type_Text__c == 'Potentials', 
IMAGE('/servlet/servlet.FileDownload?file=0151v000003E7qF', ''), 

IF( 
Candidate__r.Company_Group_Type_Text__c == 'Trusted', 
IMAGE('/servlet/servlet.FileDownload?file=0151v000003E7qZ',''), 
IF( 
Candidate__r.Company_Group_Type_Text__c == 'Associate', 
IMAGE('/servlet/servlet.FileDownload?file=0151v000003E7qK', ''), 
IF(
Candidate__r.Company_Group_Type_Text__c == 'Influencer', 
IMAGE('/servlet/servlet.FileDownload?file=0151v000003E7qU', ''),
IF(
Candidate__r.Contact_ID__c  == '0030Y00001JQUHr', 
IMAGE('/servlet/servlet.FileDownload?file=0151j000000DoLm', ''),  
IF(
Source__c == 'Vacancy Lead' &&  Source_Date__c  >= TODAY() - 7 &&  Source_Date__c  <=  TODAY(), 
IMAGE('/servlet/servlet.FileDownload?file=0151j000000Dp1y', ''),  
IF(
Source__c == 'Vacancy Lead' &&  Source_Date__c  >= TODAY() - 14 &&  Source_Date__c  <=  TODAY() - 8, 
IMAGE('/servlet/servlet.FileDownload?file=0151j000000Dp3f', ''),  
IF(
Source__c == 'Vacancy Lead' &&  Source_Date__c  >= TODAY() - 21 &&  Source_Date__c  <=  TODAY() - 15, 
IMAGE('/servlet/servlet.FileDownload?file=0151j000000Dp3G', ''),  
IF(
Source__c == 'Vacancy Lead' &&  Source_Date__c  >= TODAY() - 28 &&  Source_Date__c  <=  TODAY() - 22, 
IMAGE('/servlet/servlet.FileDownload?file=0151j000000Dp3V', ''),  
IF(
Source__c == 'Vacancy Lead' && Source_Date__c >= TODAY() - 7 && Source_Date__c  <=  TODAY() && (Candidate__r.Company_Group_Type_Text__c == 'Influencer' || Candidate__r.Company_Group_Type_Text__c == 'Potentials' ||Candidate__r.Company_Group_Type_Text__c == 'Trusted' || Candidate__r.Company_Group_Type_Text__c == 'Associate'),
IMAGE('/servlet/servlet.FileDownload?file=0151j000000Dp1y', ''),
IF(
Source__c == 'Vacancy Lead' && Source_Date__c >= TODAY() - 14 && Source_Date__c  <=  TODAY() - 8 && (Candidate__r.Company_Group_Type_Text__c == 'Influencer' || Candidate__r.Company_Group_Type_Text__c == 'Potentials' ||Candidate__r.Company_Group_Type_Text__c == 'Trusted' || Candidate__r.Company_Group_Type_Text__c == 'Associate'),
IMAGE('/servlet/servlet.FileDownload?file=0151j000000Dp3f', ''),
IF(
Source__c == 'Vacancy Lead' && Source_Date__c >= TODAY() - 21 && Source_Date__c  <=  TODAY() - 15 && (Candidate__r.Company_Group_Type_Text__c == 'Influencer' || Candidate__r.Company_Group_Type_Text__c == 'Potentials' ||Candidate__r.Company_Group_Type_Text__c == 'Trusted' || Candidate__r.Company_Group_Type_Text__c == 'Associate'),
IMAGE('/servlet/servlet.FileDownload?file=0151j000000Dp3G', ''),

IF(
Source__c == 'Vacancy Lead' && Source_Date__c >= TODAY() - 28 && Source_Date__c  <=  TODAY() - 22 && (Candidate__r.Company_Group_Type_Text__c == 'Influencer' || Candidate__r.Company_Group_Type_Text__c == 'Potentials' ||Candidate__r.Company_Group_Type_Text__c == 'Trusted' || Candidate__r.Company_Group_Type_Text__c == 'Associate'), 
IMAGE('/servlet/servlet.FileDownload?file=0151j000000Dp3V', ''),

NULL 
)))))))))))))

The Bold section shows that if the source is 'Vacancy Lead' AND the source date is within the last week AND if company group type text equals Trusted OR Potentials OR Associate OR Influencer. I have a record that meets this criteria but isn't returning the right document.

It is returning the document that is related to the criteria of the underlined section - which is if the Company group type text equals potentials

Hope someone can help!
Best Answer chosen by Sean Clark
Sean ClarkSean Clark
Thank you Dimond, after your message i realised that i could change the underlined section to the below which will work perfectly.

IF( 
Candidate__r.Company_Group_Type_Text__c == 'Potentials' && ISBLANK(Source__c), 
IMAGE('/servlet/servlet.FileDownload?file=0151v000003EEkd', ''), 
IF( 
Candidate__r.Company_Group_Type_Text__c == 'Trusted' && ISBLANK(Source__c), 
IMAGE('/servlet/servlet.FileDownload?file=0151v000003EEn3',''), 
IF( 
Candidate__r.Company_Group_Type_Text__c == 'Associate' && ISBLANK(Source__c), 
IMAGE('/servlet/servlet.FileDownload?file=0151v000003EEnc', ''), 
IF( 
Candidate__r.Company_Group_Type_Text__c == 'Influencer' && ISBLANK(Source__c), 
IMAGE('/servlet/servlet.FileDownload?file=0151v000003EEl7', ''), 

All Answers

DimondDimond
Does that record meet the underlined condition?
DimondDimond
You prob should go with most restrictive to least
Sean ClarkSean Clark
Thank you Dimond, after your message i realised that i could change the underlined section to the below which will work perfectly.

IF( 
Candidate__r.Company_Group_Type_Text__c == 'Potentials' && ISBLANK(Source__c), 
IMAGE('/servlet/servlet.FileDownload?file=0151v000003EEkd', ''), 
IF( 
Candidate__r.Company_Group_Type_Text__c == 'Trusted' && ISBLANK(Source__c), 
IMAGE('/servlet/servlet.FileDownload?file=0151v000003EEn3',''), 
IF( 
Candidate__r.Company_Group_Type_Text__c == 'Associate' && ISBLANK(Source__c), 
IMAGE('/servlet/servlet.FileDownload?file=0151v000003EEnc', ''), 
IF( 
Candidate__r.Company_Group_Type_Text__c == 'Influencer' && ISBLANK(Source__c), 
IMAGE('/servlet/servlet.FileDownload?file=0151v000003EEl7', ''), 
This was selected as the best answer