Sonoma Partners Microsoft CRM and Salesforce Blog

CRM Online Spring 2015 – Alternate Keys

With the release of CRM Online Spring 2015 Update, Microsoft delivered the ability to define alternate keys for an entity.  Alternate keys can be one or many attributes that are a unique combination of columns to uniquely identify a record instead of using the typical primary key.  Alternate keys can be made up from a combination of decimal, integer or string attributes.

Lets dig into how the new alternate keys can work in CRM based on a real-world scenario.

In my example we have an entity for States and our Contact entity has a Lookup to the State entity.  The State entity has an attribute called Code where it will store the unique two character abbreviation for the State such as IL for Illinois.  There is an integration with another system where Contacts will be passed to Dynamics along with the two character abbreviation for State.  Alternate keys work perfectly in this scenario as we can just associate the State record to the Contact record by using the State’s Code instead of needing to query to find it’s GUID.

In order to accomplish this, we first need to go to the customizations for the State entity.  Underneath the entity is a new link for Keys.


Click New which will open a dialog and let you pick one or more keys that will make up a unique combination to identify a record.  We will select Code as a Key for our State entity.


Now with the SDK when updating a Contact record, in order the set a Lookup value we would need to use the EntityReference object and provide a GUID to a State record but with the latest Spring Update and the latest SDK, we can instead pass in a value for the Code attribute like shown in the snippet below.

Therefore if our integration sends us a Code for the Contact’s State such as “IL”, we can just update our Contact record using the “IL” value and avoid needing to do a query against the State entity to find the GUID for the IL State record.

One last thing to point out about alternate keys is that CRM will put a constraint on the entity to make sure the new alternate keys make up a unique identifier for the entity.  If you try creating a record with a duplicate set of alternate keys, you will see an error similar to the one shown below.


Topics: Microsoft Dynamics CRM Microsoft Dynamics CRM 2015 Microsoft Dynamics CRM Online

How to Mass Update Business Process Stages

Today's guest blogger is Rachel Sullivan, a Senior Consultant at Sonoma Partners

CRM allows you to easily update fields on records using their bulk edit functionality.  Records that you can query and return back in a list/grid can all be edited using this functionality.

However, since it’s currently not a field on the Opportunity form, there is no way to update the Business Process stage using this native bulk edit feature.  However, fear not, as Sonoma is here to provide a workaround on how to edit this field using native export/import.

In order to update the Business Process Stage using this workaround, follow these steps below.

1. You’ll first need to find the unique identifier of each process stage (the GUID).   To accomplish this, use Advanced Find to identify the opportunities that you would like to update.

3. Edit the columns and include Process Stage

4. Click Results and from this view, you can see all of the unique Process Stages and their GUIDS


You will need to match each unique GUID with the Process Stage Name.  The easiest way to do this is to open a record of each unique Process Stage value, view the Process Stage name.  Do this for each unique Process Stage.

For Example:

  • d3ca8878-8d7b-47b9-852d-fcd838790cfd = Propose
  • 650e06b4-789b-46c1-822b-0da76bedb1ed = Develop


5. Once all GUIDS are gathered, you’ll need to export the records that you’d like to update.  When doing this, make sure to check the box that makes the file available for re-import (note that with CRM 2015 Online Update 1, all records are automatically exported for reimport and this step isn’t necessary.  This should come to CRM 2015 On Prem in the fall).  Save the file locally and open it with Excel.


6. Once in Excel, copy and paste the GUID of the business process stage (that you’d like to change each record to) into the Process Stage column.  Save the file and import back into CRM.

Topics: Microsoft Dynamics CRM Microsoft Dynamics CRM 2011 Microsoft Dynamics CRM 2013 Microsoft Dynamics CRM 2015 Microsoft Dynamics CRM Online

Customer Success Story: Grant Thornton


"In our business, relationships matter." - Rick Stow

Grant Thornton LLP is one of the world's leading organizations of audit, tax and advisory firms. Although their revenue is in excess of $1.3 billion and they operate in 57 offices across the globe, Grant Thornton hasn't set out to be the biggest accounting organization in the world.

They know they compete with four larger firms, thus being the biggest doesn't differentiate them from the rest of the crowd. What does? Establishing closer relationships and personalized service that earns credibility and increases value. Enter the tool that helps Grant Thornton build and nurture personalized relationships with their clients: Microsoft Dynamics CRM.

