Overcoming relationship restrictions in Microsoft CRM v3.0

A common complaint about Microsoft CRM is the limited number and types of relationships between entities that can be set up. For example, the vanilla CRM does not allow you to set up relationships between two opportunity entities. Plus it also prevents you from setting up more than one relationship between entities of different types; while an Opportunity has a Potential Customer, you cannot also link it to a second or third account or contact.

There is at least one way around this restriction (for one to one or one to many relationships), though it involves some programming.

So how?

Where I work, we have tailored opportunities to represent memberships of a fee-based healthcare standards accreditation and monitoring program. There are various types of memberships, of different durations. We had a requirement to allow supervisory or umbrella organisations to be able to pay for the memberships of their subordinate accounts, but have the subordinate accounts’ memberships exist as separate entities.

Without going into too much detail, we needed a one-to-many relationship between the opportunity representing the membership of the parent account, and the opportunities representing the related memberships of the subordinate accounts. None of the account/subaccount, regular CRM account/account, or customer/opportunity relationships would give us what we wanted.

What would have been ideal was a lookup attribute on each child opportunity which would link direct to the parent opportunity, and a grid of hyperlinked child opportunities on the form for the parent opportunity.

This was done – but not via standard features of CRM.

To understand this, we first need to take a detailed look at how the standard CRM Lookup control works.

The Microsoft CRM Lookup control in detail

Here is a contact with her parent account, just to remind you what a lookup field looks like on the form.


Here’s the Parent Customer HTML for an account which has an account as its parent customer.

(To see this for yourself from Microsoft CRM, hit Control-N on a Contact form. This shows the Internet Explorer toolbars, menu bar, etc. Then choose View/Source from the menu bar – which will display the HTML for the form itself. There’s a LOT there, and it doesn’t present in an easy-to-read format. Deal with it. Search for “parentcustomerid” and you should find something similar to what’s below.)

Comments added by me in italics:

<!—the “Parent Customer” label -->
<td id="parentcustomerid_c" class="n">Parent Account</td>
<!-- the “text box” containing the name of the account,
and a small icon representing the account entity.
Note the oid (the GUID for the account ID), the otypename (“account”)
and the otype (1 – the object type id of the account entity.
Also note the “ico_16_1.gif” for the image –
this is a 16x16 pixel image of the object type 1, i.e. an account) -->
<td id="parentcustomerid_d">
  <table class="lu" cellpadding="0" cellspacing="0" width="100%" 
    style="table-layout:fixed;">
    <tr><td>
      <div class="lu">
        <span class="lui" onclick="openlui()" 
          oid="{C208C9F2-B3AC-DA11-AB00-00034732FD14}"
          otype="1" otypename="account"><img class="lui"
          src="/_imgs/ico_16_1.gif">Test Account</span>
      </div>
    </td>

<!-- the “button” with the lookup magnifying glass image,
which brings up the Lookup form when clicked. This particular lookup
 is used to find both accounts (object type 1) and contacts (object type 2).
Note how this manifests in the lookuptypes, lookuptypenames,
and lookuptypeIcons parameters below.
The lookup class (ParentLookup) is discussed below in detail -->

    <td width="25" style="text-align: right;">
     <img src="/_imgs/btn_off_lookup.gif"
      id="parentcustomerid" class="lu" tabindex="1050" 
      lookuptypes="1,2" lookuptypenames="account:1,contact:2"
      lookuptypeIcons="/_imgs/ico_16_1.gif:/_imgs/ico_16_2.gif"
      lookupclass="ParentLookup" lookupbrowse="0" lookupstyle="single"
      defaulttype="0" req="0" onclick="parentcustomerid_onclick();">
    </td>
  </tr>
 </table>

<!-- code for the control’s OnClick event. Contrary to the message in the catch paragraph,
this is not custom code entered by the user
but is produced by CRM itself -->

  <script language="javascript">function parentcustomerid_onclick() {
     try {
      var sParentCustomerId = crmFormSubmit.crmFormSubmitId.value;
      var oLookup = event.srcElement;
      if (sParentCustomerId == "")
      {
        sParentCustomerId = '{00000000-0000-0000-0000-000000000000}';
      }
       /* Filter out the current contact */
       oLookup.AddParam("currentcontact", sParentCustomerId);
       oLookup.AddParam("currentaccount", sParentCustomerId);
       oLookup.Lookup(true);
     }
     catch(e)
     {
      alert("There was an error with this field\'s customized event
            \u002e\n\nField\u003a
            parentcustomerid\n\nEvent\u003a onclick\n\nError\u003a " +
         e.description);
     }
    }
  </script>
