Monday, February 27, 2012

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:


    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.


Jenna, The Admin

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

Saturday, February 4, 2012

How I Geekified My Wedding Planning

I've been married for 2 weeks to an adorably sweet and geeky man. #happyhappy

When we began planning our wedding (about 3 months before the big day... yeah, I just said 3 months), there was no question that we'd go about it in a Rather Geeky way.

So, here's a roundup of the apps and tools that helped us get things done.

Planning and Pre-Wedding 

Google Docs
I love the whole suite of Google Apps, but Docs came in especially handy. We used it to collaborate on the following:

  • Guest List
    We created a spreadsheet to store First and Last Name, Address, Category (family, friends, coworkers, etc). When preparing our invitations, we downloaded this and did a mail merge to print directly on the envelopes.
  • Reception Playlist
    We created a spreadsheet for Song, Artist, When to Play (Mingling, Dancing, Slow Dancing). I shared it with a few family members to make sure we had selections that would appeal to a large group of people.
  • Food Signup Sheet
    Since we had a potluck-style meal, I sent out a Google Form (to track Name, Contact Info, Dish) by email to our friends. It worked great!
  • Crafting Session Signup Sheet
    I had a number of DIY projects that I needed help with. So, I sent out a signup sheet and some of my friends volunteered to help.
  • Gifts Received
    We didn't want to lose track of any of the gifts that we receiving at our wedding shower and later at the wedding. So, we created a spreadsheet to track the gift, who gave it and whether or not we've send a thank you card. (I'm still working on that!)

Side note: When I first got in touch with my good friend's brother that I ended up marrying, he had a Hotmail address. His sister told me: "Don't hold it against him." Ha. It didn't take long for him to create a Gmail address too. :)

Pinterest is a visual bookmarking app that I use to catalog crafting ideas, recipes, inspiration and photos of things that I find interesting. I created a 'board' full of all the wedding ideas I liked.... and then copied the ones that I thought I might actually do to a smaller board.

Google Sites

Soon after we got engaged, I created a Google Site so that we could share our story with friends and family. It included a guest book that people could sign (as a Google Form).

We tried several task-sharing apps (such as Hunny Do and Checkvist) but we settled on Wunderlist. I like that it allows you to share individual lists and also has a web interface (as opposed to Hunny Do which is just for the iPhone). The UI is clean and easy to use. We did have some issues with it not refreshing properly on occasion but overall it was very handy.
We used a global registry site that allowed us to choose gifts from any store or website from the convenience of our computer or iPhone. When someone purchases something from the registry, you receive a notification (and it's easy to view a list for sending thank you cards). It also allows you to create a Cash Fund that people can donate to.

We planned on doing a flashmob after our first dance. We had friends in Alaska and elsewhere that planned to join. Since we couldn't practice in person, we recorded a video tutorial. Here's the final result:

We coordinated with our wedding planner using Skype. (Having a planner to handle all the details on the day-of was invaluable!)

The Day Of 

We document our daily life with the nifty Instagram iPhone camera app, which allows you to take 'polaroid'-style photos and apply various effects. I discovered that you can use hashtags just like on twitter, so we designated #weinerwedding (Weiner is my new last name.... pronounced like 'Wine-er') to be used for any photos taken on the wedding day. I downloaded the Carousel app on my iMac (which would show a live stream of photos on the screen). Over the course of the day, we ended up with over 40 photos that give a glimpse into what we and our guests experienced. En route to our honeymoon, I pulled the feed up on my phone. We were delighted to see what our guests captured.

The Honeymoon 

We headed up to a cabin near Mt Rainier for a week for our honeymoon. There was no cell phone signal... but we still wanted to take pictures of the sights with the app. So, we took them and queued them for upload, where they would sit until we had signal again.

While driving around one day, we happened upon a strong signal. So we stopped for a few minutes to reconnect and upload our photos.

Another benefit of using Instagram is that you can use a tool called Prinstagram to create miniature albums from your photos. I plan to do that with the tagged wedding photos and with our honeymoon photos.

Google Docs
  • Cabin Research
    When choosing locations for our honeymoon, I created a spreadsheet to compare features of the various cabins. That helped us make a decision on where we wanted to stay.
  • Menu Plan
    Because we were heading to a fairly rural area, we had to bring our own food (which was perfectly fine because we enjoy cooking together). So, we crafted a menu plan and created a spreadsheet with days of the week of slots for breakfast, lunch and dinner. It worked great! 

Despite the winter storm and the fairly short planning period, everything came together quite well thanks to technology... and the wonderful friends and family that did whatever they could to make our wedding day a success.

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