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
Synthia BeauvaisSynthia Beauvais 

Custom Formula Field - Data Quality Scores

I am trying to create a data quality score on the Contact object for the fields listed in the chart below. I am not quite sure how to calculate the weight. I have started the formula but I am not getting the correct "Actual Value".
  
IF( 
OR( 
ISBLANK(Phone), 
OR( 
CONTAINS(Phone, '000-0000') , 
CONTAINS(Phone, '111-1111') , 
CONTAINS(Phone, '222-2222') , 
CONTAINS(Phone, '333-3333') , 
CONTAINS(Phone, '444-4444') , 
CONTAINS(Phone, '555-5555') , 
CONTAINS(Phone, '666-6666') , 
CONTAINS(Phone, '777-7777') , 
CONTAINS(Phone, '888-8888') , 
CONTAINS(Phone, '123-4567') , 
CONTAINS(Phone, '456-7890')
) 
), 0, 1 
) 

+ 
IF( ISBLANK( MailingStreet ), 0, 1)+ 
IF( ISBLANK( MailingCity ), 0, 1)+ 
IF( ISBLANK( MailingState ), 0, 1)+ 
IF( ISBLANK( MailingCountry ), 0, 1)+ 
IF( ISBLANK( MailingPostalCode ), 0, 1) +
IF( ISBLANK(FirstName ) ,0,1) + 
IF( ISBLANK( LastName ) , 0, 1)+ 
IF( ISBLANK( Email ) , 0, 1)
/9



User-added image

MAX SCORE: 5
MIN SCORE: 0

Thanks in advance! 


 
Best Answer chosen by Synthia Beauvais
Rajan Patel 8Rajan Patel 8
Try something like this.

((IF( 
OR( 
ISBLANK(Phone), 
OR( 
CONTAINS(Phone, '000-0000') , 
CONTAINS(Phone, '111-1111') , 
CONTAINS(Phone, '222-2222') , 
CONTAINS(Phone, '333-3333') , 
CONTAINS(Phone, '444-4444') , 
CONTAINS(Phone, '555-5555') , 
CONTAINS(Phone, '666-6666') , 
CONTAINS(Phone, '777-7777') , 
CONTAINS(Phone, '888-8888') , 
CONTAINS(Phone, '123-4567') , 
CONTAINS(Phone, '456-7890')
) 
), 0, 1 
) 
* (20))
+ 
(IF( ISBLANK( MailingStreet ), 0, 1) * (3.333))+ 
(IF( ISBLANK( MailingCity ), 0, 1)* (3.333)) + 
(IF( ISBLANK( MailingState ), 0, 1)*(5))+ 
(IF( ISBLANK( MailingCountry ), 0, 1)*(5))+ 
(IF( ISBLANK( MailingPostalCode ), 0, 1)*(3.333)) +
(IF( ISBLANK(FirstName ) ,0,1) *(10))+ 
(IF( ISBLANK( LastName ) , 0, 1) *(25))+ 
(IF( ISBLANK( Email ) , 0, 1) *(25))
)/20

All Answers

Rajan Patel 8Rajan Patel 8
Try something like this.

((IF( 
OR( 
ISBLANK(Phone), 
OR( 
CONTAINS(Phone, '000-0000') , 
CONTAINS(Phone, '111-1111') , 
CONTAINS(Phone, '222-2222') , 
CONTAINS(Phone, '333-3333') , 
CONTAINS(Phone, '444-4444') , 
CONTAINS(Phone, '555-5555') , 
CONTAINS(Phone, '666-6666') , 
CONTAINS(Phone, '777-7777') , 
CONTAINS(Phone, '888-8888') , 
CONTAINS(Phone, '123-4567') , 
CONTAINS(Phone, '456-7890')
) 
), 0, 1 
) 
* (20))
+ 
(IF( ISBLANK( MailingStreet ), 0, 1) * (3.333))+ 
(IF( ISBLANK( MailingCity ), 0, 1)* (3.333)) + 
(IF( ISBLANK( MailingState ), 0, 1)*(5))+ 
(IF( ISBLANK( MailingCountry ), 0, 1)*(5))+ 
(IF( ISBLANK( MailingPostalCode ), 0, 1)*(3.333)) +
(IF( ISBLANK(FirstName ) ,0,1) *(10))+ 
(IF( ISBLANK( LastName ) , 0, 1) *(25))+ 
(IF( ISBLANK( Email ) , 0, 1) *(25))
)/20
This was selected as the best answer
dawn.kellerman-smithdawn.kellerman-smith
I love this post and am trying to re-purpose for our org, however am running into and extra ) error.  Could you please review?
((IF( 
OR( 
ISBLANK(Phone), 
OR( 
CONTAINS(Phone, '000-0000') , 
CONTAINS(Phone, '111-1111') , 
CONTAINS(Phone, '222-2222') , 
CONTAINS(Phone, '333-3333') , 
CONTAINS(Phone, '444-4444') , 
CONTAINS(Phone, '555-5555') , 
CONTAINS(Phone, '666-6666') , 
CONTAINS(Phone, '777-7777') , 
CONTAINS(Phone, '888-8888') , 
CONTAINS(Phone, '123-4567') , 
CONTAINS(Phone, '456-7890')
)
), 0, 1
) 
* (15))
+
(IF( ISBLANK( MailingStreet ), 0, 1) * (3.333))+ 
(IF( ISBLANK( MailingCity ), 0, 1)* (3.333)) + 
(IF( ISBLANK( MailingState ), 0, 1)*(3.333))+ 
(IF( ISBLANK( MailingCountry ), 0, 1)*(3.333))+ 
(IF( ISBLANK( MailingPostalCode ), 0, 1)*(3.333)) +
(IF( ISBLANK(FirstName ) ,0,1) *(10))+ 
(IF( ISBLANK( LastName ) , 0, 1) *(20))+ 
(IF( ISBLANK( Email ) , 0, 1) *(20))+
(IF( ISPICKVAL( LeadSource, "" ) , 0, 1)) *(3.333))+
(IF( ISBLANK( AccountId ) , 0, 1) *(15))
)/20
paulravinderpaulravinder
Hi There, Were you able to implement it? I'm proposing same for my organistaion. Curious to know how did it worked for you?