Microsoft CRM Custom Lookup: a solution

<< Scalability: how eBay handles it | Home | Microsoft CRM as a "building block" >>

Microsoft CRM Custom Lookup: a solution

I've written in the past about what must be changed (or improved) in Microsoft CRM and one of the things I hate about it is the lookup feature: wonderful to have the ability to create a lookup field with just setting a relation, but why there's not an easy way to create a custom lookup (for example with filters)? It's a basic feature on every "serious" system.

Today, browsing the CRM Sandbox page, I've found this wonderful post: Custom Lookup Dialog for Microsoft Dynamics CRM 3 by Curt Spanburgh. The solution that Curt has given uses fetchXML queries:

// Use additionalparams to manipulate the URL. Since additionalparams append anything to the end of the URL query, you can use 2 lines of code to trick it to do what you want.
/* Filter Lookup for Phase that has the same tractid */
crmForm.all.new_phaseid.lookupbrowse = 1; // This turn on filter
crmForm.all.new_phaseid.additionalparams = "fetchXml=<fetch mapping='logical'><entity name='new_phase'><all-attributes/><filter type='and'><condition attribute='new_tractid' operator='eq' value=' " + crmForm.all.new_tractid.DataValue[0].id+"' /></filter></entity></fetch> ";

There's an interesting post by Ronald Lemmen that explains how to use fecthXML queries: Using Advanced Find for FetchXml.

As Ronald explains, "you can just open the Advanced find page and build the query as you like. Then run the query to see if it returns the data as you wish it should. If you are satisfied with the results and you want to know what query was sent into the CRM Framework, then press F11 to get the address bar and enter this script and press enter.

javascript:alert(resultRender.FetchXml.value);

If you get a warning about leaving the page, just press 'ok' and then the query which is sent to the framework opens up in a popup. Unfortunately you cannot select the text to copy and paste. But with Windows XP and Windows Server 2003 you can copy all text on the popup by clicking somewhere on the popup (not on the button "OK" ofcourse) and pressing ctrl+c. Now in notepad you can paste the text of the FetchXML.".

These two posts makes the trick: set your filter query with Advanced Find in CRM, past the fetchXML text in Notepad and finally replace the additionalparams in the script above with your text just copied. You're ready to use your custom lookup dialog on your CRM code.

Wonderful!

Print | posted on Friday, December 29, 2006 2:24 PM

Comments on this post

# re: Microsoft CRM Custom Lookup: a solution

This is another example:

I was looking for a way to limit the "Primary Contact" lookup on the Account Form to only those contacts who are associated with the account. This is a solution:



var CRM_FORM_TYPE_CREATE = 1;
var CRM_FORM_TYPE_UPDATE = 2;

switch (crmForm.FormType)
{
case CRM_FORM_TYPE_CREATE:
// do nothing
break;

;
case CRM_FORM_TYPE_UPDATE:
var accountID = crmForm.ObjectId;

crmForm.all.primarycontactid.lookupbrowse = 1;
crmForm.all.primarycontactid.additionalparams = "fetchXml=<fetch mapping='logical'> <entity name='contact'> <all-attributes/><filter type='and'><condition attribute='parentcustomerid' operator='eq' value='" + accountID + "'/><condition attribute='statecode' operator='eq' value='0'/></filter></entity></fetch>";

crmForm.all.primarycontactid.additionalparams += "&selObjects=2&findValue=0";

break;
}

Create Account forms are left the way they were, but Update Account forms will filter the lookup results.

Left by Stefano Demiliani on Jun 19, 2007 9:37 AM

# re: Microsoft CRM Custom Lookup: a solution

Thanks for the example
We had a similar situation where we wanted a values in lookup field to appear depending on the value selected in the another lookup field.
Using fetchxml in the client side scripts was really a great idea.

Left by Nishant Rana on Jul 13, 2007 4:26 PM

# re: Microsoft CRM Custom Lookup: a solution

It's too bad you can't create a relationship to a saved view. In the Relationship:New editor, all that shows up for the primary entity is the system entities. It should also allow you to make the primary entity a reference to a saved view. Then, you could send the *reference* the paremeter(s) using OnChange or OnLoad and it automatically updates the view first, then applies the relationship. Man, that would be SLICK.

Left by dave on Aug 09, 2007 8:37 PM

# re: Microsoft CRM Custom Lookup: a solution

I am able to make use of this technique under a simple scenario, however, I am having trouble with a more complex one.

Assume two custom entities called Objective and Activity. These two entities conceptually act like pick lists, but they have been implemented as lookups so that they can be managed in real-time by folks with the right permissions.

Add a third entity called ActivitySetup that contains one pick list (that mirrors the Account's customertypecode pick list), and two entity lookups - one to Objective, one to Activity.

The ActivitySetup entity acts as a logical grouping of acceptable combinations of customer type, objective, and activity.

Enter the fourth entity: SalesCall which represents a visit to an account.
The goal here is to use the Sales Call's selected Account's customer type code to pre-filter the objective lookup so that only objectives that are valid for the account type are an option:

In the account lookup's OnChange event:
/* determine selected account’s customertypecode, assign result to customerTypeCode variable */

crmForm.all.new_objectiveid.lookupbrowse = 1; // turn on filter

crmForm.all.new_objectiveid.additionalparams = "fetchXml=<fetch mapping='logical' distinct='true'><entity name='new_scobjectivelist'><all-attributes /><order attribute='new_name' descending='false'/><filter type='and'><condition attribute='statecode' operator='eq' value='0'/></filter><link-entity name='new_scactivitysetup' from='new_objectiveid' to='new_scobjectivelistid' alias='aa'><filter type='and'><condition attribute='new_customertypecode' operator='eq' value='" + customerTypeCode + "'/></filter></link-entity></entity></fetch>";

crmForm.all.new_objectiveid.additionalparams += "&selObjects=2&findValue=0";

When creating a new sales call, I default the customerTypeCode to zero so an attempt to assign an objective before selecting an account produces a lookup dialog with no matching records. This works as expected.

However, once an account is selected and the fetchXml changes to have a non-zero customerTypeCode, the lookup dialog displays all objective records - not a filtered list.

I realize this whole endeavor is unsupported, but I am curious to find out if anyone else has run into this and/or figured out a way around it.

Left by TimDarius on Aug 22, 2007 10:26 PM

# re: Microsoft CRM Custom Lookup: a solution

Please ignore my last post. I figured out my issue.

Left by TimDarius on Aug 23, 2007 5:55 PM

# re: Microsoft CRM Custom Lookup: a solution

A perfect Solution - and very, very usefull for the servicecall.

But has anyone a idea how to make it work with CTRM 4.0???

Left by Gregor on Feb 04, 2008 4:08 PM

# re: Microsoft CRM Custom Lookup: a solution

Have a look here:
http://forums.microsoft.com/Dynamics/ShowPost.aspx?PostID=2827530&SiteID=27

Left by Kris on Feb 13, 2008 2:43 PM

Your comment:

Title:

Name:

Email: (never displayed)

(will show your gravatar)

Website:

Comment: Allowed tags: blockquote, a, strong, em, p, u, strike, super, sub, code

Please add 2 and 8 and type the answer here:

STEFANO DEMILIANI