Sonoma Partners Microsoft CRM and Blog

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)

Topics: Microsoft Dynamics CRM

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

CRM 2016 Knowledge Articles and Full Text Knowledge Search

Today’s guest blogger is Rob Swafford, a Senior Developer at Sonoma Partners

I recently had to build an integration with the new-to-CRM 2016 Knowledge Management entities for a client. As you probably know, previous versions of Dynamics CRM had some Knowledge Base functionality through kbarticle entities. These served their basic purpose, but had their limitations.

Knowledge Article Introduction

For CRM 2016, Microsoft has introduced an all-knew knowledgearticle entity that incorporates the basic KB Article functionality along with translations and versioning for articles. These are available under Service –> Collateral –> Articles, from the yellow callout bar above the Articles grid:


Clicking that link will bring you to the new Engagement Hub where you’ll find not only the new Knowledge functionality, but also active and resolved Cases and emails:


From there, click on the arrow by Service, then click on Knowledge Articles in the Site Map:


This finally brings you to the new Knowledge Article management screen. Hopefully Microsoft will make it a little faster to access in a future patch. From here you can view the articles that exist in your current CRM system. Note that these do *not* overlap with the old-style kbarticle entity at all.


From here, you can create and edit articles as needed.

Full Text Search

For my project, I needed to be able to execute full-text searches against these Knowledge Articles. Our user base wanted to send in a search string and have the system search across the full text of the article body for the words in that search string, including alternate tenses of verbs. Not an easy task when all you have is FetchXML. But wait! CRM 2016 ships with a brand-new SDK request called FullTextSearchKnowledgeArticleRequest, and a correspondingly long-winded FullTextSearchKnowledgeArticleResponse. This request/response object pair takes care of all of the heavy lifting for my exact use case.

The FullTextSearchKnowledgeArticleRequest object has a number of public properties that need to be filled in to work properly:






Sets a QueryExpression object that is used to set additional query criteria beyond the SearchText, language filtering for example.



True to remove duplicate versions of the same Knowledge Article



Sets the text to search for in Knowledge Articles



Sets the StateCode of Knowledge Articles to search for. Required – so you can’t search for both Published and Draft articles in one request.



True to use inflectional stem matching when searching articles. This is what enables us to search for different verb tenses – run versus ran for example.



Over the course of developing this search client, we ran into a few undocumented “gotchas”, primarily surrounding the required QueryExpression member in the Request object:

  • The QueryExpression’s ColumnSet in our experience has to be set up to return all columns. Specifying a subset of columns here caused the OrganizationService to return errors. This may be a bug that Microsoft has yet to fix.
  • The QueryExpression *must* have PageInfo data filled in. We had success specifying only a PageNumber and Count values
  • The FullTextSearchKnowledgeArticleRequest does rely on SQL Server Full-Text Search under the hood. This means that the articles have to be indexed by the SQL Full Text Indexer service before they can be found by this SDK method. This indexing does take some time, and we found we had to wait a couple of hours sometimes before newly published articles would be surfaced.

Our final, functional implementation looks something like this:


As you can see, we have the QueryExpression set up as I described above, we are using the Inflection and Duplicate Removal options, and filtering down to only Published articles. The Response object that comes back has an EntityCollection that will contain all of your matched article entities:


And with that, you have a working Knowledge Article search. I hope this has been helpful to you!

Topics: Microsoft Dynamics CRM 2016

Deploy Dynamics CRM with PowerShell Part 3: Auditing

Today's post is written by Ian Moore, a developer at Sonoma Partners.

Making sure that auditing is enabled and configured correctly is usually a critical step when setting up a new Dynamics CRM organization, especially a production instance. Everything related to auditing can easily be added to a deployment or setup script with a couple extra lines of PowerShell code.

The first step in configuring auditing for your organization is to make sure it is enabled at the system level. If it is not enabled here, no auditing will ever occur. Here is a simple code snippet to test if your CRM organization has auditing enabled, and to enable it if it does not:

