Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
ShowAll Questionssorted byDate Posted
Synthia 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```

MAX SCORE: 5
MIN SCORE: 0

Best Answer chosen by Synthia Beauvais
Rajan 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

Rajan 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-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
paulravinder
Hi There, Were you able to implement it? I'm proposing same for my organistaion. Curious to know how did it worked for you?