Tag Archives: oData

Defaulting a Lookup field via a REST query at Form Load

Here’s some sample code that demonstrates how to dynamically determine the GUID of a related record and use that to default a lookup field on a CRM form on form load.

In this example I have a dummy Contact record called “new contact” which I want to default onto the Phone Call form each time a new Phone Call record is created. 

i.e when I click New Phone Call on the ribbon…

image

…. I want to see this:

image

note: I have also re-configured the Phone Call entity’s Direction field so that “Incoming” is the default.  The idea being that the user is launching the Phone Call form to record an incoming call from an unknown caller.

Defaulting in a dummy Contact may some an unusual scenario but it is not uncommon to utilise a dummy record when dealing with anonymous callers or when wanting to defer the collection of contact details until later in the process.   In a Call Centre environment for example you will not achieve desired efficiencies if you except having to manually create new Contact records before being able to create Phone Calls, Cases, Opportunities, etc or before being able to convert Phone Calls to Cases or Opportunities (I expand on this Call Centre scenario in my next post).

Anyway, here’s how to make this work…

There are 2 parts to this.  Firstly, we need to find out the GUID of the Contact record that we want to default on to our Phone Call.   You could figure this out manually and then hardcode the GUID but then your code will not be transportable across environments.   Instead in this example I have hardcoded only the name of the contact (we can easily create this Contact in each environment, it’s just data).  My dummy Contact is called “new contact” and I use the REST oData endpoint to locate that record’s GUID by searching on that name (An alternative approach would be to  store the GUID in a configuration entity and then use REST to query for the GUID from there, however the same coding approach applies).   And  then secondly, once we know the GUID it’s a relatively simple matter of doing a SetValue to populate the field on the form.

Here’s the jscript:

// Set lookup value of a field 
function SetLookupValue(fieldName, id, name, entityType) { 
    if (fieldName != null) { 
        var lookupValue = new Array(); 
        lookupValue[0] = new Object(); 
        lookupValue[0].id = id; 
        lookupValue[0].name = name; 
        lookupValue[0].entityType = entityType; 
        Xrm.Page.getAttribute(fieldName).setValue(lookupValue); 
    } 
} 

function GetDefaultContactGUID() { 
    if (Xrm.Page.ui.getFormType() == 1) { 
        // Get the CRM URL 
        var serverUrl = Xrm.Page.context.getServerUrl(); 

        // Cater for URL differences between on premise and online 
        if (serverUrl.match(/\/$/)) { 
            serverUrl = serverUrl.substring(0, serverUrl.length - 1); 
        } 

        // Specify the ODATA end point (this is the same for all CRM 2011 implementations) 
        var ODATA_ENDPOINT = "/XRMServices/2011/OrganizationData.svc"; 

        // Specify the ODATA entity collection (this needs to be specific to your entity) 
        var ODATA_EntityCollection = "/ContactSet"; 

        // Specify the ODATA filter 
        var ODATA_Query = "?$select=ContactId&$filter=FullName%20eq%20\'new%20contact\'&$top=1"; 

        // Build the URL 
        var ODATA_Final_url = serverUrl + ODATA_ENDPOINT + ODATA_EntityCollection + ODATA_Query; 

        //Calls the REST endpoint 
        $.ajax({ 
            type: "GET", 
            contentType: "application/json; charset=utf-8", 
            datatype: "json", 
            url: ODATA_Final_url, 
            beforeSend: function (XMLHttpRequest) { 
                //Specifying this header ensures that the results will be returned as JSON.      
                XMLHttpRequest.setRequestHeader("Accept", "application/json"); 
            }, 
            success: function (data, textStatus, XmlHttpRequest) { 
                //This function will trigger asynchronously if the Retrieve was successful 
                SetLookupValue("from", data.d.results[0].ContactId, "new contact", "contact"); 
            }, 
            error: function (XmlHttpRequest, textStatus, errorThrown) { 
                //This function will trigger asynchronously if the Retrieve returned an error 
                alert("ajax call failed"); 
            } 
        }); 
    } 
}

The code snippet above contains 2 functions.  The “SetLookupValue” function is used to default the lookup field.  It is called by the 2nd function “GetDefaultContactGUID” which is where the REST query is constructed.

