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

I have had an opportunity recently to start getting to grips with the wonderful world of PowerBI. For those who have walked the tightrope between Excel and SQL Server Reporting Service (SSRS) Reports, PowerBI appears to be the tool with these individuals in mind. It enables you to leverage existing Excel knowledge (through PowerQuery or Excel-based formulas/calculations), whilst also offering a number of easy to setup Visualisations, that are not too dissimilar to the charts, maps and other objects that can be setup on a .rdl report. What’s also good to know, from a Dynamics CRM/365 for Enterprise (D365E) point of view, is that you can very quickly hook up your CRM data to a PowerBI dashboard/report. In addition to this, integrating with other data sources – such as SQL, JSON or flat file sources – or even with completely different application systems is possible (even with SalesForce – shock, horror!). In the days of increasing need for tight integration between a dazzling array of different application and database systems, PowerBI gives you a broad framework to achieve any reporting goal you may have in mind. Having said that, it is still rough around the edges and in need of some further development before it, arguably, becomes the de facto tool to use. For example, I have found some of the formatting and design options available to be rather basic and light-years behind what is possible with SSRS or even Excel. There are also some features missing that are somewhat baffling, such as the ability to send out dashboards/reports via email on a predefined schedule. I would hope that we see increasing attention towards PowerBI in the months and years ahead in order to bring the very best of features from these more traditional applications but exposed in a non-daunting and wholly accessible way.

As referenced above, getting set up with your Online CRM/D365E data is incredibly straightforward via the inbuilt Dynamics 365 connector “wizard” – simply login into your online organisation, specify the entity data that you want to work with and PowerBi will push this data into a table for you, enabling you to start building your report in seconds. The connector “wizard” is suited to most typical data retrieval scenarios, providing a GUI interface to visualise the entity data within your CRM/D365E instance and the ability to put together related entities and return them as part of your query. Before you start using it, however, I would highlight the following:

  • The OData feed is rather indiscriminate in its retrieval – all records from an entity will be returned. Some pre-filtering will occur based on CRM’s/D365E’s security model (e.g. if the account you log in as has Business Unit level Read privilege on the Lead entity, only records in the accounts Business Unit will be returned), but typically it will be System Administrators who set up a PowerBI Dashboard; therefore meaning you have to deal with a lot of records being returned into PowerBI. Given that the two different PowerBI plans have limitations in regards to record retrieval, this could cause problems if you are working with large CRM datasets.
  • Tied in with the above, because you have no way via the “wizard” interface to specify record retrievals, you cannot take advantage of filtering your data based on certain attributes or even take advantage of some of the native functionality within CRM to aggregate your data. Whilst PowerBI is certainly more than capable of doing this, relatively n00bish users may find this an unwelcome barrier that hinders adoption.
  • Lookup and Option Set attributes are returned as a special data type of Record – with the underlying properties of the related record (GUID, display name etc.) stored within this sub-record. Having the data stored in this manner causes additional administrative effort on the PowerBI side, as you will need to figure out how to access the underlying properties of this PowerBi data type.

Fortunately, if you are inclined towards building very specific and focused queries that you can execute against your Online CRM/D365E, there is a way – and the best thing is, we get to use something that has been recently introduced into CRM as well 🙂

The Web API to the rescue!

The Web API was introduced in Dynamics CRM 2016, which implements version 4 of the Open Data (OData) Protocol, and will eventually replace the traditional means that developers would use to access CRM via web services (namely, the Organization service and the old OData service). CRM Developers will need to start becoming increasingly familiar with the Web API in the years ahead, and no doubt a lot of work will need to be done updating existing coding to look at the new Web API.

Because the Web API is a web service, PowerBi can connect to it via the Web connector. By querying the Web API, you have access to all of the messages that are exposed to get data from CRM – Retrieve, Retrieve Multiple and Predefined Query – with multiple options available to use in terms of how you return, filter and aggregate your data. Results will be returned in JSON format, so there will be some additional work that needs to be done to get the data into an accessible format. This post will now take a look at what you need to do in order to return data based on a FetchXML query, as well as (hopefully!) providing a useful code snippet that you can adapt to your environment.

