0011500001cnVrp

Look familiar?

www.na7.salesforce.com/0011500001cnVrp

How about now?

The beloved 15-character ID.

It’s easy to copy and paste, takes you straight to the record you’re looking for, can be pulled into reports, and is used in a myriad of other ways. Unfortunately, this beauty can also be your downfall.

Remember playing kickball as a kid and calling for a mulligan because you kicked a lame grounder back to the pitcher with 2 outs and the bases loaded?

Ever have one of those moments at work? They’re the worst. And in real life, there are no re-dos.

Tell me if this rings a bell for you.

You’re wrapping up a massive data project. You click upload for the last time and the error log comes back. No errors — whew!

With a big grin on your face, you push back from your desk, and give yourself a pat on the back for a job well done. Hey, you deserve it. Maybe you’ll stretch your legs a bit and grab a cup of coffee.

Then it hits: BAM!

Emails start flooding your inbox.

Something’s wrong.

“I can’t see my accounts anymore. I literally lost all of them. HELP!”

“My contacts on Account ABC all disappeared. What’s going on?!”

“Why do my accounts show up with a NY address all of a sudden?”

Hopefully you’ve managed to avoid this debacle entirely, or maybe you’ve had the benefit of learning from a colleague’s mistakes. Chances are though, that you’ve been front and center at least once.

I call this death by 15-characters.

It’s the ultimate data disaster and the ultimate embarrassment. Been there, done that. It sucks.

It’s one of those things you don’t even realize is an issue until it’s a HUGE issue. And the way most people find out about it is to screw up a large data set and then have to go through the pain of finding the errors and unraveling everything that was done.

No bueno.

Fill me in

As a standard, Salesforce uses a 15-character case-sensitive ID to identify records. That means that Salesforce pays attention to capitalization and thus, recognizes all of these as being unique even though they contain the same characters:

• 0011500001cnVrp
• 0011500001cnVRp
• 0011500001cnVrP

That’s all fine and dandy when you’re working in Salesforce, but what happens when you export your data to another tool, like Excel (or Access), to start working with it?

Disaster strikes — that’s what happens.

In Excel, the 3 values above are all viewed as being exactly the same. That’s because Excel doesn’t care about capitalization.

It also means your VLOOKUPs are doomed. Duhn-duhn-duhn…

Let me give you an example:

Let’s say you pulled a report on accounts in your database and forgot to add in the billing state, which you’ll later use to populate missing contact data. At this point, you’ve already massaged the account data a bit so you don’t want to export a brand new report and start over. What can you do?

A quick way to solve this problem is to pull a new report with the account IDs and billing state information. From there, you can use a VLOOKUP to match the values and add the states in where they belong.

Here’s what it would look like:

Step 1: Accounts with missing state

Step 2: Accounts with state

Step 3: VLOOKUP gone awry

Uh-oh.

Umm…what’s going on?

So, here’s what’s up. When you use a VLOOKUP, Excel searches for a match and returns the first value it finds.

In this case, the first account ID value that matches returns GA as the state. And since Excel is case-insensitive, it does that for all 3 accounts even though they’re 3 unique records, ultimately leaving the NY and CA accounts in the dust and leaving your data all jacked up.

This, of course, is a simple example, but can you imagine what it would look like if you made this kind of mistake with 200,000 rows of data and then loaded it back into Salesforce?

So how do I fix it?

As with anything, there are ways to work around this issue. The key is remembering to use them when you’re working with data outside of Salesforce. Commit it to memory right now — you need to use the 18-character ID for matching and lookups in Excel. I’ll wait…

Great! Now let’s get to the good stuff.

Always save a copy

First of all, be careful when you’re working with Salesforce data, Excel, and uploads. You have the power to do a lot of good or a lot of bad when you’re manipulating data and pushing it back to Salesforce.

I always recommend saving a copy of the raw data you’re about to work with. To do this, I normally export the data, open the file in Excel, and label that tab “Raw Data”. From there, create a copy of the “Raw Data” tab and rename it “Scrubbed Data”. Then (and only then), you can start manipulating the data.

The best thing you can do is avoid a big screw up altogether. If it happens, though, the fastest way to fix your mistake is to revert back to the original data set. Don’t forget to save the original.

CASESAFEID

Realizing this was an issue, Salesforce did offer up a fix and it takes minimal effort to put into place.

The solution?

Create a formula field on all objects using the CASESAFEID function and return the value as text.

This basic formula adds a 3-character suffix to the original ID, turning 15 characters into 18. Once you have the 18-character ID, you’re all set. It’s an entirely unique value where there will be no duplicates and capitalization no longer matters.

There’s no need to add this field to page layouts, but once created, it’ll be there for your reporting. Be sure to pull this value into your reports and use it to do all of your matching and lookups.

Get some software

There are plenty of tools out there that will help you get to the 18-character ID as well.

My favorite at the moment is Enabler4Excel (E4E). It’s my go-to for working with data and reporting. With this tool, I can work in Excel, pull in a report from Salesforce, and then convert the record IDs from 15 to 18 characters in a matter of seconds.

Here’s a quick look at E4E in action:

Step 1: Highlight the 15-character IDs you want to convert

Step 2: Click Convert IDs in the E4E ribbon

Step 3: Voila! You now have the 18-character IDs

Step 4: Your VLOOKUP works — hooray!

At the time of this writing, the E4E admin license is only $299/user/year. That’s less than 25 bucks a month and definitely worth it. And no, there’s no affiliation here — it’s just that good and easy to use. They offer a 30-day free trial so check it out and see for yourself!

There are a few other tools that will let you export data with the 18-character ID as well:

• Data Loader (within Salesforce)
• DemandTools (paid)
• Weekly Export Service (within Salesforce)

Or, for a quick, one-off option to convert IDs, check out at these sites:

• Admin Booster
• Magentrix

And lastly, if you’re feeling super adventurous, you can always try to convert IDs on your own. Good luck!

Using Google Sheets instead? Check out Ezra Kenigsberg’s handy tool for converting and repairing Salesforce IDs. Save a copy for yourself and get to work.

The good news is you have options — happy converting!

That’s a wrap

Now I’d like to hear from you.

Is this the first time you’ve heard of the 15- and 18-character IDs? If not, at what point in your Salesforce or Sales Ops career did you learn this lesson?

Feel free to share your stories by leaving a note in the comments. I read every one.

P.S. Don’t forget to share the love! Be sure to pass this wisdom along to your colleagues and friends so they’ll know how to handle the Salesforce ID situation when the time comes.

 

 

Photo credit: striatic via Foter.com / CC BY