Thursday, December 1, 2011

OData with JSON - Getting related values from a lookup field in CRM 2011

One of the most common methods to improve user experience is to intelligently fill values out in a form based on previous selections. In this post, I will walk through a business case that provides for quick data entry in a form.

I have a contact lookup and a section called address with multiple fields like street name, city, zipcode, state, country etc. The goal is to fill out the values of the address fields with the contacts details. These values are updated when the contact is changed, but the address section can be over written by the user.

1. Before we start writing js code, the first step is to find the name of the Set that we will be using in our call. You can get all the names by going to a similar url (the example is for on premise, but can be used for Crm online by replacing the server name/ organization section with the right value)
http://<server_name>/<organization>/XRMServices/2011/OrganizationData.svc/

If you search for the word contact in the xml, you will get to a section like the one below. Write down the title value.
- <collection href="ContactSet">
  <atom:title>ContactSet</atom:title>
  </collection>

2. Include the jquery library and json library as webresources to the solution. The jquery1.4.1.min.js and json2.js files are in the crm 2011 sdk (\sdk\samplecode\js\restendpoint\jqueryrestdataoperations\jqueryrestdataoperations\scripts).


3. Then create a new web resource and put the following js in it.

function getContactDetails()
{
    var lookUpObjectValue = Xrm.Page.getAttribute("mc_contact").getValue();
    if ((lookUpObjectValue != null))
    {
        var lookuptextvalue = lookUpObjectValue[0].name;

        var lookupid = lookUpObjectValue[0].id;
        //alert(lookupid);


    var serverUrl = Xrm.Page.context.getServerUrl();

    //The XRM OData end-point
    var ODATA_ENDPOINT = "/XRMServices/2011/OrganizationData.svc";


    var odataSetName = "ContactSet";

    var odataSelect = serverUrl + ODATA_ENDPOINT + "/" + odataSetName + "(guid'" + lookupid + "')";

    //alert(odataSelect);

    $.ajax({
        type: "GET",
        contentType: "application/json; charset=utf-8",
        datatype: "json",
        url: odataSelect,
        beforeSend: function (XMLHttpRequest) { XMLHttpRequest.setRequestHeader("Accept", "application/json"); },
        success: function (data, textStatus, XmlHttpRequest) {

            var result_contact= data.d;
          
            //alert(result_contact.AccountNumber);
                        var mc_city1 = result_contact.Address1_City;
                        //replace the fields with the fields on your entity
                        Xrm.Page.getAttribute("mc_city").setValue(mc_city1);
                        Xrm.Page.getAttribute("mc_street1").setValue(result_contact.Address1_Line1);
                        Xrm.Page.getAttribute("mc_street2").setValue(result_contact.Address1_Line2);
                        Xrm.Page.getAttribute("mc_street3").setValue(result_contact.Address1_Line3);
                        Xrm.Page.getAttribute("mc_phone").setValue(result_contact.Address1_Telephone1);
                        Xrm.Page.getAttribute("mc_stateprovince").setValue(result_contact.Address1_StateOrProvince);
                        Xrm.Page.getAttribute("mc_zippostalcode").setValue(result_contact.Address1_PostalCode);
                        Xrm.Page.getAttribute("mc_countryregion").setValue(result_contact.Address1_Country);
                      
        },
        error: function (XmlHttpRequest, textStatus, errorThrown) { alert('OData Select Failed: ' + odataSelect); }
    });

    }

   }

Let us walk through the code real quick here. The first few lines are to get the value of the contactid from the lookup. Once we get that, we start building the ODATA_ENDPOINT variable. Notice that we have set the value of the odataSetName to ContactSet, from the xml section in step 1. Then we are passing in the guid of the contact and returning all the related attribute values. From the returned object we parse out the values and populate the crm form.


4. Go to the form of the entity that has the contact lookup. Double click on it to open the Field properties dialog. In the  events tab, add the jquery and json webresource from step 2 and the js webresource from step 3 under Form libraries. In Event Handler section, select the OnChange event and add the library and function from step 3.

































































5. In Handler Properties dependencies tab, add the fields that you are filling out values for. This prevents accidental removal of dependent fields which could cause the javascript to break.
































6. Test it out in preview mode to make sure everything work as expected. This is what my setup looks like:
















In our example, our odata query was pretty straightforward as I was filtering only on the id. if there are additional filter values, the url can get pretty messy pretty fast. I would recommend using Rhett Clinton's Query Designer, available in codeplex, for the more complicated  queries.


Note that in our example, there is only one row returned. If multiple results are available as a result of the query, you will need to loop though the results of data.d.results and extract the values out of it.

