Query Data using the Web API

It took me some time to get my first Web API query works. So I am thinking to share some basic information to help anyone writing their first query.

Basic Structure of the Web API query

*The entity set name must be plural. In this example, the entity name is new_people, the entity set name used is new_peoples

*The first system query option is appended after [?] and subsequent query options are separated using [&].


Example Query

1) Query new_people entity, returning all records and all entity properties.

https://serverurl/api/data/v8.2/new_peoples

2) Query new_people entity, returning 2 properties

https://serverurl/api/data/v8.2/new_peoples?$select=new_jobtitle,new_name

Result

{
  "@odata.context":"https://serverurl/api/data/v8.2/$metadata#new_peoples(new_jobtitle,new_name)","value":[
    {
      "@odata.etag":"W/\"30001909\"","new_jobtitle":"HR Executive","new_name":"HR 1","new_peopleid":"4b4c136a-6d07-e811-8149-e0071b6a7031"
    },{
      "@odata.etag":"W/\"30001912\"","new_jobtitle":"Sales Executive","new_name":"Sales 1","new_peopleid":"4f696911-5104-e811-8128-e0071b6a92a1"
    },{
      "@odata.etag":"W/\"30001914\"","new_jobtitle":"Sales Manager","new_name":"Sales 2","new_peopleid":"4b59221f-5104-e811-8128-e0071b6a92a1"
    },{
      "@odata.etag":"W/\"30001907\"","new_jobtitle":"Global Sales Executive","new_name":"Global Sales 1","new_peopleid":"ed949e39-5104-e811-8128-e0071b6a92a1"
    },{
      "@odata.etag":"W/\"30001908\"","new_jobtitle":"Global Sales Executive","new_name":"Global Sales 2","new_peopleid":"79e70c42-5104-e811-8128-e0071b6a92a1"
    },{
      "@odata.etag":"W/\"30001910\"","new_jobtitle":"Marketing Executive","new_name":"Marketing 1","new_peopleid":"84c0c4f2-5404-e811-8128-e0071b6a92a1"
    },{
      "@odata.etag":"W/\"30001911\"","new_jobtitle":"Marketing Executive","new_name":"Marketing 2","new_peopleid":"ec37fcfc-5404-e811-8128-e0071b6a92a1"
    }
  ]
}

3) Query new_people entity with a primary key and returning 2 properties

https://serverurl/api/data/v8.2/new_peoples(4f696911-5104-e811-8128-e0071b6a92a1)?$select=new_jobtitle,new_name

Result

{
  "@odata.context":"https://serverurl/api/data/v8.2/$metadata#new_peoples(new_jobtitle,new_name)/$entity","@odata.etag":"W/\"30001912\"","new_jobtitle":"Sales Executive","new_name":"Sales 1","new_peopleid":"4f696911-5104-e811-8128-e0071b6a92a1"
}

4)  Query new_people entity, returning 2 properties with sorting

https://serverurl/api/data/v8.2/new_peoples?$select=new_jobtitle,new_name$orderby=
new_jobtitle asc,new_name desc


Result

{
  "@odata.context":"https://serverurl/api/data/v8.2/$metadata#new_peoples(new_jobtitle,new_name)","value":[
    {
      "@odata.etag":"W/\"30001908\"","new_jobtitle":"Global Sales Executive","new_name":"Global Sales 2","new_peopleid":"79e70c42-5104-e811-8128-e0071b6a92a1"
    },{
      "@odata.etag":"W/\"30001907\"","new_jobtitle":"Global Sales Executive","new_name":"Global Sales 1","new_peopleid":"ed949e39-5104-e811-8128-e0071b6a92a1"
    },{
      "@odata.etag":"W/\"30001909\"","new_jobtitle":"HR Executive","new_name":"HR 1","new_peopleid":"4b4c136a-6d07-e811-8149-e0071b6a7031"
    },{
      "@odata.etag":"W/\"30001911\"","new_jobtitle":"Marketing Executive","new_name":"Marketing 2","new_peopleid":"ec37fcfc-5404-e811-8128-e0071b6a92a1"
    },{
      "@odata.etag":"W/\"30001910\"","new_jobtitle":"Marketing Executive","new_name":"Marketing 1","new_peopleid":"84c0c4f2-5404-e811-8128-e0071b6a92a1"
    },{
      "@odata.etag":"W/\"30001912\"","new_jobtitle":"Sales Executive","new_name":"Sales 1","new_peopleid":"4f696911-5104-e811-8128-e0071b6a92a1"
    },{
      "@odata.etag":"W/\"30001914\"","new_jobtitle":"Sales Manager","new_name":"Sales 2","new_peopleid":"4b59221f-5104-e811-8128-e0071b6a92a1"
    }
  ]
}