From there, you will need to ensure that auditing is enabled at the entity level for all data where you want to track changes. If you’re not sure which entities have been configured to audit, you can actually use PowerShell to generate a simple report of your current entity audit settings:


Once you know which entities still need to have auditing enabled, you can use metadata updates to configure each entity. The following snippet would enable auditing for accounts:

As with any metadata changes, you will need to make sure to publish changes for the entity. The easiest way from your PowerShell script would be to use the Publish-CrmAllCustomization function. If you are only updating a handful of entities, you could also create a PublishXmlRequest similar to the UpdateEntityRequest above rather than publishing all changes.

Hopefully these snippets help eliminate necessary manual steps when configuring auditing in your CRM environment.

Make sure to download the Microsoft.Xrm.Data.PowerShell module from GitHub at and follow for updates to the module!

Topics: Microsoft Dynamics CRM Microsoft Dynamics CRM Online

Mobile Friendly Experience with Dynamics CRM 2016

Microsoft Dynamics 2016 has recently been released, and with it comes a slew of new features.  Microsoft once again continued its investment in the mobile space and has made their free mobile client which is available from the app stores now have an extra level of configurability to give them an even better mobile touch.

Microsoft has constantly preached their “configure once, deploy everywhere” motto, and recent dynamics updates have continued that line of thinking by allowing users to configure the CRM record form once, and have it deploy to the Web client, Tablet client, and Phone client all at once without having to configure them separately.

Now with 2016 Microsoft has added the ability to add a little bit of mobility flavor to the form configuration.  They’ve realized that there needs to be more creativity in an application versus just a form and a list of fields, especially with mobile devices.  And with such thinking they are allowing users to configure the form once, but for the Phone and Tablet client, allowing users to specify a mobile friendly control to wrap around that field.

What does this mean?  It means that instead of having an integer field show up as a text box that allows numbers only, it can show up as a slider control that can be easily dragged and dropped when on a mobile device.  It means that instead of having a field that opens up a dropdown list of values, that the options show up as tabs that are clickable in which clicking selects that value for the field.

Sounds pretty neat right?  Well it is and below we go into more details about how to set this up.

Configuring the Mobile Friendly Controls

As mentioned above, this is all configured from the single Form definition, and administrators don’t need to go to a different location in order to enable the mobile friendly controls for the Phone client or Tablet client.  By double clicking on a field and going to the “Controls” tab, you’re able to add a control, of which you can then apply it to the Phone client, Tablet client, or both.



As you can see above, you can also populate some parameters per the control.  For example, on the Linear Slider on a currency field, you’re able to specify the minimum amount, maximum amount, and what the step amount is each time you move the slider left and right.  This configurable value can be hardcoded to a static value, or you can actually bind it to another value on the form which makes your controls much more dynamic based on the data you have in your environment.

Available Controls

Now that you’ve seen how to configure the controls, lets dive deeper and see a preview of what each of the controls looks like.  For each of the controls below I’ve first provided a screenshot of configuring the control that shows what types of fields it can be used with, and then I provide a screenshot of the actual control as it appears on the Tablet App.


The Calendar Control replaces any list on a dashboard, view, sub grid on form. This isn’t available for only Activity related records, but will work for any entity that has a date (e.g., displaying Opportunities by Created On or Estimated Close Date on the Account form)

image image

Arc Knob

The Arc Knob control binds to numeric controls and allows you to visualize the value of your field in an arc that has configurable min/max/step values.

image image


The Auto complete control provides your users a way to enter data to a Single Line Text field, using a predetermined list of values.  The values can be bound to an Option Set, or to an Entity with a specific  View.  A great use case for this control would be a list of States and Countries, where you can have an entity for the State and Country values, but provide users a quick way of entering that data versus the expensive route of using lookup controls.

image image

Bullet Graph

The Bullet Graph control allows you to to see the value of your field in relation to a target.  You’re required to set a min value, max value, and a target value.  Then you can also provide a “Good” and “Bad” value which will further assist end users with visualizing how they’re doing compared to the company standards.  A great use case of this would be binding to the Actual Revenue and Estimated Revenue fields on an Opportunity to visualize actual versus estimated revenue.