To deploy the above you need to load it into a jscript web resource and then reference that web resource on the Phone Call form and add a call in the Phone Call’s On Load event to the GetDefaultContactGUID function.   You will also need to upload json and jquery supporting  web resources and make sure they are specified in the order shown below:

image 

If you’re not sure what I’m talking about when I mention json and jquery web resources have a read of this earlier post.

That’s it, the code is well commented so have a read of that to understand the syntax. 

A solution file containing the above web resources and minor change to the Phone Call entity is available here for download.   Hope this helps someone.

G.

Advertisements

Filtered Lookup Approaches in CRM 2011

In this post I look at 3 different scenarios where lookup field filtering is required to illustrate the different approaches available in CRM 2011.  The out of the box options are demonstrated as well as more complex Jscript based approaches that can be adopted when the limitations of the standard functionality are hit…


Scenario 1:  You have 2 lookup fields on a form and one of those lookups should be filtered based on the other.

Example:  On the Contact form you add 2 new lookup fields, one for “Country” and one for “State”.   They map to 2 new entities: Country and State, where the Country entity has a 1:M relationship to State.  On the Contact form the user selects Country first and then when they select State the lookup should be filtered to only show the States that relate to the chosen Country.

Screen layout:

image

State lookup when Country = “Australia”:

image

State lookup when Country = “USA”:

image

Solution: Ok, this one is easy.  CRM 2011’s new Filtered Lookups functionality gives us this out of the box no code required.   The Filtered Lookup definition is set in the Properties of the State lookup on the CRM Form.  Here’s what you need to set:

image

The terminology employed here doesn’t read right to me, the fields feel like they’re placed upside down.   What we want is “Only show (State) records where the Country value of the State contains the Country value specified on this Contact record”. 

My suggestion is swap the fields in your head as you read the screen or don’t read the screen – instead just think of the first picklist as defining the input parameter and the second pick list as defining the field you want to filter on.

You can follow his approach works in those scenarios where your input parameter is a value on the form.   When your input parameter is on a related entity or needs to be hardcoded you need to go with another approach…


Scenario 2:  The same entity is represented twice on one form as 2 lookup fields.  Each lookup field should offer a different subset of records for the user to pick from.

Example:  The Account entity is used in CRM to store both Customers and Suppliers with the Relationship Type picklist on the Account form used to distinguish them.  The Contact form has 2 additional lookup fields to the Account entity, one labelled Customer, the other labelled Supplier.  The Customer lookup should only show Accounts of type “Customer” and the Supplier lookup should only show Accounts of type “Supplier”.

Screen layout:

image

Account Lookup for Customer field:

image

Account Lookup for Supplier field:

image

Solution:  There are 2 approaches to this, 1 simple, 1 complex.

The simple solution is to define 2 system views on the Account entity, 1 that only shows Customers and 1 that only shows Suppliers.  Then on the Contact form you assign the relevant view to each lookup.  Easy.

Create 2 system views:

image

Definition of the Customer lookup:

image

Definition of the Supplier lookup:

image

So this approach works fine in most simple scenarios.  The downside is that the 2 system views created to support this are visible throughout CRM.  In this scenario that’s probably not a big deal, the 2 views may be useful to end users, but once the volume of views increases things start to get messy. 

The workaround to this is to dynamically define new lookup views in jscript on form load and instruct the lookup fields to use these new views instead.  Here’s some sample jscript code that demonstrates how to do this.  Place this in a web resource and call the 2 functions provided from the Contact form’s OnLoad event:

