{Update} Val Rule to Prevent ‘Dear John L.’ has a Glitch – Fixed! #DataQuality @Salesforce

A few months ago, I shared a validation rule for Contacts to prevent sending emails with “Dear John L.”

My data quality check ensured that users didn’t enter a middle name in the first name field.

But, when I was doing some updates with the Excel Connector yesterday, I realized that my validation rule had a glitch.

It was correctly preventing middle names in the first name field, but it was also throwing the same error when they entered a name that happened to be abbreviated (such as “A. R.”). Or in this case, I was getting the error when I attempted to update the records en masse.

So, I’ve updated my rule to allow abbreviated first names by adding a function to my formula to cause it to fire only when the length of the field is greater than 5 characters.

This accounts for names where a space exists between the acronym or vice versa.

Here is the original error condition formula:

And here’s the updated one:

If you have a more effective way to accomplish this, please do share in the comments!

Related Posts

• A Simple Rule to Prevent ‘Dear John L.’ in Emails - #DataQuality, @Salesforce

Jenna Baze is a rather geeky girl with average social skills and an affinity for: Google Apps, Salesforce.com, Pinterest, natural light photography and finding creative ways to avoid doing dishes (which includes updating a handful of blogs).
Email: rathergeeky@gmail.com
Twitter: @RatherGeeky
Facebook: Rather Geeky Tips

The @Salesforce Community Comes to the Rescue…. Again

I began my day a tad frustrated.

At the end of each month, I have to manually update the criteria in 4 list views so that I can send out a reminder email to users with a link to update records for the current fiscal period.

Background

Since a calendar month may not match a fiscal month exactly, we created a trigger to populate Fiscal Month and Fiscal Year (custom text fields) based on Opportunity Close Date and our custom fiscal year settings. Shamrock CRM developed this trigger for us and it’s working great!

However, I’m still left with the task of updating each list view manually like this:

After a year and a half of doing this, I decided that there had to be a better way. I tweeted my question.

Inspiration for a Solution

@hammnick, @knthornt and @michaelforce replied with suggestions.

I learned that you can filter based on Fiscal Quarter quite easily. Just use Close Date = THIS QUARTER. But, when it comes down to showing records for the current Fiscal Period, there is no date keyword to use. (Believe me, I tried. THIS PERIOD just gets an error.)

@michaelforce and @knthornt’s suggestions gave me an idea.

I create a new formula field that displays either “Current Fiscal Period”, “Previous Fiscal Period” or “Future Fiscal Period.” It compares the current date with 2 other custom fields that our trigger updates: Last_Day_of_Fiscal_Period__c and First_Day_of_Fiscal_Period__c. I created these fields and added them to the trigger, but hadn’t used them effectively in logic. I’m glad we have the data available though!

Here’s the formula.

And a closer look:

IF(
   AND(TODAY()>=First_Day_of_Fiscal_Period__c,TODAY()<=Last_Day_of_Fiscal_Period__c),"Current Fiscal Period",
      IF(TODAY()> Last_Day_of_Fiscal_Period__c,"Previous Fiscal Period",
         IF(TODAY()<Last_Day_of_Fiscal_Period__c,"Future Fiscal Period","unknown")
)
)

 

Side note: I believe that I covered all scenarios, but I added the “unknown” text just in case.

It works!

Now, I don’t have to manually update my list views each month. I just filter based on this formula which does the work for me.

And the complex criteria in this other list view is greatly simplified!

Before

After

This will save me a bit of time (and potential headaches) each month.

Thanks again guys! (Gotta love the Salesforce Twitter community.)

Jenna Baze is a rather geeky girl with average social skills and an affinity for: all things Google, Remember the Milk, Salesforce.com, Inbox Zero, Etsy, natural light photography and finding creative ways to avoid doing dishes (which includes updating a handful of blogs).
Email: rathergeeky@gmail.com
Twitter: @RatherGeeky
Facebook: Rather Geeky Tips

Wal*Mart Wins: How I Scanned 119 Photos in 1 Hour for $2.99 (and saved hundreds of dollars)