</td>

Hopefully, the explanations above are reasonably straightforward.
However, we need to look closely at the lookupclass parameter in the second piece of HTML above. It’s set to “ParentLookup”. What is this?

In the \Server\ApplicationFiles subfolder of the Microsoft CRM software hierarchy, We find a variety of *.xml files. One of which is ParentLookup.xml. Here are the contents of ParentLookup.xml:

<lookup name="ParentLookup">
  <objects>
    <object type="1">
      <columns>
       <column data="name" type="normal"/>
       <column data="primarycontactid" attribute="name" type="normal" size="150"/>
       <column data="address1_city" type="normal" size="100"/>
      </columns>
      <datasource>
        <filter type="and">
          <condition attribute="accountid" operator="ne" value="!currentaccount"/>
          <condition attribute="statecode" operator="ne" value="1"/>
            <filter type="or">
              <condition attribute="name" operator="like" value="!searchvalue" />
              <condition attribute="accountnumber" operator="like" value="!searchvalue" />
              <condition attribute="emailaddress1" operator="like" value="!searchvalue" />
           </filter>
        </filter>
     </datasource>
   </object>
    <object type="2">
      <columns>
        <column data="fullname" type="normal"/>
        <column data="accountid" attribute="name" type="normal" size="150"/>
        <column data="address1_city" type="normal" size="100"/>
      </columns>
      <datasource>
         <filter type="and">
          <condition attribute="contactid" operator="ne" value="!currentcontact"/>
          <condition attribute="statecode" operator="ne" value="1"/>
          <filter type="or">
            <condition attribute="fullname" operator="like" value="!searchvalue" />
            <condition attribute="firstname" operator="like" value="!searchvalue" />
            <condition attribute="lastname" operator="like" value="!searchvalue" />
            <condition attribute="middlename" operator="like" value="!searchvalue" />
            <condition attribute="emailaddress1" operator="like" value="!searchvalue" />
          </filter>
        </filter>
      </datasource>
    </object>
    <object type="1055">
      <columns>
        <column data="name" type="normal"/>
      </columns>
      <datasource>
        <filter type="and">
          <condition attribute="uomscheduleid" operator="eq" value="!parentid"/>
          <condition attribute="uomid" operator="ne" value="!currentunit"/>
        </filter>
      </datasource>
    </object>
  </objects>
</lookup>

While all of this is undocumented (at least as far as I have been able to ascertain), this file and (most of?) the others in the \Server\ApplicationFiles subfolder appear to drive the various lookup windows.

When you click on the lookup button on a Parent Customer (with the magnifying glass icon), CRM loads an *.aspx page called lookupsingle.aspx, with parameters driven by the values in the custom control HTML.

The following URL will load a lookup view of Accounts and Contacts, very similar to that shown for the Parent Customer (line break added for clarity):