12 comments:

  1. Great Post!!! Does this not support lookup tpyes or Optionsets? I get an object object error whenever I try to do an alert with the values.

    ReplyDelete
    Replies
    1. Thanks for the feedback! Please take a look at another of my posts where I get the lookup id.
      http://gotcrm.blogspot.com/2012/03/getting-related-record-from-lookup-and.html

      Delete
  2. Great Post, thanks for that. After replication in my environment. I got the error message: Error: The value of the property "getModulDetails" is null or undefined, not a Function object. To explan: In my custom entiy, I have a lookup fiel "Modul". There is a reference to another entity "modul" with all modul detials. The details of the available ETC points I would like to update in the first entity depending of the used lookup value. Here is my code:


    var odataSetName = "mc_modulSet";

    var odataSelect = serverUrl + ODATA_ENDPOINT + "/" + odataSetName + "(guid'" + lookupid + "')";

    //alert(odataSelect);

    $.ajax({
    type: "GET",
    contentType: "application/json; charset=utf-8",
    datatype: "json",
    url: odataSelect,
    beforeSend: function(XMLHttpRequest)
    { XMLHttpRequest.setRequestHeader("Accept", "application/json"); },
    success: function(data, textStatus, XmlHttpRequest) {
    var result_modul = data.d;

    //alert(result_modul);
    var etcpoints = result_mc_modul.mc_etc;
    Xrm.Page.getAttribute("mc_etcpoints").setValue(etcpoints);

    },
    error: function (XmlHttpRequest, textStatus, errorThrown)
    {alert('OData Select Failed: ' + odataSelect); }
    });
    }
    }

    Will be great if you can give me a tip where I have done the mistake.

    ReplyDelete
  3. if you use the parseInt it might work...

    var etcpoints = parseInt(result_mc_modul.mc_etc);

    Pierre Calder

    ReplyDelete
  4. Hi its very useful.
    we have renamed the contact entity as citizen in our org.
    I am getting the error saying that the odata select query url is failed.
    Could you please help me out to get the right value.

    ReplyDelete
  5. Madhan,
    renaming the contact entity should not break anything, as contact is a system entity.
    Take the rest url that returns the contact and see if it works in an IE browser.
    If that works correctly, you should be getting some result from the GET.

    Hope this helps!

    ReplyDelete
  6. Hey Mani, I hope you are still checking your blog. Here is my code
    function getContactDetails()
    {
    var lookUpObjectValue = Xrm.Page.getAttribute("inmate_lookupbookingscreen").getValue();
    if ((lookUpObjectValue != null))
    {
    var lookuptextvalue = lookUpObjectValue[0].name;

    var lookupid = lookUpObjectValue[0].id;
    //alert(lookupid);


    var serverUrl = Xrm.Page.context.getServerUrl();

    //The XRM OData end-point
    var ODATA_ENDPOINT = "/XRMServices/2011/OrganizationData.svc";


    //var odataSetName = "ContactSet";

    var odataSelect = serverUrl + ODATA_ENDPOINT + "/" + "(guid'" + lookupid + "')";

    //alert(odataSelect);

    $.ajax({
    type: "GET",
    contentType: "application/json; charset=utf-8",
    datatype: "json",
    url: odataSelect,
    beforeSend: function (XMLHttpRequest) { XMLHttpRequest.setRequestHeader("Accept", "application/json"); },
    success: function (data, textStatus, XmlHttpRequest) {

    var result_contact= data.d;

    //alert(result_contact.AccountNumber);
    //replace the fields with the fields on your entity
    Xrm.Page.getAttribute("inmate_firstname").setValue(result_contact.inmate_FirstName);
    Xrm.Page.getAttribute("inmate_lastname").setValue(result_contact.inmate_LastName);



    },
    error: function (XmlHttpRequest, textStatus, errorThrown) { alert('OData Select Failed: ' + odataSelect); }
    });

    }

    }

    but it does not populate the fields....although it is showing me the alerts and all. Can you help.

    ReplyDelete
  7. Great post, This helped me alot, Kudos

    ReplyDelete
  8. Great post, this helped alot, Kudos
    One note to add for CRM 2013
    in line 12 of the first script change

    var serverUrl = Xrm.Page.context.getServerUrl();

    to

    var serverUrl = Xrm.Page.context.getClientUrl();


    This shouldn't cause you a problem but its recommended as the getServerUrl is deprecated.

    Thanks

    ReplyDelete
  9. You guys are doing great job. In above example, set value is in text field "Xrm.Page.getAttribute("inmate_firstname").setValue(result_contact.inmate_FirstName);" . I want to get and set value in optionset.. Anybody has any idea?

    ReplyDelete
  10. This was a huge help. Thanks much!

    ReplyDelete