Sonoma Partners Microsoft CRM and Salesforce Blog

FetchXML: Left Outer Joins with Multiple On Clauses

Having worked on CRM for ten years, I thought I understood everything that was possible with FetchXML. After all it seems pretty straight forward and each clause has almost a one to one equivalent with SQL. However, while I was recently doing some work on a project that required me to find records missing certain child records, I was surprised to find my understanding of left outer joins was incomplete.

The Requirement

In the project I was working on, we were using the native Connection entity to track relationships between contacts and users. Some of the Connections were manually created, but others needed to be automatically created based on business logic. In some cases we needed to detect all contacts that a user did not have a connection of a specified role with.  This seemed like a good case for using a left outer join and I sat down and wrote the following FetchXML:

The Concern

As I reviewed the FetchXML, I became concerned that I wouldn’t get the proper results with this query. I was assuming that CRM only used the to and from attributes on the link-entity element to build the on clause for the left join in SQL.  I knew that if the additional conditions inside the link-entity were added to the SQL where clause, that I would get no rows back.  In short, I was worried the generated SQL would look something like this:

It was enough of a concern that I decided to fire up SQL Profiler on my local dev org and see what exactly CRM generates in this case.  Much to my surprise I found the following (slightly cleaned up to help legibility):

In Summary

So in the end, CRM came through and put the link-entity’s conditions in the on clause for the left join.  This subtle change makes a huge difference in the results returned and makes left joins much more useful in CRM than one might assume based on the FetchXML structure.  This left me with an efficient query to solve the business requirement and a new found respect for FetchXML.

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

Performance Considerations Creating Custom Reports in Dynamics CRM - Part 1

Today's post is written by Rob Jasinski, Database Architect and DBA at Sonoma Partners.

The ability to create custom SSRS reports for Dynamics On-Premise is a very powerful feature and adds a lot of flexibility to CRM’s reporting ability.

However, with that power and flexibility comes the potential to create reports that can run slow, are inefficient, and in worst cases, actually affect the performance of CRM as a whole. This article will focus on some things to watch out for when creating custom SSRS reports for Dynamics CRM On-Premise. Part 2 in this series will focus on Dynamics CRM On-Line.

Use the numeric value for option set values

For option sets in CRM, filtered views return 2 values; the integer value and the name value. For example, StateCode and StateCodeName from FilteredAccount. When filtering by these values, consider using the integer value rather than the name. The integer value is stored directly in the entity table whereas the name value is derived from the stringmap table. In most cases, the numeric value is also indexed and since it’s on the primary table, will perform much better than referencing the derived name.

select name, statecodename from FilteredAccount where statecode = 0

Filtering by date

Use the “utc” version of the date field when filtering by date. Every date field in the CRM filtered views returns 2 fields. For example, actualstart on FilteredAppointment is returned as actualstart and actualstartutc. The actualstart field is actually a calculated field that converts the date to the running user’s local time zone, whereas the actualstartutc field is the raw UTC date retrieved directly from the database table. Since the “utc” version is the actual field in the table, it will leverage any table indexes and perform significantly faster in most cases.

select subject, actualstart from FilteredAppointment where actualstartutc >= '1/1/2015’

Consider using nolock

Whenever data is queried from the database, SQL Server internally manages locks on that query. In a report query that takes a significant time to run, it can prevent the data being updated from other users causing CRM to appear to lock up for other users. One solution is to use the “nolock” query hint. This tells SQL Server to not manage locks for this query, thereby not blocking access to other users who need to change the data. Keep in mind, this also allows “dirty reads”, in that there is a possibility that either the data has changed since the query first started or the original data read is no longer valid. When returning financial or other sensitive data, nolock should not be used. But for non-sensitive reports (i.e. list of open activities, list of accounts, aging reports, etc.), this can help maintain CRM usability and make CRM less susceptible to poor query performance.

select name, accountnumber from FilteredAccount(nolock)

Use Union All versus Union

In more complex reporting queries, you may find the need to union several result sets together to get the data needed for the report. If the result sets will not have any duplicate data across each set, use “Union All” which essentially just concatenates each result set and returns it directly to the report. However “Union” actually collects all the results first, sorts them, then removes any duplicates before returning the results to the report. This can be very time consuming when returning a large number of results.

Avoid “Select *”

