Friday, April 29, 2011

A Quick Fix to Prevent Getting P.O.’d - #DataQuality @Salesforce

As a Salesforce admin, I am fairly attentive to data quality.

It seems that inconsistencies and typos abound in address-related information.

So, I have a few automated tricks up my sleeve to keep our data clean.

The Problem

According to the USPS address standards, the proper abbreviation for post office box is:

PO BOX ####

Many users when entering these types of addresses, type “P.O.” This is unnecessary.

To help enforce consistent standards, I have set up a workflow rule that does the following:

  • (Account: Billing Street CONTAINS P.O.,P. O.,p.o.) OR (Account: Shipping Street CONTAINS P.O.,P. O.,p.o.)

I have two field updates connected with the rule. They are:

  1. SUBSTITUTE( BillingStreet , "P.O.", "PO")
  2. SUBSTITUTE( ShippingStreet , "P.O.", "PO")

I have created another rule to process Contact addresses in the same way.

The nice thing about using a workflow rule with a field update is that the user doesn’t have to take any action. The data is tidied up without either of us having to lift a finger.


Update (as of 4.29.2011)

@eliz_beth had a question about how to include if the street address has “A.P.O” in it as well. I haven’t run across this in my org, but I think the following modification would work.

Step One: Update the Workflow Rule criteria. Including “A.P.O” is a bit redundant, since it would find it using “P.O.” as well. But, I figured I would include it anyway.


Step Two: Update each of your field updates (for BillingStreet and ShippingStreet) to include the following logic.


Since the IF statement first evaluates whether the BillingStreet contains “A.P.O”, it should perform correctly by replacing “A.P.O” with “APO”. If the string does not contain “A.P.O.”, only then will it proceed to the next criteria. If it contains “P.O”, it will replace it with “PO”. This prevents it from updating “A.P.O” to “A.PO”.

I haven’t tested this, but I’m pretty sure it will work as expected. :) (famous last words)