Pages

Monday, December 1, 2014

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).




Friday, November 21, 2014

Contact Address 2 Outlook Sync

While researching the previous issue regarding Outlook Contact address sync issues I came across something interesting. That is, a pleasant, unexpected surprise...

Until now, the fields that sync between the CRM and their Outlook counterparts have been locked down. Well... that is still the case and looks like it will be the case for the foreseeable future.

However one of the common issues encountered with the above limitation is that only the primary contact address syncs to the CRM contact record (i.e. address1 to the Outlook business/mailing address). So if you wanted to sync the home address (typically address2) you were fresh out of luck... or you had to think of creative ways of maintaining such information to the description field so that it would appear there (something which I've configured for a client or two).

With CRM 2015, it appears Microsoft have finally seen the light and it appears that Address 2 fields will now sync to the Outlook home address. Of course, knowing this you may also want to consider any impact for existing organizations when they perform the 2015 upgrade.

Below is the link showing the CRM/Outlook sync mapping for CRM 2015. I have only given it the briefest of reviews so perhaps there may be other surprise within. If so, do let me know.

http://technet.microsoft.com/en-us/library/dn832089(v=crm.7).aspx

Thursday, November 20, 2014

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. 


Tuesday, November 18, 2014

CRM_URL Manipulation

There is a well known technique for opening an entity form from an SRS report. For example, you can create a hyperlink to the underlying CRM contact record using the following syntax:

=IIF(IsNothing(Parameters!CRM_URL.Value),Nothing,Parameters!CRM_URL.Value & "?ID={"&Fields!contactid.Value.ToString()&"}&LogicalName=contact")


However if you want to create a link to a CRM record without an underlying reference ID to pass then things can become a little more tricky. This is because while you require the server reference provided by the CRM_URL variable (shown in green below), you need to get rid of the "CRMReports/viewer/drillopen.aspx" section as that throws it off (shown in yellow).


This can be achieved by using the following function in the URL action:
=Mid(Parameters!CRM_URL.Value, 1, InStr(Parameters!CRM_URL.Value, "/CRMReports"))

Now you can rebuild the URL anyway you want. For example, you can construct a URL as follows:


And this will open up a new CRM record (with passed defaults etc.).


Monday, November 17, 2014

Convert Org Owned Entity to User Owned

A while back I discussed the pros and cons of user vs. organization owned entities. I concluded as follows:

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.
Well it's about time I made good on that promise... So here goes -

Caution: This is not for the fainthearted. Especially in CRM Online installations where you can't really make a backup (as concluded in the above referenced post - when in doubt go with user-owned entities...).

Step 1 - Export all the data

You need to save all your data linked to the entity you wish to update. Assuming this is a CRM Online instance you can use the export to Excel option (in on-premise there are of course other options). This includes (but not necessarily limited to) the following:

  • Export Main  Entity




  • Export Notes - All columns
  • Export Activities - All columns
  • Export data from other 1:N relationships that you wish to recreate/relate

You need to resign your self to the fact that you will lose information stored in system attributes such as created/modified by/on.

Generally speaking, restoring N:1 relationships is not a problem because they'll be recreated with the re-import.

However with the 1:N relationship as in the case of notes and activities there is a level of effort involved so you'll need to make a judgment call of whether it is worth it. Same applies for N:N relationships (perhaps even more so since there is no way to re-import using native tools. Bottom line if there is a lot of dependent data this exercise is likely going to be too painful and you'll need to consider other options such as duplicating the entity and migrating the data over with all the relationships (tools such as Scribe can help here).



Step 2 - Create and Export Solution


Retains all customizations - views, forms etc.

  • Export a solution containing the entity you wish to modify and workflows on that entity 
  • Export a solution with everything but entity (including workflows, security roles)....
  • Make a copy of the exported zip
  • Unzip the solution, and open customizations.xml in a text editor
  • Replace the following section... 
<attribute PhysicalName="OrganizationId">
              <Type>lookup</Type>
              <Name>organizationid</Name>
              <LogicalName>organizationid</LogicalName>
              <RequiredLevel>none</RequiredLevel>
              <ImeMode>auto</ImeMode>
              <ValidForReadApi>1</ValidForReadApi>
              <IsCustomField>0</IsCustomField>
              <IsAuditEnabled>1</IsAuditEnabled>
              <IsSecured>0</IsSecured>
              <IntroducedVersion>1.0</IntroducedVersion>
              <IsCustomizable>1</IsCustomizable>
              <IsRenameable>1</IsRenameable>
              <CanModifySearchSettings>1</CanModifySearchSettings>
              <CanModifyRequirementLevelSettings>1</CanModifyRequirementLevelSettings>
              <CanModifyAdditionalSettings>1</CanModifyAdditionalSettings>
              <ReferencedEntityObjectTypeCode>1019</ReferencedEntityObjectTypeCode>
              <LookupTypes />
              <displaynames>
                <displayname description="Organization Id" languagecode="1033" />
              </displaynames>
              <Descriptions>
                <Description description="Unique identifier for the organization" languagecode="1033" />
              </Descriptions>
            </attribute>
              
  • With:
<attribute PhysicalName="OwnerId">
              <Type>owner</Type>
              <Name>ownerid</Name>
              <LogicalName>ownerid</LogicalName>
              <RequiredLevel>systemrequired</RequiredLevel>
              <DisplayMask>ValidForAdvancedFind|ValidForForm|ValidForGrid|RequiredForForm</DisplayMask>
              <ImeMode>auto</ImeMode>
              <ValidForReadApi>1</ValidForReadApi>
              <ValidForCreateApi>1</ValidForCreateApi>
              <IsCustomField>0</IsCustomField>
              <IsAuditEnabled>1</IsAuditEnabled>
              <IsSecured>0</IsSecured>
              <IntroducedVersion>1.0.0.0</IntroducedVersion>
              <IsCustomizable>1</IsCustomizable>
              <IsRenameable>1</IsRenameable>
              <CanModifySearchSettings>1</CanModifySearchSettings>
              <CanModifyRequirementLevelSettings>1</CanModifyRequirementLevelSettings>
              <CanModifyAdditionalSettings>1</CanModifyAdditionalSettings>
              <LookupStyle>single</LookupStyle>
              <LookupTypes>
                <LookupType id="00000000-0000-0000-0000-000000000000">8</LookupType>
                <LookupType id="00000000-0000-0000-0000-000000000000">9</LookupType>
              </LookupTypes>
              <displaynames>
                <displayname description="Owner" languagecode="1033" />
              </displaynames>
              <Descriptions>
                <Description description="Owner Id" languagecode="1033" />
              </Descriptions>
            </attribute>
            <attribute PhysicalName="OwnerIdName">
              <Type>nvarchar</Type>
              <Name>owneridname</Name>
              <LogicalName>owneridname</LogicalName>
              <RequiredLevel>systemrequired</RequiredLevel>
              <ImeMode>auto</ImeMode>
              <ValidForReadApi>1</ValidForReadApi>
              <IsCustomField>0</IsCustomField>
              <IsAuditEnabled>0</IsAuditEnabled>
              <IsLogical>1</IsLogical>
              <IsSecured>0</IsSecured>
              <IntroducedVersion>1.0.0.0</IntroducedVersion>
              <IsCustomizable>1</IsCustomizable>
              <IsRenameable>1</IsRenameable>
              <CanModifySearchSettings>1</CanModifySearchSettings>
              <CanModifyRequirementLevelSettings>1</CanModifyRequirementLevelSettings>
              <CanModifyAdditionalSettings>1</CanModifyAdditionalSettings>
              <Format>text</Format>
              <MaxLength>100</MaxLength>
              <Length>320</Length>
              <Descriptions>
                <Description description="Name of the owner" languagecode="1033" />
              </Descriptions>
            </attribute>
            <attribute PhysicalName="OwnerIdType">
              <Type>int</Type>
              <Name>owneridtype</Name>
              <LogicalName>owneridtype</LogicalName>
              <RequiredLevel>systemrequired</RequiredLevel>
              <DisplayMask>ObjectTypeCode</DisplayMask>
              <ImeMode>disabled</ImeMode>
              <ValidForReadApi>1</ValidForReadApi>
              <ValidForCreateApi>1</ValidForCreateApi>
              <IsCustomField>0</IsCustomField>
              <IsAuditEnabled>1</IsAuditEnabled>
              <IsSecured>0</IsSecured>
              <IntroducedVersion>1.0.0.0</IntroducedVersion>
              <IsCustomizable>1</IsCustomizable>
              <IsRenameable>1</IsRenameable>
              <CanModifySearchSettings>1</CanModifySearchSettings>
              <CanModifyRequirementLevelSettings>1</CanModifyRequirementLevelSettings>
              <CanModifyAdditionalSettings>1</CanModifyAdditionalSettings>
              <Format></Format>
              <MinValue>-2147483648</MinValue>
              <MaxValue>2147483647</MaxValue>
              <Descriptions>
                <Description description="Owner Id Type" languagecode="1033" />
              </Descriptions>
            </attribute>
            <attribute PhysicalName="OwnerIdYomiName">
              <Type>nvarchar</Type>
              <Name>owneridyominame</Name>
              <LogicalName>owneridyominame</LogicalName>
              <RequiredLevel>systemrequired</RequiredLevel>
              <ImeMode>auto</ImeMode>
              <ValidForReadApi>1</ValidForReadApi>
              <IsCustomField>0</IsCustomField>
              <IsAuditEnabled>0</IsAuditEnabled>
              <IsLogical>1</IsLogical>
              <IsSecured>0</IsSecured>
              <IntroducedVersion>1.0.0.0</IntroducedVersion>
              <IsCustomizable>1</IsCustomizable>
              <IsRenameable>1</IsRenameable>
              <CanModifySearchSettings>1</CanModifySearchSettings>
              <CanModifyRequirementLevelSettings>1</CanModifyRequirementLevelSettings>
              <CanModifyAdditionalSettings>1</CanModifyAdditionalSettings>
              <Format>text</Format>
              <MaxLength>100</MaxLength>
              <Length>320</Length>
              <YomiOf>OwnerIdName</YomiOf>
              <Descriptions>
                <Description description="Yomi name of the owner" languagecode="1033" />
              </Descriptions>
            </attribute>
            <attribute PhysicalName="OwningBusinessUnit">
              <Type>lookup</Type>
              <Name>owningbusinessunit</Name>
              <LogicalName>owningbusinessunit</LogicalName>
              <RequiredLevel>none</RequiredLevel>
              <ImeMode>auto</ImeMode>
              <ValidForReadApi>1</ValidForReadApi>
              <IsCustomField>0</IsCustomField>
              <IsAuditEnabled>0</IsAuditEnabled>
              <IsSecured>0</IsSecured>
              <IntroducedVersion>1.0.0.0</IntroducedVersion>
              <IsCustomizable>1</IsCustomizable>
              <IsRenameable>1</IsRenameable>
              <CanModifySearchSettings>1</CanModifySearchSettings>
              <CanModifyRequirementLevelSettings>1</CanModifyRequirementLevelSettings>
              <CanModifyAdditionalSettings>1</CanModifyAdditionalSettings>
              <ReferencedEntityObjectTypeCode>10</ReferencedEntityObjectTypeCode>
              <LookupStyle>single</LookupStyle>
              <LookupTypes />
              <displaynames>
                <displayname description="Owning Business Unit" languagecode="1033" />
              </displaynames>
              <Descriptions>
                <Description description="Unique identifier for the business unit that owns the record" languagecode="1033" />
              </Descriptions>
            </attribute>


  • Replace the following...
<OwnershipTypeMask>UserOwned</OwnershipTypeMask>
  • With:
<OwnershipTypeMask>UserOwned</OwnershipTypeMask>

  • Replace the following section (be sure to update "your_entity" with the name of your entity)...
<EntityRelationship Name="organization_your_entity">
      <EntityRelationshipType>OneToMany</EntityRelationshipType>
      <IsCustomizable>1</IsCustomizable>
      <IntroducedVersion>1.0</IntroducedVersion>
      <ReferencingEntityName>your_entity</ReferencingEntityName>
      <ReferencedEntityName>Organization</ReferencedEntityName>
      <CascadeAssign>NoCascade</CascadeAssign>
      <CascadeDelete>NoCascade</CascadeDelete>
      <CascadeReparent>NoCascade</CascadeReparent>
      <CascadeShare>NoCascade</CascadeShare>
      <CascadeUnshare>NoCascade</CascadeUnshare>
      <ReferencingAttributeName>OrganizationId</ReferencingAttributeName>
      <RelationshipDescription>
        <Descriptions>
          <Description description="Unique identifier for the organization" languagecode="1033" />
        </Descriptions>
      </RelationshipDescription>
      <field name="organizationid" requiredlevel="none" imemode="auto">
        <IsCustomizable>1</IsCustomizable>
        <IsRenameable>1</IsRenameable>
        <CanModifySearchSettings>1</CanModifySearchSettings>
        <CanModifyRequirementLevelSettings>1</CanModifyRequirementLevelSettings>
        <IsSecured>0</IsSecured>
        <IsAuditEnabled>1</IsAuditEnabled>
        <displaynames>
          <displayname description="Organization Id" languagecode="1033" />
        </displaynames>
      </field>
    </EntityRelationship>

  • With:
    <EntityRelationship Name="owner_your_entity">
      <EntityRelationshipType>OneToMany</EntityRelationshipType>
      <IsCustomizable>1</IsCustomizable>
      <IntroducedVersion>1.0.0.0</IntroducedVersion>
      <ReferencingEntityName>your_entity</ReferencingEntityName>
      <ReferencedEntityName>Owner</ReferencedEntityName>
      <CascadeAssign>NoCascade</CascadeAssign>
      <CascadeDelete>NoCascade</CascadeDelete>
      <CascadeReparent>NoCascade</CascadeReparent>
      <CascadeShare>NoCascade</CascadeShare>
      <CascadeUnshare>NoCascade</CascadeUnshare>
      <ReferencingAttributeName>OwnerId</ReferencingAttributeName>
      <RelationshipDescription>
        <Descriptions>
          <Description description="Owner Id" languagecode="1033" />
        </Descriptions>
      </RelationshipDescription>
      <field name="ownerid" requiredlevel="systemrequired" imemode="auto" lookupstyle="single" lookupbrowse="0" lookuptypes="8, 9">
        <IsCustomizable>1</IsCustomizable>
        <IsRenameable>1</IsRenameable>
        <CanModifySearchSettings>1</CanModifySearchSettings>
        <CanModifyRequirementLevelSettings>1</CanModifyRequirementLevelSettings>
        <IsSecured>0</IsSecured>
        <DisplayMask>ValidForAdvancedFind|ValidForForm|ValidForGrid|RequiredForForm</DisplayMask>
        <IsAuditEnabled>1</IsAuditEnabled>
        <displaynames>
          <displayname description="Owner" languagecode="1033" />
        </displaynames>
      </field>
    </EntityRelationship>
    <EntityRelationship Name="team_your_entity">
      <EntityRelationshipType>OneToMany</EntityRelationshipType>
      <IsCustomizable>1</IsCustomizable>
      <IntroducedVersion>1.0.0.0</IntroducedVersion>
      <ReferencingEntityName>your_entity</ReferencingEntityName>
      <ReferencedEntityName>Team</ReferencedEntityName>
      <CascadeAssign>NoCascade</CascadeAssign>
      <CascadeDelete>NoCascade</CascadeDelete>
      <CascadeReparent>NoCascade</CascadeReparent>
      <CascadeShare>NoCascade</CascadeShare>
      <CascadeUnshare>NoCascade</CascadeUnshare>
      <ReferencingAttributeName>OwningTeam</ReferencingAttributeName>
      <RelationshipDescription>
        <Descriptions>
          <Description description="Unique identifier for the team that owns the record." languagecode="1033" />
        </Descriptions>
      </RelationshipDescription>
      <field name="owningteam" requiredlevel="none" imemode="auto" lookupstyle="single" lookupbrowse="0">
        <IsCustomizable>1</IsCustomizable>
        <IsRenameable>1</IsRenameable>
        <CanModifySearchSettings>1</CanModifySearchSettings>
        <CanModifyRequirementLevelSettings>1</CanModifyRequirementLevelSettings>
        <IsSecured>0</IsSecured>
        <IsAuditEnabled>0</IsAuditEnabled>
        <displaynames>
          <displayname description="Owning Team" languagecode="1033" />
        </displaynames>
      </field>
    </EntityRelationship>
    <EntityRelationship Name="user_your_entity">
      <EntityRelationshipType>OneToMany</EntityRelationshipType>
      <IsCustomizable>1</IsCustomizable>
      <IntroducedVersion>1.0.0.0</IntroducedVersion>
      <ReferencingEntityName>your_entity</ReferencingEntityName>
      <ReferencedEntityName>SystemUser</ReferencedEntityName>
      <CascadeAssign>NoCascade</CascadeAssign>
      <CascadeDelete>NoCascade</CascadeDelete>
      <CascadeReparent>NoCascade</CascadeReparent>
      <CascadeShare>NoCascade</CascadeShare>
      <CascadeUnshare>NoCascade</CascadeUnshare>
      <ReferencingAttributeName>OwningUser</ReferencingAttributeName>
      <RelationshipDescription>
        <Descriptions>
          <Description description="Unique identifier for the user that owns the record." languagecode="1033" />
        </Descriptions>
      </RelationshipDescription>
      <field name="owninguser" requiredlevel="none" imemode="auto" lookupstyle="single" lookupbrowse="0">
        <IsCustomizable>1</IsCustomizable>
        <IsRenameable>1</IsRenameable>
        <CanModifySearchSettings>1</CanModifySearchSettings>
        <CanModifyRequirementLevelSettings>1</CanModifyRequirementLevelSettings>
        <IsSecured>0</IsSecured>
        <IsAuditEnabled>0</IsAuditEnabled>
        <displaynames>
          <displayname description="Owning User" languagecode="1033" />
        </displaynames>
      </field>
    </EntityRelationship>
  • Finally, update your entity name so it can exist side by side (temporarily) with the entity you will be replace. For example, find and replace all your_entity with your_entity2.
  • Similarly, replace the entity description with a replacement value so you'll be able to distinguish from the front end.
      <Name LocalizedName="Your Entity2" OriginalName="Your Entity2">snt_Your Entity2</Name>
      <ObjectTypeCode>10016</ObjectTypeCode>
      <EntityInfo>
        <entity Name="your_entity2">
          <LocalizedNames>
            <LocalizedName description="Your Entity2" languagecode="1033" />
          </LocalizedNames>
          <LocalizedCollectionNames>
            <LocalizedCollectionName description="Your Entity 2" languagecode="1033" />

  • Save Customization.xml and copy back into exported zip

Step 3 - Import Customization

  • Import modified zip file to recreate entity
  • Import other solution file created
  • Verify forms, views etc.
  • Verify entity is user-owne
Note: If you get an invalid reference error when importing you'll need to remove the reference (could be a sub-grid or jscript web resource reference) and you'll need to manually add back after the import.

Step 4 - Import Data

Use the data import wizard to re-import the data to your new duplicated entity.


Step 5 - Post Steps

Once you're satisfied with the state of affairs of your new entity you can apply the post steps which includes:

  • Delete old entity - This can get quite hairy. Deleting an entity requires removing all kinds of dependencies. Make sure you export entities, workflows, security roles etc. and keep careful manual track of any thing that needs to be manually reset

  • Update forms - you may need to update the forms with removed references that caused import customization errors. 
  • Update security roles
  • Update workflow

Sunday, May 25, 2014

Import Organization Failed: Subquery returned more than 1 value

While trying to import a CRM 2011 organization to CRM 2013 I encountered the following error:


Viewing the log detail unfortunately file did not shed much more light on this.

The only thing I had to go on was the SQL error itself. This indicated that somewhere the system was trying to execute a nested SQL statement that looked something like the following:

select field1 from table1 where field2 = (select field3 from table2)

And that the results from table2 returned more than a single row which is not allowed as the nested relationship is using the "=" rather than "in" operator.

Armed with this information, I decided to run a SQL trace while recreating the import error. And then performed a bit of a needle-in-haystack search for something that might fit the above - using correlating times from the SQL Trace and CRM trace file (although in retrospect I think I could have searched the SQL trace for a statement like 'update organization set set state=...' as this is what happens directly after the failed error).

Lo and behold, I came across the query below which matched the kind of query pattern I was looking for. The suspect had been identified...


Running the essence of the above query in SQL it came back with the following - looks like a smoking gun to me!


In the interest of moving forward as quickly as possible, I decided to run a query that eliminate this particular error by ensuring a singleton output.

update Role set RoleTemplateId = null
where RoleId != 'BDD45E0B-7482-DC11-AB36-005056BC02DA'
and RoleTemplateId='A4BE89FF-7C35-4D69-9900-999C3F603E6F'

Re-running the import again subsequently succeeded... and yet another successful CRM 2013 upgrade under the belt.

Thursday, April 3, 2014

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.

Tuesday, March 18, 2014

Run Report button missing on Forms

We noticed that certain forms in a CRM 2013 upgraded installation were missing the embedded "Run Report" option in the command bar. It appears in some forms but not in others... with no apparent rhyme or reason. For example, below is a screenshot of the upgraded opportunity form:



Anyone see the Run Report option? Neither do I... and the reason cannot be attributed to there not being any context sensitive reports for opportunities. There indeed are!


Yet... it does appear in the command menu for the grid view. Go figure.

I found this post which pointed me in the direction of the resolution. It references two options for resolution. One by directly modifying the export XML for the entity. And the second by using the Ribbon Workbench tool.

The first solution is the way to go. Quicker and easier and does the job. That is, you just need to add the following command definition to the RibbonDiffXml of the entity.

<CommandDefinition Id="Mscrm.ReportMenu.Form">
    <EnableRules>
      <EnableRule Id="Mscrm.FormStateNotNew" />
    </EnableRules>
    <DisplayRules>
      <DisplayRule Id="Mscrm.ReadReport" />
    </DisplayRules>
    <Actions />
</CommandDefinition>

After importing and publishing, the reports have returned to their rightful place (ok I know the report icon is still missing but that is a battle for another day).



Friday, February 7, 2014

CRM 2013: Compelling Reasons to Upgrade

There are many posts that describe and illustrate the new features that CRM 2013 has introduced. Here are a few choice samples:




Having now spent quite a bit of time working and getting familiar with the new platform, my intention is to try and determine what might be considered features that provide compelling reasons to upgrade assuming that you are on the previous version of the product. That assumption includes the fact that you've spent some time and effort configuring CRM to suit your needs. So while there may be some features that make it easier to achieve certain configuration options that were implemented previously - that in my opinion would not provide a compelling reason to upgrade as there generally is not going to be a burning need to rework the option that has already been implemented and working as desired.

Let's also stipulate that I'm not advocating against upgrading. Rather upgrading is a strategic decision that needs to be weighed with other competing priorities and budgets. And therefore this is more of a question of whether this should be prioritized now or perhaps down the line. And let's make no mistake about it - Microsoft will be spending their time and effort improving the new platform rather than CRM 2011, so if you're planning on performing any major process improvements then you'd be well served to bite the upgrade bullet so you can leverage new capabilities. This post is not for you if you fall into that category. If however you are somewhere in between and looking for features that just may take you to the next level - then this might help put things in perspective.

In my opinion too many posts out there just hype features of the product without providing critical analysis of the real world advantage they're likely to bring. In my list below, I am endeavoring to analyze the features on their practicality and real world application.


POTENTIALLY COMPELLING

  • Business Process Flow - This is a net new feature, easy to configure/implement and can have a fairly significant impact to the ease of use. Especially for mobile devices such as tablets.
  • Exchange Server Side Sync - Although the CRM Outlook Client has made tremendous progress over the years, it still remains the Achilles heel of the product - in my opinion. Mainly because of the decentralized nature of the configuration and troubleshooting headaches when there is a syncing issue. There were 3rd party solutions that allowed this to happen at the server level. And now Microsoft has finally added this functionality to the core product. If you are experiencing issues with contact synchronization then this could well provide the single compelling reason to upgrade.
  • Mobile Optimization - The design of the product has been optimized to all mobile devices. Even more impressive - they have produce apps for the iPad, iPhone, Surface, and Android. If you think that mobile enabling your workforce will increase adoption and overall usage, then this is indeed a compelling reason for upgrading.


FACILITATIVE

  • Business Logic - There is nothing new about this feature that could not be achieved with form jscript. In fact, you will still need jscript for more involved scenarios. But it does give less techie users the ability to configure simple dynamic behavior on the form and therefore become more self sufficient.
  • Quick View Forms - This is definitely a nice new feature that makes it easy to display information from other related entities on the current form. But if you had that requirement previously, there were ways and means to achieve it.
  • Real Time Workflows - I'd say this is a very nice new feature that could replace a lot of jscript and plugin requirements. But again this does not enable a capability that could not be achieved in CRM 2011.
  • Access Teams - This is a nice new feature that is most likely to be used in scenarios of collaborative selling. The functionality was achievable with the standard team model in previous versions but is a lot more flexible and "on the fly" in the latest version.
  • Actions - This extends the programming capability of the product essentially providing the ability to abstract business logic even further. I can see this feature as potentially very useful to minimize the amount of custom code logic making it easier to modify without having to update plugins etc. But again this does not enable a capability that could not be achieved in CRM 2011. 


INTERESTING

  • User Interface/Navigation - The new version is pretty nice but in some ways can be more challenging to navigate. There are merits to the new user interface but also to the previous user interface. The flat user interface is nice but does not come without a cost (e.g. the benefits of alt+tabbing vs. having to re-navigate to where you were previously). I suspect there will still be a few improvements to come to help optimize this particular experience. And make no mistake about it - upgrading will require you to optimize your form layout to leverage and benefit from the new form layout. If you do not allocate budget for that effort, you are in fact likely to be negatively impacted by the new user experience.
  • Command Bar - Microsoft thought it had it all figured out when they introduced the ribbon interface. Only to apparently discover that it was also too overwhelming for the average user and have now scaled it back to the simpler command bar. I do think that they are moving in the right direction with this design change but I hardly think this will make a meaningful difference to those who have gotten familiar with the ribbon interface.
  • Dashboards - This feature has been slightly enhanced such that a different default dashboard can be configured for each area (sales, marketing, service, etc.) and per role.


NICE TO HAVE
  • Auto-Save - Honestly? BFD.
  • Social Pane - Ditto. Maybe I'm old school but this is a feature that I have rarely seen employed. There's already activities and notes and at some point the lines gray between what should be what. And this doesn't really help. Perhaps for larger companies.
  • Quick Create Forms - If I'm not mistaken this is kind of a re-introduced feature from CRM 3.0 and earlier. Not revolutionary and unlikely to improve the user experience in any significant way. 
  • Images - All entities now support associating images to them. Most likely usage is for contacts (although still doesn't sync with the Outlook contact image). Nice if you need it.

Monday, February 3, 2014

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:


Debugging workflow plugins:

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. 


Monday, January 27, 2014

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


Thursday, January 23, 2014

Error running a report via Outlook Client

Update from Microsoft (3/25/14):

          The issue appears to be fixed:
I received confirmation that the team has processed a change with the NLB system used by CRM Online. As a result, this incident with receiving script errors when running reports from your CRM Online organization should now be resolved.

Update from Microsoft (2/20/14):

After working with my team further, we were able to reproduce this problem internally (not using CRM Online). The problem is specific to a setting on the load balancers used by CRM Online. As such, it appears that this will affect Office365 based organizations (which to be honest makes more sense now). The setting on the load balancers was something recently applied in the move to CRM 2013, however our internal teams are getting approval to undo that change which should resolve this problem. They gave me an approximate 2 week timeframe to get proper approval and undo that change. We’re currently about 1 week through that, so we should be close to having this problem fully resolved.

Update from Microsoft (1/31/14):

The core problem is still being investigated, however a “workaround” was discovered that ties in with the new ‘Process Isolation’ feature of the CRM Client for Outlook. There is a registry key that can be applied on client systems to disable the feature. Testing on our side so far looks promising as the script errors on reports have ceased after disabling the feature. This is by no means a solution to the problem and our teams are still actively investigating. If you wish to test this, I’ve included the registry key below to implement on a client. If you confirm it also works from your side, we can discuss pros/cons of this workaround and if it’s feasible for your organizations.
Key Path: HKEY_CURRENT_USER\Software\Microsoft\MSCRMClientKey (DWORD): UseWebFormProcessIsolationValue: 0
-----------------------------------------------------------------------------------------------------------

In one of our CRM 2013 online instances we are receiving errors whenever trying to run a report from the Outlook client. The error looks something like in the examples below (errors occur one after the other).




This does not happen when running reports via a browser so clearly some kind of platform issue. It is somewhat sporadic too and seems to be related to browser cache. So it might go away and then come back at a later point in time. For example, one solution I tried was to delete/recreate the report as I figured that it was perhaps due to some upgrade issue from 2011. This solved the issue for a while but then it returned.

And this was confirmed by Microsoft who are currently troubleshooting the issue. The following is the current guidance provided by Microsoft:

Per our phone conversation, this issue with running reports in the CRM Client for Outlook against a CRM Online 2013 organization on our CTP authentication platform is currently a known issue that has recently been brought to our attention. We are actively investigating this problem and I’ll keep you posted as I know more. Currently, we believe this may be a server side setting – but the investigation has not yet yielded the exact cause of the problem. Unfortunately there is no workaround for the web client other than using the CRM Web Client (i.e. Internet Explorer).


Wednesday, January 8, 2014

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.