To get Accounts only, use this URL (removing the “,2” from the object types in the above URL’s objecttypes parameter in the query string:

http://{server}/_controls/lookup/lookupsingle.aspx?class=ParentLookup
    &objecttypes=1,2&browse=0&DefaultType=0

and now we get:

http://{server}/_controls/lookup/lookupsingle.aspx?class=ParentLookup
    &objecttypes=1&browse=0&DefaultType=0

and:

Note how the picklist of entity types has now disappeared, and we can only select an account.

If this were an Account instead of a contact form, and you wanted to exclude the currently selected account if, to avoid possibly making an account a parent of itself – or exclude any other account for that matter, like any preselected Parent Customer value on this (the contact) form, you could add a current account parameter to the query string like so:

http://:5555/_controls/lookup/lookupsingle.aspx?class=ParentLookup&objecttypes=1
    &currentaccount={00000000-0000-0000-0000-000000000000}&browse=0&DefaultType=0

I encourage you to match up the query string parameters with the contents
of the ParentLookup.xml, and the HTML describing the Parent Customer Control in the Contact form. Lookups for other objects work similarly. Here’s one for opportunities:

http://{server}:5555/_controls/lookup/lookupsingle.aspx?class=opportunity
   &objecttypes=3&browse=0&DefaultType=0

And one for products:

http://{server}:5555/_controls/lookup/lookupsingle.aspx?class=ProductWithPriceLevel
&objecttypes=1024&browse=0&DefaultType=0

Where do these “classes”, like ParentLookup , ProductWithPriceLevel, etc. come from? AS near as I can make out, they match deprecated methods for retrieving data used in CRM v1.2. Download the CRM v1.2 SDK and see for yourself. If you find out exactly what this is all about, please let me know.

We could go for a long time describing the possibilities, but my intention is not to document the internals and quirks of Microsoft CRM. That’s Microsoft’s job … FWIW, they haven’t done it particularly well IMO.

Hopefully I’ve pointed you in the right direction to explore further. So, now we hopefully have a more detailed understanding of CRM Lookup controls. Let’s get back to setting up our new Parent/Child relationship between opportunities.

Setting up a Relationship between Opportunities

A true CRM Lookup field stores the GUID of the related entity in the lookup attribute and database field. The CRM software uses a specialised custom control on the primary entity form to display the name of the entity (actually, I presume, the relationship’s primary attribute) in the field instead of the actual GUID from the database, and to allow a button on the custom control to be clicked to allow selection of the particular entity concerned from a list.

We instead use a text field rather than a GUID/lookup field to store the GUID of the related entity. The string representation of the GUID is stored in the field with enclosing braces, { and }.

JavaScript/DHTML in the CRM Form’s OnLoad Event, and CRM low-level functions are used to effectively “hijack” CRM’s representation of that text field on the form to effectively display the same custom control and allow a related lookup when the custom control’s lookup button is clicked.

So, if we want to set up relationships between opportunities, complete with a lookup field to set the related parent opportunity on the child opportunity form, we do the following:

• Add a new nvarchar (text) attribute to the Opportunity entity. This field will be used to store a text representation of the GUID representing the opportunityid if the related parent opportunity for each child opportunity.
• Add the new attribute to the Opportunity form.
• If that field is not null when the form is loaded, use a CRM Web Service call from the form’s OnLoad event to find out the Opportunity Topic, for display in the Opportunity Lookup field you are about to create. This will then display the Opportunity Topic rather than the GUID in the next step.
• Add JavaScript code to the form’s OnLoad event to replace the text field for the new attribute with the HTML for a CRM .lookup custom control which allows a lookup on Opportunities. If the text field is already populated with a GUID, we display the Opportunity Topic you found in the previous step as the “hyperlink” in this Lookup control.

Let’s look at how we might set this up.

First, we go to Customisation, and add a new nvarchar attribute to the Opportunity entity.

(Note: I have changed the prefix for custom attributes from the default, “new_”).

Add the new attribute to the form:

Save and Publish.

What we need to do now is find the name of the new field as it appears in the HTML rendered by CRM when the form is loaded. Bring up a Contact form in CRM, then click Control-N. This will give you the Internet Explorer Menu Bar, Address Bar, Toolbars, etc. back which CRM hides by default. Then click View/Source on the IE (not the CRM) menu, and you will see the form’s HTML source loaded in Notepad (or the default application used to edit text files, if you have changed it from Notepad).

Now search for the schema name for our new field, in this case achs_parentopportunity.

The HTML does not get laid out real well, but I find (edited for clarity):

<td id="achs_parentopportunity_c" class="n">Associated Parent Opportunity</td>
<td colspan=3 id="achs_ parentopportunity _d">
<input type='text' id="achs_ parentopportunity" tabindex="1320" 
    maxlength="100" value="" req="0">
</td>

It is the <input> field (id="achs_ parentopportunity") which will be our target for the following operations. As you can see, the id matches the name of the attribute, which is fortunate for searching purposes.

Remember the Parent Customer lookup control on the Contact form? We are going to steal the functionality of this custom control and adapt it to our purposes to facilitate the new relationship between the parent and child opportunities.

We are going to replace the HTML for the achs_parentopportunity field with HTML to emulate an opportunity Lookup field. To do this, we need to add the following code to the opportunity form’s OnLoad() event:

/* replace the existing text control with an opportunity lookup
*
* lookuptypes=3 - opportunity
* lookupclass = opportunity (v1.2 deprecated method)
*/
crmForm.all.achs_parentopportunity.outerHTML = 
    "<td id='achs_parentopportunity_d'><table class='lu' 
      cellpadding='0' cellspacing='0' width='100%' 
      style='table-layout:fixed;'>
     <tr><td><div class='lu'> </div></td>
       <td width='25' style='text-align: right;'>
         <img src='/_imgs/btn_off_lookup.gif' id='achs_parentopportunity'
           class='lu' tabindex='1000' lookuptypes='3'
           lookuptypeIcons='/_imgs/ico_16_3.gif'
           lookupclass='opportunity' lookupbrowse='0' lookupstyle='single'
           defaulttype='0' req='2'></td>
</tr></table></td>";
crmForm.all.achs_parentopportunity.parentNode.previousSibling.innerHTML= 
    "<DIV class=lu><SPAN class=lui onclick=openlui() 
       otype=\'3\' oid=\'" + sassocfullmemvalue + "' data=\'\'>
     <IMG class=lui src=\'/_imgs/ico_16_3.gif\'>" +
     sav_achs_assocfullmemidname + "<B style=\'PADDING-LEFT: 4px\'>
     </B></SPAN></DIV>";

Basically what we have here is a replacement of the text control displaying the GUID with:

a selectable clickable image (the lookup “button” with magnifying glass) , lookuptype=3 (the object type id of an opportunity), lookupclass=opportunity. Lookup class appears to refer to objects in the \server\ApplicationFiles directory in the Microsoft CRM directory tree, rather than to specific CRM entities (more on this below), preceded (via .ParentNode.previousSibling) by

a custom control displaying an opportunity icon (per the lookuptypeIcons clause above), followed by the opportunity name we looked up using the getlookupname() function.

And here’s how it looks now:

Just like an opportunity lookup box. If we click on the magnifying glass image button, we get an opportunity lookup view:

And if we choose the first opportunity the lookup box then looks like this:

So, in effect, we have produced a lookup box allowing the setup or relationships between the two opportunities. If we click on the hyperlink in our Javascript-created lookup box, it opens the form for the appropriate opportunity we have designated as the parent opportunity of this one.

We have more work to do. When the opportunity form is loaded for an opportunity which already has the Associated Parent Opportunity field set, we need to include the hyperlink in the lookup box which will allow us to link back to the associated opportunity. To do this we need to find the opportunityid, a GUID, and find out the string value of the Topic of the Associated Parent Opportunity.

Retrieving CRM data from client JavaScript

The opportunityid GUID is a gimme. It’s loaded into the Associated Parent Opportunity text field when we load the form, before our OnLoad event code morphs it into a lookup. If we load the form with the OnLoad event disabled, we see this:

Getting the topic is a bit more problematic. We need to initiate a lookup from Javascript. I’ve seen this implemented via an *.aspx page written in C# (though it could be any .NET-compatible language, of course) which accepts the GUID and object type as QueryString parameters, and returns XML in the page’s Response object, which Javascript can parse. But you can also do this directly from Javascript by packaging and sending a SOAP message to the CRM web service directly in Javascript. Here’s a Javascript function to do just that:

function GetAttributeValueFromID(sEntityName, sGUID, sAttributeName)
{
/*
* sEntityName: the name of the CRM entity (account. opportunity, contact, etc.)
* whose attribute value wish to look up
* sGUID: string representation of the unique identifier (accountid, opportunityid,
* contactid, etc. of the specific object whose attrbuite value we wish to look up
* sAttributeName - the schema name of the attribute whose value we wish returned
*/
var sXml = "";
var oXmlHttp = new ActiveXObject("Msxml2.XMLHTTP");
var serverurl = "http://<yourservername>";
//set up the SOAP message
sXml += "<?xml version=\"1.0\" encoding=\"utf-8\" ?>";
sXml += "<soap:Envelope xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\""
sXml += " xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\""
sXml += " xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">";
sXml += "<soap:Body>";
sXml += "<entityName xmlns=\"http://schemas.microsoft.com/crm/2006/WebServices\">" +
sEntityName + "</entityName>";
sXml += "<id xmlns=\"http://schemas.microsoft.com/crm/2006/WebServices\">" +
sGUID + "</id>";
sXml += "<columnSet xmlns=\"http://schemas.microsoft.com/crm/2006/WebServices\""
sXml += " xmlns:q=\"http://schemas.microsoft.com/crm/2006/Query\""
sXml += " xsi:type=\"q:ColumnSet\"><q:Attributes><q:Attribute>" +
sAttributeName + "</q:Attribute></q:Attributes></columnSet>";
sXml += "</soap:Body>";
sXml += "</soap:Envelope>";
// send the message to the CRM Web service
oXmlHttp.Open("POST", serverurl +
"/MsCrmServices/2006/CrmService.asmx",false);
oXmlHttp.setRequestHeader("SOAPAction",
"http://schemas.microsoft.com/crm/2006/WebServices/Retrieve");
oXmlHttp.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
oXmlHttp.setRequestHeader("Content-Length", sXml.length);
oXmlHttp.send(sXml);
// retrieve response and find attribute value
var result = oXmlHttp.responseXML.selectSingleNode("//" + sAttributeName);
if (result == null)
{
return "*** Error***";
}
else
return result.text;
}

Here’s how it is used to find the opportunity topic corresponding
to the GUID/string value in the achs_associatedopportunity field:

/* Here we set up a lookup from the GUID of the associated opportunity */

var sassocopportunityvalue = crmForm.all.achs_associatedopportunity.DataValue;
var sav_achs_assocopportunityname = "";

/* if the related GUID field is not null, lookup the opportunity name/topic for the related
* opportunity*/

if (crmForm.all.achs_associatedopportunity.DataValue != null)
{
sav_achs_assocopportunityname = GetAttributeValueFromID(
"opportunity",crmForm.all.achs_associatedopportunity.DataValue, "topic");
}

Note that I have bolded the variable names, so that you can pick them up below.

After we have determined the name (topic) of the associated opportunity, we can then substitute the text control with our adjusted lookup field:

/* replace the existing text control with an opportunity lookup
*
* lookuptypes=3 - opportunity
* lookupclass = opportunity
*/
crmForm.all.achs_associatedopportunity.outerHTML = 
  "<td id='achs_associatedopportunity_d'><table class='lu' 
     cellpadding='0' cellspacing='0' width='100%' style='table-layout:fixed;'>
    <tr><td>
      <div class='lu'> </div></td><td width='25' 
        style='text-align: right;'>
       <img src='/_imgs/btn_off_lookup.gif' id='achs_associatedopportunity'
            class='lu' tabindex='1000' lookuptypes='3'
            lookuptypeIcons='/_imgs/ico_16_3.gif' lookupclass='opportunity'
            lookupbrowse='0' lookupstyle='single' defaulttype='0' req='2'>
   </td></tr></table></td>";
crmForm.all.achs_associatedopportunity.parentNode.previousSibling.innerHTML= 
   "<DIV class=lu><SPAN class=lui onclick=openlui() otype=\'3\' 
     oid=\'" + sassocopportunityvalue + "' data=\'\'>
     <IMG class=lui src=\'/_imgs/ico_16_3.gif\'>" +
     sav_achs_assocopportunityname + 
     "<B style=\'PADDING-LEFT: 4px\'></B>
  </SPAN></DIV>";

And here is the result:

And that’s basically it.

The other side of the many-to-many relationship

You might wish to have a link from the parent opportunity to its related child opportunities. This presents a different challenge both in data lookups and presentation.

There’s more than one way to do it, but what I ended up doing was to create an *.aspx page, accepting the GUID of the parent opportunity in the QueryString. Then I looked up all the opportunities with that GUID’s string value in the achs_associatedopportunity field, using a CRM Web Service RetrieveMultiple call (though you could just as easily use database operations to look up a filtered view).

I return them as hyperlinks to URL addressable CRM forms in a datagrid on the aspx page. My hyperlink ends up something like this:

<a href="javascript:;" 
    onClick=\"window.open('http://<yourservername>/sfa/opps/edit.aspx?
         id={0a7781ff-43fb-da11-a8be-001320d5b432}',
         '_blank','toolbar=no,status=no,menubar=no,scrollbars=yes,resizable=yes');
         return false">ECorp 1 - 4 Yrs-Test Account</a>

This hyperlink loads an opportunity form for the parent opportunity
(with the opportunityid defined by the hyperlinks id parameter) in a new window without toolbar, status bar, or menu bar, i.e. the same way CRM would load it.

I include this *.aspx page as an IFrame on the opportunity form, properties similar to the following:

Note that you must check the “Pass record object-type code and unique identifier as parameters” check box to have those values appear in the QueryString of your *.aspx page, and uncheck the “Restrict cross-frame scripting” or the hyperlinks inside the IFramed page will not work.

Credit where due

I must credit Jeffry van de Vuurst from Microsoft.public.crm.developer for the info on looking up CRM via SOAP calls from JavaScript, and Michael Hohne from the same place for the info on setting up my IFrame hyperlinks.

I cannot claim credit for devising this method. I was assigned to work with code, including this methodology, provided by consultants who refused to document it, despite a number of requests. I have no idea whether they invented it or got it from still another source. I would not wish to promote developers, no matter how inventive or resourceful, who regard documentation as an optional extra.

The IFrame was my idea, FWIW.