Before starting, ensure that you have installed the CRM Rest Builder Managed Solution within your CRM development environment. This tool allows you to quickly generate code snippets in JScript that perform web service calls into CRM/D365E and is a massive help in a number of different ways. A big shout out and thanks to Jason Lattimer for the work he has done on this.

  1. To begin, you need a FetchXML query that returns the data you need. This can be written manually or generated automatically via Advanced Find. In this example, we are going to use the following snippet that queries the Case entity, that will return 7 sample data records from CRM:
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="incident">
    <attribute name="title" />
    <attribute name="ticketnumber" />
    <attribute name="createdon" />
    <attribute name="incidentid" />
    <attribute name="caseorigincode" />
    <attribute name="customerid" />
    <order attribute="ticketnumber" descending="false" />
    <filter type="and">
      <condition attribute="prioritycode" operator="eq" value="1" />
    </filter>
  </entity>
</fetch>
  1. Next, we need to generate the URL that will be used to query the Web API endpoint. There are two challenges here – the first being that the FetchXML query needs to be included in the URL and that it needs to be encoded so that it is a valid URL. The start of the URL is fairly straightforward to put together – it’s just your CRM organisation URL, in the following format:

https://<Organisation Name>.<Region Code>.dynamics.com

So if your organisation name is crmchap and your CRM tenant is based in the EMEA region, then the URL would be as follows:

https://crmchap.crm4.dynamics.com

The rest of the URL can be obtained from the CRM Rest Builder. Open the Managed Solution, navigating to the Configuration page. It will look something like this:

1

Update the page so that the Action is set to Predefined Query. Ensure that the Primary Entity is set to Incident (always something you have to remember when working with the Case entity 🙂 ) and then copy and paste the FetchXML query into the box below. The window should look similar to the below once ready:

2Press Create Request to put together the code snippet. On Line 2, you will see the following piece of code:

