Pseudo-Filtered Lookup Dialog in Microsoft Dynamics CRM 4.0

One of the great unsupported customizations in Microsoft CRM 3.0 was the ability to apply a filter to a lookup with just a few lines of JavaScript.  Ronald Lemmen had a nice post describing this approach that used a FetchXml query to filter the lookup values.  However, in Microsoft CRM 4.0 this customization no longer works and when you attempt it you receive the following depressing error:

CRM Parameter Filter - Invalid parameter 'fetchXml=...' in Request.QueryString on page /_controls/lookup/lookupsingle.aspx

Figuring Microsoft must have renamed the parameters I spent some time today scanning the Microsoft CRM DLL files (Lutz Roeder's Reflector for .NET is a great tool for reverse engineering).  Unfortunately I came up dry and have come to the conclusion this approach no longer works.  Hey, it was unsupported in the first place -- can't really complain ;-)  Since filtering is such an important feature of usability we still wanted to find a way to apply a filter to lookups.  In our world we have relationships where entity names are not always unique across the organization, though they are unique within the context of their parent relationship.

Of the query string parameters still available one still gave promise of a possible solution - search.  When this parameter is specified it defaults the search string in the lookup dialog and applies the search when the dialog is opened.  While we are not able to target our filter to a specific field, we can still leverage the lookup search (and the search columns for the lookup view) to filter the records returned.

For this example assume we have two entities - state and city - with the state being the parent of city.  In the City Lookup View add a find column for the state name:

In the onload event of the form we add the following:

document.FilterLookup = function(source, target)
{
    if (IsNull(source) || IsNull(target)) { return; }

    var name = IsNull(source.DataValue) ? '' : source.DataValue[0].name;

    target.additionalparams = 'search=' + name;
}

In the onchange event of the state field on the form we add the following:

document.FilterLookup(crmForm.all.awx_stateid, crmForm.all.awx_cityid);

When the user selects a state...

... and then chooses a city, they see this:

Filtered?  Yes!  Supported?  Maybe not, but should be good until 5.0 :-)

Extending duplicate detection with Soundex

The duplicate detection included in Microsoft CRM 4.0 is great.  It provides a foundation for creating a variety of rules to detect duplicate records using "like" matches across multiple entity types.  However, "like" matches can start to breakdown when the spelling of names differs, which often occurs with proper names.  This article describes how to extend the duplication detection in Microsoft CRM 4.0 to include "fuzzy" matching based on the Soundex algorithm.

The Soundex algorithm converts a name into a four-character string, originally used for indexing, that can also be used for comparisons.  In Microsoft CRM 4.0 to implement Soundex duplicate detection we created a new field on the entities to be compared for storing the Soundex value and added JavaScript to the "onChange" event of the name field to store the value.  The steps involved are:

1. Add "Soundex" attribute to your entity
   
    
2. Add "Soundex" attribute to your form (set as read-only).  This step helps illustrate the example, validates the logic is working correctly, and makes the field available to the JavaScript in step # 3.  However, it is probably best not to display this value to the user and it could be hidden as described here on Ronald Lemmen's blog.
   
   
    
3. In the "onLoad" event set the ForceSubmit flag to true for the Soundex field
   
   
    
4. Apply "onChange" event to the primary name attribute using JavaScript such as the code found here to set the value of the Soundex field.
   
5. Create a new duplicate detection rule comparing the Soundex values
   
   
    

Once everything is in place, if you create an account named John's Bait Shop and try and create a new account named Jon's Bait Shop the duplication rules will detect a potential duplicate and alert the user accordingly.

What we found is not only is this rule effective in finding potential duplicates, but if Soundex rules are in place there is not really a need to use "like" matches based on the the primary field.  Once the Soundex values are in place they can be used across entities like other duplication rules.

It is worth noting the "onChange" event only fires when the data is input via the standard UI.  It is probably better to apply the Soundex logic via a server-side callout, but for the simplicity of this example we are just demonstrating a client-side approach.

Migrating Security Roles

At the Worldwide Partner Conference in Denver Microsoft revealed the next version of Microsoft CRM (Titan) will include the ability to export and import security roles.  On the surface this seems like a small change, but it was significant enough to get my attention as this has always been a sore spot for us.