In my example below, my min is 1, max is 100, and target is 75.  Then I have a good marker of 90, and bad marker of 50.  Given the value of the field which is 60, you can see how the other values are represented on the control.

image image 

Flip Switch

The Flip Switch control is another way of visualizing a bit field with only two options.  Instead of a checkbox, or radio buttons, the control displays the selected value in an “on/off” electrical switch type of manner.

image image

Input Mask

Input Mask controls are a great way to get data in a Single Line of Text field formatted a specific way.  For example, if you want to have all of your phone numbers formatted with parenthesis and dashes, then you’d have your Mast value set to the following:


Doing so provides the end user an experience as displayed in the screenshots below.  The user will not be able to save the value entered in the field until the entire input mask is honored.

However, note that the data IS NOT stored in the database field with the mask.  That means for my example below, if I entered (312) 627-0700, the data would appear on the CRM web form (and in the database) as 3126270700.

image image image

Linear Gauge

The Linear Gauge control is another visual representation of a number / currency field.  It gives you the ability to configure a min, max, and step values.  This is very similar to the other numeric controls but simply another variation to provide you with multiple options.

image image

Linear Slider

Similar to Linear Gauge, Linear Slider is another variant on displaying a number / currency field in a more visual aspect.  The same min, max, and step values are configurable on this control.

image image


The Multimedia control is a great way to take a URL field (Single Line of Text formatted as such) and have it play audio or video directly within the application without having to leave and visit a web browser.


Number Input

Another way to quickly update number and currency fields is the Number Input control.  This provides users the ability to type a number directly in the middle white area, or use the plus and minus buttons to quickly increment or decrement the value based on the step that’s configured within the control on the form.

image image

Option Sets

The Option Set control is used with, well you probably already guessed it, Option Set fields.  The control provides tabs in a horizontal line with the available options for the user to quickly tap and select a value.

Note that this control is best used with a small number of options (2 or 3) and also ideal for option sets where the length of the text in the values is short.  You want to make sure that the options horizontally visually fit in a single line otherwise your users won’t see the benefit of this control.

image image

Pen Control

The Pen Control is a way to use the touch feature of mobile devices such as a Surface or iPad, in order to capture free form drawings or written text.  The control is only available for Multi Line Text fields

 image image

Radial Knob

The Radial Knob control is yet another flavor of how to display a number or currency field on a mobile device.  With this control, clicking and dragging clockwise around the knob increments the value of the field based on the step configured.

image  image

Star Rating

The Star Rating control allows you to set a numeric value via the use of up to 5 stars.  Since the field in CRM could have a max larger than 5, what happens is that the tablet would represent any value of 5 or higher as 5 stars.  Similarly, any value of 0 or less will be represented as 0 stars.


Website Preview (CRM Online Only)

This control allows any Single Line of Text URL field to preview the beginning of a website entered into it.  Microsoft provides a disclaimer with using this control indicating that you consent to allow users to share certain identifiable device information with an external system as this makes use of an external service.  Note that this control is only available for CRM Online orgs.

image image

What Next?

As you can see the focus on mobility by the Dynamics CRM team has led to great improvements in the mobile clients and we’re excited to see where the product goes next.

If you have any questions about the mobile clients, CRM 2016, or anything else please don’t hesitate to reach out and Contact Us.

Topics: CRM Upgrade Microsoft Dynamics CRM 2016

Utilizing the Audit History for Fun and Profit

Today's post is written by Rob Montague, a developer at Sonoma Partners.

I was working on a project for a customer that wanted to send an email to all of the previous owners of a case when a case is resolved.  We had auditing enabled, and everything looked like it should be some simple fetch queries, but it wasn’t quite so simple.  Here were the catches.

