Create A Data Quality Progress Bar 🔋

The problem: 

In my last post I discussed the importance of identifying your data quality so you can plan how to tackle it. After reading that post someone asked me if I could combine my data quality score with a declarative progress bar instead of stars. The answer is YES and I’d love to show you how!

I contacted my guest blogger and author of the original piece “Create a Declarative Progress Bar“, James Beezley. In that post James was working with percentage fields so he helped me re-write the original formulas to work with my numerical field (data quality score). Massive thanks to James for helping me out with this piece.

In case you didn’t play along the first time, I’m going to run through creating the data quality score again and then creating the data progress bar. Skip ahead if you already have the data quality score set up.

The solution:

In this tutorial we’ll create 2 custom fields on the Lead object. The first field will be a number based on how many relevant fields are completed. The second will be a progress bar based on the previous score. So, the higher the score the fuller the progress bar.

1. Lets start by creating a formula field that gives our lead a total score out of 100, based on which fields are completed
2. Navigate to Setup > Object Manager > {Leads} > Fields & Relationships
3. Create a new field:
   • Data Type = Formula
   • Field Label = Data Quality Score
   • Formula Return Type = Number
   • Decimal Places = 0
   • Treat blank fields as zeroes
   • Enter a description and help text (so your users understand what fields are driving the score)
   • Enter the following formula but use ‘Insert’ to ensure you use the correct API name:

     IF(LEN( FirstName ) = 0, 0,15) 
     + IF(LEN( Company ) = 0, 0,15) 
     + IF(LEN( MobilePhone ) = 0, 0,15) 
     + IF(LEN( Email ) = 0, 0,15)
     + IF(LEN( Title  ) = 0, 0,10)
     + IF(LEN( City ) = 0, 0,10)
     + IF(LEN( Country ) = 0, 0,10)
     + IF(ISPICKVAL( Industry,""), 0,10)

What is this formula doing?

The LEN function returns the number of characters in a specified text string. In layman’s terms, it’s counting the characters in our field, so if First Name is empty then the number of characters will be 0.

Our formula then says, if the number of characters is 0 then add 0, otherwise add 15.

4. Make the field available to all relevant profiles and I recommend adding it to the page layout
5. Next, copy and save the following 2 images to your PC (you can right click on the image and choose ‘Save As’ to do this)

‘Complete’:

‘Incomplete’:

6. Go to Setup > Custom Code > Static Resources and upload these 2 images. I chose to call the 2 Static Resources “Green” and “Grey”

7. Once both Static Resources are saved, you’ll need to get the URL for the image; open each Static Resource and click on “View File”.

8. You will then see the image in the browser and can copy to clipboard the URL as per the highlighted text shown below (you’ll need these for your formula). Important note: do not copy anything before the “.com”!

9. Create another new field, this time to display the progress bar:
   • Data Type = Formula
   • Field Label = Data Quality Progress Bar
   • Formula Return Type = Text
   • Treat blank fields as blank
   • Enter a description and help text
   • Enter the following formula but use ‘Insert’ to ensure you use the correct API name:

     /* Create an opening border of grey on the far left, 18 px high and 2 px wide */ 
     IMAGE("/resource/1551439369000/Grey", "Grey", 18, 2)
     &
     /* Create a green bar to represent completeness of the task, 18 px high and of a width of the numerical field value */
     IMAGE("/resource/1551439352000/Green?", "Green", 18, Data_Quality_Score__c)
     &
     /* Create a grey bar to represent incompleteness of the task, 18 px high and of a width controlled by 100 - numerical field value */
     IMAGE("/resource/1551439369000/Grey", "Grey", 18, (100 - Data_Quality_Score__c))
     &
     /* Create a closing border of grey on the far right, 18 pixels high and 2 px wide */ IMAGE("/resource/1551439369000/Grey", "Grey", 18, 2)

What is this formula doing?

Our formula is checking the data quality score and then, depending on that score, displaying a progress bar where the green section indicates the data quality.

The first part of the formula creates a bar of grey which is 18px high and 2 wide (this is just a border).

The next part says assess the number field, and make a green bar as wide as the number is big. So if the number is 66, it’s 66 pixels wide (and 18 high).