True, Microsoft does provide some migration tools.  However, the simple task of deploying an implementation from a development environment to a QA or production environment usually meant the manual update of security roles -- not only was this a time consuming practice, it was also error prone.  Because of the number of records involved in the configuration of security roles and privileges and the differences of unique identifiers from one database to another there did not appear to be a simple way to script out the security configuration to automate this step in the deployment process.

Well, now there is :-)

This is something I had meant to do for a while, but never was motivated enough to sit down and work out the SQL statements.  Until today when I found myself clicking little green circles one after another applying security changes from one environment to another.  I came to a quick conclusion that I could write a script to automate this task faster than it would take me to complete the manual application of the security settings.

Warning: Unsupported actions ahead

Assumptions

1. The source and target environments have the same business unit names
2. The source and target environments have the same security role names

Instructions

Script out the source security role privileges from the source system as follows (carriage returns have been added for positing purposes):

SELECT DISTINCT 'INSERT INTO ROLEPRIVILEGES (ROLEPRIVILEGEID, ROLEID, 
PRIVILEGEID, PRIVILEGEDEPTHMASK) SELECT NEWID(), (SELECT ROLEID FROM 
ROLEBASE R INNER JOIN BUSINESSUNITBASE B ON R.BUSINESSUNITID = 
B.BUSINESSUNITID WHERE R.NAME = ''' + R.NAME + ''' AND B.NAME = '''
+ U.NAME + '''), (SELECT PRIVILEGEID FROM PRIVILEGEBASE WHERE NAME =
''' + G.NAME + '''), ' + CAST(P.PRIVILEGEDEPTHMASK AS VARCHAR(10)) +
'' SQL FROM ROLEBASE R INNER JOIN ROLEPRIVILEGES P ON R.ROLEID = 
P.ROLEID INNER JOIN BUSINESSUNITBASE U ON R.BUSINESSUNITID = 
U.BUSINESSUNITID INNER JOIN PRIVILEGEBASE G ON P.PRIVILEGEID = 
G.PRIVILEGEID WHERE R.NAME <> 'System Administrator'

OK -- I know, it's ugly.  What is it doing?  Basically, this script creates a set of insert statements to create the security role privileges in the target system.  The insert statement using the names of the business units and security roles to get their ID's in the target system.

Note, the script does not export the privileges associated with the System Administrator role as this role is constant from one installation to another.  Here is an example of the insert statement created:

INSERT INTO ROLEPRIVILEGES (
       ROLEPRIVILEGEID, 
       ROLEID, 
       PRIVILEGEID, 
       PRIVILEGEDEPTHMASK ) 
SELECT NEWID(), 
       (SELECT ROLEID 
        FROM   ROLEBASE R INNER JOIN BUSINESSUNITBASE B
                          ON R.BUSINESSUNITID = B.BUSINESSUNITID 
        WHERE  R.NAME = 'Role Name' 
        AND    B.NAME = 'Business Unit Name'),
       (SELECT PRIVILEGEID 
        FROM   PRIVILEGEBASE 
        WHERE  NAME = 'prvAppendAccount'), 
       32

As you can see the generated statement does not contain any GUID's and has the statements necessary to locate the appropriate identifiers embedded.  Just image this line repeated 1000's of times and you can start to see the value of this approach.  To run the generated statements in the target environment make sure the business units and security roles have been created (they do not need to be configured).  If you are re-applying the security privileges to an existing implementation you can clear the existing records using the following statement:

DELETE FROM  ROLEPRIVILEGES
       WHERE ROLEPRIVILEGEID NOT IN (
             SELECT     P.ROLEPRIVILEGEID 
             FROM       ROLEPRIVILEGES P 
             INNER JOIN ROLEBASE R ON P.ROLEID = R.ROLEID 
             WHERE      R.NAME = 'System Administrator')

As a reminder, making updates to the database via SQL scripts is not supported by Microsoft.  Errors in the scripts or execution of the scripts can result in damage to your Microsoft CRM installation, your data, or both.  Please proceed with caution.

How to brand your CRM implementation

Warning: unsupported (but minor) customizations ahead!

This is something we use in our development environments to quickly identify the client associate with the implementation, but is also a simple way to brand a production installation of Microsoft CRM:

To add a logo follow these steps:

1. Open the bar_top.aspx file in the <Microsoft CRM Web>\_root folder using a text editor
   
2. Replace the table with the class stdTable stageContextBar with the following:
   

   <table class="stdTable stageContextBar" cellspacing="0"
           cellpadding="0">
       <tr>
           <td class="bar">
               <nobr id="tdStageContextBar">
   <%HttpUtility.HtmlEncode(stageContextBarTitle, Response.Output);%>
               </nobr>
           </td>
           <td align="right">
               <img src="../_imgs/logo.png" />&nbsp;
           </td>
       </tr>
   </table>

3. Copy an image file to the <Microsoft CRM Web>\_imgs folder (in the example above the image is named logo.png).

If you wish you can specify the image dimensions in the HTML, as well as add a title for the mouse over.  That's it.  Nothing fancy, but it gets the job done ;-) 

Creating your own customer fields

Michael Höhne has posted another great article showing just what is possible with client-side scripting in Microsoft CRM.  This particular post discusses how to add a customer field to a form, which is a data type not available as an option in Microsoft CRM (though it is used extensively internally).

If I had the wherewithal to extend what Michael has created I would encapsulate his code in a CSS behavior file and in the OnLoad event set the behavior for the account lookup field to the file created.  This would allow the code to be quickly reused on other forms.  But alas... it is late and I am tired, so I will just imagine myself doing this :-)

There are a lot of great tips and advice on Michael's site, so if you have a moment I suggest you check out:

http://www.stunnware.com

Or if you just want to subscribe to his blog you can get to that here:

http://www.stunnware.com/crm2/atom.aspx

Editable Grid now available

AdvantageWorks would like to invite you to download a trial of our editable grid control for Microsoft CRM 3.0. As part of our Software Development Kit (SDK), the editable grid allows the end user to edit the contents of a grid directly in the grid by the click of a single button.

The grid supports editing of the following CRM data types:

• Picklist
• Date (no time)
• Lookup
• Integer
• Money
• Float
• Boolean
• Status
• Customer
• Owner
• Decimal
• String

Additionally, the grid enforces Microsoft CRM's built-in security model and prevents users from editing the following types of data:

• State attributes
• Fields with a display mask of ObjectTypeCode
• Fields marked as read-only
• Fields disabled on their associated edit form
• Fields with an active OnChange event on their associated edit form
• Fields the user does not have write privileges for (both record and entity level)

The AdvantageWorks Editable Grid for Microsoft CRM 3.0 is designed to work in conjunction with our Split View control. However, it can also be used as a stand-alone component in your own custom ASP.NET 2.0 pages.

If you are interested in obtaining a copy of the editable grid, please visit our download page to submit a download request.

Inline Grid Editing

AdvantageWorks is excited to announce the upcoming release of our CRM Grid control, now with inline editing! These screenshots are just a preview of the control in action, which we plan to release in June to our current customers with a trial version following shortly.

Figure A – AdvantageWorks Grid control within our Split View component

Figure B – Enabling Inline Grid editing

Figure C – Applying Changes

Want to know more? We will be posting additional information in the coming weeks here and on our web site at http://www.advantageworks.com, so check back later for updates.

 

One-to-Many Relationships - Part II

This is an update to my previous post with additional instructions for maintaining the lookup value's name on the edit form.

CAUTION: Unsupported customizations ahead -- use at your own risk!

Creating one-to-many relationship in Microsoft CRM can usually be accomplished through standard configuration using the tools included in the product. However, relationships between two system entities not previously created by the system cannot be added using the tools. Instead, a custom attribute must be added to the entity via the Export / Import XML customization file.

The following steps describe the process for creating a one-to-many relationship via custom attribute

Creating a custom attribute

1. Export the customizations for the entity to be on the “child” side of the one-to-many relationship
   
2. Add a new attribute with a physical name describing the primary key of the “parent” entity
   
