Useful Dynamics CRM 2016 Web API Queries: Part 2

John Towgood, 23 October 2017

Web API is an easy way to send queries to CRM with Javascript. Since the XMLHttpRequest and JSON libraries are available with Javascript we can use these libraries to send a HTTP request to the Web API and parse the JSON response data to objects.

Here are some more queries which I found useful.

Updating Lookup field

When updating a lookup field on a record in Dynamics CRM 2016 using the name of the field the following error will be presented.

image

PATCH http://testserver/testorg/api/data/v8.0/mag_resolutions(639ea864-a071-e711-80d6-00155d048d70)
Request {
  "mag_resolutionnumber": "5264",
  "mag_issueid@odata.bind" : "/mag_issues(93f66077-a071-e711-80d6-00155d048d70)"
}
Response {
   "error":{
     "code":"","message":"An undeclared property 'mag_issueid' which only has property annotations in the payload but no property value was found in the payload. In OData, only declared navigation properties and declared named streams can be represented a
…………
}

Using the Schema Name of the lookup field resolves the issue (i.e. it is case sensitive). For other field types in Dynamics CRM 2016, you must use the field name. Best practice is to create fields with lower case names so the Name and Schema Name will both be lower case therefore updating a lookup field will be the same as updating other field type fields using lower case name of the field.

image

PATCH http://testserver/testorg/api/data/v8.0/mag_resolutions(639ea864-a071-e711-80d6-00155d048d70)
Request {
"mag_resolutionnumber": "5264",
"mag_issueid@odata.bind" : "/mag_issues(93f66077-a071-e711-80d6-00155d048d70)"
}

Querying a record by Lookup Id

When querying Dynamics CRM 2016 records filtering against a lookup field using the name or schema name of the field the following error will be presented.

image

GET    http://testserver/testorg/api/data/v8.0/mag_resolutions?$select=mag_name,mag_IssueId&$filter=mag_IssueId eq 93f66077-a071-e711-80d6-00155d048d70
{
   "error":{
     "code":"","message":"The navigation properties are not supported with $select clause","innererror":{
       "message":"The navigation properties are not supported with $select clause","type":"Microsoft.Crm.CrmHttpException","stacktrace":"   at …………
}

Adding the underscore character (_) followed by the name of the lookup and ending with ‘_value’ results in a successful query.

GET    http://testserver/testorg/api/data/v8.0/mag_resolutions?$select=mag_name,_mag_issueid_value&$filter=
_mag_issueid_value eq 93f66077-a071-e711-80d6-00155d048d70
{
"@odata.context":"http://testserver/testorg/api/data/v8.0 /$metadata#mag_resolutions(mag_name,_mag_issueid_value)","value":[
     {
       "@odata.etag":"W/\"1255350408\"","mag_name":"Resolution 1","_mag_issueid_value":"93f66077-a071-
e711-80d6-00155d048d70","mag_resolutionid":"639ea864-a071-e711-80d6-00155d048d70"
     }
   ]
}

Executing a Fetch XML Query

Instead of using OData queries you can also use the familiar Fetch XML queries. But to use Fetch XML the query would need to be URL encoded.

Fetch <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
   <entity name="mag_resolution">
     <attribute name="mag_name" />
       <filter type="and">
         <condition attribute="mag_name" operator="eq" value="Resolution 1" />
       </filter>
   </entity>
< /fetch>
URL Encoded    %3Cfetch%20version%3D%221.0%22%20output-format%3D%22xml-platform%22%20mapping%3D%22logical%22%20distinct%3D%22true%22%3E%0A%20%20%3Centity%
20name%3D%22mag_resolution%22%3E%0A%20%20%20%20%3Cattribute%20name%3D%
22mag_name%22%20%2F%3E%0A%20%20%20%20%20%20%3Cfilter%20type%3D%22and%22%3
E%0A%20%20%20%20%20%20%20%20%3Ccondition%20attribute%3D%22mag_name%22%20operator
%3D%22eq%22%20value%3D%22Resolution%201%22%20%2F%3E%0A%20%20%20%20%20%20%3C%
2Ffilter%3E%0A%20%20%3C%2Fentity%3E%0A%3C%2Ffetch%3E

The query will contain ‘fetchXML=’ followed by the URL encoded fetch query.

Category List

Filter by Author