function ChangeLookupView_Customer() { 
    //Note: in the form designer make sure the lookup field is set to "Show all views" in its "View Selector" property  

    //Set parameters values needed for the creation of a new lookup view... 
    var viewId = Xrm.Page.data.entity.getId();      // Your new lookup views needs a unique id.  It must be a GUID.  Here I use the GUID of the current record. 
    var entityName = "account";                     // The entity your new lookup view relates to 
    var viewDisplayName = "Account - Customers";    // A name for new lookup view 
    var viewIsDefault = true;                       // Whether your new lookup view should be the default view displayed in the lookup or not 

    //Define the Fetch XML query your new lookup view will use.  You can create this via Advanced Find.  You'll need to massage the syntax a little though 
    var fetchXml =  
              "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>" + 
              "<entity name='account'>" + 
              "<attribute name='name' />" + 
              "<attribute name='accountid' />" + 
              "<attribute name='customertypecode' />" + 
              "<filter type='and'>" + 
              "<condition attribute='customertypecode' operator='eq' value='3' />" + 
              "</filter>" + 
              "<order attribute='name' decending='false' />" + 
              "</entity>" + 
              "</fetch>"; 

    //Define the appearance of your new lookup view 
    var layoutXml =  
                    "<grid name='resultset' object='1' jump='name' select='1' icon='1' preview='1'>" +   
                    "<row name='result' id='accountid'>" +  // id = the GUID field from your view that the lookup should return to the CRM form 
                    "<cell name='name' width='200' />" +   
                    "<cell name='customertypecode' width='200' />" + 
                    "</row>" + 
                    "</grid>"; 
 
    //Add your new view to the Lookup's set of availabe views and make it the default view 
    Xrm.Page.getControl("new_customer").addCustomView(viewId, entityName, viewDisplayName, fetchXml, layoutXml, viewIsDefault); 
} 


function ChangeLookupView_Supplier() { 

    //Set parameters values needed for the creation of a new lookup view... 
    var viewId = Xrm.Page.data.entity.getId();  // Using the same GUID, the viewId only has to be unique within each lookup's set of views 
    var entityName = "account"; 
    var viewDisplayName = "Account - Suppliers"; 
    var viewIsDefault = true; 

    //Define the Fetch XML query your new lookup view will use   
    var fetchXml = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>" + 
          "<entity name='account'>" + 
          "<attribute name='name' />" + 
          "<attribute name='accountid' />" + 
          "<attribute name='customertypecode' />" + 
          "<filter type='and'>" + 
          "<condition attribute='customertypecode' operator='eq' value='10' />" +   // A different value provided here to produce a different result set 
          "</filter>" + 
          "<order attribute='name' decending='false' />" + 
          "</entity>" + 
          "</fetch>"; 

    //Define the appearance of your new lookup view 
    //No changes required here compared to the above, the lookups should have the same appearance 
    var layoutXml = "<grid name='resultset' object='1' jump='name' select='1' icon='1' preview='1'>" +   
             "<row name='result' id='accountid'>" + 
              "<cell name='name' width='200' />" + 
              "<cell name='customertypecode' width='200' />" + 
             "</row>" + 
            "</grid>"; 

    //Add your new view to the Lookup's set of availabe views and make it the default view 
    //The supplier field is specified here, we want to add this view to that lookup field 
    Xrm.Page.getControl("new_supplier").addCustomView(viewId, entityName, viewDisplayName, fetchXml, layoutXml, viewIsDefault); 
}

 

Rather than talk you through the code in this post I’ve put a lot of comments into the code so have a read through that.   2 tricks that I stumbled on are:

1) The view ID that you assign to your new view must be a GUID value.

2) In the form designer make sure your lookup field has "Show all views" set in its "View Selector" property.


Scenario 3:  A lookup field needs to be filtered based on a lookup field that resides on a related record.

Example:  The Account form has a Country field, the Contact form a State field.  When selecting the State on the Contact only those States related to the Country specified on the Contact’s parent Account should be offered to the user.

Screen layout – Account screen:

image

Screen layout – Contact screen:

image

State lookup on Contact form when parent Account’s Country = “USA”:

image

Solution:

This is the trickiest scenario to address because the filtering needs to be based on a piece of data that is not available to us on the form.  We can get to that data though and my preferred approach at the moment for this is to make an ODATA query to CRM’s REST end point.   So, our approach is as follows:

– Read the Parent Account GUID value from the Contact form

– Execute an ODATA query along the lines of: “Select the Country field from the Account entity where the Account’s GUID = this Contact’s Parent Account GUID”.

– Once we have that Country value we can then dynamically create a new view and assign it to the State lookup field on the Contact form just like we did in the last scenario.

Here’s the jscript that achieves this:

function GetCountryFromAccount() { 
    //Get the GUIDvalue of this Contact's Parent Account 
    var ParentAccount = Xrm.Page.data.entity.attributes.get("parentcustomerid"); 
    if (ParentAccount.getValue() != null) { 
        var ParentAccountGUID = ParentAccount.getValue()[0].id; 
    } 

    // Read the CRM Context to determine the CRM URL 
    var serverUrl = Xrm.Page.context.getServerUrl() 

    // Specify the ODATA End Point and Entity Collection 
    var ODATA_ENDPOINT = "XRMServices/2011/OrganizationData.svc"; 
    var ODATA_EntityCollection = "/AccountSet"; 

    // Specify the ODATA Query 
    var ODATA_Query = "(guid\'" + ParentAccountGUID + "\')?$select=new_Country"; 

    // Combine into the final URL 
    var ODATA_Final_url = serverUrl + ODATA_ENDPOINT + ODATA_EntityCollection + ODATA_Query; 

    //Calls the REST endpoint to retrieve the Country value of the Parent Account 
    $.ajax({ 
        type: "GET", 
        contentType: "application/json; charset=utf-8", 
        datatype: "json", 
        url: ODATA_Final_url, 
        beforeSend: function (XMLHttpRequest) { 
            XMLHttpRequest.setRequestHeader("Accept", "application/json"); 
        }, 
        success: function (data, textStatus, XmlHttpRequest) { 
            //This function will trigger asynchronously if the Retrieve was successful 
            var CountryGUID = data.d.new_Country.Id; 
            if (CountryGUID == null) { 
                // country on acct is null, we won't bother filtering the view 
                return; 
            } 
            else { 
                // call function to add custom view 
                ChangeLookupView_State(CountryGUID); 
            } 
        }, 
        error: function (XmlHttpRequest, textStatus, errorThrown) { 
            //This function will trigger asynchronously if the Retrieve returned an error 
            //Error encountered, we won't bother filtering the view 
            return; 
        } 
    }); 
} 

function ChangeLookupView_State(CountryGUID) { 
    //Set parameters values needed for the creation of a new lookup view... 
    var viewId = Xrm.Page.data.entity.getId();              // Your new lookup views needs a unique id.  It must be a GUID.  Here I use the GUID of the current record. 
    var entityName = "new_state";                           // The entity your new lookup view relates to 
    var viewDisplayName = "States for Account Country";     // A name for new lookup view 
    var viewIsDefault = true;                               // Whether your new lookup view should be the default view displayed in the lookup or not 

    //Define the Fetch XML query your new lookup view will use.  You can create this via Advanced Find.  You'll need to massage the syntax a little though 
    var fetchXml = 
              "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>" + 
              "<entity name='new_state'>" + 
              "<attribute name='new_stateid' />" + 
              "<attribute name='new_name' />" + 
              "<attribute name='new_countryid' />" + 
              "<order attribute='new_name' descending='false' />" + 
              "<filter type='and'>" + 
              "<condition attribute='new_countryid' operator='eq' value='" + CountryGUID + "' />" + 
              "</filter>" + 
              "</entity>" + 
              "</fetch>"; 

    //Define the appearance of your new lookup view 
    var layoutXml = 
                    "<grid name='resultset' object='1' jump='name' select='1' icon='1' preview='1'>" + 
                    "<row name='result' id='new_stateid'>" +  // id = the GUID field from your view that the lookup should return to the CRM form 
                    "<cell name='new_name' width='200' />" + 
                    "</row>" + 
                    "</grid>"; 

    //Add your new view to the Lookup's set of availabe views and make it the default view 
    Xrm.Page.getControl("new_statelookupid").addCustomView(viewId, entityName, viewDisplayName, fetchXml, layoutXml, viewIsDefault); 
}

Note: Because I’m following an oDATA approach this jscript needs the jquery and json2 libraries loaded into your CRM instance as web resources and needs those web resources referenced on the CRM form.  I talk more about these prerequisite steps in an earlier post.

 

Footnote:

The more I play with filtered lookups the more evident it becomes that all you are really able to do is improve the user experience, you are not able to effectively implement any sort of validation control, there are too many loopholes that will allow users to pick values that disobey the default filtering behaviour.   So, in addition to configuring your filtered lookups you really need to build out validation rules, either via jscript or Plug-in.