Tuesday, October 27, 2009

MSCRM 4.0 : Convert a Lookup Field to Picklist

If you have any experience with MSCRM, you should be very familiar with CRM lookup as it’s the primary UI control to help you create and manage relationship between two entities that have a 1:N relation.

Overall, CRM lookup field is user friendly and also very informative. But the problem is, it’s not a very productive UI control, as it requires user to go through several mouse clicks to get things done. Today, I am trying to use CRM native lookup and present it as a picklist to help your CRM user become more productive. Give an example, CRM account form has a "Territory" lookup field, user would need to do at least 3 mouse clicks in order to choose a territory record, also she has to be patient enough to wait for the popup lookup window to be loaded.

CrmLookupCrmLookupPopupWith the script that I am presenting today, you can convert the lookup into a picklist, which will only requires 2 clicks in most cases. The following is the screen shot after such conversion. CrmPicklistHere is the code:
ConvertLookupToPicklist = function(lookupControl, lookupEntityName, lookupEntityPrimaryAttribute) {
    /**
     * CRM Lookup to Picklist Converter 1.0
     *
     * @author Daniel Cai
     * @website http://danielcai.blogspot.com/
     * @copyright Daniel Cai
     * @license Microsoft Public License (Ms-PL), http://www.opensource.org/licenses/ms-pl.html
     *
     * This release is provided "AS IS" and contains no warranty or whatsoever.
     *
     * Date: Oct 27 2009
     */

    var lookupEntityPrimaryKeyField = lookupEntityName + "id";
    
    var RetrieveLookupValues = function() {
        var fetchXml = 
"<fetch mapping='logical'>" + 
   "<entity name='" + lookupEntityName + "'>" + 
      "<attribute name='" + lookupEntityPrimaryKeyField + "' />" + 
      "<attribute name='" + lookupEntityPrimaryAttribute + "' />" + 
      "<order attribute='" + lookupEntityPrimaryAttribute + "' />" + 
   "</entity>" + 
"</fetch>";

        var lookupValues = [];
            
        var fetchResult = CrmServiceToolkit.Fetch(fetchXml);
        if (fetchResult !== null) {
            for( i = 0; i < fetchResult.length; i++) {
                var item = new Object();
                item[lookupEntityPrimaryKeyField] = fetchResult[i].getValue(lookupEntityPrimaryKeyField);
                item[lookupEntityPrimaryAttribute] = fetchResult[i].getValue(lookupEntityPrimaryAttribute);
                 
                lookupValues[i] = item;
            }
        }
        return lookupValues;
    };
    
    var PopulatePicklist = function(picklistControl, lookupValues) {
        picklistControl.options.length = 0;
        
        // Add blank option item
        var option = document.createElement("option");
        option.value = "";
        option.innerText = "";        
        picklistControl.appendChild(option);        
        
        // Add all options from code table (lookup entity)
        if (typeof lookupValues !== "undefined") {
            for (var i = 0 ; i < lookupValues.length ; i++) {
                option = document.createElement("option");
                option.value = lookupValues[i][lookupEntityPrimaryKeyField];
                option.innerText = lookupValues[i][lookupEntityPrimaryAttribute];
                
                picklistControl.appendChild(option);
            }
        }
    };    
 
    (function Convert() {    
        var picklistControl = document.createElement("select");
        picklistControl.id = lookupControl.id;
        picklistControl.name = lookupControl.name;
        picklistControl.tabIndex = crmForm.all[lookupControl.id + '_ledit'].tabIndex;        picklistControl.req = lookupControl.req;
        picklistControl.Disabled = lookupControl.Disabled;
        picklistControl.className = "ms-crm-selectBox";

        var lookupValues = RetrieveLookupValues();
        PopulatePicklist(picklistControl, lookupValues);

        if (lookupControl.DataValue !== null) {
            picklistControl.DataValue = lookupControl.DataValue[0].id;
        }
        
        var lookupControlCell = document.getElementById(lookupControl.id + "_d");
        lookupControlCell.removeChild(lookupControlCell.childNodes[0]);
        lookupControlCell.appendChild(picklistControl);
        return picklistControl;
    })();
};
To use the converter, you simply call it with 3 parameters, the CRM lookup field, lookup entity's name, and lookup entity's primary attribute name, which by default is usually "name" for native entities, or "new_name" for custom entities (assuming that "new" is your customization prefix) unless you have specified a different one. For instance, if you are working with account form as mentioned above, you may want to convert two lookup fields, "Territory" (Details tab) and "Preferred User" (Administration tab), to picklist. You can simply copy the following code to do the job.
ConvertLookupToPicklist(crmForm.all.territoryid, "territory", "name");
ConvertLookupToPicklist(crmForm.all.preferredsystemuserid, "systemuser", "fullname");
A few notes about the script:
  • I am again using my CRM Web Service Helper to facilitate CRM FetchXML query. The CRM Web Service Helper was designed as a reusable JavaScript toolkit which tries to make it easy to talk to CRM Web Services in your CRM form script.
  • This script gives you a lot more flexibility than using native lookup field. You can use your own query to select what to be displayed in the picklist which is not supported by CRM native lookup out of box. I have previously provided a solution for CRM4 Filtered Lookup, if you need to stick to lookup but you also want filtering on the records being displayed.
  • Comparing to CRM native picklist, this picklist is sort of a dynamic list, as user can add / edit / delete any record in the lookup entity, which will then affect the list being displayed in the picklist. This actually makes the lookup entity hehaves like a code table in the 1:N relationship. That is something we are missing on CRM platform at this time.
  • Due to the nature of picklist, the lookup entity should not have too many records, so the user doesn't have to go through a long list to pick what she wants. 