I was on a mission: get 119 hardcopy photos scanned onto disc in two hours.

I needed to get the pictures (spanning the life of my nana) into a format that my family could review before passing the pictures on to the funeral home. I don't have a scanner, so I knew I'd have to pay for the service.

My first stop was Office Depot. They told me that it would cost an additional $2.99 for hand setting...each picture. They could group several photos on each page, but even if they put 4 images on a page, I would still have to pay more than $75 just for someone to put the photos on the scanner. I asked if they had any self-scanners. Nope. I left.

Next, I went to Ritz Camera. The cost there would be 19 cents per picture, but they wouldn't have them finished for another day.

I checked in at a local shipping and mailing store. When I saw the cost of $3 per scan, I walked out.

By this time, I was getting a bit antsy. I was eating into my time to actually get the photos scanned.

I decided to try one last place: Wal*Mart. The lady at the photo desk was quite helpful. She showed me how to use the scanning machine and I got to work. Fortunately, I could scan up to 5 pictures at a time and the machine would automatically split them into individual files. I figured I'd spend at least 19 cents per picture.

After about an hour of scanning the pictures, I was done. 5 minutes after I removed my last photo from the scanner, I had a disc in my hands. When I went to the register to pay, my total was: $2.99.

I did a happy dance and left. I got back home just in time to give the disc to my mom, who was on her way to check in with the rest of the family.

Thanks for saving the day Wal*Mart.

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.

Yay.

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)

A Simple Rule to Prevent ‘Dear John L.’ in Emails - #DataQuality, @Salesforce

I have a thing about naming conventions and consistent data.

I often see Lead and Contact records where the First Name is something like “John L.”, even though I have added a Middle Initial field.

I personally would prefer not including a middle name at all, but it is something that is widely accepted in our industry. So, I’m doing my best to keep my users happy.

Here’s the simple rule that should effectively nip this in the bud.

It works the same for Leads and Contacts.

Yay for data quality!

Update

You can also prevent users from entering “John T” (without a period) by changing the error condition formula to this:

OR(
   CONTAINS(FirstName, "."),
   CONTAINS(RIGHT(FirstName,2)," ")
)

This formula runs if either:

1. There’s a period in the First Name field, or
2. The second character from the end of the First Name field is a space.

Thanks @hak_a_tak for the suggestion!

Admin Journal: Preventing My @Salesforce Users from Including Time-Specific Information in Description Fields with a #Simple Validation Rule

I’ve noticed while doing data cleaning that some users add date-specific information to Description fields on Accounts, Leads or Contacts.

Why This is Bad

No one may notice ‘must follow up by 12/11/2007 or earth will explode’ in the Description of a record such as an Account. That could be bad news.

An Easy Fix (Sorta)

To encourage users to log this information in the most efficient way, I an create a validation rule that looks for keywords in the Description field of a specific object, such as Account.

I can customize the rule based on the typical phrases that I know my users enter, such as ‘follow up on’.

I like to include a detailed error message so that the user will have a good idea of what their next step should be.

Error: You have included a 'Follow up' task in the Description field, where it may be overlooked. Please remove that reference from the Description field and add it instead as a related task.

I gave it a quick test and it worked.

I’m sure there are plenty of other ways to accomplish the same thing, but this simple rule is one way to gently herd my users in the right direction.

 

Additional Resources

• Haven’t heard of Salesforce? Find out about it here.

 

Jenna Baze is a rather geeky girl with average social skills and an affinity for: all things Google, Remember the Milk, Salesforce.com, Inbox Zero, Etsy, natural light photography and finding creative ways to avoid doing dishes (which includes updating a handful of blogs).
Email: rathergeeky@gmail.com
Twitter: @RatherGeeky
Facebook: Rather Geeky Tips

Just for kicks: Gmail Conversation View

I’ve heard from some Outlook holdouts that they just can’t give up Conversation View and switch to Gmail.

I decided to turn off Conversation View in Gmail.

My inbox normally looks like this:

When I turned Conversation View off, it looked like this:

Really? People prefer this? Ick. I had to turn it back.