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.


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


SOLUTION

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

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

Forms
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;
}

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