Feeding the Impostor with a Validation Rule

There it was, a seemingly simple user request. Prevent users from putting a “#” in the street address for both the shipping and billing address fields, and no “PO Boxes” in the shipping address. I was all excited, a chance to break out a validation rule!

Why are we doing this? We found that downstream, “#” were causing problems with address geolocation. Data examples like Suite #123 or Apartment #432 caused the geolocation software to error out. Likewise, the mapping software saw PO Boxes and decided to pinpoint the location in the city center, not a great help when you are locating the impact of economic programs in the state.

Here’s what I wanted to accomplish:

In field ShippingStreet, do not allow a record to be saved if it contains any of the following:

#, PO Box, P.O. Box, POBox, P.O.Box

In field BillingStreet, do not allow a record to be saved if it contains:

#

Simple enough, but then, Impostor Syndrome kicked in. What’s Impostor Syndrome? Simply, it’s the voice in my head that says, “I’m not good enough. I can’t do this on my own. Others know way more about Salesforce than I do.”

I’m somewhat new to working on things like validation rules. I’ve been architecting solutions that involve Salesforce for a while now, but only recently have taken on more administrative tasks. Previously, I’d just spec out a solution with a validation rule, and then hand off to development to implement. The exact syntax, didn’t really matter. Until now.

Trying to determine the syntax of what I wanted, took me quite a bit of time. I kept trying to build a simple IF statement, but in a validation rule, the formula is already an IF statement. Wrapping my brain around the difference, I then tried to figure out how to pass multiple criteria to the contains function. Turns out, you can’t.

Sometimes, Salesforce doesn’t work like I think it should.

At this point, I’m pretty deep into research on what I thought was something pretty simple. And then, more Impostor Syndrome. I start getting data-type mismatch errors. WHAT AM I DOING WRONG NOW?! Aren’t these fields just simple text fields?! The error says I’m using a field type of location. More research on how to convert location field data to text. You can’t.

Lesson 1: Step back and reevaluate. I needed to step away from the computer. All it took, was a few minutes. I’ve been in IT for a long time. It doesn’t matter if you’re building a Windows server, configuring a network device, or working with Salesforce, when everything points to “It should be working,” you’ve probably just forgotten a damn checkbox, i.e. something simple. Coming back, I immediately realized my mistake. Somehow, I had swapped ShippingStreet with ShippingAddress and BillingStreet with BillingAddress.

Sometimes, it’s just a damn checkbox.

While I realize everyone makes mistakes like this, it sure helps feed the Impostor Syndrome.

Lesson 2: All of us are Impostors — well, maybe not an impostor, but we experience these feelings of doubt. It was surprising to me when a mentor of mine went to Amy Oplinger ☁’s session on Impostor Syndrome at Midwest Dreamin’, and came back talking about the tips he’d gained. Here’s someone who I’ve always looked up to, has successfully run multiple large IT shops, won awards, and is respected by peers and direct reports, and yet still experiences Impostor Syndrome. I never knew he sometimes questioned himself.

Lesson 3: The validation rule. I’m sure there’s a better way to write this, but here’s how I thought I could solve my validation needs:

OR(
contains(ShippingStreet, “#”),
contains(ShippingStreet, “P.O. Box”),
contains(ShippingStreet, “POBox”),
contains(ShippingStreet, “P.O. “),
contains(ShippingStreet, “PO Box”),
contains(BillingStreet, "#")
)

Lesson 4: We’re not done yet! Be sure to keep evaluating your solutions. The above code snippet technically accomplishes what I wanted. However, formulas and validation rules in Salesforce are case specific. What if my users, and no doubt they will, use different capitalization? Off to modify the validation rule to normalize the data.

OR(
contains(ShippingStreet, “#”),
contains(upper(ShippingStreet), “P.O. BOX”),
contains(upper(ShippingStreet), “POBOX”),
contains(upper(ShippingStreet), “P.O. “),
contains(upper(ShippingStreet), “PO BOX”),
contains(BillingStreet, “#”)
)

Ok, data normalized. Thinking again, are there any processes that might need to run on the data where I don’t want this validation? We have a number of integration pieces, have partners who share data, and Data.com Clean, all of which could be trying to modify a record, even if it’s not touching address data. Yep, I don’t want records to not be saved if one of the above processes are working on it. One more piece, verify that it’s our Standard User Profile making this change:

AND($Profile.Name = “Company Standard User”,
OR(
contains(ShippingStreet, “#”),
contains(upper(ShippingStreet), “P.O. BOX”),
contains(upper(ShippingStreet), “POBOX”),
contains(upper(ShippingStreet), “P.O. “),
contains(upper(ShippingStreet), “PO BOX”),
contains(BillingStreet, “#”)
))

Finally, a complete and working solution.

Footnote: Talk about Impostor Syndrome, throughout this entire piece, I continued to spell “Impostor” with an “E — Imposter,” despite constant correction, my brain couldn’t get it right. Gotta keep the Impostor away from my writing too!