Hope you find this script useful.

[UPDATE - Jan 26, 2010] I have updated the script so it uses the latest CRM Web Service Toolkit.
[UPDATE - Mar 23, 2010] I didn't like my previous code very much (it actually stank badly, to be honest), so I did another update of refactoring, hope you like it better.

18 comments:

  1. Very nice, one question : does it use the sync or asynch versions of the fetch function. if the sync, is there a way to use the async version?

    ReplyDelete
  2. @itayR, it's sync version. But it should be pretty easy to change to async version, as the toolkit itself supports async service calls.

    ReplyDelete
  3. This is great code that works well. The only problem I have found is that this does not hide the “Form Assistant” lookup on the left side of the main form. Is there some way to hide/disable the Matching Form Assistant?

    ReplyDelete
  4. @Filo, CRM form assistance can be turned off on the form by unchecking "Enable form assistance" option, and you could also programmatically turn it off using script such as this one:

    document.getElementById('RelatedInformationPane').style.display='none';

    ReplyDelete
  5. Is there a CRM 2011 version?

    ReplyDelete
    Replies
    1. Sorry, I haven't got a chance to port the code to CRM2011.

      Delete
  6. I had to use this on CRM 2011. It seems that whenever you strip a lookup from a form in CRM 2011, this breaks the save event somehow. I had to create a text field to serve as the place holder for the picklist created by this function. I also replaced the CRM 4.0 object model, upgrading it with the 2011 one.

    For those interested, here is the code:

    //will not work in CRM 2011 with a lookup. Will work with a text field.
    ConvertFieldToPicklist = function (lookupControl, lookupAttribute, lookupEntityName, lookupEntityPrimaryAttribute, fieldToReplace) {

    var lookupEntityPrimaryKeyField = lookupEntityName + "id";

    var RetrieveLookupValues = function () {
    var fetchXml =
    "" +
    "" +
    "" +
    "" +
    "" +
    "" +
    "";

    var lookupValues = [];

    var fetchResult = CrmServiceToolkit.Fetch(fetchXml);
    if (fetchResult !== null) {
    for (i = 0; i < fetchResult.length; i++) {
    var item = new Object();
    item[lookupEntityPrimaryKeyField] = fetchResult[i].getValue(lookupEntityPrimaryKeyField);
    item[lookupEntityPrimaryAttribute] = fetchResult[i].getValue(lookupEntityPrimaryAttribute);

    lookupValues[i] = item;
    }
    }
    return lookupValues;
    };

    var PopulatePicklist = function (picklistControl, lookupValues) {
    picklistControl.options.length = 0;

    // Add blank option item
    var option = document.createElement("option");
    option.value = "";
    option.innerText = "";
    picklistControl.appendChild(option);

    // Add all options from code table (lookup entity)
    if (typeof lookupValues !== "undefined") {
    for (var i = 0; i < lookupValues.length; i++) {
    option = document.createElement("option");
    option.value = lookupValues[i][lookupEntityPrimaryKeyField];
    option.innerText = lookupValues[i][lookupEntityPrimaryAttribute];

    picklistControl.appendChild(option);
    }
    }
    };

    (function Convert() {
    var picklistControl = document.createElement("select");
    var replaceFieldControl = document.getElementById(fieldToReplace);
    picklistControl.id = replaceFieldControl.id;
    picklistControl.name = replaceFieldControl.name;
    picklistControl.tabIndex = replaceFieldControl.tabIndex;
    picklistControl.req = replaceFieldControl.req;
    picklistControl.Disabled = replaceFieldControl.Disabled;
    picklistControl.className = "ms-crm-selectBox";
    picklistControl.onchange = function () { UpdateLookupWhenPicklistChange(picklistControl, lookupEntityName); };

    var lookupValues = RetrieveLookupValues();
    PopulatePicklist(picklistControl, lookupValues);

    if (lookupAttribute.getValue() !== null) {
    picklistControl.DataValue = lookupAttribute.getValue()[0].id;
    }

    var lookupControlCell = document.getElementById(fieldToReplace + "_d");
    lookupControlCell.removeChild(lookupControlCell.childNodes[0]);
    lookupControlCell.appendChild(picklistControl);
    return picklistControl;
    })();
    }

    ReplyDelete
    Replies
    1. Thanks Dave for posting your update here.

      Delete
    2. This comment has been removed by the author.

      Delete
    3. I did like this

      ConvertFieldToPicklist(crmForm.all.alv_sno, Xrm.Page.data.entity.attributes.get('alv_sno'), "alv_taxtable", "alv_name", 'alv_sno');

      All loaded, and a default value established
      But I have a Save button on the tape were not active. What is the problem might be?

      sorry for my english) I'm from Russia)))

      Delete
  7. This comment has been removed by the author.

    ReplyDelete
  8. I solved the problem this :)
    Here's my working code for MS CRM 2011

    ConvertFieldToPicklist = function (lookupControl, lookupAttribute, lookupEntityName, lookupEntityPrimaryAttribute, fieldToReplace) {

    var lookupEntityPrimaryKeyField = lookupEntityName + "id";

    var RetrieveLookupValues = function () {
    var fetchXml =
    "" +
    "" +
    "" +
    "" +
    "" +
    "" +
    "";

    var lookupValues = [];

    var fetchResult = CrmServiceToolkit.Fetch(fetchXml);
    if (fetchResult !== null) {
    for (i = 0; i < fetchResult.length; i++) {
    var item = new Object();
    item[lookupEntityPrimaryKeyField] = fetchResult[i].getValue(lookupEntityPrimaryKeyField);
    item[lookupEntityPrimaryAttribute] = fetchResult[i].getValue(lookupEntityPrimaryAttribute);

    lookupValues[i] = item;
    }
    }
    return lookupValues;
    };

    var PopulatePicklist = function (picklistControl, lookupValues) {
    picklistControl.options.length = 0;

    // Add blank option item
    var option = document.createElement("option");
    option.value = "";
    option.innerText = "";
    picklistControl.appendChild(option);

    // Add all options from code table (lookup entity)
    if (typeof lookupValues !== "undefined") {
    for (var i = 0; i < lookupValues.length; i++) {
    option = document.createElement("option");
    option.value = lookupValues[i][lookupEntityPrimaryKeyField];
    option.innerText = lookupValues[i][lookupEntityPrimaryAttribute];

    picklistControl.appendChild(option);
    }
    }
    };

    BuildLookupFieldValue = function(id, typename, name) {
    if (id === null || id === "") {
    return null;
    }

    var dataValues = [];
    var dataValue = {};
    dataValue.id = id;
    dataValue.typename = typename;
    dataValue.name = name;
    dataValues[0] = dataValue;

    return dataValues;
    }

    UpdateLookupWhenPicklistChange = function(picklistControl, lookupControl) {
    var selectedOption = picklistControl.options[picklistControl.selectedIndex];
    if (selectedOption.value === "")
    lookupControl.DataValue = null;
    else
    lookupControl.DataValue = BuildLookupFieldValue(selectedOption.value, lookupEntityName, selectedOption.text);

    Xrm.Page.getAttribute(fieldToReplace).fireOnChange();
    //lookupControl.FireOnChange();
    };

    (function Convert() {
    var picklistControl = document.createElement("select");
    var replaceFieldControl = document.getElementById(fieldToReplace);
    picklistControl.id = replaceFieldControl.id;
    picklistControl.name = replaceFieldControl.name;
    picklistControl.tabIndex = replaceFieldControl.tabIndex;
    picklistControl.req = replaceFieldControl.req;
    picklistControl.Disabled = replaceFieldControl.Disabled;
    picklistControl.className = "ms-crm-selectBox";
    picklistControl.onchange = function () { UpdateLookupWhenPicklistChange(picklistControl, lookupControl); };

    var lookupValues = RetrieveLookupValues();
    PopulatePicklist(picklistControl, lookupValues);

    if (lookupAttribute.getValue() !== null) {
    picklistControl.DataValue = lookupAttribute.getValue()[0].id;
    }

    var lookupControlCell = document.getElementById(fieldToReplace + "_d");
    lookupControlCell.childNodes[0].style.display = 'none';
    //lookupControlCell.removeChild(lookupControlCell.childNodes[0]);
    lookupControlCell.appendChild(picklistControl);
    return picklistControl;
    })();
    }

    ReplyDelete
  9. Hi Daniel,
    I have used the above modified code by "kh_heckfy" in CRM 2011.
    I issue I had is while I try to save the record,it gets saved with the selected value perfectly.But when I try to open that saved record, It again sets the lookup field to blank.
    Can you tell me how to set the previous value in that lookup field which is converted to picklist?

    ReplyDelete
  10. Has anybody figured out a way to get this to work with UR12 compatibility?

    ReplyDelete