Monday, November 28, 2011

Dynamics crm 2011 n to n lookup

N:N lookup in Dynamics CRM 2011.

For to 4.0 version: http://dynamicslollipops.blogspot.com/2011/07/mscrm-40-nn-lookup-field.html

Prerequest customization:
1) 1:N relation, and lookup placed on the form.
2) N:N relationship for holding the values.

Walkthrough:
1) Create new javascript webresource with the following code:

var CRM_FORM_TYPE_CREATE = 1;

RetreiveAssociatedEntities = function(relationshipSchemaName, entity1SchemaName, entity1KeyValue, retreiveAttribute) {
    var fetchXml = "<fetch mapping='logical'>"
    + "  <entity name='" + relationshipSchemaName + "'>"
    + "    <all-attributes />"
    + "    <filter>"
    + "      <condition attribute='" + entity1SchemaName + "id' operator='eq' value ='" + entity1KeyValue + "' />"
    + "    </filter>"
    + "  </entity>"
    + "</fetch>";

    var fetchResults = Fetch(fetchXml);

    var nodeList = fetchResults.selectNodes("resultset/result");

    var returnList = new Array();
    if (nodeList == null || nodeList.length == 0) {
        return returnList;
    } else {
        for (i = 0; i < nodeList.length; i++) {
            var idValue = nodeList[i].selectSingleNode('./' + retreiveAttribute).nodeTypedValue;
            returnList[i] = idValue;
        }
        return returnList;
    }
}
MischiefMayhemSOAP = function(serviceUrl, xmlSoapBody, soapActionHeader, suppressError) {
    var xmlReq = "<?xml version='1.0' encoding='utf-8'?>"
    + "<soap:Envelope xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'"
    + "  xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'"
    + "  xmlns:xsd='http://www.w3.org/2001/XMLSchema'>"
    + GenerateAuthenticationHeader()
    + "  <soap:Body>"
    + xmlSoapBody
    + "  </soap:Body>"
    + "</soap:Envelope>";

    var httpObj = new ActiveXObject("Msxml2.XMLHTTP");

    httpObj.open("POST", serviceUrl, false);

    httpObj.setRequestHeader("SOAPAction", soapActionHeader);
    httpObj.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
    httpObj.setRequestHeader("Content-Length", xmlReq.length);

    httpObj.send(xmlReq);

    var resultXml = httpObj.responseXML;

    var errorCount = resultXml.selectNodes("//error").length;
    if (errorCount != 0) {
        var msg = resultXml.selectSingleNode("//description").nodeTypedValue;

        if (typeof (suppressError) == "undefined" || suppressError == null) {
            alert("The following error was encountered: " + msg);
        }

        return null;
    } else {
        return resultXml;
    }
}

Fetch = function(fetchXml) {
    var xmlSoapBody = "<Fetch xmlns='http://schemas.microsoft.com/crm/2007/WebServices'>"
    + "  <fetchXml>"
    + FetchEncode(fetchXml)
    + "  </fetchXml>"
    + "</Fetch>";

    var fetchResponse = MischiefMayhemSOAP("/MSCRMServices/2007/CrmService.asmx", xmlSoapBody, "http://schemas.microsoft.com/crm/2007/WebServices/Fetch");

    if (fetchResponse != null) {
        var fetchResults = new ActiveXObject("Msxml2.DOMDocument");

        fetchResults.async = false;
        fetchResults.resolveExternals = false;
        fetchResults.loadXML(fetchResponse.text);

        return fetchResults;
    } else {
        return null;
    }
}
FetchEncode = function(strInput) //_HtmlEncode
{
    var c;
    var HtmlEncode = '';

    if (strInput == null) {
        return null;
    }
    if (strInput == '') {
        return '';
    }

    for (var cnt = 0; cnt < strInput.length; cnt++) {
        c = strInput.charCodeAt(cnt);

        if (((c > 96) && (c < 123)) ||
  ((c > 64) && (c < 91)) ||
  (c == 32) ||
  ((c > 47) && (c < 58)) ||
  (c == 46) ||
  (c == 44) ||
  (c == 45) ||
  (c == 95)) {
            HtmlEncode = HtmlEncode + String.fromCharCode(c);
        }
        else {
            HtmlEncode = HtmlEncode + '&#' + c + ';';
        }
    }

    return HtmlEncode;
}

