Retrieve and execute predefined queries

Article
09/15/2017

Applies To: Dynamics 365 (online), Dynamics 365 (on-premises), Dynamics CRM 2016, Dynamics CRM Online

Microsoft Dynamics 365 provides a way for administrators to create system views that are available to all users. Individual users can save the advanced find queries for re-use in the application. Both of these represent predefined queries you can retrieve and execute using the Web API. You can also compose a query using FetchXml and use that to retrieve data.

In this topic

Predefined queries

Use custom FetchXML

Predefined queries

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

Query type	Description
Saved Query	System-defined views for an entity. These views are stored in the savedquery EntityType. More information: Customize entity views
User Query	Advanced Find searches saved by users for an entity. These views are stored in the userquery EntityType. More information: UserQuery (saved view) entity

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

Apply a query to any collection of the appropriate type

In addition to simply applying the saved query to the main entity set collection, you can also use a saved query or user query to apply the same filtering on any collection of the appropriate type of entities. For example, if you want to apply a query against just the entities related to a specific entity, you can apply the same pattern. For example, the following URL will apply the Open Opportunities query against the opportunities related to a specific account via the opportunity_parent_account collection-valued navigation property.

GET [Organization URI]/api/data/v8.2/accounts(8f390c24-9c72-e511-80d4-00155d2a68d1)/opportunity_parent_account/?savedQuery=00000000-0000-0000-00aa-000010003001

Use custom FetchXML

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

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

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_'.

Paging with FetchXML

With fetchXML you can apply paging by setting the page and count attributes of the fetch element. For example, to set a query for accounts and limit the number of entities to 2 and to return just the first page, the following fetchXML:

<fetch mapping="logical" page="1" count="2">
 <entity name="account">
  <attribute name="accountid" />
  <attribute name="name" />
  <attribute name="industrycode" />
 <order attribute="name" />
 </entity>
</fetch>

With a request using fetchXML you can also request a paging cookie and include it with your query. More information: Page large result sets with FetchXML

A paging cookie must be requested as an annotation. Set the odata.include-annotations preference to use (or include) Microsoft.Dynamics.CRM.fetchxmlpagingcookie and a @Microsoft.Dynamics.CRM.fetchxmlpagingcookie property will be returned with the result.

Retrieve and execute predefined queries

In this topic

Predefined queries

Apply a query to any collection of the appropriate type

Use custom FetchXML

Paging with FetchXML

See Also

Additional resources