Whilst adding JavaScript to forms in Dynamics 365 we often use OData to query data. But what is OData and how do you write JavaScript to query data? I will try to address these questions in this post.

As CRM / Dynamics has evolved I’ve actually found that I write less and less JavaScript! Business rules, calculated fields, roll up fields, quick view forms (etc) have all added to our configuration options, each of these features has reduced the need to code. But there are some things we still need to code. Using OData to query data being one of them!

Open Data Protocol (OData) is a standard protocol for consuming data exposed by Dynamics. OData is a Representational State Transfer (REST) based protocol. It can be applied to all types of technologies, including JavaScript Object Notation (JSON).

Now at this point I think I should pause! I am not (nor do I want to be) a hardcore developer, terms like OData, REST and JSON always make me feel like I might be straying into the dark side. Well, yes but then no! Using OData can be easy, in this post I hope to show a few examples that might help shed light on how you can leverage OData queries in your solution without needing an IT Degree in Software Development.

In this post, I will try to explain how to query CRM data in easy to follow terms. Using OData to query data is pretty straight forward. I won’t do this by listing every combination instead I will show you some examples that I commonly use.

OData Simple Example

I like to explain things by showing examples, so here is a simple example of using OData. In this example, I will make use of a couple of functions I repeatedly add to my JavaScript code. MakeRequest and GetRequestObject. We don’t “need” to worry about the detail of what these functions do! We just need to understand that we pass a query string to the MakeRequest function and what gets returned are the results.

function MakeRequest(query) {
    var serverUrl = Xrm.Page.context.getClientUrl();
    var oDataEndpointUrl = serverUrl + "/XRMServices/2011/OrganizationData.svc/";
    oDataEndpointUrl += query;
    var service = GetRequestObject();
    if (service != null) {
        service.open("GET", oDataEndpointUrl, false);
        service.setRequestHeader("X-Requested-With", "XMLHttpRequest");
        service.setRequestHeader("Accept", "application/json, text/javascript, */*");
        service.send(null);
        var retrieved = JSON.parse(service.responseText).d;
        var results = new Array();
        for (var i = 0; i < retrieved.results.length; i++) {
            results.push(retrieved.results[i]);
        } // End for
        return results;
    } // End if
    return null;
} // End function
function GetRequestObject() {
    if (window.XMLHttpRequest) {
        return new window.XMLHttpRequest;
    } else {
        try {
            return new ActiveXObject("MSXML2.XMLHTTP.3.0");
        } catch (ex) {
            return null;
        } // End catch
    } // End if
} // End function

Below you can see a simple example of how we build a query and return results. In this example I am returning the job title of the owner of a record.

    // *** First of all we get the GUID of the owning user, from owner lookup field.
    var lookupObject = Xrm.Page.getAttribute("ownerid");

    if (lookupObject != null) {
        var lookUpObjectValue = lookupObject.getValue();
        if ((lookUpObjectValue != null)) {
            var lookupid = lookUpObjectValue[0].id;
        } // End if
    } // End if
    // *** Next we build a query to find that user in system user entity and return job title and name.
    var selectQuery = "/SystemUserSet?&$filter=SystemUserId eq guid'" + lookupid + "'&$select=JobTitle, FullName";
    // *** Now we make a request, using our query
    var oDataResult = null;
    oDataResult = MakeRequest(selectQuery);
    // *** And finally we can view the results.
    alert("Name = " + oDataResult[0].FullName + "\n" + "Job Title = " + oDataResult[0].JobTitle);

In my simple example the following alert is created when this code is run.

Query in Detail

Let’s look at the query in a little more detail

var selectQuery = "/SystemUserSet?&$filter=SystemUserId eq guid'" + lookupid + "'&$select=JobTitle, FullName";

Firstly we have the name of the entity “set” we want to query. Notice the case is SystemUser, you will find this is very specific! If you need to find the correct case / name to user for any entity you should, view the entity details in customizations. See below that I have highlighted the schema name for the User entity.

var selectQuery = "/SystemUserSet?&$filter=SystemUserId eq guid'" + lookupid + "'&$select=JobTitle, FullName";

The next part of our query is the filter, I will give you some more details on the filter “statement” a little later in this post. But notice that I am filtering in the GUID field of the user. Again getting the schema name and case correct on your filter fields is important. This time I looked at he fields on the entity to confirm the exact schema name.

var selectQuery = "/SystemUserSet?&$filter=SystemUserId eq guid'" + lookupid + "'&$select=JobTitle, FullName";

The final part of our query gives us a list of the fields to return. In this example they are the users job title and full name. Again getting the schema name correct is important, so just as with the filter fields I checked the schema name under fields in customizations.

$filter

In my simple example my filter included “eq” as my operator. Other operators are available;

We can also use logical operators;

And we have these query functions;

Tip: In our $filter we might want to filter based on an optionset, you use syntax like this for that;

$filter=<>/Value ne 100000001

Select

We have seen that a list of columns can be given in the select portion and we can then review the results.

But what if your results are a lookup or option set. How do we then view the details?

To view the value of an optionset ….

oDataResult[0].<your field name>.Value;

To view the ID and name of a lookup field …

oDataResult[0].<your field name>.Name;

oDataResult[0].<your field name>.Id;

Order Results

You can sequence your results by using $orderby and then adding asc or desc as required.

$orderby=<your field name 1>, <your field name 2> asc

Limit Results

I find it quite common that my query will potentially return multiple rows but I just want any one of them. Or maybe in other circumstances you want the top 3 records. (etc.)

For this we add $top to the query.

$top=3

A Second Example

A second example, as always, I like to understand things by looking at a working example. So below I have created some code to illustrate the capabilities I have described above. This isn’t supposed to be the the most useful code in the world. I have simple created something tha contains all of the features mentioned!

My code runs from the contact entity, it lists upto three opportunities for the contact. ($top) It orders them by name. ($orderby)

And in the results, I return a lookup and also I filter ($filter) on an optionset.

Hopefully this query is a good example of everything I’ve described working together. Enjoy. J

    // *** First of all we get the GUID of the current record
    var contactId = Xrm.Page.data.entity.getId();
    // *** Opporuntities that are "InProgress have a status reason (optionset) with value 1
    // *** Next we build a query to find three opporuntities, that are open, ordered by their name field (Descending)
    var selectQuery = "/OpportunitySet?&top=3&$filter=ParentContactId/Id eq guid'" + contactId + "' and StatusCode/Value eq 1&$select=Name, ParentAccountId&$orderby=Name desc&$top=3";
    
    // *** Now we make a request, using our query
    var oDataResult = null;
    oDataResult = MakeRequest(selectQuery);
    // *** And finally we can view the results.
    // *** In this example we have upto three results, so lets loop round them!
    var arrayLength = oDataResult.length;
    for (var i = 0; i < arrayLength; i++) {
        var displayResult = "Opportunity Subject = " + oDataResult[i].Name + "\n";
        var displayResult = displayResult + "Account Name = " + oDataResult[i].ParentAccountId.Name + "\n"
        var displayResult = displayResult + "Account GUID = " + oDataResult[i].ParentAccountId.Id
        alert(displayResult);
    } // End for