Slight change of pace with this week’s blog post, which will be a fairly condensed and self-indulgent affair – due to personal circumstances, I have been waylaid somewhat when it comes to producing content for the blog and I have also been unable to make any further progress with my new YouTube video series. Hoping that normal service will resume shortly, meaning additional videos and more content-rich blog posts, so stay tuned.

I’ve been running the CRM Chap blog for just over 2 years now. Over this time, I have been humbled and proud to have received numerous visitors to the site, some of whom have been kind enough to provide feedback or to share some of their Dynamics CRM/365 predicaments with me. Having reached such a landmark now seems to be good a time as any to take a look back on the posts that have received the most attention and to, potentially, give those who missed them the opportunity to read them. In descending order, here is the list of the most viewed posts to date on the crmchap.co.uk website:

  1. Utilising SQL Server Stored Procedures with Power BI
  2. Installing Dynamics CRM 2016 SP1 On-Premise
  3. Power BI Deep Dive: Using the Web API to Query Dynamics CRM/365 for Enterprise
  4. Utilising Pre/Post Entity Images in a Dynamics CRM Plugin
  5. Modifying System/Custom Views FetchXML Query in Dynamics CRM
  6. Grant Send on Behalf Permissions for Shared Mailbox (Exchange Online)
  7. Getting Started with Portal Theming (ADXStudio/CRM Portals)
  8. Microsoft Dynamics 365 Data Export Service Review
  9. What’s New in the Dynamics 365 Developer Toolkit
  10. Implementing Tracing in your CRM Plug-ins

I suppose it is a testament to the blog’s stated purpose that posts covering areas not exclusive to Dynamics CRM/365 rank so highly on the list and, indeed, represents how this application is so deeply intertwined with other technology areas within the Microsoft “stack”.

To all new and long-standing followers of the blog, thank you for your continued support and appreciation for the content ūüôā

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).

When working with CRM extensively across multiple environments, you do start to get into a fairly regular rhythm when it comes to completing key tasks again…and again…and again. One of these tasks is the process of rolling out a Solution update. To briefly summarise, this is where you export an updated version of your solution from your development environment and then import the solution file on top of a previous version in a different instance. CRM will then go through the solution import file, detect and then apply any changes which have been made. The original solution will be overwritten as part of this and you will be informed at the end of the import on any warnings or errors that were encountered during import. Warnings will generally not cause the solution import to fail, whereas errors will stop the import completely.

Like with anything, Solution updates can sometimes fall-over for a whole host of different reasons. They can fail altogether, or sometimes just hang and become unresponsive.¬†If you are running On-Premise CRM, then you can interrogate the SQL database on the instance to see how your solution import is (or is not) progressing. Ben Hosking (whose blog is mandatory reading for anyone who works closely with CRM) has written a really useful post which contains the SQL query you need to use on your organization database in order to identify any problematic job imports. ¬†The good thing with this approach is, if the import has errored, the Data column contains the raw XML file that you are able to download via the CRM GUI¬†using the ‘Download Log File’ button below when a solution import proceeds as you would expect normally:

SolutionUpdate_ExportLog

You can therefore very quickly drill-down to see if the solution import has failed due to a customisation issue. A common reason for failure may be due to duplicate logical name(s) for attributes, relationships etc.

If you are using CRM Online, then the first assumption (which I admittedly made) may be that there is no way in which to access the above information.¬†Fortunately, there is an entity called ‘importjob’ that is¬†made available for access via FetchXML

So, for example, to return all solution imports that have gone through your CRM, you would use the following FetchXML query:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="importjob" >
    <attribute name="completedon" />
    <attribute name="solutionname" />
    <attribute name="progress" />
    <attribute name="startedon" />
    <attribute name="completedon" />
    <attribute name="createdby" />
    <attribute name="data" />
    <attribute name="organizationid" />
    <attribute name="createdon" />
  </entity>
</fetch>