Even though you have auditing turned on for a field, be sure to check to make sure that the auditing is enabled for the entity type you are using (especially if it’s native) 

  1. Settings -> System -> Auditing -> Global Audit Settings -> Start Auditing
  2. Make sure the entity types that you are using are enabled. In Global Audit Settings, under Enable Auditing in the Following Areas I had to enable Customer Service Entities to enable auditing on Case.
  3. Make sure you test to make sure your user will have access to the audit history field that you are accessing
  4. If you have an administrator that clears out audit logs OR you have an automated process to do so, you could possibly lose some historical data in the audit trail.

You can’t query the audit history directly, you must make an API call. I had tried fetching against the audit history table and the API would deny the request.  This is the call I had to make:


The call will get all of the field changes to the target field (in this case, ownerid) that have been done to the record (in this case incident which is the system name for the Case entity).

The audit detail records will have two properties Old Value and New Value. I then created a dictionary of owners so that I wouldn’t have duplicates when I was sending the emails out and went through the audit collection. With this approach you could notify salespeople that have modified a record when a contact buys a product, or when their contract is about to expire.


Topics: Microsoft Dynamics CRM

How to Use Word Templates in CRM 2016

Today's post is written by Chelsea Paice, a Sales Engineer at Sonoma Partners.

One of the great new features just released with Dynamics CRM 2016 Online is the ability to use Word Templates on individual records. These templates allow you to create easy-to-edit, pre-formatted Word documents that are already populated with your CRM data.

It’s a great way to create invoices, account summaries, form letters, and more for your CRM records.

In this post I’ll walk you through creating your own Word Template in Dynamics CRM 2016. Though they can be used on any kind of individual record, in this scenario, I’m generating a client/account summary for a partner in an accounting firm.

Generate a Base Template

The first step is to export a base template from CRM so that you can make your edits in Word. If you are a System Administrator or Customizer, you can get to the Templates area from the Settings Page. Go to Settings > Templates > Document Templates, and create a new template from there. If you don’t have admin permissions, you can still create personal templates by going to any individual record or view, and looking for the Word Templates option in the record’s toolbar.


Once you click Create Word Template, you’re given your choice of Excel vs. Word Template, and asked to choose the appropriate entity. This will be the basis of all of the other related entity information you will be able to pull in, so make sure you choose the correct one.


Next you can select the relationships you’d like to include in your template. This is what determines which fields will be available to you, so make sure you include everything you need. In my client summary example, I’d like to include a list of opportunities we’ve had with them, so I’ll include that from the 1:N Relationship list, and then click Download Template.


Editing your Template

After the template downloads, open it in Word. You need to make a couple of changes to Word before you can edit the template properly. First, enable the Developer tab. Go to File > Options > Customize Ribbon, and make sure the Developer item is checked in the Main Tabs area on the right.


While still in the Options window, go to Proofing in the left-hand menu, and select AutoCorrect Options. Here, you need to deselect Capitalize first letter of sentences, and Automatically use suggestions from the spelling checker. This is vital because it prevents AutoCorrect from making automatic changes to your content controls, which can cause Word to freeze.



Now you’re all set to begin editing your document template. Open the developer tab in your Word ribbon, and click XML Mapping Pane.


When the pane opens, it will have the default schema selected. You’ll need the CRM schema instead, so open the dropdown and select the correct one - it will start with “urn:microsoft-crm/document-template/”.


Now you have all of the fields in the entities you selected available to you in the XML Mapping Pane.

It’s time to go nuts!

You can insert these fields into your Word template at will, putting them in your preferred order, formatting, etc.

Some key things to remember:

  1. Insert a field (here called a Content Control) by right-clicking on it in the XML Mapping tab. In the resulting menu, make sure to only choose either Plain Text or Picture - otherwise, Word will freeze and your template won’t work.

  2. If you have something in your document that should appear in a repeating list (such as, in our example, a list of opportunities related to the client), create a table with one row (plus a header, if you want one) that contains the fields you want from the related entity. Once that row is complete, select the entire row, then go the XML Mapping Pane and right-click on the entity relationship which contains the fields you used. Then under Insert Content Control, select Repeating.
  3. Quick tip: If you want to add in the image associated with the record, that field is called entityimage by default. Add it in as a picture!

    Here’s what my example client summary looks like now that I’m done putting in all of my content control fields:

