Wednesday, January 27, 2016

CRM Outlook Client: Emails Tracked to Inactive Records

The way the CRM Outlook client tracks emails and links them to CRM records is based on email address.  It will take the customer's address from the email and search the entire CRM system for any records with an exact match.  This search covers any entity with an email address field, including custom entities, regardless of record status or anything else, really.  This can sometimes create a couple issues:

Problem #1: Emails are tracked to entity types that you don't necessarily want them tracked to. 
Unfortunately, CRM does not let you dictate which record types the Outlook Client tracks emails to.  As long as there's an email address field on the entity, the Outlook Client will attempt to link to it.

The fix: There is no way to turn off individual entity types from Outlook tracking.  This is something to keep in my when designing your entities.  If you have an entity that must have an email address stored on it, consider creating the field as a simple, single line of text.  Unless it's configured as an email-type field, the Outlook client will ignore it.

It's important to note, too, that once you create an email field on an entity, Outlook will search that entity for matches, and CRM doesn't let you undo or disable that.  

Problem #2: Emails get tracked to inactive records.  
One thing we've noticed after several years of using the CRM Outlook client is that it does not discriminate between active or inactive records.  CRM will try to link emails to matching email addresses on records regardless of record status.  As a result, you may notice inactive records being linked to your emails.

The fix: As we already know, CRM take a blanket approach to linking emails.  The key in linking track an email against a record is the email address.  So, if you want inactive records to be ignored by the Outlook client you can create a workflow or plugin that deletes or changes the email address upon deactivation.  For one of my clients we just add "-inactive" to the end of the email address.  Adding this text will cause CRM to not recognize the email address and skip over it.

Tuesday, January 26, 2016

CRM Ellipses Missing from Subgrid Command Bar

This may be the simplest post I ever make.  Recently we had a remote user who had an issue where the ellipses (the '...' icon) would not appear in the command bar of a subgrid.  They were looking for the Export to Excel button.  We are running CRM 2015 on premise.  This is apparently an issue with CRM 2015 and happens when the labels in the command bar run too long (see here).  Usually CRM accounts for this by hiding the buttons and displaying the ellipses at the end.  After checking for Export to Excel permissions (on the Business Management tab of a Security Role), customizations, and every piece of functionality I could think of, I finally found the fix.  The fix is...

...(drum roll)...

Widen your browser window.  

Sometimes getting caught up in the complexities of the system can distract you from the most obvious and simple solutions.

Thursday, December 24, 2015

How to Find Your CRM Online IP Address

I was recently asked to build a CRM extension to submit Opportunity records to an external API. We decided to build this as an asynchronous custom workflow activity so it would trigger upon Create, but also could be called manually. Behind the scenes I use C# to make an HTTPWebRequest, using values from the Opportunity record.  So, the user would create an Opportunity record, then run the workflow which gathers data from the Opportunity and sends it the the external API for processing.

Making a web call from CRM is generally straightforward, but this case was a little different - the external API required us to register our IP address with them for authentication, and then use that IP address in all subsequent calls.  At first I though finding CRM's IP address would be easy - I'd just use one of the many IP lookup sites out there and plug in the web url (e.g., "https://myorg.crm.dynamics.com"), and we'd be done.  But it's not that straightforward (it never is).  

CRM uses separate servers for asynchronous processes (asynchronous workflows included).  So while your front end application server ("https://myorg.crm.dynamics.com") has one IP address, the custom workflow activity will come from a different one.  How do you find the IP address of your asynchronous server?  After trying many methods, I used the code below to do this.  This code makes a WebRequest call to a site called "ipify", which tells you your IP address from programmatic calls.  It then reads the response (the IP Address) into an Output argument which the CRM workflow puts into a field on the Opportunity record.  

