Filtered View Performance

As is well known it is best practice to use Filtered Views when performing queries on the CRM database most notably for report writing. This is because doing so adheres to the SDK standard and also yields many benefits such as:

• Security model adherence - reports written this way will automatically adhere to the CRM security model without having to do anything further.
• Exposing "name" attributes for Options Sets (including "Two Options") - these are denormalized in the filtered view so writing additional joins to retrieve the friendly names is unnecessary
• Embedding and Context Awareness - reports can be easily embedded into the CRM forms and run based on the current context without further input
• Advanced Find pre-filter - This filter mechanism becomes available eliminating the need to hard-code parameters in the report definition
• Formatting - Date, number, and currency formats defined in CRM can easily be applied to CRM reports for a consistent experience
• Built in parameters - Various other built in parameters that the CRM platform can leverage to simplify the report writing experience, avoid unnecessary hard-coding, and ensure consistency and cohesiveness between the CRM application and custom reporting (e.g. CRM_URL)

There is one downside - there can be a performance degradation when running queries against Filtered Views (vs. say the regular entity views). Of course this can be very significant as if your report takes a long time to run it more than negates all the benefits cited above.

However, the query performance of Filtered Views needs some additional analysis. From what I've read, most attribute this degradation to the fact that the Filtered Views (unlike other views) perform additional security checks against the CRM security model. And while this is indeed the case, my experience has indicated that this particular aspect does not really impact adversely. In fact, this additional check is fairly trivial (it might be that in cases where there is a heavily nested security hierarchy that this impacts more - so not to be completely disregarded).

So what causes the performance degradation? The following analysis of queries against various entities (customized - not OOTB) is very telling:

The crux of the analysis are columns 2 and 3. For example, "account" has 118 Option Sets and 169 Two Options for a total of 287 Options. As you can see, a simple query against the FilteredAccount vs. account entity yields a difference of 20 seconds! That drops dramatically for "event" where there are only 191 Options - dramatically but still not sufficient as the query difference remains at 6 seconds. The difference drops as the number of Total Options decreases such that when there are around 100 Options the difference becomes fairly trivial.

So in short, it is the number of Options on the entity that are the major cause of query degradation and that can be attributed to the additional joins required to obtain the friendly name (as described above). Consider that in the case of account the Filtered View is performing a little over 287 additional joins as opposed to the regular entity view ("a little over" as there are a few other joins required for the security model interrogation). And also the analysis shows that the degradation is exponential rather than linear after the "degradation threshold" is hit.

So what are the takeaways?

First of all it's important to mention that this performance analysis was run using SQL 2010. The experience might be otherwise on later versions. But according to the literature the number of query joins in this version of SQL is only limited by system resources (as opposed to previous versions where there was a hard upper limit). So one way of minimizing this issue is to add some horse power to the SQL Server. 

Secondly, it is noteworthy that the row count has no noticeable impact on query difference (for example, see row count of "account" vs. "invoice"). And similarly the difference is not caused by indexing issues. The basic Filtered View query leverages existing system generated indexes.