5)  Query new_people entity, returning top 3 records with sorting

https://serverurl/api/data/v8.2/new_peoples?$select=new_jobtitle,new_name&$top=3&$orderby=
new_jobtitle asc,new_name desc


Result

{
  "@odata.context":"https://serverurl/api/data/v8.2/$metadata#new_peoples(new_jobtitle,new_name)","value":[
    {
      "@odata.etag":"W/\"30001908\"","new_jobtitle":"Global Sales Executive","new_name":"Global Sales 2","new_peopleid":"79e70c42-5104-e811-8128-e0071b6a92a1"
    },{
      "@odata.etag":"W/\"30001907\"","new_jobtitle":"Global Sales Executive","new_name":"Global Sales 1","new_peopleid":"ed949e39-5104-e811-8128-e0071b6a92a1"
    },{
      "@odata.etag":"W/\"30001909\"","new_jobtitle":"HR Executive","new_name":"HR 1","new_peopleid":"4b4c136a-6d07-e811-8149-e0071b6a7031"
    }
  ]
}

6)  Query new_people entity, returning 2 properties with filter and sorting

https://serverurl/api/data/v8.2/new_peoples?$select=new_jobtitle,new_name&$filter=new_jobtitle eq 'Global Sales Executive'&$orderby=
new_jobtitle asc,new_name desc

Result

{
  "@odata.context":"https://serverurl/api/data/v8.2/$metadata#new_peoples(new_jobtitle,new_name)","value":[
    {
      "@odata.etag":"W/\"30001908\"","new_jobtitle":"Global Sales Executive","new_name":"Global Sales 2","new_peopleid":"79e70c42-5104-e811-8128-e0071b6a92a1"
    },{
      "@odata.etag":"W/\"30001907\"","new_jobtitle":"Global Sales Executive","new_name":"Global Sales 1","new_peopleid":"ed949e39-5104-e811-8128-e0071b6a92a1"
    }
  ]
}

7)  Query new_people entity, returning 2 properties with filters

https://serverurl/api/data/v8.2/new_peoples?$select=new_jobtitle,new_name&$filter=new_jobtitle eq 'Global Sales Executive' and new_name eq 'Global Sales 2'

Result

{
  "@odata.context":"https://serverurl/api/data/v8.2/$metadata#new_peoples(new_jobtitle,new_name)","value":[
    {
      "@odata.etag":"W/\"30001908\"","new_jobtitle":"Global Sales Executive","new_name":"Global Sales 2","new_peopleid":"79e70c42-5104-e811-8128-e0071b6a92a1"
    }
  ]
}

8)  Query new_people entity, returning lookup properties

https://serverurl/api/data/v8.2/new_peoples(4f696911-5104-e811-8128-e0071b6a92a1)?$select=new_jobtitle,new_name&$expand=new_Group($select=new_name)

*new_Group is the lookup field schema name

Result

{
  "@odata.context":"https://serverurl/api/data/v8.2/$metadata#new_peoples(new_jobtitle,new_name,new_Group,new_Group(new_name))/$entity","@odata.etag":"W/\"30001912\"","new_jobtitle":"Sales Executive","new_name":"Sales 1","new_peopleid":"4f696911-5104-e811-8128-e0071b6a92a1","new_Group":{
    "@odata.etag":"W/\"29928575\"","new_name":"Sales","new_peoplegroupid":"a11ef467-5004-e811-8128-e0071b6a92a1"
  }
}

Editable Grids Lookup Field Custom Filter

At the time I posted this, there is no supported api to add custom filter to the lookup in editable grid. So until an official supported method is introduced, I have to dig into the execution context to get the correct object, and change its FetchXml to filter based on my needs.

Here is the example scenario where I need to filter the lookup based on the form field value.

Idea form is a place for the group administrator to create an idea and collect feedback from the group members. Besides his own group, the parent group member can also provide feedback to the idea. Therefore, the lookup should be able to display his group members and also the parent group members.

Below are the Members and their associated group:

Here is all the group available with its parent group:

In the Idea form, this is the lookup where currently it is displaying all the members without any filtering.

In order to filter the lookup based on the scenario, I will need to create a javascript file and publish it to my solution. Then add the javascript into the Idea form properties.

Next is to add the Control and Event to the editable grid.

Open up the form subgrid properties, and click on Controls tab. Select the Editable Grid.

In the Editable Grid section, click Add Lookup.

Select the view and column that you wanted to display in the lookup and click ok.

Next click on the Events tab. Select the lookup field, change the Event to "OnRecordSelect" and click Add.

The "Function" is the javascript function that will be called every time the subgrid record is selected. Make sure "Pass execution context as first parameter" is checked.

