November 24, 2006

Free XML tools you need

XML Notepad 2007

Three days ago Microsoft released a new version of XML Notepad. They fixed the bug in Vista installation and added some new features. Tree View is synchronized with Node Text View for quick editing of node names and values, drag/drop support for easy manipulation of the tree, instant XML schema validation and more.

Get it here:

XPath Builder

Another great free app is the XPath Builder. It allows you to quickly create XPaths, and display results. Intellisense supported!!!

November 21, 2006

Creating many to many relationships

OK. Don't be too excited. As I said in previous posts, custom many-to-many relationships are not possible, but there is a way to create "quasi" m:n relationships.

My idea was to create a many-to-many relationship in a way it is done in a standard SQL server. If you want to connect two entities with this type of relationship, you have to create a connecting entity.
Actually this is not so bad at all. The connecting entity can provide us with additional info about the relation.
The form will have two additional tabs with IFRAME objects, where the related object forms will be displayed. You can see the results in these two pictures

I want to create a solution that is supported by CRM SDK, so it can be upgraded to next versions of Microsoft CRM.

We have two entities, a Contact and a custom entity Car. The Car entity represents a company car that we rent to our customers. We want to create a many-to-many relationship (a contract) between Car and Contact entities. In the relationship we want to store some additional info, let's say the contract number.


At first we have to create a connecting relationship. I will name it "Contact car". The primary attribute is the contract number.

The Car entity's primary attribute is the license plate number, the Contact's primary attribute is full name, so we won't have a problem identifying the objects in the views.

Create two many-to-one relationships in the "Contact car" entity. I decided to create a parental relationship with Contact and "Referential, restrict delete" with Car. Restrict delete is important, otherwise you can lose data when deleting a car. Well... You can decide your own logic here.

First add both lookup fields to the form (Car and Contact). Then add two tabs to the Contact car form named Car and Contact. Add an IFRAME in each tab. Name them IFRAME_Car and IFRAME_Contact. Set the URL to about:blank.
Now let's go to scripts:
You'll need Car entity type code, which you can find in http://serverName/sdk/list.aspx

Form onLoad
On each load of the page, a related entity must be displayed in the IFRAME:

var contactLookupItem = new Array;
contactLookupItem = crmForm.all.new_contactid.DataValue;

if (contactLookupItem && contactLookupItem[0] != null)
var sCustUrl = "http://ServerName/sfa/conts/edit.aspx?ID=" + contactLookupItem[0].id;
document.all.IFRAME_Contact.src = sCustUrl;

var carLookupItem = new Array;
carLookupItem = crmForm.all.new_carid.DataValue;

if (carLookupItem && carLookupItem[0] != null)
var sCarUrl = "http://
ServerName/userdefined/edit.aspx?id=" + carLookupItem[0].id + "&etc=CarEntityTypeCode";
alert("REdirecting..." + sCarUrl);
document.all.IFRAME_Car.src = sCarUrl;

We have to change the forms if lookup fields change:

Car lookup field onChange

var oField = event.srcElement;

var carLookupItem = new Array;
carLookupItem = oField.DataValue;

if (carLookupItem && carLookupItem[0] != null)
var sCarUrl = "http://ServerName/userdefined/edit.aspx?id=" + carLookupItem[0].id + "&etc=EntityTypeCode";
document.all.IFRAME_Car.src = sCarUrl;

Contact lookup field onChange

var oField = event.srcElement;

var contactLookupItem = new Array;
contactLookupItem = oField.DataValue;

if (contactLookupItem && contactLookupItem[0] != null)
var sContactUrl =
"http://ServerName/sfa/conts/edit.aspx?ID=" + contactLookupItem[0].id;
document.all.IFRAME_Car.src = sCarUrl;

Update all Contact car views to display Car and Contact lookup, so users can quickly identify the records.

This is it. I can't say it's GREAT solution, but it's still better than nothing. Users can see Contact's an Car's data in the same form. They can even change them, using the buttons in the IFRAME.

You could hide the buttons in the IFRAME forms or disable the form (document.all.IFRAME_Car.document.body.disabled = true;), but I haven't found a supported way to do this.

November 17, 2006

Custom entity limitations

This is a summary from the book Working with Microsoft Dynamics CRM 3.0.

A list what you can't do with custom entities:
  • You cannot create many-to-many relationships between entities. Well... you cannot create many-to-many relationships at all
  • You can not merge two custom entities together
  • Export and import is possible only by using some third party tool. Microsoft CRM Data Migration Framework does not support custom entities
  • The Microsoft CRM system entities include a relationship to Customer in which users can select an Account or a Contact. For custom entities, you can specify a relationship with the Account entity and the Contact entity, but you cannot create a relationship to the composite Customer entity (in which users can select an Account or a Contact on a single lookup).
  • Only one parent relationship per custom entity is allowed
  • Custom entities don't appear in an entity rollup
  • Organization-owned custom entities can't participate in Microsoft CRM workflow
  • You cannot create a custom relationship between an entity and itself
  • You could not create relationships in such a way that a form has two or more lookups that connect to the same custom entity.
  • Custom entities cannot have a parental relationship behavior to system entities

November 16, 2006

How to change duration or time picklist values

In previous post I suggested tweaking the form in the onLoad method to change the values of the duration or time picklist.

Values are stored in a table that looks like this:
<TR><TD val=""></TD></TR>
<TR><TD val="1 minute">1 minute</TD></TR>
<TR><TD val="2 minutes">2 minutes</TD></TR>