Third, the number of Option Sets shown in the analysis has to be slightly unusual. In most cases, I would imagine that there would be far fewer of these and therefore using the Filtered View for your queries will simply not have any noticeable impact on performance (although it's easy to run a quick comparison query to determine if this statement is correct or not).

Fourth (and perhaps most importantly), make sure to purge unnecessary or legacy attributes. The likelihood in cases where there is such a high number of options is that many of them have fallen into disuse over time. Opening the entity form and viewing how many of them no longer even appear on the form will probably shed some light here. This is not only good practice from a query performance point of view, but also helps to keep the data neat and clean which in turn contributes to a simplified and confusion-free user experience.

Finally, information is power and understanding what is underlying a Filtered View can be factored into design decisions well in advance. In fact, it is very likely that the reasons for going with a whole bunch of custom Option Sets vs. product-line-item kind of design have fundamentally changed with recent overhauls of the CRM user interface. In previous versions, the number of clicks to add line items was often prohibitive - an experience which has been significantly optimized especially in CRM 2013 (and probably more so with the very imminent CRM 2015 release).

Contact/Outlook Address Sync Issues

There appears to be an issue with syncing the contact address with Outlook under certain circumstances. Specifically this circumstance appears to occur when line 1 and line 3 of the contact address are filled out but line 2 is left blank. In such a situation the following occurs when syncing the contact:

1. The contact syncs correctly to the corresponding Outlook contact record
2. Outlook then immediately syncs back to the CRM contact, moving line 3 back to line 2

The following screenshots illustrates this phenomenom visually:

To re-emphasize - this boomerang update effect happens immediately i.e. in the single sync transaction that is initiated from the CRM Outlook contact. The result can be quite perplexing and unsettling as it results in the following set of circumstances:

1. You notice that an update made on the contact record is continuously reverting 
2. The audit tells you an end user made the reversal (and it could be any end user - really depends on who performed the syncing action first)
3. The audit tells you that the reversal happened within mere seconds of the update

The result is a manhunt to find the offending plugin, workflow, third party Outlook add-on that someone is using that might be behind this issue. And of course based on the analysis above all that is a red herring...

I think the reason why this occurs is because Outlook just arranges the address into separate lines rather than separate fields so that when line 2 is missing line 1 and line 3 follow one another making Outlook interpret line 3 as line 2.

I have not seen any literature about this issue although circumstantial evidence points to the fact that this issue has been around for quite some time (i.e. I seem to recall perplexing unexpected Outlook updates fitting the above description that until now I have not been able to put my finger on).

Anyway, I am able to reproduce this issue at will so it's definitely some kind of product defect. 

This issue has been reported to Microsoft and at the moment I don't have a solution to this issue although at a minimum it is comforting to at least know what this issue so it can be avoided and sanity be preserved. 

User-Owned or Organization-Owned Entities?

When creating new entities you have to choose whether you want the entity to be user-owned or organization-owned. This setting cannot be changed after the entity is created. So it is important to get this right from the outset or otherwise this will come back to bite you at some later point in time. Especially if the entity you create has a lot of dependencies (i.e. relationships) set up. And this will be compounded a hundred-fold if a requirement surfaces post go-live that requires such a setting change...

The primary impact of entity ownership is related to the security model. With organization-ownership, access is binary - either the user can view (or edit, create etc.) ALL or NONE. With user-ownership, access is much more nuanced allowing for much finer control over the SCOPE of which records can be viewed (or edited etc.).

The issue is that it can be difficult to anticipate what to expect in the distant future. Meaning that all good analysis and critical thinking aside, a requirement might surface in phase 6 of the project that was heretofore unforeseen. And suddenly we're left ruing a decision made some years back - a decision which made perfect sense at the time it was being made.

When we think of user vs. organization ownership, we tend to think of it in terms of whether anyone "cares" if a record could be or should be owned by a particular individual in the organization. If the answer is negative then we tend to think of it as organization-owned. And not only do I think there's nothing wrong with that thinking, I think that if the designer of the system takes a moment to ponder this then that is in fact highly commendable.

However, as I've discovered over the course of many projects, the issue with record ownership is that the business logic of the concept may not always mirror the technical requirement. It may well be the case that no-one "cares" who owns a record. It might even be the case that seeing an "owner" on the record might be unnecessary or even confusing (i.e. the owner field can or should be hidden on the form). However there could still be a requirement to limit access to the record that still needs to leverage the user-ownership security model.

The "sharing" feature is a classic example of this scenario. For example:

• There might be a user or team ("team A") that you only want to grant access to a limited subset of records ("entity B"). 
• That subset might have nothing to do with ownership e.g. we want to grant "team A" access to "entity B" records which have been flagged as "Vendor" 
• The above effectively means that we wish to grant "team A" access to a subset of "entity B" records based on a non-ownership type attribute
• If "entity B" is organization-owned, there is no way to configure the above security restriction. Organization-owned entities are binary - either you can see all "entity B" or you cannot see "entity B" at all.
• In order to accomplish this requirement, "entity B" will need to be recreated as a user-owned entity.

Hopefully the above scenario illustrates how it's possible - best practice design considerations aside - to land up in situation where an entity needs to be converted to user-owned. And I'm sure I'm not the only one who has come up against this issue.

Based on this, we can come up with a rule of thumb for record ownership. And that is - if there is not a clear requirement for a record to be user-owned or organization-owned at design time, and even if it might seem to make sense from a logical business perspective that the entity should be organization-owned (using the "no-one cares" formula mentioned above), it probably still make sense to create the record as user-owned. After all, it's very easy to hide ownership fields on the form and one can still set up security so it behaves in a binary fashion as organization-owned entities. 

In short, if you got it wrong and set up an entity as user-owned, the price to pay is relatively small. On the other hand, if you got it wrong and set up an entity as organization-owned, you could be looking at open heart surgery.

The theory might all be well and good, but let's say you're presently in the predicament described above i.e. you're in production and have come across a requirement that means that you'll need to convert an entity from organization-owned to user-owned - what are the steps to achieve this? In the next post I'll attempt to address this with a step by step walk through.

Debugging Workflow Plugins in CRM Online

The Plugin Registration Tool has some important features to enable the effective troubleshooting of workflow plugins (and plugins in general). The following posts contain useful walkthroughs of these debugging features:

Debugging standard plugins:

http://guruprasadcrm.blogspot.in/2011/11/how-to-debug-crm-2011-online-plugin.html

Debugging workflow plugins:

http://blogs.microsoft.co.il/rdt/2013/03/08/debugging-workflow-custom-activities-with-plugin-registration-tool/

But sometimes I find that adding good old-fashioned trace statements to the plugin code can also come in very handy.

The following illustrates this form of debugging for workflow plugins:

1. Add the ITracingService reference
2. Output trace information at various points
3. Throw an exception to force the trace information to be output

The plugin will fail as you have forced an error by throwing an exception. And you can now go into the failed workflow and view the trace information that was outputted. 

Workflow FetchXml Query

Out of the box, workflow does a pretty good job of allowing you to specify test conditions to perform the appropriate branching logic. For example, say you wanted to determine whether a contact's parent account has a credit hold - well... configuration-wise requirements don't get much simpler than this.

Now let's add a wrinkle. Let's say you have an account hierarchy such that you wish to determine whether the contact's grandparent account (i.e. the parent account of the contact parent account) has a credit hold in order to make the necessary branching decision. This minor change in requirement of just pushing the test condition up one level cannot be achieved with the out of the box workflow tools. This is because the workflow Check Condition (and for that matter Wait Condition too) can only interrogate attributes of the current record and of parent records. Any relationship beyond that definition cannot be queried using the standard CRM workflow UI tools.

Fortunately we have a way of breaking out of this box using workflow plugins. I have created a plugin and published it on CodePlex that allows you to pass in a FetchXml query that will be evaluated. The plugin will return to the workflow whether the said FetchXml query returns results and can therefore be used to take necessary branching logic.

Let's use the example above to illustrate how this works.

First define a FetchXml query that will perform the evaluation that you are looking to do (you can use the Advanced Find "Download Fetch XML" to help you with constructing this query). In our case this would be as follows:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">  <entity name="contact">    <attribute name="fullname" />    <attribute name="telephone1" />    <attribute name="contactid" />    <order attribute="fullname" descending="false" />    <filter type="and">      <condition attribute="contactid" operator="eq" value="{0}" />    </filter>    <link-entity name="account" from="accountid" to="accountid" alias="ae">      <link-entity name="account" from="accountid" to="parentaccountid" alias="af">        <filter type="and">          <condition attribute="creditonhold" operator="eq" value="1" />        </filter>      </link-entity>    </link-entity>  </entity></fetch>

NB: If you wish to pass in the current record's context as one of the filter criteria make sure that the following is included in FetchXml's condition clause (the attribute should be primary key of the entity that this workflow is running against):

<condition attribute="contactid" operator="eq" value="{0}" />

Now create a workflow and add the FetchXmlQuery step:

Pass in the FetchXml as a parameter to the FetchXmlQuery plugin:

And then define the rest of your branching logic based the results of this query.

If you have configured this correctly, you should be able to see the following results:

• When the contact's grandparent account has a credit hold

• When the contact's grandparent account does not have a credit hold

Workflow: Comparing Two Null Fields

You can use the Check or Wait Condition in workflow to compare one field to another field. However if both fields are null then the comparison evaluates to false. For example, say we compare "street 3" of the contact to "street 3 of the parent contact record - if both of these fields are not populated then the comparison evaluates to false rather than true. The following workflow snippet illustrates this phenomenon:

Typically that's what happens when comparing nulls from a database perspective because when you compare null to null you are essentially comparing an "unknown" value to another "unknown" value rather than "nothing" to "nothing" (nothing = nothing; whereas an unknown value cannot be said to equal another unknown value). CRM workflow behaves accordingly. I'm not sure that I would have designed it this way as most humans would look at two empty address fields and you would have a hard time convincing them that they in fact have different values which is what the workflow evaluation is essentially telling them. However this is how it has been designed, so null values need to be taken into account when designing such comparisons.

Out of the box  workflow does not have the equivalent to the SQL isnull conversion function which allows you to specify what null "means" and therefore perform a simple comparison (e.g. isnull(field1,'x') = isnull(field2,'x') will evaluate to true if both field1 and field2 are null since in that event they are both converted to "x" and of course x=x).

So one option is to create a workflow plugin that will perform this logic.

In the absence of such a plugin, you could use the following workflow logic to essentially implement this isnull comparison feature. It does tend to make the workflow a little longer... especially if there are multiple conditions to be evaluated as then you need to perform this comparison block for each field be compared. But it does tend to do the trick.

The basic comparison logic is that when the condition evaluates to true then "do nothing". If it evaluates to false, then perform the action associated with a false evaluation and stop processing. At the end of the comparisons, perform the action associated with a true evaluation as if the workflow got to this point, it must have passed all comparisons.

CRM Online Bulk Update Options

Having previously discussed the challenges with running bulk updates in CRM Online, I thought it would only be right to follow up with a post that provides some guidelines for doing so in the event that this is required. For simple updates you can obviously use the multi-edit feature and for slightly more complex requirements you can obviously use the "re-import from Excel" technique whereby you can export the data, making changes to the data and then import it back into CRM. And finally, if you have third party tools available such as Scribe Online or Scribe Insight you can obviously use those to cover pretty much any update requirement you have.

But what options do you have in the absence of such tools in cases where the update is beyond the complexity of the simple tools provided by CRM without having to resort to programming techniques to achieve the objective?

One alternative is to combine the power of workflow with the "re-import from Excel" technique.

CRM workflow is pretty powerful these days providing a lot of capability of manipulating CRM data using standard workflow actions and freely available codeplex solutions. So assuming your update requirement can be constructed via a workflow all you need to do is to "trigger" that workflow to run against all the records you want it to run against.

This can be achieved as follows:

• Create a custom text attribute called something like "Temp Workflow Update" 
• Create your workflow to trigger off the "field change" of this field
• Construct an Advanced Find query that retrieves the subset of records you want to update and include the "Temp Workflow Update" field in the columns

• Export the rows for re-import

• Go into the Excel file and add some text value to your "Temp Workflow Update" field, copy down, save and re-import

• This re-import should now trigger the workflow which will carry out the update that you configured it to perform.

Spell Checking Options

I came across this topic in my email archives. I think it's still a relevant topic so I thought I'd post it. It deals with options for spell checking within Dynamics CRM.

One of the simplest options of course is to use the spell checkers that come built into the various add-on toolbars such as the Google toolbar.

More sophisticated options employ the use of a spell checker API where the experience is similar to spell checking in applications such as Microsoft Word.I have implemented 2 such versions of the spell checker.

The first one powered by webspellchecker.net is a paid for solution ($250 per year for up to 10000 sessions and then $0.025 for each additional sessions - see web site for additional pricing options). This tool is very intuitive and will be familiar to anyone who has used the Microsoft Word spell checker solution.

The second option was to use a free product called NetSpell – this is deployed on the server entirely i.e. the check occurs against a dictionary defined on the server. This tool is also fairly intuitive however the way in which the spell checker works is not quite as comprehensive as that as webspellchecker.net which I think is in evidence by comparing the screenshots.

In summary, below  are the pros/cons of each approach:

webspellchecker.net Pros

• Quite a bit more comprehensive in terms of functionality including an easier to use interface as well as grammar and thesaurus options
• Very intuitive
• Allows for custom dictionary to be defined
• Is a supported product and will be maintained over time presumably with incremental improvements, dictionary term additions, etc.

webspellchecker.net Cons:

• Is not free. $250 per 10000 sessions. To get a true cost it is necessary to determine the number of users who will be using this on a daily basis multiplied by the number of times they are likely to use it per day e.g. the formula would look something like: #users*#dailysessions*200 (200 being roughly the number of work days per year)
• Is an online solution although there is also a licensed downloadable version for a not insignificant cost

NetSpell Pros

• Fairly comprehensive
• Fairly intuitive
• Allows for custom dictionary to be defined
• On premise solution so not reliant on network performance
• It is free

NetSpell Cons

• Is not a supported product in case of product issues
• Dictionary is not auto maintained
• User interface not as slick as that of webspellchecker.net 

Cannot add more picklist or bit fields

I came across this issue today in a 4.0 environment that still uses SQL 2005. Time to upgrade, huh? Happily that's soon to be the case - so this hopefully is just for the record books.

Anyway the symptom was that while the user was able to add nvarchar fields to the accounts entity, it would error out each time the user wanted to add a new bit field (or for that matter a picklist) it would fail with an error message.

Using the trace tool it took pretty quick to identify the underlying cause. The following exception appeared in the trace file:

Exception: System.Data.SqlClient.SqlException: Too many table names in the query. The maximum allowable is 256.

So what is causing the "too many table names" in query?

Simple. Every time an attribute is added to an entity, the entity views (regular entity view and filtered view) are updated. The filtered view in particular joins to the StringMap view for picklists and bit fields to obtain the corresponding friendly "name" field. For example, for an account field called "new_flag" it will join to StringMap view to create a new virtual field in the FilteredAccount called "new_flagname".

One only needed to look up a little higher in the trace file to see the view being constructed with many joins for the bit and picklist fields. Such that if the number of these two types of field combined exceeds 256 (or thereabouts given other joins that may already exist) it will cause this join limitation to occur. Which is only a limitation on SQL 2005. This is no longer a limitation from SQL 2008 and up.

The options for resolving this issue are therefore:

1. Upgrade. Really. The technology you are using is around 7 years old (at least) and there does come a point where the compelling reason to upgrade is just the combined benefits of all the various enhancements that have been introduced over time (that is, if you cannot find a single compelling reason).
2. Clean up your system and review whether you actually do need all those fields in your environment. This actually is relevant whether you upgrade or not. I'm a big proponent of keeping the environment as clean as possible as my first post on this blog will attest to (disclosure: the above environment is extended directly by the client as we like to encourage our clients to do so they are not reliant on us for every little change required).

Report not showing under "Run on Current Record"

This has happened to me on a number of occassions. I modify an existing report making sure it adheres to all the CRM SDK reporting standards and in the report definition I make sure to specify that he appropriate entity/entities are entered in the "Related Record Types" and that the "Display In" is set to "Forms for related record types" and/or "Lists for related record types".

Yet when I open up the entity form (in the example above this would be a Marketing List record), the report appears under the "Run on All Records" section rather than under the "Run on Current Record". While this would be ok had the report not been linked using the context-sensitive "CRMAF" nomenclature - in this case, the report has been written such that it should be context-sensitive. So why does it show in the wrong section?

The behavior that I've observed is that if the report is created initially such that it does not adhere to context-sensitivity, it will rightly appear under the "Run on All Records" section. Later on, if you modify the report definition to include context-sensitivity it will still remain in this section.

If that is the case simply deleting and recreating the report will resolve this issue. At least that's been my experience.

Dynamics CRM Search

One feature that is still not offered out of the box as part of the Dynamics CRM offering is a cross purpose search engine. While you can effectively perform searches using Advanced Find, Quick Find, Custom Reports etc. all of those searches involve navigating to a particular area of the application and pulling up the relevant view or report. A much desired feature in many organizations is the ability to have a single search "landing page" where you can enter in your search criteria and have that search across the relevant entities in CRM. Many competitive solutions to Dynamics CRM offer such a search capability.

The Dynamics CRM Search tool is designed to fill such a gap. This solution is fairly "low tech" in that it leverages the powerful reporting capability of SSRS in order to deliver the results. And it can be configured to suit the needs of any organization. This CRM Search tool is compatible with CRM 2011 and CRM 4.0.

Below are some configuration samples to illustrate usage scenarios.

"Out of the box" deployment


When deployed out of the box this is how the CRM Search will appear. By default it is configured to search across the account, contact, lead opportunity, and case entities. As illustrated, the more search criteria specified the further the result is refined. So for example, the search criteria shown in the screenshot will return all accounts beginning with "a" along with any contacts that belong to an account beginning with "a" and have a last name beginning with "d".

Highly Modified


The search criteria can be modified to search the particular requirements of your CRM deployment. Most of the fields and search criteria represent data that do not exist in the out of the box CRM deployment.

"Search All" 

This example illustrates the ability to have a “Search All” search field. If for example, you enter “Thomp” in the Search All field – the search will run a “contains” search as follows:

• In accounts for any accounts that have a matching name, address, contact information
• In contacts for any contacts that have matching first name, last name, address, contact information
• In any other entity/fields that the "search all" is configured to run against (according to your installation requirements)

The remaining fields can be used to do a more targeted search if that’s your preference

Notes search capability

This illustrates the ability to search against notes entered in the CRM database. So for example, if you remember having entered a note containing a particular phrase but cannot recall who you entered it for, you can enter it in the Notes field and have the account, contact, etc. where that note was entered returned to you.

Of course the above are just samples of how this can be configured - you can configure it to have a combination of some of the features shown or to be entirely different based on the specific requirements of your CRM installation. In addition, you are not limited to a single search console, you can create as many as necessary to meet the requirements of different departments or teams.

For more information on how to obtain a license for this tool, please visit our product page.

CRM 4.0: Cannot delete or export an entity

We were recently confronted with a situation where we were trying to export customizations in our 4.0 environment but the export kept on failing. We narrowed it down to one troublesome entity. We even tried deleting this entity. But no matter what operation we tried (export, delete, publish), we kept on receiving the following unhelpful error message:

We ran a trace and various other standard troubleshooting operations but nothing pointed us in the direction of the error. I've seen this error in the past where an attribute that was deleted is still referenced from the form and the solution is to open the form and remove this artifact and then publish. This wasn't the case here. So were a little bit stuck.

We turned to Microsoft support who indicated that the issue was indeed related to a missing attribute. However we still were unable to locate the missing attribute even after performing a thorough check against each relationship and verifying that the lookup field was present in the list of attributes.

We were able to finally resolve this by installing an XML comparison tool that allows you to pick an entity that runs a comparison between what is found in the database and what is found in the list of attributes on the form. The tool found the missing field. And to resolve we added that field again to the entity at which point we were able to publish and perform all the usual actions associated with entity customization.

Microsoft have been a little cryptic about this tool that they used claiming that it is still "being developed" and therefore uninstalled it after performing the above troubleshooting.

However I was able to find the following post that seems to reference a similar tool (and includes a download option to boot). I guess we will only know for sure if/when we are faced with this issue again at another client installation.

Microsoft CRM Email Resolution Issues

My previous post discussed promoting Outlook activities (email, appointments, activities) to CRM using Track and Set Regarding, the subtleties between these two options, and when it might be appropriate to use one vs. the other approach.

As mentioned both approaches will attempt to auto-resolve the email distribution list to User and Contact records in CRM. So if you just used Track for an email that has a corresponding contact in the CRM database (matched on email address), it will auto-resolve to that contact and appear in the Closed Activity list. If on the other hand you decided to use Set Regarding to track that same email to a say a case in CRM, then this email will  appear in both the Closed Activities for the case as well as for the contact it was auto-resolved to (the same would be true if you were using Set Regarding to an entity that has no relation to contacts).

It therefore goes without saying that if the "auto-resolution" functionality was somehow broken then you would get none of the above mentioned benefits. Or perhaps even worse - if you elected to "auto-create" contacts as part of the tracking functionality (see screenshot below) - if the "auto-resolution" functionality was broken, it could result in duplicate contacts entering your CRM system as the system fails to match it to an existing contact, and therefore decides to create a new contact record.

The above scenario is not only theory but something that actually was occurring in one of our installations. It is not exactly clear when the problem was introduced into the environment as the client was not using the email tracking features. Until recently that is...

After further analysis and discussions with Microsoft support it turned out that the reason the auto-tracking feature was not working was because the EmailSearch view in the CRM database was not getting updated. Probing a little deeper it turned out that the root cause of this issue was due to the fact that the "Format" on the email attributes of the contact record was text rather than email. This is not a setting that can be changed from the front end, so it's not clear how it came to be but it originated in the 4.0 environment and remained that way through the conversion to CRM 2011.

The steps for resolving this are twofold:

1. Revert the field back to the Email format. 
2. Update the EmailSearch view for existing records

Steps for reverting the field back to Email Format:

1.      Open CRM web client as an administrator and create a new Solution; open IE, browse to CRM, click Settings, click Solutions and then click the new button under Solutions.

2.      Within the new Solution window fill out the form as below and click the Save button. Display Name: Contact Name: Contact Publisher: Default Publisher for CRM2011. Version: 1.1.1.0

3.      Now click on Entities within the Solution and then click the Add Existing button.

4.      Check the Contact entity and then click OK.

5.      When prompted about the missing components select “No, do not include required components” and then click OK.

6.      Click the Save button.

7.      Now click the Export Solution button.

a.      Click Next unless you have not published customizations. Click Publish Customizations first and then click Next.

b.      You will be prompted about other missing components. Just click Next to ignore these as we will be importing this Solution right back into the same organization.

8.      At the next window do not select any Settings and just click next.

9.      Since this will be an unmanaged package so just leave the current option “Unmanaged” selected and click Export button.

a.      You will now be prompted to save the file. Just save it to the desktop of the CRM server.

10.   Go to the desktop of the CRM server and open the newly created zip file titled Contacts_1_1_1_0.zip.

a.      Extract the file customizations.xml.

b.      Open the customization.xml file with an XML editor.

11.   Press Ctrl+F and search for the string EmailAddress1.

a.      Under EmailAddress1 locate the element “<Format>text</Format>”.

b.      Within in the element change the word text to email. Save and close the file.

12.   Now place the modified customization.xml file back into the zipped solution Contacts_1_1_1_0.zip.

13.   Back in CRM Solutions click the Import button.

14.   Browse to the location of the Solution file Contacts_1_1_1_0.zip and then click next.

15.   Click Next to import the Solution.

Update the EmailSearch view:

One way to go about updating this view is to "touch" the email for all contact records. For example, you could do this by creating a workflow that copies the email to a temporary field and then in a subsequent step copies it back. You could then apply this workflow to all your contact records.

If your contact database is large the above approach might not be practical and you may want to use a script instead. Below is a script that can be used to update emailaddress1 on the contact record. This script is not supported so please use and update at your own discretion.

DECLARE @id              uniqueidentifier

DECLARE @email         nvarchar(100)

DECLARE @entitycode    int = 2   -- Contact

DECLARE @columnnumber  int = 42  -- Email Address 1 (45 for emailaddress2, 47 for emailaddress3)

BEGIN

        declare DetailCursor cursor for

                  select contactid, EMailAddress1

                  from Contact

                  where emailaddress1 is not null

                  and ContactId not in (

                  select ParentObjectId

                  from EmailSearchbase

                  where ParentObjectTypeCode = @entitycode

                  )

                 

        open DetailCursor

        fetch DetailCursor into @id, @email

        while @@fetch_status = 0

        begin

              insert into EmailSearchBase (EmailSearchId, EmailAddress, ParentObjectId, ParentObjectTypeCode, EmailColumnNumber)

              values (newid(), @email, @id, @entitycode, @columnnumber)

          fetch DetailCursor into @id, @email

        end --while

        close DetailCursor

        deallocate DetailCursor

END

CRM 4.0 Explorer View

Ever faced the dilemma of knowing where to store/find your documentation? For more advanced document management features, we would recommend a Sharepoint integration methodology (in CRM 2011 it is a no brainer to use the SharePoint integration as that comes out of the box, but if for some reason you do not have SharePoint 2010 - SharePoint Foundation is free so there really should be no reason - you can follow this approach in CRM 2011 too although it will need some adaptation). But for those who have simple needs or have a smaller budget for solving such problems, the Explorer view provides a very simple straight forward solution to this problem. The screenshot below illustrates this concept. Users can navigate and update documentation right from CRM just as they would with Windows Explorer (identical user experience).

The following will step through configuration of the option (you can download referenced files from here):

ASPX Configuration

Create a shared folder root on one of the servers (e.g. \\<server>\CRMFiles). Make sure you grant the necessary access permissions. It is recommended that the root share location be a folder without spaces in the name.

Create a subfolder called "Test" under the root folder:

Open default.aspx and make the following changes (pay close attention to the number and placement of the “\” character):

• On line 20 update the “src” tag to point to the correct share location (src=\\<server>\CRMFiles\)
• On line 21 update the “ddf_src” tag to point to the correct share location (ddf_src="\\\server\CRMFiles">)
  

Copy the modified file from above to the \crmweb\ISV\Sentri\Explorer location on the CRM server:

Test the above by trying to connecting to your test folder:

http://<server>:<port>/ISV/Sentri/Explorer/default.aspx?FolderName=Test

If you do not get an explorer view returned similar to the above, you have made a mistake in the deployment steps – please review the steps until you get a successful explorer view returned.

CRM Entity Configuration

On the CRM entity where you wish to configure the Explorer to appear you will need to make the following changes.

Add an IFrame to the form with a name of IFRAME_DOCS (note this name can be anything but it must match the jscript code – see later in this document).  The recommended settings are shown in the screenshots below.

Open onload.js and make the following changes:

• On line 5 update the logic for setting the folder name as necessary (see configuration section for more about this)
• On line 6 update the share location (var netshare = "\\\\server\\CRMFiles\\";)
• On line 7 ensure the url points to the correct path (should be if you followed the deployment instructions above)

Copy the onload.js into the form load event – you will need to ensure that the “fileshare()” function is called from somewhere in the onload jscript.

Configuration

In above implementation, there will need to be folder names under the share root matching the account name (assuming the jscript in  loaded in the account form). This of course can be modified as necessary to suit implementation requirements.

Reassigning personal views for disabled CRM users

The ability to share personal views in CRM with other users in CRM is a powerful tool. It allows for a central admin users to create or assist other users with creating views and thereafter they can be assigned to individual users or to a group of users. When sharing these views the user has the ability to limit the permissions on the shared views. For example, you may want to share a view but prevent the users who this view has been shared with from deleting the view. That can make sense especially in cases where the shared view is shared amongst a group of users and you do not want to give any individual user the arbitrary opportunity to accidentally or intentionally delete that view and inadvertently have that action ripple across the organization.

This all works very nicely... until the power user who has assigned all his/her views to other users leaves the organization and you want to manipulate these shared views in cases where that manipulation (write, delete, share to others) has been limited. One obvious solution is to have the IT admin temporarily update the AD credentials for this user so you can log into CRM as this user and re-assign the views to a new power user.

But the above action is not always an option. And fortunately there is another better option. Enter the Microsoft CRM 4.0 Administration Console. This console has a number of useful functions including tools for managing security roles but we are just going to focus on the ability to copy personal views for the specific scenario mentioned in this write up.

Below are the steps to copy the personal views:

• Download the console from codeplex
• Launch and enter the credentials and select the "Manage Views" utility

• In the ensuing form, first select "Get Users/Clear Cache". You can click just click Ok to select all users in the organization or you can filter by Business Unit

• Then perform the following steps to assign views from one user to another user
• In View Types ensure only "Main Application View (MAV)" view and "- Filter Active Views" are selected
• Right Column: Select the user you wish to assign from
• Middle Column: Select the view or views you wish to assign (use Ctrl and Shift for multi-select)
• Right Column: Select the user or users you wish to assign the views to (use Ctrl and Shift for multi-select)
• Click "Activate Views for Selected Users" and Confirm

CRM 4.0 Mashups

In CRM 4.0 we have the ability to create "mash ups" in order to combine CRM with other web applications. This essentially creates a visual integration between CRM and the other application such that an end user does not necessarily know where one application begins and the other one ends. This "graying" of the application boundaries is a good thing as at the end of the day, an end user should be prevented from having to access silos of information in favor of having a single access point for all relevant business data. Be it, native CRM data, physically integrated accounting data, or visually joining the applications as in this case.

This kind of integration could be achieved by either employing the use of an I-Frame approach or by adding a link to the Navigation Pane. Although there are cases where the I-Frame approach is justified, generally the Navigation Pane approach is better as it is more visually appealing and provides better use of the screen real estate.

There are many applications for this type of integration:

• It can be used to pull relevant information from a web page (msn, google, yahoo etc.) and have it presented to the user when they access an account. 
• It can also be used to accomplish virtual integration with another business application. For example, instead of physically bringing over invoice data from your back end accounting system into CRM for access via your front office, you could integrate the applications virtually by means of this approach. 
• You could integrate reports into a form to provide graphical, summary or other displays of information that cannot be achieved through other means. Alternatively if say your accounting data, is not accessible via URL, you could create an SRS report on the data you wish to view and combine it with your CRM form.

Below is an example of the first of these i.e. a mash-up between a CRM account and a Google map. The map is context sensitive to the address of the account and when the "Map" link is clicked it will show the address of the account on the map. The same is true for the News and Stock links shown in the screenshot.

You can download the files and instructions for configuring this in the Navigation Pane in the 4.0 environment from here.

Outlook CRM contact not showing with a CRM icon

We encountered the scenario where a contact that was clearly marked as a synced CRM contact was not showing in the Outlook client with the CRM icon,

We discovered the reason for this. In this case the client wanted centralized contact management. Meaning they did not want the ability for any of the end users to be able to update the contact record other than through a central admin user to protect the integrity of the contact data. They were not satisfied with data audit or alternative workflow/notification options.

And in addition they also wanted the ability to have the contacts be synced with the end users' Outlook clients. Since there is nothing preventing a contact from being updated in Outlook via the end users, we had to prevent any contact updates from hitting the CRM server during the syncing process by removing the contact update privileges from these end users.

And therein lay the problem with the contact icon. The following is a scenario where a CRM contact in Outlook can have it's contact icon flip back and forth between the standard Outlook contact icon and the CRM contact icon:

1. The Outlook client user actually updated one of the CRM contacts in Outlook. 
2. During syncing the user would first get a permission error (that can be ignored) and then the icon would change to a standard Outlook contact icon (as the sync process would presumably think that since the contact can no longer be updated that the contact is no longer a CRM contact).
3. The icon will remain a standard Outlook icon until a change is made again to the contact on the Web client at which time the change would be synchronized to the Outlook client and the contact icon would update again. Note that a duplicate contact would not be created since even though the contact was no longer showing as a CRM contact in Outlook, the CRM contact GUID wherein the actual link to the CRM server contact is housed, would remain intact and hence the sync process could still locate the contact record in the client.

Why the CRM contact icon is therefore not determined by the presence or absence of the CRM contact GUID is beyond me... Sigh. Anyway, this is presently a program "feature" in CRM 4.0. I have not tested this function in CRM 2011 yet.

Microsoft CRM Phase 1 Deployment

In my experience when a company decides to embark on a CRM initiative they almost always under estimate the impact that such a project is going to have. And I mean "impact" in a positive sense. The client is not to blame for this because they are invariably involved in their day to day activities and rarely have the time to dedicate to really appreciate what they are getting themselves into by having made such a decision. That is, in most cases a client will have decided that they need to be doing a better job with, say, tracking their sales process, or managing their client "touch points" etc. etc. and based on this they will likely come up with a set of scenarios that they will want their CRM solution to cover.

Therefore in my opinion, a CRM initiative is usually driven by something akin to an organizational "gut feeling". The client is normally somewhat educated about the high level features that a typical CRM product brings to the table - sales automation, marketing automation, service management, along with typical features such as workflow, alerting, and follow up - and their initial requirement will likely be a reflection of some these "high level" features that they know can improve efficiencies (customer experience, hand offs, visibility, oversight etc.) within the organization.

If a client is replacing an existing CRM solution or implementing CRM to replace a custom built or vertically specific application then it's likely that they will have a better idea of the features that they need the new product to cover and this will be reflected in their requirements as well as in the product selection process.

However, in most cases - during the project initiation phase and prior to that - the client is unlikely to have a much deeper appreciation of the myriad other capabilities that a product such as Microsoft Dynamics CRM brings to the table. And this is where it gets interesting...

The question therefore is - in light of this introduction - what is the best way of going about Phase 1 of a CRM deployment?

I would venture to say that the answer lies within the fact that a major factor of the Phase 1 deployment is education. And not only education for the organization undertaking the CRM initiative but education of those you have entrusted in realizing your CRM dreams. First and foremost the client needs to be educated about the features and capabilities of the product as it pertains to their business. A good consultant must help the client envision what the future can hold while at the same time grounding the deployment in real achievable chunks. And at the same time, the consultant must get educated about the client's business model, their unique requirements, the client culture and the intricacies of the client operation. This mutual education process is iterative and it is a discovery process that happens naturally and dynamically and should be controlled and driven by the consultant running the workshop (it's easy to get carried away, so it's important to know when a discussion needs to be tabled for later review in order not to take away from the core objectives).

A workshop session should also include - at some point - various other players within the organization. And it is again likely that most of these additional players will have had limited involvement in the run up to the project initiation and will have little to no knowledge of what this so-called CRM application is. There's a good chance that they will never have even seen the CRM user interface. But these players will also likely have key input that may have not been considered during the selection process. And while managing expectations and preventing the process from becoming a "free for all", the insights gained from such participation could prove to be very valuable indeed.

Unlike perhaps most other systems that a company may choose to implement in its organization, CRM is typically a very horizontal type of application. That is, because CRM is your front office and your interaction with your customer, prospect, partner vendor etc. - be it during the sales process or later on in the life cycle during customer service - it tends to be a reflection of way you go about doing business (or should be). And this may also explain the common refrain that consultants such as myself love to repeat that "no two CRM implementations are alike" because just as no two business are exactly alike, so too the CRM product and the way in which it needs to be configured for your specific differentiation, interaction with clients, or way of doing business, needs to be similarly different. Not to belabor the point, but I think this also demonstrates quite well why a CRM solution needs to be so configurable - especially one created for the masses such as Microsoft CRM - it needs to be able to be tailored to your organization and be flexible enough to model your business as it evolves.

And being a horizontal application, it typically needs to "serve up" whatever information is required by your end users in order to enable them to do what they need to do to make your business a success. And this might be tapping into your back end accounting system, your custom built ordering system or with some other data source or system within your organization. And once again, the way in which the data needs to be integrated, migrated or mashed up with your CRM application is realistically only going to come out of a workshop session where you have had time to dedicate and be educated as to the capabilities and possibilities that can be realized.

The challenge of fix pricing a CRM deployment based exclusively on the requirements provided by the client is that the process defined above (or parts thereof) will almost certainly occur in some way, shape or form but the project will be limited to the scope of the originally signed SOW. And this of course can in turn lead to strained relations and/or missed expectations. Or to put it another way, the risk is that the customer may get exactly what they asked for... instead of having the benefit of being educated and design the system so they get exactly what their business model requires.

Phase 1 of any CRM undertaking is without doubt the most difficult phase of the CRM initiative. It is an intense period of mutual education and if done well, it also lays the foundation upon which the business can evolve and grow. And therefore it is essential that it be performed with the proper due diligence, understanding and aptitude.

Therefore I would humbly submit that the best way of doing a Phase 1 deployment of CRM is by holding a workshop session with the client using a limited scope engagement for this workshop session. During this workshop, Microsoft CRM itself can be used to facilitate the discussion by configuring the application sufficiently so it can help with the envisioning process (a picture here is worth at least 1000 words). The output of the workshop session may be a partially configured CRM environment (the extent configured will depend on the size and nature of the undertaking) along with an SOW that will provide the client with a detailed breakdown of what is required to round out the Phase 1 deployment, along with outlines for future phases and an overall blueprint of the CRM landscape for your organization.