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)


  1. Well done, Jenna!

    Simple, yet powerful!!

    -- David B.

  2. Keeping Address Data clean is always a chore, this is a much welcome, simple, solution!

  3. Very nifty - I might have to use this one next week.

  4. What happens if there is an APO address typed A.P.O. - not completely unheard of depending on your business...

    I love the way you think through data quality and find ways to resolve it!

  5. Awesome AND screenshots!!!

    You're brilliant! :)

    I tend to over think a lot of stuff, though honestly it tends to pay off more than not! And my biggest peeve with my users requests is that they don't think about it at all sometimes, and don't realize that they will shortchange their experience or data. But then again, that's why I'm the admin and they're not...

  6. @eliz_beth: I'm glad you approve. :) I agree on the over-thinking. I'm the same way. But, it's something that makes us good admins!

  7. Awesome tip (even though I'm just now reading it). Now if only we can get all our business units to adopt the new data entry policy.