AssociateEntities = function(moniker1name, moniker1id, moniker2name, moniker2id, RelationshipName) {
    var authenticationHeader = GenerateAuthenticationHeader();
    // Prepare the SOAP message.
    var xml = "<?xml version='1.0' encoding='utf-8'?>";
    xml += "<soap:Envelope xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance\' xmlns:xsd='http://www.w3.org/2001/XMLSchema\'>";
    xml += authenticationHeader;
    xml += "<soap:Body><Execute xmlns='http://schemas.microsoft.com/crm/2007/WebServices'><Request xsi:type='AssociateEntitiesRequest'>";
    xml += "<Moniker1><Id xmlns='http://schemas.microsoft.com/crm/2006/CoreTypes'>" + moniker1id + "</Id>";
    xml += "<Name xmlns='http://schemas.microsoft.com/crm/2006/CoreTypes'>" + moniker1name + "</Name></Moniker1>";
    xml += "<Moniker2><Id xmlns='http://schemas.microsoft.com/crm/2006/CoreTypes'>" + moniker2id + "</Id>";
    xml += "<Name xmlns='http://schemas.microsoft.com/crm/2006/CoreTypes'>" + moniker2name + "</Name></Moniker2>";
    xml += "<RelationshipName>" + RelationshipName + "</RelationshipName>";
    xml += "</Request></Execute></soap:Body></soap:Envelope>";

    // Prepare the xmlHttpObject and send the request.
    var xHReq = new ActiveXObject("Msxml2.XMLHTTP");
    xHReq.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
    xHReq.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/crm/2007/WebServices/Execute");
    xHReq.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
    xHReq.setRequestHeader("Content-Length", xml.length);
    xHReq.send(xml);

    // Capture the result.
    var resultXml = xHReq.responseXML;

    // Check for errors.
    var errorCount = resultXml.selectNodes('//error').length;

    if (errorCount != 0) {
        var msg = resultXml.selectSingleNode('//description').nodeTypedValue;
        alert(msg);
    }
}

DisassociateEntities = function(moniker1name, moniker1id, moniker2name, moniker2id, RelationshipName) {
    var authenticationHeader = GenerateAuthenticationHeader();
    // Prepare the SOAP message.
    var xml = "<?xml version='1.0' encoding='utf-8'?>";
    xml += "<soap:Envelope xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance\' xmlns:xsd='http://www.w3.org/2001/XMLSchema\'>";
    xml += authenticationHeader;
    xml += "<soap:Body><Execute xmlns='http://schemas.microsoft.com/crm/2007/WebServices'><Request xsi:type='DisassociateEntitiesRequest'>";
    xml += "<Moniker1><Id xmlns='http://schemas.microsoft.com/crm/2006/CoreTypes'>" + moniker1id + "</Id>";
    xml += "<Name xmlns='http://schemas.microsoft.com/crm/2006/CoreTypes'>" + moniker1name + "</Name></Moniker1>";
    xml += "<Moniker2><Id xmlns='http://schemas.microsoft.com/crm/2006/CoreTypes'>" + moniker2id + "</Id>";
    xml += "<Name xmlns='http://schemas.microsoft.com/crm/2006/CoreTypes'>" + moniker2name + "</Name></Moniker2>";
    xml += "<RelationshipName>" + RelationshipName + "</RelationshipName>";
    xml += "</Request></Execute></soap:Body></soap:Envelope>";

    // Prepare the xmlHttpObject and send the request.
    var xHReq = new ActiveXObject("Msxml2.XMLHTTP");
    xHReq.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
    xHReq.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/crm/2007/WebServices/Execute");
    xHReq.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
    xHReq.setRequestHeader("Content-Length", xml.length);
    xHReq.send(xml);

    // Capture the result.
    var resultXml = xHReq.responseXML;

    // Check for errors.
    var errorCount = resultXml.selectNodes('//error').length;

    if (errorCount != 0) {
        var msg = resultXml.selectSingleNode('//description').nodeTypedValue;
        alert(msg);
    }
}

//returns the item location in array if not found -1
GetIndexFromArray = function(item, recordArr) {
    for (var i = 0; i < recordArr.length; i++) {
        if (recordArr[i] != null && recordArr[i].id == item) {
            return i;
        }
    }
    return -1;
}