The next part says subtract the number in the field from 100 to get the remainder. So if the data quality score is 66, it would be 34 (100 – 66) pixels wide of a grey box. Lastly, finish off with the border again.

Anything between /* */ is a note to explain what the next line of formula is doing. You could write the formula without this!

10. Make the field available to all relevant profiles and add to the page layout

---

Update!

I was asked if we could display the data quality score with the progress bar (as opposed to simply in another field). I’m pleased to report you can add the score to the end of the progress bar by referencing the data quality score in your formula.

Here is the updated formula:

/* Create an opening border of grey on the far left, 18 px high and 2 px wide */ 
IMAGE("/resource/1551439369000/Grey", "Grey", 18, 2)
&
/* Create a green bar to represent completeness of the task, 18 px high and of a width of the numerical field value */
IMAGE("/resource/1551439352000/Green?", "Green", 18, Data_Quality_Score__c)
&
/* Create a grey bar to represent incompleteness of the task, 18 px high and of a width controlled by 100 - numerical field value */
IMAGE("/resource/1551439369000/Grey", "Grey", 18, (100 - Data_Quality_Score__c))
&
/* Create a closing border of grey on the far right, 18 pixels high and 2 px wide */ IMAGE("/resource/1551439369000/Grey", "Grey", 18, 2)
&
/* Add a space, then display the data quality score */ 
" " & TEXT(Data_Quality_Score__c)

What have I added?

I’ve added 2 aspects to this formula. First I’ve added a space between our progress bar and the data quality score, otherwise it looks cramped. This is the bit that looks like & ” “.

I’ve then added our data quality score field. It’s a numeric field but needs to be text to work so I’ve added a TEXT function. This bit looks like & TEXT(Data_Qaulity_Score__c)

And the result!

---

The result:

You can add the new lead quality fields to lead records, list views and reports:

And easily identify leads with good or bad data quality:

Good 👍

Bad 👎

---

Gotchas & hints:

*You don’t have to use the same fields as me! Choose fields that are most important to your business.

*Don’t include fields that are always required/required on the page layout/required by validation rules! If you have different page layouts/record types you may need a more complex calculation to determine the data quality score based on record type.

*Your data quality score should add up to 100. You could change this to add up to 5 or 500 but my point is be sure you know what it adds up to and change the image formula accordingly.

*You could use ISBLANK instead of LEN in the data quality score formula.

*If you have bad data, as well as identifying it, start preventing it. I’m talking about required fields on the page layout, validation rules and more. Otherwise bad data might flow in faster than you can fix it…

---

Useful links:

Salesforce Help: Sample Image Link Formulas

Shell Black: Using Image Formula Fields

Shell Black: Create a Data Quality Lead Score Using Formulas

AppExchange: Lead Scoring by Salesforce Labs

AppExchange: Data Quality Analysis Dashboards by Salesforce Labs

Trailhead: Data Quality

---

Resources:

Data Quality Formula:

IF(LEN( FirstName ) = 0, 0,15) 
+ IF(LEN( Company ) = 0, 0,15) 
+ IF(LEN( MobilePhone ) = 0, 0,15) 
+ IF(LEN( Email ) = 0, 0,15)
+ IF(LEN( Title  ) = 0, 0,10)
+ IF(LEN( City ) = 0, 0,10)
+ IF(LEN( Country ) = 0, 0,10)
+ IF(ISPICKVAL( Industry,""), 0,10)

Data Quality Progress Bar Formula:

/* Create an opening border of grey on the far left, 18 px high and 2 px wide */ 
IMAGE("/resource/1551439369000/Grey", "Grey", 18, 2)
&
/* Create a green bar to represent completeness of the task, 18 px high and of a width of the numerical field value */
IMAGE("/resource/1551439352000/Green?", "Green", 18, Data_Quality_Score__c)
&
/* Create a grey bar to represent incompleteness of the task, 18 px high and of a width controlled by 100 - numerical field value */
IMAGE("/resource/1551439369000/Grey", "Grey", 18, (100 - Data_Quality_Score__c))
&
/* Create a closing border of grey on the far right, 18 pixels high and 2 px wide */ IMAGE("/resource/1551439369000/Grey", "Grey", 18, 2)

---

Written by Christine Marshall & James Beezley