Following from my earlier Post, Salesforce IDs and the 15-18 digit problem, here’s some Visual Basic code that can be installed as a User Defined Function (UDF) in Microsoft Excel (or, Microsoft Access) to easily convert Salesforce 15 character IDs to the “unique”, required by Microsoft applications, form of 18 characters … and a YouTube video to get you started.   Enjoy!

If you have any queries concerning this code, or installing it in MS Excel or MS Access, don’t hesitate to contact me.

May you live in interesting times

These are certainly challenging economic times for charities, for  nonprofits; indeed for-profit as well.  Now, more than ever, at the end of each day, you must be questioning whether your internal system are fit for purpose:

• that those monthly all too critical reports are all too time consuming to produce
• that essential data is still only available on paper!
• that even timely metrics are taking too much time to produce! That you’re unable to track even the most basic of activities
• that all tasks appear to be totally dependent on a proliferation of spreadsheets, all in different folders, with nobody following a consistent naming convention …
• that the details of the last contact with that very generous donor, that’s just called you for an impromptu meeting, are in the fund managers head – and they’re off ill …

Familiar experience?

You’re not alone.  Essentially, it’s time you must consider implementing that CRM (Contact or Client Relationship Management) system you know you need; or, at the least, schedule a review of your existing CRM implementation.

Still unsure as to why you need a CRM?   Well with a CRM configured to your needs you’ll most certainly:

• be enabled to automate a variety of processes and procedures, and in doing so become more efficiently at train new staff, or enabling existing staff to adapt
• have better coordination within your organisation
• have a detailed overview of communication with all your clients, and have better connect with your constituants
• have a comprehensive overview of all contacts; have better contact sharing and management as all contact details (e-mail, phones, address, …, last contact details, …) are all stored in one single place
• be able to share personal calendars and tasks; monitor and forecast performance to focus efforts and work to produce reports: internal and external

But be warned:

It’s never straight forward implementing a CRM system – be they peaceful times, or chaotic.  But, be reassured:

• there’s no perfect CRM solution out there – and they’ll never will be one
• CRM implementations mostly fail because of lack of buy-in
• the CRM itself is often a fraction of the overall total cost (for charities some CRMs subscriptions may even be free!  See my earlier post about Salesforce.com’s philanthropic 1:1:1 model)
• don’t forget the network, and access to the web; annual ongoing support, implementation and configuring additional modules, …, conversion costs: pooling, tidying, and re-structuring all the existing data in your unsupported database and spreadsheet ready for importing …
• that CRMs are for the long-term … so expect that after several weeks, or months, to find that some operation,  functions or processes could be addressed better, and that you would like to modify your present solution …

Where to start?

The trick?  Don’t procrastinate!

• start small!
• you’ll need a team – but don’t let the techies make the decision alone
• identify needs and priorities - what are the deal breakers
• what do you really need?  The classic: Must have, Like to have, …
• ensure that the implementation partner fits your culture, staff, and budget

Require the CRM to support:

• Mass Emails & Mail Merge
• Calendar, Email & Contact Management
• Marketing and Campaign Management
• Lead Management
• Customer Service Management
• Reporting and Analytics
• Interactive Marketing
• Document and Content Management
• and,
• if it’s the current best of breed, as in Salesforce.com, an social media application like Chatter …

May you live in …

Apparently there is no Chinese language equivalent of the curse “May you live in interesting times.” The nearest authentic Chinese proverb, according to Wikipedia, is “It’s better to be a dog in a peaceful time than be a man in a chaotic period.”

Cartoon: Non Sequitor © Wiley Ink INC

Salesforce CRM user?

Are you a Salesforce CRM user?  Use Microsoft Excel for analysis or reports?  Then you’ll have met the Salesforce 15 digit ID problem.

Internally Salesforce IDs are 18 digits long.  But, Salesforce Reports reduce them to 15 digits.  So, for example, an Organisation ID internally might be 0014000000LmabcAAB, but in a report it will appear as 0014000000Lmabc. The missing last three digits (the “AAB” in the example) are check-digits; used for error detection.

Now that wouldn’t be a problem if it wasn’t that Windows applications, such as Excel and Access, are case insensitive.  The 15 digit IDs are unique, which is what you need when you use the Excel function like VLOOKUP, but Excel treats 0014000000Lmabc just the same as 0014000000LMABC (case insensitive!) - so, your Salesforce IDs are no longer unique and VLOOKUP will no long find the correct match … (incidentally, the 18 digit IDs are unique to VLOOKUP!)

There are algorithms you can use to reconstruct the last three digits of your Salesforce IDs (search Salesforce Help for “How can one convert a 15 character id to a 18 character id?“), but better still there’s a script that works in Google Docs online spreadsheet.

Convert 15 to 18 Digit Salesforce IDs with Google Spreadsheets [video] – enjoy:

My thanks to David Engel, of The Engel Journal, for his Post and Video on this topic.  Similarly, David’s acknowledges in his post Damon Douglas, David Padbury, and Stefan Kuehlechner.

If you need further support processing you Salesforce data using Microsoft Excel, or Access, then please don’t hesitate contact me.  Alternatively, there’s a Java script that converts Salesforce 15 digit IDs to 18 digits that can be found here.