There may be easier ways to find your IP address, but I did it this way because of the limited access we have to server level information in the cloud.  I tried several DNS property calls and got security errors.  So the last way I could think was to record on the receiving end of the call (thanks https://www.ipify.org/ !!!).  

Disclaimer: CRM Online uses several IP ranges which you can find here.  While this code will tell your current IP address at the time of the call, your IP address can and will change unexpectedly.  That said, this will tell you want range you're in, and I hope this code is helpful to someone out there because I spent many hours before finally figuring this out.  

string tmaurl = "https://api.ipify.org";
WebRequest request = WebRequest.Create(tmaurl);                                    
WebResponse response = request.GetResponse();

WebHeaderCollection header = response.Headers;
using (var reader = new System.IO.StreamReader(response.GetResponseStream()))
{
    string responseText = reader.ReadToEnd();
    this.ServerMessage.Set(executionContext, responseText);
}

[Output("Server Message Output")]
public OutArgument<string> ServerMessage { get; set; }

Thursday, August 13, 2015

Dynamics CRM Performance Test with PowerShell

This is a little script I made as a sort of performance test for CRM.  Basically, how it works is it queries 1000 Contact records from CRM and tracks the time it takes to loop through them and make an update to each one.  It's designed to update a single field (in this case, 'fax') with a dummy value, then change it back.  So, to not overwrite valid data it queries only records where the field to be updated is null, makes the update, then it changes it back to null.

Furthermore, it writes the time it takes to make all the updates to a csv file of your choosing and then restarts itself to run after a certain amount of time (currently 1 hour).  Over time it creates a log of these durations which you can build charts and reports off of.

One thing to point out is that the list of 1000 contact records is queried through the SQL database, so as written this script only works for on premise deployments.  In order to make it work for CRM Online instances you'll need to change the SQL query to a CRM query call to the API.  A few of my other posts should show you how to accomplish this.

Please remember, this is a performance test.  Speedy, efficient code is not the goal here, but rather understanding the time it takes to run.  You may notice some unnecessary steps being performed.  I'm just trying to hit as many areas of the system as possible to get as much of a complete read as possible.

This script might be useful to measure differences in speed throughout the day, or to compare the system before and after large changes.  In my own case, I built this script to get some kind of measurement of the system prior to us enabling CRM Auditing.  There were concerns that this feature would slow down CRM.  And for those interested, there was no noticeable difference shown to us.

I hope this is useful to someone out there.

#Add SDK references
    Add-Type -Path "[mySDKPath]\Bin\Microsoft.Xrm.Sdk.dll";
    Add-Type -Path "[mySDKPath]\Bin\Microsoft.Xrm.Client.dll";
    Add-Type -Path "[mySDKPath]\Bin\Microsoft.Crm.Sdk.Proxy.dll";

#CRM Connection
    $CRMUrl = "https://[myCRMPath]";
    $CRMLogin = "[myCRMAccount]";
    $CRMPwd = Read-Host "Enter password for account $CRMLogin"
    $CRMConnection = [Microsoft.Xrm.Client.CrmConnection]::Parse("Url=$CRMUrl; Username=$CRMLogin; Password=$CRMPwd");
    $CRMService = New-Object -TypeName Microsoft.Xrm.Client.Services.OrganizationService -ArgumentList $CRMConnection;

#SQL Connections
    $sqlUser = "[mySQLAccount]"
    $sqlPwd = Read-Host "Enter password for SQL account $sqlUser" -AsSecureString
   
#Source datasource
    $sourceDatabaseServer = "[mySQLDatabaseServer]"
    $sourceDatabase = "[mySQLDatabase]"
    $sourceConnectionString = "Server=$sourceDatabaseServer;uid=$sqlUser; pwd=$sqlPwd;Database=$sourceDatabase;Integrated Security=true;"
    $sourceConnection = New-Object System.Data.SqlClient.SqlConnection
    $sourceConnection.ConnectionString = $sourceConnectionString

function PerformanceTest{
#SQL Query
    #Source Database Query
    $top = "1000"
    $sourceConnection.Open()
    $sourceQuery = “SELECT TOP $top contactid, fax
                    FROM [mySQLTable]
                    WHERE fax is null”
    $sourceCommand = $sourceConnection.CreateCommand()
    $sourceCommand.CommandText = $sourceQuery
    $sourceResult = $sourceCommand.ExecuteReader()
    $Global:sourceTable = New-Object “System.Data.DataTable”
    $sourceTable.Load($sourceResult)
    $sourceConnection.Close()

    #Looping Through Records
    $count = 0
    $startTime = Get-Date
    foreach($record in $sourceTable){
        $count ++;
        #Query Record
        $CRMQuery = New-Object -TypeName Microsoft.Xrm.Sdk.Query.QueryExpression -ArgumentList "contact";
        $CRMQuery.ColumnSet.AddColumn("firstname");
        $CRMQuery.ColumnSet.AddColumn("lastname");
        $CRMQuery.ColumnSet.AddColumn("telephone1");
        $CRMQuery.ColumnSet.AddColumn("telephone2");
        $CRMQuery.ColumnSet.AddColumn("gendercode");
        $CRMQuery.ColumnSet.AddColumn("address1_country");
        $CRMQuery.ColumnSet.AddColumn("ownerid");
        $CRMQuery.ColumnSet.AddColumn("address2_country");
        $CRMQuery.ColumnSet.AddColumn("address1_line1");
        $CRMQuery.ColumnSet.AddColumn("address2_line1");
        $CRMQuery.ColumnSet.AddColumn("emailaddress1");
        $CRMQuery.ColumnSet.AddColumn("emailaddress2");
        $CRMQuery.Criteria.AddCondition("contactid", [Microsoft.Xrm.Sdk.Query.ConditionOperator]::Equal, $record.contactid.Guid);
        $CRMQuery.Criteria.AddCondition("fax", [Microsoft.Xrm.Sdk.Query.ConditionOperator]::Null);
        $CRMQueryResults = $CRMService.RetrieveMultiple($CRMQuery);
        $CRMRecords = $CRMQueryResults.Entities

        #Create Entity Object and identify
        $targetRecord = New-Object -TypeName Microsoft.Xrm.Sdk.Entity -ArgumentList "contact";
        $targetRecord.Id = $CRMRecords[0].Id.Guid;
       
        $targetRecord["fax"] = "test data";
        $CRMService.Update($targetRecord);

        $targetRecord["fax"] = $null;
        $CRMService.Update($targetRecord);

        Write-Host "Record"$record.contactid": $count/$top"
    }
    $endTime = Get-Date
    Write-Host "`n`nStarted: "$startTime
    Write-Host "Ended:   "$endTime
    $duration = New-TimeSpan $startTime $endTime
    Write-Host "`nDuration: $duration"
    Add-Content -Path "[myPath]\PerformanceTest.csv" "$startTime,$duration,$top,PROD" -Force

    Write-Host "Timing out for 1 hours"
    Start-Sleep -Seconds 3600

    PerformanceTest
}
PerformanceTest

Monday, July 13, 2015

CRM Outlook Client Settings

There are many settings to configure of the CRM Outlook Client, and they're spread across several places.  I've brought all these settings into one page to be used as a reference.  Please note, the screenshots below are for example only.  I'm not intending them to serve as an example of what you should set.  

System Wide Settings
These settings will be applied to everybody.  

CRM System Settings Page
(CRM > Administration > System Settings)

    Email Tab: 
    Use this tab to configure system-wide, server level, email processing settings.  

    Outlook Tab: 
    Use this tab to set default schedules for Outlook Client synchronization.  
   
Synchronization Tab: 
    This tab has settings for filters and data that will be synchronized with the Outlook Client.  

Security Permissions
Sync to Outlook: This is located on the Business Management tab of any security role and allows the user to connect CRM to their Outlook if they have the client installed.  Without this setting the user won't be able to connect their Outlook to CRM.  

Go Offline: This also is located on the Business Management tab of the security role form and determines whether or not the user can download CRM data to their computer for offline use.  

Other:

  • Users will need Create, Append, Append to, and Assign permissions on the Activity entity if they want to track emails in CRM
  • Users will need Append To permissions for any entity that has an email address field on it.  This will allow tracked emails to be linked to those records as well.  

User Settings

Outlook Personal Options
(Outlook > File > CRM > Options)

Synchronization Tab
Synchronization settings are automatically inherited from the defaults set in the System Settings, but this tab can be used to make user-level changes from the system defaults.  

Email Tab
A handful of miscellaneous settings are available here to configure on a user level.  


Microsoft Dynamics CRM Diagnostics Window

Lastly, there are additional settings you can enable or disable in the separate Diagnostics program that gets installed along with the Outlook Client.  You access this by going to Start > All Programs > Microsoft Dynamics CRM > Diagnostics.  Most of these are pretty self explanatory.  

Synchronization Troubleshooting Tab

Advanced Troubleshooting Tab

Tuesday, June 16, 2015

CRM Outlook Client Troubleshooting

I've spent a measurable portion of my life troubleshooting errors from the CRM Outlook client from version 2011, 2013 and 2015.  It's been a very buggy experience for us, but we've put up with it because we need to track our emails in CRM.  I've put together a few ideas I've learned over the years and some thing you can check if you're having issues too.

General Pointers
  • Permissions: You'll need at least these permissions to use the Outlook client.  There undoubtedly more, but here's what I've come across.  
    • Sync to Outlook (Business Management tab)
    • Go Offline in Outlook (Business Management tab), if you want people to go offline.  
    • Create, Append, Assign permissions on Activity
    • Append To permissions to any entity with an email address field
  • Offline capability for CRM for Outlook (entity setting): Leave this setting disabled on the entity level unless you actually want to take this entity offline.  Otherwise it creates offline filters for the user behind the scenes and may create more burden on the system.  
  • View Pinning: Views in the Outlook client have a small pushpin icon, that when clicked, will automatically keep the view open whenever Outlook is opened.  It also downloads all of the records in the view and caches them on the user's computer.  If you pin multiple large views it can slow down the system.  
  • Group Mailboxes: Shared mailboxes (e.g., support@company.com) generally can't have their emails tracked directly within the group inbox.  The Outlook client can only sync with a user's default mailbox on one computer.  The workaround is to forward the email to your personal inbox to track it.  

Troubleshooting 

General Troubleshooting: My first go-to for troubleshooting for cryptic error messages is to enable tracing.

  1. Enable tracing on the user's computer: Start > All Programs > Microsoft Dynamics CRM > Diagnostics > Advanced Troubleshooting tab > Check the Tracing box
  2. Wait until the error happens
  3. Disable tracing
  4. Retrieve trace log files from C:/Users/[user]/AppData/Local/Microsoft/MSCRM/Traces
  5. View log using CRM Trace Log Reader tool.  Do a web search for Dynamics CRM Trace Reader.  It's a simple executable that organizes the content of these trace files in a readable format.  

Here are some error messages I've come across and what I know of them.

An unknown error occurred while synchronizing data to Outlook.Item Name=[Email Subject]
I've seen this error when users try to track an email from a shared, group inbox that is not their personal inbox configured to sync with CRM.

The requested record was not found or you do not have sufficient permissions to view it.
For me this has been a permissions error.  Enable tracing using the steps above and look for a privilege error.

Only items in the default Microsoft Outlook store can be promoted to Microsoft Dynamics CRM.Item Name =[Email Subject]
We've received this when trying to track emails in a shared group mailbox or exchange folder.  The only workaround I'm aware of is to forward messages to your personal mailbox for tracking.

This email will be tracked in CRM.  
I've seen this message in the email address sometimes when tracking it.  Then it will just stay in that state forever.  After much troubleshooting I found out that this appears when you have the email open, but use the Track button in the main Outlook window.  It will then only be tracked in CRM if you forward or reply to the email.  This seems like a bug that Microsoft should fix.  My guess is that when you have the email open, CRM thinks it's a draft waiting to be sent.  To avoid this, if you have the email open, use the Track button in the email.  If the email is not open, select the email and use the Track button in the main Outlook window.

Wednesday, May 20, 2015

CRM Queries Using PowerShell

This is a continuation of several posts about interacting with the Microsoft Dynamics CRM API using PowerShell and the SDK.  Please see my earlier posts for more information on the structure of these scripts.

For this post I will show how to query a list of records using the SDK through PowerShell using the CRM query expression object.  As you read you'll notice that it resembles the Advanced Find more that regular SQL, so if you have a more complex query then SQL would be better.  I may show how to use SQL against the CRM database through PowerShell in a later post.

When the tools in the user interface aren't enough, this is the easiest way to get a list of records to process in CRM (that I know of).  It will return records as an object that includes the GUID and any other fields you specify.  You can then lead the records into a variable and loop through them for processing (updates, deletes, appends, etc.).

Section 1 below is the standard load of the sdk dll's so we can call the sdk.

Section 2 below is the usual CRM connection setup.

Section 3 contains the CRM query.
-----------------------------------------------------------------------------------
#1 Add SDK references
Add-Type -Path "C:\[mySDKLocation]\Bin\Microsoft.Xrm.Sdk.dll";
Add-Type -Path "C:\[mySDKLocation]\Bin\Microsoft.Xrm.Client.dll";
Add-Type -Path "C:\[mySDKLocation]\Bin\Microsoft.Crm.Sdk.Proxy.dll";

#2 CRM Connection
$CRMUrl = "https://mycrm.com";
$CRMLogin = "myUserAccount";
$CRMPwd = Read-Host "Enter password for account $CRMLogin" 
$CRMConnection = [Microsoft.Xrm.Client.CrmConnection]::Parse("Url=$CRMUrl; Username=$CRMLogin; Password=$CRMPwd");
$CRMService = New-Object -TypeName Microsoft.Xrm.Client.Services.OrganizationService -ArgumentList $CRMConnection;

#3 CRM Query
$CRMQuery = New-Object -TypeName Microsoft.Xrm.Sdk.Query.QueryExpression -ArgumentList "myEntityType";
$CRMQuery.ColumnSet.AddColumn("myFieldName");
$CRMQuery.Criteria.AddCondition("myFieldName", [Microsoft.Xrm.Sdk.Query.ConditionOperator]::Equal, "myValue");
$CRMQueryResults = $CRMService.RetrieveMultiple($CRMQuery);
$CRMRecords = $CRMQueryResults.Entities

#Add code to loop through and process each record, or whatever
--------------------------------------------------------------------------------------------------------------

Section 3 Explained: 
This block of code creates the Query Expression object and defines which entity will be queried.  The AddColumn command lets you add fields on the entity as attributes to the results.  The AddCondition method lets you add a Condition Operator to use (Equal, Contains, Begins With, etc.) to filter your results.  After that's set up, we use the CRM Service connection to execute the query and load the results into a variable.  And lastly, we load the entities from the results into another variable to isolate just the records.  At this point the $CRMRecords variable is a list of records that you can loop through using a "foreach" block.

Additional Condition Operators:
The example above uses the "Equals" condition operator, but this can be replaced with a variety of others much like the Advanced Find through the CRM user interface.  You could rely on intellisense to show you the list, or you could look at all the filter options displayed in the Advanced Find.  They are largely the same.