If you wanted to just return solution imports that are either stuck at 0% or have not yet successfully completed:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" >
  <entity name="importjob" >
    <attribute name="completedon" />
    <attribute name="solutionname" />
    <attribute name="progress" />
    <attribute name="startedon" />
    <attribute name="completedon" />
    <attribute name="createdby" />
    <attribute name="data" />
    <attribute name="organizationid" />
    <attribute name="createdon" />
    <filter type="or" >
      <condition attribute="progress" operator="eq" value="0" />
      <condition attribute="completedon" operator="null" />
    </filter>
  </entity>
</fetch>

Sometimes your import may fail due to a problem with CRM itself. In these instances, the best course of action depends, once again, on your CRM deployment:

  • For On-Premise users, then the old IT adage applies here: try restarting¬†the CRM and SQL Database Server instance. You may first need to locate the active process on SQL Management Studio that is performing the solution import and kill the process first. A database instance reset¬†should automatically cancel this and prevent it from running again on instance startup, but its better to be safe than sorry.
  • The only recourse for Online users is to log a support request with Microsoft via the Office 365 portal. It is best to provide as much evidence as possible up-front and be advised that the Microsoft support engineer may ask you to demonstrate the problem again, which might prove difficult to perform¬†during normal working hours if the problem is happening on your Production instance.

I was glad to discover that there is half way method of being able to interrogate possible platform issues yourself on CRM Online, but this particular example illustrates one of the drawbacks of CRM Online: little or no direct access to the organization database and/or instance level services. I would hope that in time Microsoft may develop the platform further in order to start exposing these elements to organization administrators. I would imagine that the vast majority of support queries that go through on the Office 365 portal would relate to requests that could be safely performed by CRM Administrators or Partners, leading to a cost and efficiency saving for Microsoft should this be implemented.

Working with Dynamics CRM can present some interesting challenges. What¬†you tend to find is that you can pretty much say “Yes!” when it comes to doing most things you would expect from a CRM/database system, but there is a learning curve involved in figuring out the best approach to take. Often, as well, you may ¬†over-complicate matters and overlook a much easier solution to achieve what you need.

Take, for example, modifying the FetchXML queries in a Public View that you have created programmatically. Let’s say you’ve created your own view within CRM using the following C# code snippet (adapted from the SDK sample):

SavedQuery sq = new SavedQuery
     {
        Name = "My New View",
        Description = "My view created in C# for the Account entity",
        ReturnedTypeCode = "account",
        FetchXml = fetchXml,
        LayoutXml = layoutXml,
        QueryType = 0
    };

_customViewId = _serviceProxy.Create(sq);
Console.WriteLine("A new view with the name {0} was created.", sq.Name);

A few things to point out first with the above:

  • In order for this code to work, you would need to declare System.String values for fetchXml and layoutXml, as well as first connecting to CRM using the OrganizationServiceProxy (_serviceProxy).
  • As well as specifying the FetchXML query you would like to use, you also have to specify a LayoutXML as a parameter in order to. Although Microsoft do have dedicated articles on MSDN that goes over the schema for this, there is a potential learning curve involved here for those who are unfamiliar with working with XML.
  • ReturnedTypeCode is your entity logical name, which will need changing depending on the entity you are attempting to query
  • Be sure to add in the appropriate namespace references, otherwise this code will not work.

The code example above is all very well and good if you are just wanting to create a brand new view. But what happens if you need to change it in the future? We can modify the base properties of a view (Name, Description etc.) as well as the column layout via the CRM GUI, but when we attempt to modify the filter criteria (i.e. the FetchXML query), we will notice that the option is not available to use:

View_NoFilterCriteriaButton

The next logical step would therefore be to look at creating some C# code that would take the existing view and modify the fetchXML query property. Unfortunately, Microsoft have not provided code examples on how this can be done, although it is in theory possible via the many methods at your disposal through the SDK.

Rather then spend days and potentially weeks writing a bespoke piece of code to do the job, it was then that I realised that I was being a little dense (as tends to happen) and that the Solution was sitting right in front of me. See what I did there?