/*"new_new_entity1_new_entity2new","new_multynewid", "new_entity2", "new_name"*/
FillMultiLookup = function(relationshipSchemaName, lookupSchemaName, relatedEntitySchemaName, relatedEntityPrimaryAttributeSchemaName) {
    var relatedValues = RetreiveAssociatedEntities(relationshipSchemaName, Xrm.Page.data.entity.getEntityName(), Xrm.Page.data.entity.getId(), relatedEntitySchemaName + "id");
    var value = new Array();
    for (var i = 0; i < relatedValues.length; i++) {
        value[i] = new Object();
        value[i].id = relatedValues[i];
        value[i].name = RetreiveAssociatedEntities(relatedEntitySchemaName, relatedEntitySchemaName, relatedValues[i], relatedEntityPrimaryAttributeSchemaName)[0];
        value[i].typename = relatedEntitySchemaName;
    }
 crmForm.all[lookupSchemaName].DataValue = value;
}

UpdateN2N = function(nnId, relatedEntitySchemaName, relatedEntitySchemaId, lookupSchemaName) {
    var oldValues = RetreiveAssociatedEntities(nnId, Xrm.Page.data.entity.getEntityName(), Xrm.Page.data.entity.getId(), relatedEntitySchemaId);
 var value = crmForm.all[lookupSchemaName].DataValue;
    //if there's records in lookup
    if (value != null) {
        //go over all the related records and remove them if not in the new list (lookup)
        var temp = value;
        for (var i = 0; i < oldValues.length; i++) {
            //if not in the new list disassociate them
            var index = GetIndexFromArray(oldValues[i], temp);
            if (index == -1) {
                DisassociateEntities(Xrm.Page.data.entity.getEntityName(), Xrm.Page.data.entity.getId(), relatedEntitySchemaName, oldValues[i], nnId);
            }
            else { // if in the list remove them from the list
                temp[index] = null;
            }
        } //ends for
        //go over all the remaining records and associate them
        for (var i = 0; i < temp.length; i++) {
            if (temp[i] != null) {
                AssociateEntities(Xrm.Page.data.entity.getEntityName(), Xrm.Page.data.entity.getId(), relatedEntitySchemaName, temp[i].id, nnId);
            }
        }
    }
    else if (oldValues != null) {
        for (var i = 0; i < oldValues.length; i++) {
            DisassociateEntities(Xrm.Page.data.entity.getEntityName(), Xrm.Page.data.entity.getId(), relatedEntitySchemaName, oldValues[i], nnId);
        }
    }
}

FilterN2NLookup = function(filterByLookup, filterLookup, entityName, nnName, nnFrom, linkedEntityName, linkedAttributeName) {
    var filter = '';
    var values = filterByLookup.DataValue;
    if (values != null) {
        //filter = '<filters><filter entity="' + entityName + '"><condition attribute="' + attributeName + '" operator="in">';
        //for (var i = 0; i < values.length; i++) {
        //    filter += '<value uiname="' + values[i].name + '">' + values[i].id + '</value>';
        // }
        //filter += '</condition></filter></filters>';

        filter = '<link-entity entity="' + entityName + '" name="' + nnName + '" from="' + nnFrom + '" to="' + nnFrom + '" visible="false" intersect="true"><link-entity name="' + linkedEntityName + '" from="' + linkedAttributeName + '" to="' + linkedAttributeName + '"><filter type="and"><condition attribute="' + linkedAttributeName + '" operator="in">';
        for (var i = 0; i < values.length; i++) {
            filter += '<value uiname="' + values[i].name + '">' + values[i].id + '</value>';
        }
        filter += '</condition></filter></link-entity></link-entity>';

    }
    filterLookup.AddParam('filters', filter);
}

/*"new_new_entity1_new_entity2new","new_multynewid", "new_entity2", "new_name"*/
function ConvertN2N(relationshipSchemaName, lookupSchemaName, relatedEntitySchemaName, relatedEntityPrimaryAttributeSchemaName)
{
 document.getElementById(lookupSchemaName).setAttribute("lookupstyle", "multi");
 document.getElementById(lookupSchemaName).setAttribute("_lookupstyle", "multi");

 document.getElementById(lookupSchemaName).onchange = function(){UpdateN2N(relationshipSchemaName, relatedEntitySchemaName, relatedEntitySchemaName + "id", lookupSchemaName)};

 Xrm.Page.getAttribute(lookupSchemaName).setSubmitMode("never");

 if (crmForm.FormType != CRM_FORM_TYPE_CREATE) {
  FillMultiLookup(relationshipSchemaName, lookupSchemaName, relatedEntitySchemaName, relatedEntityPrimaryAttributeSchemaName);
 }
 else {
  Xrm.Page.getControl(lookupSchemaName).setDisabled(true);
 }
}

2) Add it to the form
3) Add to the onload 
   3.1) Function: ConvertN2N
   3.2) Parameters: N:N Relationship name, lookup attribute name, Related Entity Schema Name, Related Entity Primary Attribute Schema Name