Only return the fields the report needs and never use “Select *” when returning data from reports. There are many calculated fields returned from filtered views, from dates converting to local times and currencies converting to local currency. And if these fields are not required for the report, it will still make the calculation for every one of these fields for every row returned. This will take up server resources and cause the query to return data much more slowly. Also, only specifying the fields needed will reduce the amount of network traffic and make the results get back to the report much quicker.

Primary attribute values

Don’t forget that primary attribute values (usually [customprefix]_name) for most lookups are already included in the primary filtered view for the entity being queried. For example, for FilteredContact, the attribute AccountID is a lookup to Contact. The primary attribute on Contact is Name. So the filtered view, FilteredContact, also has a field called AccountIDName, which is the Name value from the associated Contact record. There is no need to join FilteredAccount into the query unless you need additional information outside of the primary attribute.

select FullName, AccountIdName from FilteredContact


select FullName, Account.Name


      FilteredContact Contact

      join FilteredAccount Account on Contact.AccountId = Account.AccountID

If you have any questions about creating custom SSRS reports for Dynamics CRM On-Premise, please contact us

Dynabacus the Microsoft Dynamics CRM Record Count Tool

Topics: Microsoft Dynamics CRM

How to Get Your Executives to Pay Attention to CRM


Today's post is written by Scott Zelinski, Senior Director of Sales at Sonoma Partners.

We often meet marketing leaders or sales managers that ask us how we can help them drive home the importance of a CRM tool to their executive leadership.

They either don’t have an effective CRM tool in place, or more often, they have a CRM tool but are not getting the value out of it that their vendor had advertised. We approach both situations similarly, but that requires understanding that a CRM tool is only an enabler of the business capabilities it supports, and talking tools with executive leadership may not be the best way to present the problem.

CRM encompasses the following stages of your customer life cycle:

  • Reach – your universe of potential customers until qualified prospects are identified
  • Acquire – your qualified prospects through a sales cycle until the sale is closed and won
  • Develop – your new customer through the fulfillment process for your product or service
  • Retain – effectively support your customer’s questions and service issues through resolution
  • Inspire – your customers growth through additional services and/or products that you offer

It always amazes us how seldom yet effective getting the proper management together for a short session can be. With proper leadership representing the life cycle stages above, we ask them to answer 3 simple questions:

  1. How can each stage be accomplished strategically better in the future?
  2. What are the metrics that will be impacted by the suggested improvements?
  3. What are the obstacles (people, process, technology) that will need to be overcome to achieve the suggested improvements?

These types of sessions rarely disappoint. They are an opportunity for leaders to brainstorm, collaborate and come together with some very solid, thought out ideas for real improvement in how prospects and customers are taken care of.

Condensing the output from the three questions above in a digestible summary creates the type of language that executives will pay attention to because it addresses real issues in their company. It identifies metrics on their P&L that their management team believes in, and it provides some initial ideas on what it will take to solve them including the functions and features that a CRM tool can bring.

If you need help getting these types of discussion started in your firm, we welcome the conversation. Please contact me at to learn more. 

Topics: CRM Best Practices

Microsoft Convergence Morphs into Two Conferences

Today's post is written by Ryan Toenies, VP of Sales at Sonoma Partners.

Microsoft recently announced that their long-standing Microsoft Convergence conference will cease to exist. Opting for a “One Microsoft” strategy, Microsoft has decided to roll all of their Dynamics content into two conferences. Microsoft Envision and Ignite will now replace Convergence. 

So which conference should you attend?

The first thing we all need to understand is that Convergence as we knew it is now gone. Don't be alarmed! This is not a bad thing. The new conference layout allows individuals to attend the event that most closely aligns to their needs. 

Microsoft Envision: 

April 4th - April 6th, 2016 | New Orleans, LA

This conference is designed for CxO’s and Senior Leadership to discuss how technology is impacting their business strategies. Filled with 3 different customer panel sessions, an opening keynote from Satya Nadella and some industry breakout sessions; business leaders will have ample time to discuss key business needs with peers from around the world.

The takeaway: Attendees will come away from this conference understanding how Microsoft is approaching business strategies both today, and into the future.

Microsoft Ignite: 

September 26th - September 30th, 2016 | Atlanta, GA

This conference is where all the traditional Dynamics technical content will be found. The conference is designed for IT professionals, system administrators and power users. Product roadmaps, features and functions of the latest releases, and Microsoft product team members will all be a key components of this conference.