3. Set the element IsCustomField equal to 1
4. Set the element ReferencedEntityObjectTypeCode to the object type code of the “parent” entity. For example:

   <attribute PhysicalName="FieldId">
       <Type>lookup</Type>
       <ValidForCreateApi>1</ValidForCreateApi>
       <ValidForUpdateApi>1</ValidForUpdateApi>
       <ValidForReadApi>1</ValidForReadApi>
       <IsCustomField>1</IsCustomField>
       <ReferencedEntityObjectTypeCode>1</ReferencedEntityObjectTypeCode>
       <DisplayMask>ValidForAdvancedFind|ValidForForm|ValidForGrid</DisplayMask>
       <Description />
   </attribute>

   
   
5. Import the customizations and publish the changes
   
6. Add a second attribute via the customizations form for storing the name of the selected lookup. This field will not be visible to the user, but is needed to properly display the lookup on the form.
   
7. Add both attributes to the edit form
   
8. Add the following JavaScript to the OnLoad event of the form:
   
   Note: This sample assumes the lookup attribute is named FieldId and the name attribute is named FieldIdName

   /* Hide the name field */
   crmForm.all.fieldidname_c.style.display = 'none';
   crmForm.all.fieldidname_d.style.display = 'none';
   
   /* Retrieve the lookup control */
   var o = crmForm.all.fieldid;
   var a = o.getLookupField().getElementsByTagName("SPAN");
   
   /* Update the name if a lookup was found */
   if (a && a.length > 0)
   {
       /* Retrieve the name value */
       var name = crmForm.all.fieldidname.DataValue;
       
   
       /* Update the lookup with the name value */
       a[0].innerHTML = a[0].innerHTML.replace(/>.*$/, '>' + HtmlEncode(name));
       
   
       /* Reset the IsDirty flag by setting the default value */
       o.DefaultValue = o.DataValue;
   }

9. Add the following JavaScript to the OnChange event of the lookup:

   /* Retrieve the lookup value */
   var items = crmForm.all.fieldid.DataValue;
   var o = (items == null  items.length == 0) ? null : items[0];
   
   /* Update the name field */
   crmForm.all.fieldidname.DataValue = (o == null) ? '' : o.name;

One-to-Many Relationships

Creating one-to-many relationship in Microsoft CRM can usually be accomplished through standard configuration using the tools included in the product. However, relationships between two system entities not previously created by the system cannot be added using the tools. Instead, a custom attribute must be added to the entity via the Export / Import XML customization file.

The following steps describe the process for creating a one-to-many relationship via custom attribute:

1. Export the customizations for the entity to be on the “child” side of the one-to-many relationship
2. Add a new attribute with a physical name describing the primary key of the “parent” entity
3. Set the element IsCustomField equal to 1
4. Set the element ReferencedEntityObjectTypeCode to the object type code of the “parent” entity

The following is an example of adding an attribute to a “child” entity where the “parent” entity is account:

<attribute PhysicalName="New_AccountId">
    <Type>lookup</Type>
    <ValidForCreateApi>1</ValidForCreateApi>
    <ValidForUpdateApi>1</ValidForUpdateApi>
    <ValidForReadApi>1</ValidForReadApi>
    <IsCustomField>1</IsCustomField>
    <ReferencedEntityObjectTypeCode>1</ReferencedEntityObjectTypeCode>
    <DisplayMask>ValidForAdvancedFind|ValidForForm|ValidForGrid</DisplayMask>
    <Description />
</attribute>

Entity Inheritance

One request we seem to see a lot from our customers is the need to have different flavors of accounts and contacts.  For instance, an account may be a financial institution, a tenant, or a vendor.  We could create three different custom entities to represent the uniqueness of each of these account types, but we would then lose the built in functionality regarding an account in Microsoft CRM.

Alternatively, we could add attributes to the account entity to fulfill the needs of each account type..  However, that would result in having attributes that apply to one type (say tenants) visible for another type (say vendors).  That might reduce the usability of the system.  Granted, we could create a tab for each account type and hide / display the tab based on the type selected.  This is a valid approach, but still requires quite a bit of custom client side code and does not prevent the unrelated attributes from showing in other areas such as the advanced find or system views.

I would like to hear how others have addressed this question.  Ideally, I would like to be able to inherit from the account entity and create custom entities that are based on account.  Unfortunately, this is not a supported customization and may not be possible in the current version of Microsoft CRM.  Please share your solutions, as this is probably a question many of us have had to answer at one time or another.