Prior to their Dynamics CRM implementation, Grant Thornton lacked visibility into their contact and account records, as well as information that spanned across multiple employees and practice areas. Maintaining various customer management solutions, including SalesLogix, proved to be a challenge that sometimes led to duplication of effort and missed opportunities.


With one consolidated system, Grant Thornton is determined for their firm to maintain a meaningful relationship with each client in their CRM. Here are 3 ways their CRM solution helps them achieve this goal:

1. Commitment to Mobility 

A majority of Grant Thornton's employees spend a majority of their time outside the office, making the mobile component of their CRM project essential to their success. Not only did a mobile solution give their team members the functionality where and when they needed it, but it helped drive widespread user adoption - the key component of a successful CRM project.  

 2. Rise of Collaboration 

Relationships are everything in professional services firms, and people are very protective of their relationships and their contact records. Grant Thornton worked to overcome this mindset in order to understand the full scope of a client relationship. To foster effective collaboration, Grant Thornton integrated SharePoint, Lync and Yammer to promote a dialogue around client and account activities.

3. Enhanced Sales Processes 

Their new CRM solution accommodates different sales processes and regional requirements, helping to achieve the "One Firm" goal they had when starting the project. As collected data enters the CRM solution, employees can pinpoint the strengths, weaknesses, and threats of an opportunity. Because this information is centrally located and visible to appropriate parties - account managers can collaboratively develop a plan for pursuing the relationship. 

We worked with Grant Thornton for 11 months to create their custom Microsoft Dynamics CRM solution. Here are some lessons learned from their deployment: 


Don’t bite off more than you can chew. Your CRM project is ongoing and can be done in phases. It's okay to go live with fewer requirements and communicate broadly as you roll out additional functionalities. 

 Reduce the scope of the project: 

Don't go down the rabbit hole of adding on endless nice-to-have features. Most of your employees don't operate at that level of detail and if they do, they don't want to maintain a system that requires that much detail. Phase out the introduction of the system to drive widespread adoption.  

Are you ready to write your own CRM success story? We’re ready to help. 

Topics: CRM for Professional Services Microsoft Dynamics CRM Online

Summer ’15 – View Query Plan Notes

In a previous post, we talked about a (then pilot) feature of Salesforce that assists in debugging slow running queries. With the Summer ’15 release, Salesforce has improved this feature by allowing us to see any notes the query optimizer has for the highest ranked query plan (the query plan most likely to be used).

What This Means for Me

Ultimately this release is about empowering the developer to dig deeper into the platform and solve their own problems, without having to involve Salesforce support. This allows us to find and fix problems quickly, while reducing time spent in development (and therefore cost). While this isn’t a feature that most of us will use on a daily basis, it’s always good to have more options and information when things do go wrong.

Enabling Query Plans

By default, query plans (and therefore the query plan notes) are not shown in the developer console. To enable them, open the developer console and go to Help > Preferences and change the ”Enable Query Plan” options to true:


After saving, the Query Plan button will be displayed on the Query Editor tab:



You might notice that almost every query you view the plan for will have a note of

“Not considering filter for optimization because unindexed. Table: {your table}: [“IsDeleted”]”

The reason for this note is that Salesforce internally appends IsDeleted = false to your queries when you use the normal Query endpoint (or SOQL without the ALL ROWS key words), which filters out any records in the trash bin. The query plan is telling you that this field is unindexed and therefore the query will not be optimized based on that field. In general you can ignore this note, since there’s not a lot you can do about it without contacting Salesforce. However, this does demonstrate that the query you send to Salesforce is not necessarily the actual query that gets run, and these notes are a good way to sometimes discover hidden issues that will let you open more intelligent cases for Salesforce support to assist with.

Topics: Salesforce

Integrating data with Salesforce via SSIS and CData


A common need in the enterprise world is the need to be able to integrate data between disparate systems (ERP, POS, data warehousing, etc...). Increasingly CRM systems are becoming common-place in the mix of systems needing to be able to talk to each other, and one of the largest CRM systems in the market is Salesforce. Unfortunately, Salesforce’s size and breadth of scope can make it intimidating for developers to get started, and even figuring out how to connect can be daunting if you don’t already know where to look. Thankfully, there are third party drivers such as the one provided by CData that make this task easy, and let developers focus on business logic instead of Salesforce connections and APIs.

 Today we will be looking at how to integrate data with Salesforce using SSIS and CData’s SSIS driver.