Adding your template to CRM

Save the document, and you’re ready to add it into CRM. Go back to your browser and follow the exact same step you took at the beginning to get your original template, but this time, click upload.


Select your edited template as requested, and click Upload. Keep in mind that if you are a non-admin, this template will only be available to you. However, if it turns out well, you could share the document with your CRM Admin so that they could make it more widely available to others.

Once the template is uploaded, you will be taken to the template’s CRM record, where you can rename it if you like, and add a description.


Now your template is ready to use. Go to any individual record in the entity you used as the base entity for the template, and navigate to Word Templates. If you don’t see your new template’s name there, try re-opening your browser window. Once you see it, just click it and get your new document! Here’s what my example looks like for a sample client, Aetna:


As you can see, the new Word Templates feature allows you to quickly and easily generate a formatted document that’s specific to your business. For more detail on word templates, you can access Microsoft’s how-to here:

Check out other new features from the 2016 update here:



Topics: Microsoft Dynamics CRM 2016

Deploy Dynamics CRM with PowerShell Part 2: Importing SLAs

Today's post is written by Ian Moore, a Developer at Sonoma Partners.

If your Dynamics CRM deployment includes Service Level Agreements, you have probably encountered the following error in the import log when importing solutions from a dev environment to a testing environment:

“You can't edit an active SLA. Deactivate the SLA, and then try editing it.”


This error occurs because your solution contains an SLA that is active in the current environment. Having an SLA in your solution can complicate imports if you forget to deactivate the SLA before starting, as the error will come at the end of the import process and you will have to start over. Thankfully we can use PowerShell to automatically deactivate any active SLAs and re-activate after importing an updated solution.

For this example we will use a simple solution that contains one SLA component, and try importing it into an organization that already has an active previous version of the SLA.


After getting a connection with Get-CrmConnection, we can query for active SLA’s with Get-CrmRecords in the org we will be importing to:

$crmOrg = Get-CrmConnection –InteractiveMode

# get any active SLAs in the org
$SLAresults = Get-CrmRecords -conn $crmOrg -Entitylogicalname sla -Fields slaid, statecode, isdefault -FilterAttribute statecode -FilterOperator -eq -FilterValue Active

From here we can deactivate the SLAs with Set-CrmRecordState and import the solution:

# deactivate the active SLAs
$SLAresults['CrmRecords'] | % { Set-CrmRecordState -conn $crmOrg -CrmRecord $_ -StateCode Draft -StatusCode Draft }

# import solution
Import-CrmSolution -conn $crmOrg -SolutionFilePath C:\Path\To\Solutions\ -ActivatePlugins -PublishChanges

With the SLAs in draft status, the solution should import successfully. Once it is completed, we can re-activate the SLAs. One catch to this process is that any SLA that was not marked as Default will not be restored when it is reactivated – so after reactivation we can use Set-CrmRecord to update the “Is Default” property as necessary.

# re-activate SLAs
$SLAresults['CrmRecords'] | % { Set-CrmRecordState -conn $crmOrg -CrmRecord $_ -StateCode Active -StatusCode Active }

# update any default SLAs to be default again
$SLAresults['CrmRecords'] |
? { $_.isdefault -eq 'Yes' } |
% { Set-CrmRecord -conn $crmOrg -EntityLogicalName sla -Id $_.slaid -Fields @{'isdefault'=$true} }

Now any SLAs should be restored to their original state, and the solution with SLA updates is imported and applied. Here is a sample script that shows the whole process:

Make sure to download the Microsoft.Xrm.Data.PowerShell module from GitHub at and follow for updates to the module!

Topics: Microsoft Dynamics CRM Microsoft Dynamics CRM Online

Dynamics CRM – Specialized Operations

Specialized Operations were introduced in Dynamics CRM 2015 Online Update 1 and now included in Dynamics CRM 2016 as well.  They provide the ability to do what used to take separate messages (such as Assign or SetState) all in a single Update message. 