req.open("GET", Xrm.Page.context.getClientUrl() + "/api/data/v8.0/incidents?fetchXml=%3Cfetch%20version%3D%221.0%22%20output-format%3D%22xml-platform%22%20mapping%3D%22logical%22%20distinct%3D%22false%22%3E%3Centity%20name%3D%22incident%22%3E%3Cattribute%20name%3D%22title%22%20%2F%3E%3Cattribute%20name%3D%22ticketnumber%22%20%2F%3E%3Cattribute%20name%3D%22createdon%22%20%2F%3E%3Cattribute%20name%3D%22incidentid%22%20%2F%3E%3Cattribute%20name%3D%22caseorigincode%22%20%2F%3E%3Cattribute%20name%3D%22description%22%20%2F%3E%3Cattribute%20name%3D%22customerid%22%20%2F%3E%3Corder%20attribute%3D%22ticketnumber%22%20descending%3D%22false%22%20%2F%3E%3Cfilter%20type%3D%22and%22%3E%3Ccondition%20attribute%3D%22prioritycode%22%20operator%3D%22eq%22%20value%3D%221%22%20%2F%3E%3C%2Ffilter%3E%3C%2Fentity%3E%3C%2Ffetch%3E"

The bit we are interested in is the string value after the Xrm.Page.context.getClientUrl() function, which will need to be appended to our CRM URL. So based on the above, our URL to use with PowerBI would be as follows:

https://crmchap.crm4.dynamics.com/api/data/v8.0/incidents?fetchXml=%3Cfetch%20version%3D%221.0%22%20output-format%3D%22xml-platform%22%20mapping%3D%22logical%22%20distinct%3D%22false%22%3E%3Centity%20name%3D%22incident%22%3E%3Cattribute%20name%3D%22title%22%20%2F%3E%3Cattribute%20name%3D%22ticketnumber%22%20%2F%3E%3Cattribute%20name%3D%22createdon%22%20%2F%3E%3Cattribute%20name%3D%22incidentid%22%20%2F%3E%3Cattribute%20name%3D%22caseorigincode%22%20%2F%3E%3Cattribute%20name%3D%22description%22%20%2F%3E%3Cattribute%20name%3D%22customerid%22%20%2F%3E%3Corder%20attribute%3D%22ticketnumber%22%20descending%3D%22false%22%20%2F%3E%3Cfilter%20type%3D%22and%22%3E%3Ccondition%20attribute%3D%22prioritycode%22%20operator%3D%22eq%22%20value%3D%221%22%20%2F%3E%3C%2Ffilter%3E%3C%2Fentity%3E%3C%2Ffetch%3E

A bit of a mouthful I agree!

  1. Now that we have the URL, we can connect up to CRM within PowerBI. Create or Open a new PBIX file and select Get Data -> Web:

3

  1. On the From Web window, copy and paste the URL we’ve built and press OK. You will be prompted to log into your CRM organisation; select Organisational account and log in as you would normally via the Office 365 login page. Once logged in, the data will be retrieved and the Query Editor will open, displaying something similar to the below:4
  2. Some additional work is required in order to get our data into a standard, tabular format. In addition, the data at the moment is returning the underlying Option Set and Lookup values from the Incident entity, as opposed to the Display Name; not good from a reporting point of view:

5We, therefore, need to modify the underlying PowerQuery in order to achieve the following:

Right click on Query1 and select Advanced Editor to open the underlying PowerQuery text. Delete everything here and then copy and paste the following into the window:

let
    //Get the CRM data, including the Formatted Values as part of the returned data
    Source = Json.Document(Web.Contents("https://crmchap.crm4.dynamics.com/api/data/v8.0/incidents?fetchXml=%3Cfetch%20version%3D%221.0%22%20output-format%3D%22xml-platform%22%20mapping%3D%22logical%22%20distinct%3D%22false%22%3E%3Centity%20name%3D%22incident%22%3E%3Cattribute%20name%3D%22title%22%20%2F%3E%3Cattribute%20name%3D%22ticketnumber%22%20%2F%3E%3Cattribute%20name%3D%22createdon%22%20%2F%3E%3Cattribute%20name%3D%22incidentid%22%20%2F%3E%3Cattribute%20name%3D%22caseorigincode%22%20%2F%3E%3Cattribute%20name%3D%22customerid%22%20%2F%3E%3Corder%20attribute%3D%22ticketnumber%22%20descending%3D%22false%22%20%2F%3E%3Cfilter%20type%3D%22and%22%3E%3Ccondition%20attribute%3D%22prioritycode%22%20operator%3D%22eq%22%20value%3D%221%22%20%2F%3E%3C%2Ffilter%3E%3C%2Fentity%3E%3C%2Ffetch%3E", [Headers=[#"Prefer"="odata.include-annotations=""OData.Community.Display.V1.FormattedValue"""]])),
    //Get the underlying list of records returned
    values = Source[value],
    //Create a new table with one column, populated with the values list of records
    #"Table from List" = Table.FromList(values, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    //Query will error if no results, therefore use an if statement to build an empty table with the correct column headers
    Expand = if List.IsEmpty(values) then #table({"title", "ticketnumber", "createdon", "incidentid", "caseorigincode", "_customerid_value@OData.Community.Display.V1.FormattedValue"}, {"title", "ticketnumber", "createdon", "incidentid", "caseorigincode", "_customerid_value@OData.Community.Display.V1.FormattedValue"}) else Table.ExpandRecordColumn(#"Table from List", "Column1", {"title", "ticketnumber", "createdon", "incidentid", "caseorigincode", "_customerid_value@OData.Community.Display.V1.FormattedValue"}, {"title", "ticketnumber", "createdon", "incidentid", "caseorigincode", "_customerid_value@OData.Community.Display.V1.FormattedValue"}),
    //For some reason, if there are no results, then the empty table contains error records - so need to specifically remove them
    #"Removed Errors" = Table.RemoveRowsWithErrors(Expand),
    //Finally, rename the _customerid_value field
    #"Renamed Columns" = Table.RenameColumns(#"Removed Errors",{{"_customerid_value@OData.Community.Display.V1.FormattedValue", "Customer Name"}})

in 
   #"Renamed Columns"

The comments should hopefully explain what the code is doing, but to summarise: the PowerQuery is parsing the JSON data into a tabular format, using the returned data to build column headers, and then renaming the _customerid_value field to match our requirements. There is also a logic statement in there to check if we actually have any data; and if not, then build an empty table (since JSON does not return anything that we can use if 0 records are returned).

With our updated PowerQuery, our result set should look similar to the below:

6

Nice, presentable, filtered and exactly what we need to start building our PowerBI report! 🙂

Conclusions or Wot I Think

Whilst I’ll be the first to admit the above example is rather code-heavy and would require significant tinkering to suit specific scenarios, I would argue that the approach demonstrated adheres most closely to some of the common rules when it comes to querying data for reports:

  • Only return the columns you need
  • Filter your data to reduce the number of results returned
  • Ensure that your report-side columns are giving presentable database column values

This approach is the only one adheres most closely to the above and, therefore, I feel that the extra effort is warranted; particularly when it means that those who are more familiar with CRM can stick to the tools they know best. As the default Dynamics 365 connector uses the Organization Data service, I would imagine that eventually this will be updated to use the new Web API instead. I hope that when this happens, we can begin to achieve all of the above via the PowerBI interface, as opposed to resorting to code.

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!