All you need is to change the table values so here's an example of the onLoad script that deletes all values from phone duration field and adds two new - 11 and 22 minutes to the list.
Of course you can decide to delete only the vales you want or just add new values.

//Get the duration field
var oDuration = document.getElementById("actualdurationminutesSelect");

//Get the values table. The values table is the second table
var oTable = oDuration.getElementsByTagName("TABLE")[1];

//Get the table TBODY element
var oTbody = oTable.firstChild;

var childs = oTbody.childNodes;

//Delete all values
for(var i=childs.length; i-- ; i>= 0)

//Create TR and TD elements
var oTr = document.createElement("TR");
var oTd = document.createElement("TD");

oTd.innerText = "11 minutes"; //Set value

var oValue = document.createAttribute("val"); //Create attribute node
oValue.value = "11 minutes"; //Set value of the attribute

//Add elements to the table

oTr = document.createElement("TR");
oTd = document.createElement("TD");

oTd.innerText = "22 minutes"; //Set display value

var oValue = document.createAttribute("val");
oValue.value = "22 minutes";


Picklist hell

Here are three main problems with picklists developers should be aware of:
  • Deleting values from a picklist
  • Mapping picklists
  • Picklists as duration or time fields
1. Deleting values from a picklist
When you are deleting values from a picklist be aware that the value will be deleted also from all records that contain the deleted value. You will loose data. Fortunately CRM 3.0 warns you before you attempt to do this.

2. Mapping picklists
Value type of a picklist is integer. Every time you add a value to the picklist, CRM automatically assigns integer values. You cannot edit these values since they are read-only. So if you have two mapped picklists be sure to enter both picklists in the same order. If you made a mistake, delete the values from both picklists and add them again in the same order.

Wouldn't it be nice to have some sort of "global" picklists that can be used in different entities and edited on one place?

3. Picklists as duration or time fields
You can not add values to duration or date time fields. Users can click in the field and enter the value themselves. The database stores the duration in whole minutes, so 14,25 minutes will be stored as 14 minutes.

You can tweak the lists in onLoad script to add or delete values.

November 15, 2006

Search in lookup views

I've noticed users have problems searching custom fields in lookup views. Actually it does not have anything with custom fields.

In a lookup view search is performed only on columns displayed in the view. Even if you add a column to "Find columns" list, you still have to add it to "Displayed columns" list.

So if you want to find a Contact by his, let's say, birth date, you have to modify Contact's lookup view to display birth date and add the birth date column to "Find columns" list.

November 14, 2006

Creating new attributes -Take a moment and think again

Imagine you just discovered that you make an appointment with Mr Smith mostly on Mondays, Mrs Brown prefers Wednesdays etc.

So you decide to put a picklist in the Contact form. You create a new attribute and name it new_PreferredAppointmentDay containing all days in a week. Problem solved!

Not exactly!

Everything looks great until you want to use mail merge in Outlook to make an appointment with Mr Smith on his preferred day. What happened to my custom attributes? Where is new_PreferredAppointmentDay?

You cannot include custom attributes as Mail Merge fields.
Take a look at the attributes list in the Contact entity. You will find the attribute named preferredappointmentdaycode.

1. Take a close look at the predefined attributes in an entity

Microsoft created about 15.000 attributes right out of the box. And they are there for you to use them. They are accessible through mail merge in Outlook and they save you the time you need to create custom attributes.
Custom attributes are accessed through filtered views, so you can use them in other applications when you connect directly to the database, but not in Outlook mail merge.

You can access the list of all entities and their attributes also with metadata browser on http://crmServer/sdk/list.aspx

2. Create new attributes with care
  • When creating new attribute it is really important to enter a description of the attribute. At least put your name and date in. Do try to enter a purpose for adding the attribute.
  • The name of the attribute should be short, but descriptive. For god's sake do not name it new_att1! The name must not be too long, otherwise you'll get lost in SQL queries.

3. Limitations when modifying an attribute
  • Maximum length - You cannot increase maximum length of the nvarchar or ntext field. You can decrease it, but not increase. So think again when setting the limit of the text field length.
  • Picklist values - When you delete a value from picklist you can permanently loose your data. Microsoft CRM automatically deletes the picklist value from all records that used this value. Those records will display blank picklist.

November 13, 2006

So what is this "Afna" thing all about?

I ran into MS CRM 3.0 about six months ago and immediately I got addicted to it. Really simple interface for the end-users and flexible design for developers.

Microsoft really did it's best to create a really useful out of a box platform that can be used in most companies almost (and I mean it) immediately. All basic entities, attributes and relationships are prepared for the "first use".

The slogan for CRM 3.0 should be MS CRM 3.0 - Just add data.

Meanwhile I developed an application that imports data from any SQL database to CRM. I really suggest that you take a look at Michael Hohne's site ( and the newsgroups:

Currently I am reading a great book from MS press: Working with Microsoft Dynamics CRM 3.0. (link to Amazon).
I already went through all CRM at my own, had three day training, went deep into the database and CRM metadata, but this book still gives me new things and what is more important - very useful tips.

I took a glance at Microsoft CRM 3 For Dummies. (link to Amazon). Not disappointed, but not for developers. Nice to read if you really don't know what CRM really is.

I still owe you an explanation of the word "Afna". In my language we use it to describe the @ sign. But originally it means a person who is acting funny, usually making a fool of himself. So a play of words.