I was recently showing a colleague how to use the rather excellent CRM REST Builder Managed Solution, in particular, its ability to generate code snippets for predefined query requests into Dynamics CRM/Dynamics 365 for Enterprise (CRM/D365E). During the demo, I noticed the following options under the Query Type drop-down with interest:

I did some further digging on MSDN to confirm that my suspicions were correct, and I was pleased to be able to confirm the following:

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.

Source: https://msdn.microsoft.com/en-gb/library/mt607533.aspx

So to clarify the above, there are 3 ways we can query CRM’s/D365’s Web Services with FetchXML based queries: either with a direct FetchXML query, by referencing a System View or by referencing a Personal View. The benefits of using a System/Personal view are significant, such as:

• By having your Web API query setup as a view within CRM, you can utilise it within the application as part of a dashboard, entity view etc.
• You can reduce the size of your request and obfuscate information relating to your CRM instance (such as entity and attribute names) by using a saved query.
• Your FetchXML query can be stored within the application, meaning that you don’t need to worry about finding alternative means of backing up/storing your query.

Knowing the above would have been quite useful during my recent PowerBI exploits involving the CRM/D365 Web API, so this is definitely something that I will be reviewing again in future. If you want to get started using Saved/User queries in the application yourself, there are a few things to decide on in the first instance and slight hurdles to overcome initially, depending on the nature of your FetchXML query.

So first things first, how do I create my user/system query in CRM?

This will depend on the complexity of the query you are attempting to execute. To demonstrate this, let’s take a look at FetchXML Query # 1:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="phonecall">
    <attribute name="subject" />
    <attribute name="statecode" />
    <attribute name="prioritycode" />
    <attribute name="scheduledend" />
    <attribute name="createdby" />
    <attribute name="regardingobjectid" />
    <attribute name="activityid" />
    <order attribute="subject" descending="false" />
    <filter type="and">
      <condition attribute="directioncode" operator="eq" value="0" />
    </filter>
  </entity>
</fetch>

The above is a nice and straightforward query to return Phone Call records with a directioncode of “Incoming”, that can be built as an Advanced Find Personal View or System View very straightforwardly. But things change significantly when we take a look at FetchXML Query # 2 (an adapted query, provided courtesy of MSDN):

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
  <entity name="lead">
  <attribute name="fullname" />
    <link-entity name="task" from="regardingobjectid" to="leadid" alias="ab" link-type="outer" />
    <filter type="and">
      <condition entityname="ab" attribute="regardingobjectid" operator="null" />
    </filter>
  </entity>
</fetch>

There is no way that we can specify an outer join query within the CRM interface; so the only way in which we can get this query saved back into CRM is by writing a bespoke C# app that will add it in for us. Here is a code example for a method achieving this:

static void CreateSystemView(IOrganizationService service)
{
  Guid viewID;
  
  string layoutXML = @"<grid name='resultset' object='4' jump='fullname' select='1' preview='1' icon='1'>
                          <row name='result' id='leadid'>
                            <cell name='fullname' width='150' /> 
                          </row>
                      </grid>";
                      
  string fetchXML = @"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='true'>
                        <entity name='lead'>
                          <attribute name='fullname' />
                          <link-entity name='task' from='regardingobjectid' to='leadid' alias='ab' link-type='outer' />      
                            <filter type='and'>
                              <condition entityname='ab' attribute='regardingobjectid' operator='null' />
                            </filter>
                        </entity>
                      </fetch>";
  Entity savedQuery = new Entity("savedquery");
  
  savedQuery["name"] = "Complex View Test";
  savedQuery["description"] = "Test view to demonstrate how to create a view with a complex FetchXML query";
  savedQuery["returnedtypecode"] = "lead";
  savedQuery["fetchxml"] = fetchXML;
  savedQuery["layoutxml"] = layoutXML;
  savedQuery["querytype"] = 0;
  
  viewID =  service.Create(savedQuery);
  Console.WriteLine("Created system view " + viewID);
}

Two things to point out with the above:

• For the layoutXML, be sure to modify the object value so that it is the correct value for the entity you are working with. Otherwise, although your view will be successfully created within the application, you will be unable to load it correctly from within the interface. You can find a list of all system Entity codes here. For custom Entity codes, you will need to use a tool like the Metadata Browser in the XRMToolBox to determine the correct value.
• The above code example is using late-bound classes to generate the appropriate data to create the view, contrary to the official sample code provided by Microsoft. I was a little bit unsure initially whether views could be created in the manner, so I was pleased when I was able to confirm the opposite 🙂

With your view created, what’s next?

You’ll need to obtain the database GUID for the view record in CRM. If you have created your view for the complex example above, then you can very easily grab this value by setting a breakpoint in your application in Visual Studio and accessing the viewID value. An alternative way is via the application:

• For System Views, navigate to the View within the solutions page and open it up as if you were about to edit it. Maximise the window to full screen by pressing the F11 key. The URL of the page should now be visible if you move your mouse to the top of the screen, and available for copying. It should look something like this:

http://mycrminstance/tools/vieweditor/viewManager.aspx?appSolutionId=%7bFD140AAF-4DF4-11DD-BD17-0019B9312238%7d&entityId=%7bDC6574CB-92CE-446C-A5D6-885A75107D52%7d&id=%7b6979F60B-D5D4-E611-80DC-00155D02DD0D%7d

The GUID of the view will be the last query parameter string, with the encoded curly braces values (%7b and %7d) removed. So, based on the above, the GUID is:

6979F60B-D5D4-E611-80DC-00155D02DD0D

• Personal Views are a little more tricky. The most straightforward way I can think of obtaining this is by going to a Users list of Active Saved Views, exporting the list to Excel via the Static Worksheet (Page Only) button and then grabbing the GUID from the hidden Cell A in Excel:

This would obviously require you to have access to the Personal View, either via user login details or by having the user share the view to you. An alternative way to get this information would be via querying the Saved View entity via FetchXML/T-SQL.

Once you’ve got your GUID, you’re all set - you can now build your web service request in the language/format of your choosing. An example via XmlHttpRequest in JScript can be found below:

var req = new XMLHttpRequest();
req.open("GET", Xrm.Page.context.getClientUrl() + "/api/data/v8.1/leads?savedQuery=6979F60B-D5D4-E611-80DC-00155D02DD0D", true);
req.setRequestHeader("OData-MaxVersion", "4.0");
req.setRequestHeader("OData-Version", "4.0");
req.setRequestHeader("Accept", "application/json");
req.setRequestHeader("Prefer", "odata.include-annotations=\"*\"");
req.onreadystatechange = function() {
  if (this.readyState === 4) {
    req.onreadystatechange = null;
    if (this.status === 200) {
      var results = JSON.parse(this.response);
    } else {
      Xrm.Utility.alertDialog(this.statusText);
    }
  }
};
req.send();

The actual request header should resemble the below:

GET /JG/api/data/v8.1/leads?savedQuery=6979F60B-D5D4-E611-80DC-00155D02DD0D HTTP/1.1
OData-MaxVersion: 4.0
OData-Version: 4.0
Accept: application/json
Prefer: odata.include-annotations="*"
Accept-Language: en-GB
Accept-Encoding: gzip, deflate
Connection: Keep-Alive
Cookie: ReqClientId=9f48373a-aa68-462c-aab0-15ebd9311ce4; persistentNavTourCookie=HideNavTour; dea5e364-6f18-e611-80b5-00155d02dd0d_08f6129c-ce5b-4b98-8861-b15d01523fe1=/Date(1472324901665)/; excelDownloadToken=-1

Encapsulating your CRM/D365E queries as part of a System or Personal View is an effective way of reducing the size of your web service requests and simplifying the contents of the request whilst in transit. I would argue that a System View is a far better candidate for this job compared to a Personal View. Unless you have a specific business requirement not to have the view available to all users within the application, utilising this could save on lots of troubleshooting and administrative headroom down the line compared with a Personal View (such as if, for example, the person who has created the view originally leaves the business).