Whilst the general rule of thumb is “DON’T DO IT!!” when it comes to modifying an exported solution file, it is possible to do and pretty much anything within a solution file can be changed or modified to suit a particular requirement. And, as luck would have it, modifying Public¬†Views (either ones created by yourself or system ones) is a supported task that you can perform on the solution file:

Definitions of views for entities are included in the customizations.xml file and may be manually edited. The view editor in the application is the most commonly used tool for this purpose. Editing customizations.xml is an alternative method

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

So, in order to modify a custom Public Views FetchXML query, all you would need to do is:

  1. Create a temporary, unmanaged solution file containing the entity with the custom Public View you want to change.
  2. Export as an unmanaged solution, unzip and open the customizations.xml file either in Notepad, Visual Studio or the XML editor program of your choice
  3. Use Ctrl + F to locate the savedquery node of the view you wish to change. It should look like this:
<savedquery>
    <IsCustomizable>1</IsCustomizable>
    <CanBeDeleted>1</CanBeDeleted>
    <isquickfindquery>0</isquickfindquery>
    <isprivate>0</isprivate>
    <isdefault>0</isdefault>
    <returnedtypecode>1</returnedtypecode>
    <savedqueryid>{8e736028-47c7-e511-8107-3863bb345ac8}</savedqueryid>
    <layoutxml>
        <grid name="resultset" object="1" jump="firstname" select="1" preview="1" icon="1">
            <row name="result" id="accountid">
                <cell name="name" width="150" />
                <cell name="statecode" width="150" />
                <cell name="statuscode" width="150" />
                <cell name="ownerid" width="150" />
                <cell name="createdon" width="150" />
            </row>
        </grid>
    </layoutxml>
    <querytype>0</querytype>
    <fetchxml>
        <fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='true'>
            <entity name='account'>
                <attribute name='createdon' />
                <attribute name='statuscode' />
                <attribute name='ownerid' />
                <attribute name='name' />
                <attribute name='statecode' />
                <attribute name='accountid' />
                <order attribute='name' descending='false' />
                <link-entity name='email' from='regardingobjectid' to='accountid' alias='ab' link-type='outer'>
                    <attribute name='regardingobjectid' />
                </link-entity>
                <link-entity name='lead' from='parentaccountid' to='accountid' alias='al' link-type='outer'>
                    <link-entity name='email' from='regardingobjectid' to='leadid' alias='lp' link-type='outer'>
                        <attribute name='regardingobjectid' />
                    </link-entity>
                </link-entity>
            <filter type='and'>
                <condition entityname='ab' attribute='regardingobjectid' operator='null' />
                <condition entityname='lp' attribute='regardingobjectid' operator='null' />
                <filter type='or'>
                    <condition entityname='ab' attribute='createdon' operator='olderthan-x-weeks' value='1' />
                    <condition entityname='ab' attribute='createdon' operator='null' />
                </filter>
            </filter>
            </entity>
        </fetch>
    </fetchxml>
    <IntroducedVersion>1.0</IntroducedVersion>
    <LocalizedNames>
        <LocalizedName description="My New View" languagecode="1033" />
    </LocalizedNames>
    <Descriptions>
        <Description description="My view created in C# for the Account entity" languagecode="1033" />
    </Descriptions></savedquery>
  1. Modify the FetchXML query within the <fetchxml> node to your updated query
  2. (Optional) If your FetchXML query is simply making changes to the filter criteria, you can skip this step. Otherwise, if you have new fields that you would like to be displayed as part of the changes, you will also need to modify the <layoutxml> node so that it contains your new fields.
  3. Save the changes back into the solution file and then import the solution back into CRM.
  4. Test your view by opening it within the application and confirm everything looks OK.

I’m sure you’ll agree that this is definitely a much easier and simple way to make changes to your view. Just be careful when working within the solution file that you don’t accidentally delete/overwrite something!