Verify mappings between picklists

When you need to maintain a large number of picklists, it can be time consuming to keep the mappings between entity picklists consistent. This is because every picklist item has a label and a corresponding number. And this number is not visible when working with Microsoft CRM. When you map a picklist to a picklist in another entity, you should verify whether the underlying numbers are ok.

So I created this SQL script to verify picklist mappings. Just replace the text YOUR_ORGANIZATION_NAME_HERE with your organisation name, and run it against the CRM database. Funny, when I run it against a fresh (vanilla) Microsoft CRM installation, already some query results are showing up..... any explanation on this?

(edited on 20 July 2007, adjusted alias and sorting to make it more usable)

use YOUR_ORGANIZATION_NAME_HERE_MSCRM
select
em.sourceentityname as SourceEntity
, am.sourceattributename as SourceAttribute
, em.targetentityname as TargetEntity
, am.targetattributename as TargetAttribute
, s1.attributevalue as Number
, s1.value as SourceLabel
, s2.value as DestLabel
from
YOUR_ORGANIZATION_NAME_HERE_MSCRM.dbo.attributemap am
,YOUR_ORGANIZATION_NAME_HERE_MSCRM.dbo.entitymapbase em
,YOUR_ORGANIZATION_NAME_HERE_MSCRM.dbo.stringmap s1
,YOUR_ORGANIZATION_NAME_HERE_METABASE.dbo.Entity s1name
,YOUR_ORGANIZATION_NAME_HERE_MSCRM.dbo.stringmap s2
,YOUR_ORGANIZATION_NAME_HERE_METABASE.dbo.Entity s2name
where am.entitymapid = em.entitymapid
and am.sourceattributename = s1.attributename
and am.targetattributename = s2.attributename
and em.sourceentityname = s1name.name and s1.objecttypecode = s1name.objecttypecode
and em.targetentityname = s2name.name and s2.objecttypecode = s2name.objecttypecode
and s1.attributevalue=s2.attributevalue
and exists(
select *
from
YOUR_ORGANIZATION_NAME_HERE_MSCRM.dbo.stringmap s1
,YOUR_ORGANIZATION_NAME_HERE_METABASE.dbo.Entity e1
,YOUR_ORGANIZATION_NAME_HERE_MSCRM.dbo.stringmap s2
,YOUR_ORGANIZATION_NAME_HERE_METABASE.dbo.Entity e2
where s1.objecttypecode = e1.objecttypecode
and s2.objecttypecode = e2.objecttypecode
and e1.name = em.sourceentityname
and e2.name = em.targetentityname
and am.sourceattributename = s1.attributename
and am.targetattributename = s2.attributename
and s1.attributevalue=s2.attributevalue
and rtrim(ltrim(s1.value)) <> ltrim(rtrim(s2.value))
)
order by
em.sourceentityname
,am.sourceattributename
,em.targetentityname
,am.targetattributename
,s1.attributevalue

Moved to CodePlex

I moved the CRM Documentation generator from GotDotNet to CodePlex. CodePlex is a great site for maintaining software projects. It uses Team Foundation Server, this way it is possible to check-in source code using Visual Studio! Also the Work Item feature is a great help to organize the work that needs to be done on software projects.

Copy address record from account to subcontact

How to automatically copy the address record from an account record to all contacts beneath it?

My first thought was to use the Microft CRM 3.0 SDK. With help of the SDK it is possible to create an executable that periodically copies addresses. However having tens of thousands customer records, it could take hourse to complete.

My second thought was to use a SQL update script. However, modifications on the CRM database are not supported by Microsoft MSFT, and worse, could ruin your installation. One of my customers decided to have it anyway. So I created a script and scheduled it to run nightly at 04:00. Till today this customer is a very happy customer. So I would like to share this with you.

This is how it works
The user decides whether he/she wants the address from the parent account automatically copied in into the contact address. When the users decides to copy in the addresses (by selecting a radio button) the address fields will be greyed out. This is to prevent the user from entering values that would be overwritten. Every night a scheduled SQL Server job copies the addresses from account to subcontacts.