The takeaway: Attendees will learn about O365, Azure, PowerBI, Dynamics AX, NAV, GP, CRM and more.  All of Microsoft’s key business technologies will be on display and there will be separate roadmap sessions diving into each product line.

There is one more conference that you might consider attending:

CRM UG Summit: 

October 11th - October 14th, 2016 | Tampa, FL

This is a conference that has really exploded over the last couple of years. This is the “how-to” conference for the Dynamics CRM community. The audience will be primarily power users of CRM, with a mix of business and IT leadership looking to understand how other companies are utilizing Dynamics CRM. Microsoft attendees will include the product support team, engineers and some senior leaders on the product development team. 

If your goal is to understand how you can better maximize your CRM investment, this is the conference for you.

Sunsetting the Convergence conference was a bold move. This further solidifies Satya’s vision of a "One Microsoft" strategy. Since the inception of Dynamics, it has always been a separate business unit within Microsoft. Transforming that business into the broader ecosystem of Microsoft will benefit the entire Dynamics community. Removing Convergence and replacing it with Envision and Ignite is just one more sign from Microsoft that Dynamics is a key part of their evolving cloud strategy. 

Topics: Microsoft Convergence Microsoft Dynamics CRM

Editable Grid for 2015 is Now Available

Editable Grid for 2015 is now available!

Some background information for you: years ago, Sonoma Partners developed Editable Grid, a popular tool that allowed users to edit records inline within a View. Based on user feedback, we have rebuilt the tool for CRM 2015, with improvements that increase both functionality and usability. Updates include:

  • Users can edit any field type, allowing update of multiple records at one time. This saves the user from having to open each record individually.
  • Works with native, custom, and personal Views.
  • Works with native and custom entities. Previously, Editable grid was limited to the core entities: Contact, Account, Lead, and Opportunity.

Editable Grid

Download Editable Grid for 2015 now. If you have any questions about the Editable Grid utility, or anything related to Microsoft Dynamics CRM 2015, please contact us.

Upgrading to Microsoft Dynamics CRM 2015

Topics: Microsoft Dynamics CRM 2015

CRM DevTools - Easily Update Hidden or Read-Only Fields for Testing

When testing functionality in CRM, most likely you will need to update some data in order to complete the test.  But what happens if the field doesn’t exist on the record form or is read-only?  One option would be to edit the form temporarily to display the field on the form, update the field value and then remove the field from the form after your test.  A better option, to save the hassle of all that publishing, would be to create an on demand workflow that updates your field directly and then manually run that workflow against your record.  However, CRM DevTools provides an even better option through the Test tab by allowing a System Administrator to update the field value through the record form even if the field doesn’t exist on the form or it is read-only.

First, you need to head over to the Chrome web store and add the CRM DevTools extension to your Chrome browser.  Also make sure you are System Administrator of your environment, otherwise the Test tab will not display.

Next, head to the record you want to update and press F12 which will pop open Chrome’s DevTools pane.  There should be a CRM DevTools tab at the top of the pane.


Click CRM DevTools and once it loads, click on the TEST tab.  You can now select up to three fields at a time.  Once you select a field, it will display the existing value in the Value column and then you can change that value.  If the field is a Lookup then you will need to enter the schema name for that Lookup in the Entity Schema Name column.  Then click Update and your field will be set to the new value.


Now you can easily update hidden or read-only fields in just a few easy steps without messing with the form or creating unnecessary workflows! 

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

Dynamics CRM - Entity Change Tracking

With the release of Dynamics CRM 2015 Online Update 1, Microsoft has provided developers many different ways to optimize performance when integrating with other systems.  We already covered Alternate Keys in a previous post which help increase performance by reducing the need to make retrieve API calls in order to find the primary key of a record.  In this post I will cover Entity Change Tracking, another feature of 2015 Online Update 1 which increases performance when needing to send data to an external system by retrieving only a subset of data that has changed since the last retrieval. 

So how does it work?

First you need to enable Change Tracking for the specific entity you want to use it on.  You can do this through the Entity customizations as shown below.


Then the RetrieveEntityChanges request can be used in code to get a list of entity records that have changed since the last retrieval.  If it is the first request then you’ll want to pass in an empty string or null to the DataVersion property but subsequent requests should use a token (which is returned from RetrieveEntityChangesRespone) in order to retrieve a list of records that have been changed since the last request.