(Example: "new_incident_new_problem", "new_problemid", "new_problem", "new_name")



That's it, everything else is inside the code. 
Plug and Play.

22 comments:

  1. very good post, But when I tried this code I'm getting the following errors similar to errors which were shown in the following post with screenshots.http://social.microsoft.com/Forums/is/crm/thread/6fbffd53-8df0-4799-89bb-4decb9a58129
    Please help me. plsssssssssssssssss.
    regards
    praveen

    ReplyDelete
  2. Hi, which one of the screenshots :)
    Can you pack the entites + web resources in solution and send to me ?

    I'll try to look at it soon..

    ReplyDelete
  3. This comment has been removed by a blog administrator.

    ReplyDelete
  4. I'm not sure it's a good idea to post your env. with the user and password.
    Next time it's better to email them or send the solution.

    I'll look at it later on.

    ReplyDelete
  5. I've fixed your code and updated mine.
    Hope it helps.

    ReplyDelete
  6. Thank you veryyyyyyy much for your help and spending your valuble time.It's working successfully

    ReplyDelete
  7. Hi, When I'm trying to create new record it's still throwing the error.But for existing records it's wrkng fine.Please help me

    ReplyDelete
  8. Sorry, but because n:n relationship needs the Guid of the record, which is only available after the records has been created. It's not supporting create form.

    ReplyDelete
  9. thanks for your valuble response

    ReplyDelete
  10. This is the most promissing solution I've found yet for the problem I'm trying to solve. I am trying to create a filtered lookup in the Account entity consisting of a set of geographical regions as the parent and countries contained in those regions as the child multi-select lookup. The regions can contain the countries only once, but the countries could be in more than one region (i.e. Aruba would be both in the Caribbean region and the North America region). Because the lists can get quite long, the lookup view is best for this function. Could you please be a bit more detailed in steps 1 and 2 above (I'm still earning my CRM merit badge)?

    ReplyDelete
    Replies
    1. Hi,
      Thank you and I'm sorry for the late response...
      About steps 1-2)
      It simple dynamics crm customization, you should go to your entity customization and had it 2 relationships to the other entity (I'm sorry but I could find a tutorial on the subject),
      1:N - It will craete you an attribute which you could place in your form (lookup field)
      N:N - Subgrid, you will see it in the left navigation.
      After you have these 2, you could start synchronize between these 2 realtionships.

      Delete
  11. Can u pls explaine with scrren shot these parametres
    N:N Relationship name,
    lookup attribute name,
    Related Entity Schema Name,
    Related Entity Primary Attribute Schema Name

    ReplyDelete
  12. Hi,

    Am getting this error.

    ---------------------------
    Message from webpage
    ---------------------------
    The following error was encountered: An exception System.FormatException was thrown while trying to convert input value 'null' to attribute 'new_incident_systemusermtom.incidentid'. Expected type of attribute value: System.Guid. Exception raised: Guid should contain 32 digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx).
    ---------------------------
    OK
    ---------------------------
    Any help would be greatly appreciated.

    ReplyDelete
  13. Hi Yairrose,

    Can you please help me fix this error.

    ReplyDelete
  14. Hi,

    While saving will the selected multiple values get saved and while retrieving will the control get ppopulated with multiple values. If yes then it is a life saver.

    ReplyDelete
  15. Hi Praveen,

    Is this working as required. I mean is the selected values getting saved and while retrieving whether the control is getting populated with multiple values.

    Thanks,
    Prem

    ReplyDelete
  16. I have a question about these lines:

    document.getElementById(lookupSchemaName).setAttribute("lookupstyle", "multi");
    document.getElementById(lookupSchemaName).setAttribute("_lookupstyle", "multi");

    I thought document.getElementById calls were unsupported in the latest CRM 2011 rollups. We haven't upgraded yet, but I'm wondering if I should be concerned about this. I haven't found a substitute for this and I looked everywhere.

    ReplyDelete
    Replies
    1. I have upgraded to UR12 and plan UR13 this weekend. Is this accurate? I'd like to put this into my system, but if the multi lookup doesn't work anymore, then I can't take the risk. Please advise.

      Delete
  17. Works great! Thank you!

    ReplyDelete
  18. how to create multi select lookup field in account form or any forms in crm 2011

    ReplyDelete
  19. saving multiple records isn't working for me, the "onchange" event doesn't get fired. Has anyone else encountered this problem and maybe know any possible solution? I am using CRM 2011 UR 18

    ReplyDelete