The following steps are needed to get the job done:

Create a new attribute on the contact entity
Using Settings-Customizations, customize the contact entity and add the following attribute:

• Display Name: Copy address from account?
• Schema Name: new_addressfromaccount
• Requirement Level: No Constraint
• Searchable: No
• Type: bit
• Default Value: Yes

Add the new attribute to the contact form
Customize the contact form and add the new field to the address section. When you add the attribute to the form, make sure you choose the "Two radio buttons" Control Formatting (or the scripting will not work).

Add code to the onload event
Add some code to the onload event of the contact form. This will grey out the necessary fields when the checkbox is active. This will prevent the user from entering values that will be overwritten.

  // This script adds functionality to the contact form
  // so the necessary fields are greyed out when a user
  // chooses to copy in addresses from the parent account.

  document.copyAddressFromAccount = function()
  {
    if (crmForm.FormType != 5) //don't want this on quick create form
    {
    var oCheckBox = crmForm.all.new_addressfromaccount;

    var bDisabled = false;
    if (oCheckBox.DataValue == true) bDisabled = true;
    {
      crmForm.all.address1_name.Disabled = bDisabled;
      crmForm.all.address1_line1.Disabled = bDisabled;
      crmForm.all.address1_line2.Disabled = bDisabled;
      crmForm.all.address1_line3.Disabled = bDisabled;
      crmForm.all.address1_city.Disabled = bDisabled;
      crmForm.all.address1_stateorprovince.Disabled = bDisabled;
      crmForm.all.address1_postalcode.Disabled = bDisabled;
      crmForm.all.address1_country.Disabled = bDisabled;
      crmForm.all.address1_telephone1.Disabled = bDisabled;
      crmForm.all.address1_addresstypecode.Disabled = bDisabled;
      crmForm.all.address1_shippingmethodcode.Disabled = bDisabled;
      crmForm.all.address1_freighttermscode.Disabled = bDisabled;
    }
  }
  document.copyAddressFromAccount();

Add code to the onchange() event of the attribute
Add the following code to the onchange() event of the new attribute new_addressfromaccount:

  document.copyAddressFromAccount();

Test and publish the contact form
When you are ready making the modifications (add attribute, add field to form, add scripts), test the form. When setting the field "Copy address from account" from No to Yes you should notice the address fields will be greyed out. Finally publish the contact form when you are satisfied.

Schedule a SQL script
This next task requires you to logon to the database server:

• start SQL Server Management Studio
• open the SQLServer Agent folder
• open the Jobs folder
• make a new job and name it "Copy address from account to subcontacts"

Add a new step to the job:
Each SQLServer job includes one or more steps, this way it will keep your scripts organized.

• add a new step to the job, name it "Update fields for address 1"
• select the _MSCRM database
• enter the following sql script:

  update
    customeraddressbase
  set
    [name]=caba.[name]
    ,line1 = caba.line1
    ,line2 = caba.line2
    ,line3 = caba.line3
    ,city = caba.city
    ,stateorprovince = caba.stateorprovince
    ,postalcode = caba.postalcode
    ,country = caba.country
    ,telephone1 = caba.telephone1
    ,addresstypecode = caba.addresstypecode
    ,freighttermscode = caba.freighttermscode
    ,shippingmethodcode = caba.shippingmethodcode
  from
    customeraddressbase
    ,contactbase cb
    ,customeraddressbase caba
    ,contactextensionbase ceb
  where
    cb.contactid = customeraddressbase.parentid
    and cb.contactid = ceb.contactid
    and caba.addressnumber = customeraddressbase.addressnumber
    and caba.parentid = cb.accountid
    and customeraddressbase.addressnumber = 1 -- this is the first address
    and ceb.new_addressfromaccount = 1

Add a second step to the job:
The second job will update the second address fields

• add a new step to the job, name it "Update fields for address 2"
• select the _MSCRM database
• enter the following sql script:

  update
    customeraddressbase
  set
    postofficebox = caba.postofficebox
    ,postalcode = caba.postalcode
    ,city = caba.city
    ,stateorprovince = caba.stateorprovince
  from
    customeraddressbase
    ,contactbase cb
    ,customeraddressbase caba
    ,contactextensionbase ceb
  where
    cb.contactid = customeraddressbase.parentid
    and cb.contactid = ceb.contactid
    and caba.addressnumber = customeraddressbase.addressnumber
    and caba.parentid = cb.accountid
    and customeraddressbase.addressnumber = 2 -- this is the second address
    and ceb.new_addressfromaccount = 1

Schedule the job
When the new job is finished, you can run the job, and schedule the job to run nightly.

Automatic deployment of Microsoft CRM for Outlook

A common question I receive as a CRM consultant is how to automatically rollout the Microsoft CRM for Outlook client using a software distribution tool.

A quick look at chapter 16 of the Microsoft CRM 3.0 Implementation Guide points out that the supported scenario is to use an administrative install location. Let's explain this concept. The .msi installation image will be stored on a central location (network share). Next the image will be published in a Group Policy in Active Directory. This allows the user to install Microsoft CRM from the shared location. Or install it from the Add or Remove Programs using the Control Panel. The policy grants extra rights (so called elevated install) to the user so he/she will be able to install Microsoft CRM.

The supported scenario will require manual interaction of the end user. Although it is just about pressing next a few times, many system administrators don't like this. So then the question arises why not use a software distribution tool to automatically distribute the package? Here are a number of reasons why this currently is difficult to achieve.

- The Microsoft CRM installation program includes a wizard checking a large number of prerequisites before it allows the software to be installed. You will need to find a way to reproduce those checks to have predictable results.

- During installation the Outlook profile of the currently logged on user is needed. The user must be logged on and Outlook must be started at least once. The Microsoft CRM client will install on top of the Outlook profile.

- Administrative rights will be required to install the CRM client .msi package. Installation of the MSI package will simply fail if the user does not have administrative rights.

To be able to let a software distribution tool do its work successfully, you will need some basic functionality. It should be possible to automatically check whether the PC and the logged on user meet all requirements so the Microsoft CRM for Outlook will run happily. Next you will need to package the software using a differencing tool and distribute it. You will need to find a way to apply the settings to the user's Outlook profile. Next it should be possible to collect some log files and information on the installation result. This way the administrator can effectively track down and resolve failures.

Some documentation and tools are available on how to install the desktop client on a Terminal Server environment. This is great because now you will be able to package the CRM desktop client. However you still need to be an infrastructure specialist to get it done. And - please correct me if I'm wrong - it is not possible for the laptop client. The laptop client is different because it includes offline capabilities including the SQL Server 2005 destkop engine.

Recently Microsoft released the new V3C client containing many improvements, also for the automatic distribution. Just an example is the usage of MAPI stores instead of addtional .PST files. However there is still some work for Microsoft to do to get everyone happy. Let's hope the new Titan release will bring lots of relief.

CRM Documentation Generator

Add-in for Microsoft Office 2007 to automatically generate documentation for Microsoft Dynamics CRM 3.0

At several projects utilizing Microsoft CRM I realized it takes a lot of effort to create useful documentation on fields/screens that exist in Microsoft CRM. Is it possible to generate the documentation from an exported customizations file from Microsoft CRM? (as opposed to retyping all fields in Excel?) The CRM Documentation Generator just does that! It is a hotel room effort, however it is already becoming quite useful. The following technologies are used: Visual Tools for Office SE, and Office 2007. You can install this add-in on top of Microsoft Office 2007. All code is included, enjoy.

Automatically Resolving Postal Codes

Wouldn't it be nice when a user fills in one field with a postal-code, other fields are filled in a from another (address) database automatically? Microsoft just released a Technical Article. It shows how a user can select one or more records from a grid, and execute a custom action on those selected records. It includes a sample demonstrating how to automatically enhance customer address information upon entering a postal-code.

Microsoft Dynamics CRM 3.0: Accessing Selected Records in a Grid

I had my application up-and-running in a breeze. Great stuff!