Retrieve and execute predefined queries

Applies to Dynamics 365 (online), version 9.x

Dynamics 365 Customer Engagement 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.

Predefined queries

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/v9.0/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/v9.0/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/v9.0/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/v9.0/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 query string 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/v9.0/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/v9.0/$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."  
    }  
  ]  
}  

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.

Use FetchXML within a Batch request

The length of a URL in a GET request is limited. Including FetchXML as a parameter in the URL can reach this limit. You can execute a $batch operation using a POST request as a way to move the FetchXML out of the URL and into the body of the request where this limit will not apply. More information: Execute batch operations using the Web API.

Example

Request

POST [Organization URI]/api/data/v9.0/$batch HTTP/1.1

Content-Type:multipart/mixed;boundary=batch_AAA123
Accept:application/json
OData-MaxVersion:4.0
OData-Version:4.0

--batch_AAA123
Content-Type: application/http
Content-Transfer-Encoding: binary

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

--batch_AAA123--

Response

--batchresponse_cbfd44cd-a322-484e-913b-49e18af44e34
Content-Type: application/http
Content-Transfer-Encoding: binary

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

{  
   "@odata.context":"[Organization URI]/api/data/v9.0/$metadata#accounts(accountid,name,telephone1,creditonhold)",
   "value":[  
      {  
         "@odata.etag":"W/\"563737\"",
         "accountid":"1f55c679-485e-e811-8151-000d3aa3c22a",
         "name":"Fourth Coffee (sample)",
         "telephone1":"+1-425-555-0121",
         "creditonhold":false
      },
      {  
         "@odata.etag":"W/\"563739\"",
         "accountid":"2555c679-485e-e811-8151-000d3aa3c22a",
         "name":"Litware, Inc. (sample)",
         "telephone1":"+1-425-555-0120",
         "creditonhold":false
      }
   ]
}
--batchresponse_cbfd44cd-a322-484e-913b-49e18af44e34--

See also

Web API Query Data Sample (C#)
Web API Query Data Sample (Client-side JavaScript)
Perform operations using the Web API
Compose Http requests and handle errors
Query Data using the Web API
Create an entity using the Web API
Retrieve an entity using the Web API
Update and delete entities using the Web API
Associate and disassociate entities using the Web API
Use Web API functions
Use Web API actions
Execute batch operations using the Web API
Impersonate another user using the Web API
Perform conditional operations using the Web API