The following is the list of special messages that can now be executed in a single Update message:

  • AssignRequest
  • SetStateRequest
  • SetParentSystemUserRequest
  • SetParentTeamRequst
  • SetParentBusinessUnitRequest
  • SetBusinessEquipmentRequest
  • SetBusinessSystemUserRequest

Let’s jump into some sample code.  Before CRM 2015 Online Update 1, this is what it would look like to make an update to a record and then assign it to a different owner.

    var update = new Entity("contact");
    update.Id = contactId;
    update["firstname"] = "Blake";
    var assignRequest = new AssignRequest();
    assignRequest.Assignee = new EntityReference("systemuser", ownerId));
    assignRequest.Target = new EntityReference("contact", contactId);


As you can see, both the Update message and the Assign message need to be executed.

Now with CRM 2015 Online Update 1 and now CRM 2016, the Assign message can be merged into the Update message and save an extra round-trip to CRM as shown below.

    var update = new Entity("contact");
    update.Id = contactId;
    update["ownerid"] = new EntityReference("systemuser", ownerId));
    update["firstname"] = "Blake";


This new feature also has an impact for plug-ins and workflows.  If you perform an Update message that contains the Owner field then any workflow or plug-ins that are registered on Update will trigger once for all non-owner fields and then again for the owner fields.  The same goes when updating state/status fields.

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

How many tables and records are in your Dynamics CRM Database?

Today's post is written by Keith Mescha, Principal Architect at Sonoma Partners.

There are many reasons to want to know how many records are in each table in your MSCRM database. One of the primary instances in which we use this information is when we are assessing the level of effort required to upgrade a client from older versions of CRM, or from MSCRM On-Premise to MSCRM Online.

When migrating customers from one version of MSCRM to another, it's good to get a handle on what's in the current system from both an entity and record count standpoint. This is also a good way to know which entities are being used, and which are not. It's also very easy to pick out non-native objects as they will have a prefix on them. Sometimes you will see more than one prefix, which will indicate that 3rd party solutions are installed or they have had many different groups or people in their customizing without maintaining consistency.

There are a few tools out there that do this type of thing, but if you are On-Premise and have access to the underlying SQL database, and have a little bit of experience working in SQL, this can be quite simple.

BTW if you have an Online Microsoft Dynamics CRM org and want to get some record counts, see this blog to learn about Dynabacus, an application we built to help count records within Microsoft Dynamics CRM Online.

Once you get into that database via SQL Server Management Studio (SSMS) you can get row counts for all tables in a couple of different ways. One easy way is to run this simple SQL query and copy and paste the data from the results pane to Excel, and then start analyzing from there.

/* Change <orgname_dbname> to be the name of your organization database* */

USE <orgname_dbname>



SELECT, s.row_count, s.index_id

from sys.tables t

JOIN sys.dm_db_partition_stats s

ON t.object_id = s.object_id

AND t.type_desc = 'USER_TABLE'

AND s.index_id <2

order by 1


Depending on what version of SQL Server you are running there are also some built-in reports into SSMS you can use if you right click on the database name in SSMS and select the reports option. Run the Disk Usage by Table report. This will query the database and return you a simple interactive report where you can do some basic sorting. This will help you quickly uncover which tables have the most records, or which tables are taking up the most disk space.

Photo 1

Finally, within SSMS there is the ability to use the Object Explorer Details option from the View menu. You can then drill into the tables object. This will bring up a list of all your tables and some SQL technical stuff you don't care much about. If you right click on the header row you can select and deselect what columns to show. To make it cleaner, I typically remove all the other columns and only add the Row Count column. This is my go-to method when trying to get a quick glance at how many rows are in the tables in a MSCRM Org. The nice part about this one is your column selection is saved, so next time you go in you just browse to the tables and you get a quick read of the row counts.


Photo 3

Photo 4


Want to discuss the tables and records in your Dynamics CRM Database? Contact us to learn. In the meantime, download Dynabacus, the Microsoft Dynamics CRM Record Count Tool.

Dynabacus the Microsoft Dynamics CRM Record Count Tool


Topics: Microsoft Dynamics CRM