string token = "";

var records = new List<Entity>(); 
var request = new RetrieveEntityChangesRequest(); 
request.EntityName = "account"; 
request.Columns = new ColumnSet("name"); 
request.PageInfo = new PagingInfo() { Count = 5000, PageNumber = 1, ReturnTotalRecordCount = false }; 
request.DataVersion = token;    
while (true) 

     var response = (RetrieveEntityChangesResponse)orgService.Execute(request); 
     records.AddRange(response.EntityChanges.Changes.Select(x => (x as NewOrUpdatedItem).NewOrUpdatedEntity).ToArray()); 
     records.ForEach(x => Console.WriteLine(x.Id)); 
     if (!response.EntityChanges.MoreRecords) 
         token = response.EntityChanges.DataToken; 
     request.PageInfo.PagingCookie = response.EntityChanges.PagingCookie; 

Running this code using LinqPad in a brand new Online trial org with no token provided a list of 10 records that have been changed:


And then the DataToken:

568840!02/10/2016 21:11:27

Running the same code above but passing in the token of “568840!02/10/2016 21:11:27” will now yield no results as expected as Account records haven’t been changed since we made our RetrieveEntityChanges request.

As you can see, this new request will be very handy when there is a need to synchronize data to external systems.  Now you can easily optimize performance by only synchronizing records that have been changed since the last sync.

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

Chatter to Yammer Migration? You can do it, we can help!

Today’s guest blogger is Ross Talbot, a Development Principal at Sonoma Partners


While both Microsoft Dynamics CRM and are both top-ranked CRM applications…sometimes customers migrate from one tool to the other. The reasons to migrate vary by customer, but pretty much all of them say something like “Hey it’s not you, it’s me”.

As part of some recent Microsoft Dynamics CRM deployments for Sonoma Partners customers, we also migrated the customer’s Chatter data to Microsoft’s Yammer app. While The customer obviously wanted to save their years of Chatter history and posts, and they didn’t want to start with a blank Yammer app. Since Yammer and Chatter offer very similar capabilities and use comparable data models, the customer asked if we could help migrate all of their Chatter data to Yammer?

At first blush, this might seem like a pretty straight-forward request. Both Chatter and Yammer have API’s…we should be able to migrate the historical Chatter data to Yammer pretty easily right? Unfortunately there are some constraints in the Yammer API that increased the level of difficulty. Having completed multiple Chatter to Yammer migrations, we have learned a lot of tips and tricks to make this process go more smoothly each time. Let’s consider some high level information about our most recent Chatter to Yammer migration:

  • Large enterprise customer – 5,000+ users
  • More than 3 years of Chatter data including:
    • 175,000+ posts
    • 300,000+ comments
    • 125+ GB of files/documents

Getting the data out of Chatter was no problem using their API, and we were able to migrate the following types of data into Yammer using their API:

  • Posts and comments (along with user impersonation so that it preserved which users previously made the comments)
  • Files (including both Chatter Files and Salesforce Content)
  • Images (interesting to note that Yammer supports up to 25 images per post, while Chatter only supports one image per post…the customer liked that)
  • Private messages (being very careful to respect the security model so that the private Chatter messages stayed private in Yammer!)
  • Topics and hashtags (like #chatterMigration)
  • User profile information, including manager info
  • @ mentions of users in posts
  • Groups
  • Deep CRM linking (linking Yammer posts to the appropriate CRM case, account, contact, opportunity, etc.). Maintaining this deep CRM linking proved key to the customer because more than 90% of their old Chatter posts were directly linked to CRM records.
  • Likes – the customer didn’t ask us to do this in the end, but this was supported if someone wanted it in the future

With that said, there were a few areas that we had to “get creative” on importing the data into Yammer. These areas included:

  • There is no supported way in the Yammer API to import group memberships (which users belong to which groups). We can’t reveal our secret sauce here, but the hint is Fiddler trace. :)
  • The Yammer API throttles imports to 10 posts per user every 30 seconds. With 175,000 posts and 300,000 comments for 5,000 users…we had to leverage multi-threading to get the data import done in a reasonable amount of time.
  • There’s no such thing as Yammer sandboxes, so during UAT and production migration we had to employ a bunch of tricks to prevent 500,000 @mention notification emails going out to users! We also developed some scripts and utilities to bulk delete the Yammer test records from the org (we needed the Yammer org empty for the final import!). Again not all of delete actions we needed were not supported in the Yammer API so we developed our own processes.
  • Sadly, there was no supported way (and no creative workaround that we found) to maintain the original Chatter post date in Yammer. So all of the imported posts have the same posted on date and we simply appended an “Originally posted on xx/xx/xxxx” line into the Yammer post so that users knew how old the original post was.