In this article, we assume you already have basic familiarity with SSIS and will not go through the setup steps for SSIS itself. We’ll be integrating Account data from a local database table in to Salesforce, and have set up the SSIS package to read the Account data as the first step.

Upsert Accounts

Retrieve Accounts

For our sample, we’ll use the following Account table:

Account Table

In this table, the Name field is the name of the Account, and the Id is the database generated Id.

Installing the CData SSIS-Salesforce driver

In order to connect to and interact with Salesforce, we’ll need to install a driver. We’ll be using the CData SSIS-Salesforce driver to accomplish this task, which you can download from their site. After downloading, simply follow the prompts to install and restart Visual Studio if you left it open.

Connecting to Salesforce

To begin, we need to first be able to authenticate against your Salesforce instance, which means we need Salesforce credentials.

Note: If you’re following along just for fun, you can sign up for a free Salesforce org from The free developer org has lower data limits, but for our purposes is identical to most other Salesforce orgs you’ll need to connect to.

There are a few pieces of information you’ll need before you can continue further:

  • The salesforce username + password for the account you want to use as the integration account.
  • The type organization you are connecting to (Developer, Production, Sandbox, etc.)
  • An optional security token, depending on your organization’s security settings.

Gathering the username + password

This is the same as the username and password you use when you log in to Salesforce through the browser.

Gather the type of organization

A vast majority of the organizations you will have to communicate with will be one of 3 types: Production, Sandbox, or Developer. For our purposes, we only need to know if we are in a Sandbox organization. Generally you should be able to find this out by asking your Salesforce administrator, but you can also look in the setup menu to find this information:

Salesforce Edition

Gathering the security token

The security token is a random string of letters, numbers and symbols that is tied to your user’s password. It is needed when accessing Salesforce through the API if your organization’s security settings have not whitelisted your IP address. Generally this value should be provided by your Salesforce administrator, but if you don’t know it (or you have a new user) you can reset it.

Warning: Resetting the security token resets it for all applications using the account. If you have other integrations using this account, do not reset the security token unless you plan on updating it in all locations.

There are two places you go to reset your security token, depending on your organization version.

Through the Setup Menu

In some organizations, resetting your security token can be done through the Setup Menu:

Setup Menu

Reset Security Token Setup

Reset Security Token Setup Confirm

Through “My Settings” Menu

If you don’t see Reset My Security Token in the setup menu, then you need to go through the My Settings menu:

Reset Security Token My settings

Reset Security Token My Settings Confirm

Regardless of which route you need to take, once you click the confirm button you will receive an email from Salesforce with your security token.

Adding the connection to SSIS

Now that we have the required information, we can create a new SSIS connection. In the Connection Managers section, add a new connection of type CDATA_SALESFORCE:

SSIS Connection

On the following screen, fill in the Authentication portion with the information we just gathered. If you do not need a security token, leave the field blank.

The “Use Sandbox” field is where we use your organization type: if you have a Developer or Production organization leave this as false, if you have a Sandbox organization set this box to true.


SSIS Connection Test

Leave all of the other settings as their defaults and click OK.

Mapping the data

Now that we have a connection to Salesforce established, we can begin mapping your data from the database table to the Account object in Salesforce.

To start, we need to add a new CData Salesforce Destination:

CData Salesforce Destination

CData Connection Manager

Once you have the destination established, it follows the same conventions that most data mappings follow. You can pick from the input list, and map it to a field in the output list:

CData Mappings

Error Handling

With the mapping set up, we need to add error handling for any records that fail to be inserted. The simplest method is to log the errors to a flat file, which we’ll use for now:

Error Logging

Of course, since this is using the standard SSIS error redirection, you can add any logic needed by your business requirements.

Wrap Up

At this point, you have a fully functioning Salesforce connection, and can push data from your local database to Salesforce. Hopefully this walk through makes finding the connection information easy, and utilizing CData’s Salesforce driver for SSIS authentication allows for interacting with Salesforce in a straightforward manner. Using the driver makes integrating your ERP, web site, and POS systems with Salesforce easy, allowing you to keep your data in sync and ensure everyone is always on the same page.

Topics: Salesforce