Retrieve and execute predefined queries

Predefined queries

Use custom FetchXML

Microsoft Dynamics 365 allows you to define, save, and execute two types of queries as listed here.

Records for both of these types of entities contain the FetchXML definition for the data to return. You can query the respective entity type to retrieve the primary key value. With the primary key value, you can execute the query by passing the primary key value. For example, to execute the Active Accounts saved query, you must first get the primary key using a query like this.

GET [Organization URI]/api/data/v8.2/savedqueries?$select=name,savedqueryid&$filter=name eq 'Active Accounts'

You can then use the savedqueryid value and pass it as the value to the savedQuery parameter to the accounts entity set.

GET [Organization URI]/api/data/v8.2/accounts?savedQuery=00000000-0000-0000-00aa-000010001002

Use the same approach to get the userqueryid and pass it as the value to the userQuery parameter to the entity set that matches the corresponding returnedtypecode of the saved query.

GET [Organization URI]/api/data/v8.2/accounts?userQuery=121c6fd8-1975-e511-80d4-00155d2a68d1

FetchXML is a proprietary query language that provides capabilities to perform aggregation. The saved queries and user queries stored in savedquery EntityType and userquery EntityType respectively both include a fetchxml property that defines the query. You can use FetchXML directly with the IOrganizationService. RetrieveMultiple method or with the RetrieveMultipleRequest. More information: Build queries with FetchXML

You can pass URL encoded FetchXML as a query to the entity set corresponding to the root entity of the query using the FetchXML parameter to return the results from the Web API. For example, you can have the following FetchXML that has account as the entity.

<fetch mapping='logical'> 
   <entity name='account'>
      <attribute name='accountid'/> 
      <attribute name='name'/> 
</entity>
</fetch>

The URL encoded value of this FetchXML is as shown here.

%3Cfetch%20mapping='logical'%3E%3Centity%20name='account'%3E%3Cattribute%20name='accountid'/%3E%3Cattribute%20name='name'/%3E%3C/entity%3E%3C/fetch%3E

Most programming languages include a function to URL encode a string. For example, in JavaScript you use the encodeURI function. You should URL encode any request that you send to any RESTful web service. If you paste a URL into the address bar of your browser it should URL encode the address automatically. The following example shows a GET request using the FetchXML shown previously using the entity set path for accounts.

Request

Copy

GET [Organization URI]/api/data/v8.2/accounts?fetchXml=%3Cfetch%20mapping='logical'%3E%3Centity%20name='account'%3E%3Cattribute%20name='accountid'/%3E%3Cattribute%20name='name'/%3E%3C/entity%3E%3C/fetch%3E HTTP/1.1
Accept: application/json
OData-MaxVersion: 4.0
OData-Version: 4.0

Response

Copy

HTTP/1.1 200 OK
Content-Type: application/json; odata.metadata=minimal
OData-Version: 4.0

{
  "@odata.context":"[Organization URI]/api/data/v8.2/$metadata#accounts(accountid,name)","value":[
    {
      "@odata.etag":"W/\"506678\"","accountid":"89390c24-9c72-e511-80d4-00155d2a68d1","name":"Fourth Coffee (sample)"
    },{
      "@odata.etag":"W/\"502172\"","accountid":"8b390c24-9c72-e511-80d4-00155d2a68d1","name":"Litware, Inc. (sample)"
    },{
      "@odata.etag":"W/\"502174\"","accountid":"8d390c24-9c72-e511-80d4-00155d2a68d1","name":"Adventure Works (sample)"
    },{
      "@odata.etag":"W/\"506705\"","accountid":"8f390c24-9c72-e511-80d4-00155d2a68d1","name":"Fabrikam, Inc. (sample)"
    },{
      "@odata.etag":"W/\"506701\"","accountid":"91390c24-9c72-e511-80d4-00155d2a68d1","name":"Blue Yonder Airlines (sample)"
    },{
      "@odata.etag":"W/\"502180\"","accountid":"93390c24-9c72-e511-80d4-00155d2a68d1","name":"City Power & Light (sample)"
    },{
      "@odata.etag":"W/\"502182\"","accountid":"95390c24-9c72-e511-80d4-00155d2a68d1","name":"Contoso Pharmaceuticals (sample)"
    },{
      "@odata.etag":"W/\"506704\"","accountid":"97390c24-9c72-e511-80d4-00155d2a68d1","name":"Alpine Ski House (sample)"
    },{
      "@odata.etag":"W/\"502186\"","accountid":"99390c24-9c72-e511-80d4-00155d2a68d1","name":"A. Datum Corporation (sample)"
    },{
      "@odata.etag":"W/\"502188\"","accountid":"9b390c24-9c72-e511-80d4-00155d2a68d1","name":"Coho Winery (sample)"
    },{
      "@odata.etag":"W/\"504177\"","accountid":"0a3238d4-f973-e511-80d4-00155d2a68d1","name":"Litware, Inc."
    }
  ]
}

Note
Response of FetchXML queries containing link-entities and their attributes contains Unicode special characters such that '.' becomes '_x002e_' and '@' becomes '_x0040_'.