Generally speaking Sonoma Partners avoids unsupported techniques at all costs, but we tested these scenarios very thoroughly and received approval from the customer to move forward knowing some of these areas were unsupported. Since these were just one-time unsupported techniques for the data migration (versus being used ongoing in production), everyone felt the reward was worth the risk.

In summary, if you’re looking to migrate from Chatter to Yammer… and you’re bumping into some of these issues please give us a ring, we can help you out! We have the kinks worked out, the data mapping documents done, and the migration scripts pre-built.

(Likewise if you want to go from Yammer to Chatter, we bet we can help you out there too)

New Call-to-action
Topics: Microsoft Dynamics CRM Salesforce

Dynamics Web API – Querying a Multi-level Relationship

Today’s guest blogger is Brad Bosak, a VP of Development at Sonoma Partners

The Dynamics CRM Web API (OData v4) has a lot of great functionality and in most cases is on par with the Organization (SOAP) service. The old Organization Data (OData v2) service, introduced in Dynamics CRM 2011, is being deprecated in favor of this new endpoint.

A few months ago, I hit a query limitation while using the Organization Data service that I had to work around. With the release of the new Web API endpoint, I wanted to revisit my options and hopefully streamline my mobile app.

The Old – Organization Data Service

The snag I encountered was the OData service’s restriction on querying multi-level relationship properties. Unfortunately, it only supports going one level deep. For example, we can query for an account and information from the account’s primary contact pretty easily:


This query will correctly return the account name along with the primary contact’s name and phone number. However, if we wanted to add another level to this query, for instance retrieve information from the primary contact record’s owning user, then things get more complex. For example, the following query does not work:


Running this will result in the following error:

The request includes an $expand path with 2 segment(s), but the maximum allowed is 1

In my case, I wasn’t working with a huge data set, so I ended up just making separate queries to get the related data needed for my mobile app. That said, the multiple query approach still bothered me, so I dug into the new endpoint to see if anything had changed.

The New – Web API

I was hoping the new Web API had more support for querying multiple levels. I’m happy to announce that it does…just not exactly how I had hoped it would.

The following is a translation of our query to retrieve the account name and related primary contact’s name and phone number:


My next step was to see if we could expand beyond 1 level. The Web API does allow you to add some additional options other than $select inside of the $expand. Supported options include $select, $filter, $orderby, and $top. Unsupported options inside of the $expand are $skip, $count, $search, $levels…and $expand. I was hoping to be able to add another expand inside of the first to go another level deeper, but doing that gave me a ‘Not Implemented’’ exception.

Things were not looking good, but good ol’ FetchXML came to the rescue!

No, I am not telling you to go use the old SOAP endpoint. The Web API actually has support for fetch queries. If we want to do our multi-level relationship query in one call, we can use the following fetch:

<fetch mapping="logical" version="1.0">
  <entity name="account">
     <attribute name="name" />
     <filter type="and">
        <condition value="56958E9C-5D67-DA11-82FE-0003FF19CD51" attribute="accountid" operator="eq" />
     <link-entity name="contact" to="primarycontactid" from="contactid">
        <attribute name="fullname" />
        <attribute name="telephone1" />
        <link-entity name="systemuser" to="ownerid" from="systemuserid">
           <attribute name="internalemailaddress" />

We can then encode and execute our fetch (it’s ugly…but it does work):


Running the above query will give us a result with the following format:

          "name":"Account Name",
          "contact1_x002e_fullname":"Contact Name",
          "contact1_x002e_telephone1":"+1 (555) 555-5555",
          "systemuser2_x002e_internalemailaddress":"Owner's email"

In most cases the standard OData queries will get the job done, but it’s nice to know we can fall back to FetchXML if things get too complex.

Topics: Microsoft Dynamics CRM 2016