Admin Journal: Crafting a Unique Id for Updating when Record Id is Missing (@salesforce)

Dear Admin Journal:

Sometimes, users do something silly... like exporting a report to Excel, making changes that they'd like me to import and forgetting something pretty important: the record id.

It's not impossible to update records without the record id. It's just not handy... and it can be error-prone.

Over the years, I've figured out various ways to get around the missing record id, usually involving several queries and some rather complex vlookups.

But, here's a pretty simple method that I used today when the csv I received from a user had only 7 columns + one column for reassigning record owner.

1. I queried Salesforce to pull out the same 7 columns + record id with LexiLoader.
   
2. In my user's spreadsheet, I created a new column. I concatenated 3 fields that I knew would have at least 2 unique values, so it looked kind of like this:
   
   AccountName&LEFT(BillingAddresss,5)&BillingCity.
   
   Side note: I used the LEFT() function because my user had copied the data from a report in their browser and pasted it into Excel. The Billing Address had split into two rows in the spreadsheet. So, it wouldn't match my query.
   
3. In my query spreadsheet, I created 2 new columns. In one of them, I concatenated the same 3 fields to use as a unique id. In the other column, I created a vlookup to match my manually created unique id and return the column that had been updated in my user's spreadsheet.
   
4. Before updating using LexiLoader, I could remove the columns that I'm not using (Owner Name, Forged Unique ID, etc), or I could just leave them out of the field mapping.

It sounds kind of complex. But trust me, it's simple. 

Here's a rather lame screenshot (Skitch doesn't support blurring? What!?):

So, when I'm stuck with a spreadsheet without the record id and the user is waiting for me to update their data, I calmly remind myself of this.

In other news, I switched to a MacBook Pro last week. While I really like my Mac (I hesitate to say 'love'... but I'm sure someday I will), I've found that quite a few of the tools that I relied on are not available. I'm missing the Excel Connector, but I'm making it work with LexiLoader (a version of the Apex DataLoader for OSX) and Excel (although OpenOffice or Google Docs would work fine too).

Now, I'm off to finish updating.

Love,

Jenna, The Admin

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