All the configuration is done. Now let's finish off the javascript (new_idea.js).

The function onPeopleSelected is the script that will be called when the editable grid record is selected. We only need to change the FetchXml once and the execution should be skipped if it has been set. So we need initializedPeopleLookup variable for this purpose.   

The function will first get the Administrator Group Id and his Parent Group Id. Then these 2 ids will be used to construct the FetchXml and subsequently passed to UpdateLookupFetchXml function to update the lookup Fetchxml.

 var initializedPeopleLookup = false;  
 function onPeopleSelected(execContext) {  
     if (initializedPeopleLookup) {  
       return;  
     }  
     try {  
                var ideaAdmin = Xrm.Page.getAttribute("new_ideaadministrator").getValue();  
                if (!ideaAdmin) {  
                     return;  
                }  
                var ideaAdminId = ideaAdmin[0].id.replace("{","").replace("}","");  
                var peopleGroupId = null;  
                var peopleParentGroupId = null;  
                var dataGridLookupAttributeName = "new_peoplename";  
                var serverURL = Xrm.Page.context.getClientUrl();  
                var oDataWebApiUrl = serverURL + "/api/data/v8.2/";  
                var oDataQueryUrl = oDataWebApiUrl + "new_peoples?$select=new_Group&$expand=new_Group($select=new_parentgroup)&$filter=new_peopleid eq " + ideaAdminId;  
                var req = new XMLHttpRequest();  
                req.open("GET",encodeURI(oDataQueryUrl), true);  
                req.setRequestHeader("Accept", "application/json");  
                req.setRequestHeader("Content-Type", "application/json; charset=utf-8");  
                req.setRequestHeader("OData-MaxVersion", "4.0");  
                req.setRequestHeader("OData-Version", "4.0");  
                req.onreadystatechange = function () {  
                     if (this.readyState == 4) {  
                          req.onreadystatechange = null;  
                          if (this.status == 200) {  
                               var data = JSON.parse(this.response)  
                               peopleGroupId = data.value[0].new_Group.new_peoplegroupid;  
                               peopleParentGroupId = data.value[0].new_Group._new_parentgroup_value;  
                               var fetchXml = '<fetch version="1.0" mapping="logical"><entity name="new_people"><attribute name="new_peopleid" /><attribute name="new_name" /><attribute name="createdon" /><filter type="and"><condition attribute="statecode" operator="eq" value="0" /><condition attribute="new_group" operator="in"><value>' + peopleGroupId + '</value><value>' + peopleParentGroupId + '</value></condition></filter></entity></fetch>';  
                               UpdateLookupFetchXml(execContext, dataGridLookupAttributeName, fetchXml);  
                          }  
                          else {  
                               var error = JSON.parse(this.response).error;  
                               console.log(error.message);  
                          }  
                     }  
                };  
                req.send(null);            
           }  
     catch (err) {  
     }            
   }  

This is the function to do the job updating the lookup FetchXml. Do take note on the following to make sure it is working:

- dataGridLookupAttributeName is correct. This is the attribute name of the lookup field that you want the FetchXml to change.

- "Add Lookup" property are configured in the editable grid as mentioned in one of the step in this blog. If this is not configured, the lookupObjects will be null, and we will not be able to update the FetchXml.

 function UpdateLookupFetchXml(execContext, dataGridLookupAttributeName, lookupFetchXml)  
 {  
       var formContext = execContext.getFormContext();  
       var data = execContext.getFormContext().data;  
       var entityObject = execContext.getFormContext().data.entity;  
       var entityAttribute = entityObject.attributes.getByName(dataGridLookupAttributeName);  
       if (!entityAttribute) {  
         return;  
       }  
       var propertyName = null;  
       var lookupObjects = null;  
       for (var key in entityAttribute) {  
         propertyName = Object.getOwnPropertyNames(entityAttribute[key]);  
         if (propertyName && propertyName.indexOf("lookups") > -1) {  
           lookupObjects = eval("entityAttribute." + key + ".lookups");  
           break;  
         }  
       }  
       if (lookupObjects == null) {  
         return;  
       }  
       var lookupObject = null;  
       for (var key in lookupObjects) {  
         if (key && key.indexOf("." + dataGridLookupAttributeName) > -1) {  
           lookupObject = eval("lookupObjects['" + key + "']");  
           break;  
         }  
       }  
       if (lookupObject == null) {  
         return;  
       }       
       if (lookupFetchXml.length > 0) {  
         lookupObject.fetchXml = lookupFetchXml;  
         initializedPeopleLookup = true;  
       }  
 }  

Here is the result after the lookup FetchXml is changed.

Since this is not a supported api, do review it for every major updates of Dynamics 365 to make sure this is still working. Of course, change it to use the supported way when it is available. :)