Organisations that deploy Dynamics CRM/Dynamics 365 for Enterprise (CRM/D365E) can immediately take advantage of a number of inbuilt functionality, processes and data models that can be re-purposed with minimal effort. Whilst this approach can often lead to more streamlined deployment of your CRM/D365E solution, individuals customising the system should take care not to make the system fit around a business too much; rather, the opposite must be achieved where ever possible and careful analysis should be carried out in the outset to ensure that this balance is maintained. Sacrificing sensible business processes to accommodate for the quirks of a particular business system is a major pitfall that should be avoided as part of any major IT system deployment.

A good example of this can be found in the pricing calculation engine within CRM/D365E, which is utilised by the following entities within the system:

  • Opportunity
  • Opportunity Product
  • Quote
  • Quote Product
  • Order
  • Order Product
  • Invoice
  • Invoice Product

Rather than having to implement your own logic to generate prices for these entities, businesses can choose to utilise the pricing engine to automatically generate the net total for your Products, calculate appropriate Discounts and then figure out the final total at the end.

For those who are dissatisfied with how CRM performs this calculation, you will be pleased to hear that you have the option to override the default pricing engine and specify your own via a C# plugin. More information, and a very handy code example, can be found on our good friend MSDN:

The pricing engine in Microsoft Dynamics 365 supports a standard set of pricing and discounting methods, which might be limiting to your business depending on your specific requirements for applying taxation, discounts, and other pricing rules for your products. If you want to define custom pricing for your products in opportunities, quotes, orders and invoices, you can use the CalculatePrice message.

To use the custom pricing for your opportunities, quotes, orders, and invoices:

  1. Set the value of the Organization.OOBPriceCalculationEnabled attribute to 0 (false). You can also use the Sales tab in the system settings area in Microsoft Dynamics 365 or Microsoft Dynamics 365 for Outlook to disable system pricing. More information:  Configure product catalog information
  2. Create a plug-in that contains your custom pricing code for calculating the price for your opportunity, quote, order, or invoice.
  3. Register the plug-in on the CalculatePrice message.

Source: https://msdn.microsoft.com/en-us/library/dn817885.aspx

I think the most key thing as part of the above is not to overlook the simplest step – namely, modifying the setting within CRM/D365E that lets you specify your custom pricing engine in the first place. If this is not set, then you may spend many hours trying to figure out why your beautifully developed plugin is not working! It can be found very straightforwardly in Administration area of the application, on the System Settings page:

Whilst the code example provided by Microsoft gives you a good flavour of what you can potentially achieve with your own custom logic, I thought I would share two further examples that I recently was involved in developing, which may also prove useful when putting together your own custom pricing engine.

Calculating Custom Fields/Attributes

Arguably one of the biggest benefits of implementing your own custom pricing engine is being able to incorporate additional fields as part of the calculation. A recent real life example best demonstrates this. I was implementing a quoting solution for a business within Dynamics CRM 2015. The organisation was, fortunately, able to utilise much of the out of the box functionality within CRM as part of their existing processes. The only caveat was that they wanted the ability to add a Margin value at the Order level, in a similar vein to the Discount fields currently on the Quote entity – a Discount value and a Discount percentage value. The organisation wanted the option to do both, either or neither i.e. have the ability to specify a Margin value AND an additional percentage on top of that.

After configuring the appropriate fields within CRM to store both a currency value for the Margin and a decimal value for the Margin (%), we then proceeded to write some custom code that would achieve this aim. A snippet of this can be found below, which takes an existing total value of all Products on a Quote and then applies the correct calculation. It is worth explaining that the system returns NULL values if there is no data in the field when using the GetAttributeValue method (a fact I was already well aware of), which is why we have to specifically set the variables with a default value of 0 and perform the NULL check:

decimal margin = 0;
decimal marginPercent = 0;

if (quote.GetAttributeValue<Money>("new_mycustommarginamountfield") != null)
    {
        margin = quote.GetAttributeValue<Money>("new_mycustommarginamountfield").Value;
    }

if (quote.GetAttributeValue<Money>("new_mycustommarginpercentagefield"))
    {
        marginPercent = quote.GetAttributeValue<decimal>("new_mycustommarginpercentagefield");
    }

//Calculate margin amount based on the total amount

total = total + margin;
quote["totalamountlessfreight"] = new Money(total);
service.Update(quote);

//Calculate margin percentage based on the total amount

decimal marginPercentVal = marginPercent / 100 * total;
total = total + marginPercentVal;
quote["totalamountlessfreight"] = new Money(total);
service.Update(quote);

Calculating Sales Tax

CRM/D365E makes the assumption that tax will always be calculated on the Product Detail level. That’s why the Quote Product, Opportunity Product, Order Product and Invoice Product entities have a Tax field, demonstrated below on the Quote Product form:

There are a few problems with this, however:

  • You cannot set a default Tax for each Product in the system. What this means is that you have to drill down to every Product details entity and populate the Tax manually. Whilst you could look at a Business Rule, Workflow or some custom code to get around this issue, this seems like a rather complicated solution to something that you would expect to be easy to configure.
  • My experience indicates that most companies in the UK calculates tax on the gross amount of an order, and not at an individual Product level. Attempting to try and change a common practice to fit around a business system is a good example of what I spoke about in the introduction to this blog post.
  • Generally, most organisations will work with a flat rate of Tax for all Products (unless they are dealing with other countries). With this in mind, it seems a little crazy having to set this on an individual basis.

By using our own custom calculation logic, we can get around the above and implement a solution that best meets our need. For example, here is a code snippet that will take the total value of all Products on the Order entity and then calculate the VAT tax amount at 20%, saving the tax-only amount and the Net Total back to the system:

decimal vat = 0.20m;
decimal total = 0;
decimal tax - 0;

for (int i = 0; i < ec.Entities.Count; i++)
    {
        total = total + ((decimal)ec.Entities[i]["quantity"] * ((Money)ec.Entities[i]["priceperunit"]).Value);
        (ec.Entities[i])["extendedamount"] = new Money(((decimal)ec.Entities[i]["quantity"] * ((Money)ec.Entities[i]["priceperunit"]).Value));
        service.Update(ec.Entities[i]);
    }

//Calculate total from tax

tax = total * vat;
total = total + tax;
order["totaltax"] = new Money(tax);
order["totalamount"] = new Money(total);                
service.Update(quote);

It would be disingenuous of me not to point out that the above solution has its own faults – the biggest one being that your code would require manually updating if the tax rate ever changes in the future. You can perhaps get around this issue by instead storing the current tax rate within a CRM entity, that can be updated in line with any future changes. Your plugin could then query this entity/attribute each time the plugin is executed.

Conclusions or Wot I Think

Whilst the ability to override a key feature within CRM/D365E is incredibly welcome and a great example of how you can leverage the system without compromising on your existing business processes, it is arguable that this process is hardly straightforward. A passing knowledge of C# is mandatory to even begin to start implementing your own custom pricing engine, as well as good awareness of how CRM/D365E plugins work. This is all stuff that an administrator of the application may struggle to grasp, thereby requiring dedicated resource or knowledge within the business to implement the desired solution. It would be nice to perhaps see, as part of a future version of D365E, the ability to specify a custom pricing engine within the application itself – similar to how Business Rules were introduced and reduced the need for form-level JScript functions to achieve common tasks. Nevertheless, it has been good to discover that the CalculatePricing message is exposed within the application and that the application has the flexibility for end users to modify (and perhaps improve upon 🙂 ) one of its key features.

The single biggest challenge when developing a reporting solution is data. Invariably, you won’t always have one database that contains all the information you need; often, you will need to bring across disparate and unrelated data sources into a common model. This problem can be exasperated if your organisation has a number of application or database systems from different vendors. Finding a tool that can overcome some of these hurdles is a real challenge. For example, whilst I am a huge fan of SQL Server Reporting Services, the out of the box data connection options are generally limited to common vendor products or ODBC/OLE DB data sources. Case in point: Finding or even developing a Data Source that can support a JSON stream can be troublesome and complicated to configure. With the landscape as such, the reporting tool that can offer the most streamlined method of overcoming these challenges is the tool that is going to win the day.

Following on from my deep-dive with the product last year, I have been working more and more with PowerBI in recent weeks. What I like most about the tool is that a lot of the hassle is taken out of configuring your data sources. PowerBI does this by leveraging the existing Power Query language and equipping itself with a large number of Data Source Connectors. The most surprising aspect of this? Microsoft products form only a subset of the options available, with connectors in place for many of competitor products from the likes SAP, SalesForce and Oracle. In my limited experience with the product to date, I have yet to find a data source that it does not support, either as part of a data connector or a manual Power Query.

A recent work example can best illustrate the above, as well as showcasing some of the built-in functionality (and learning curves!) that come to working with data via Power Query and writing Data Analysis Expressions (DAXs). There was a requirement to generate an internal department dashboard for an IT service desk. The dashboard had to meet the following key requirements:

  • Display a summary of each team members movements for the current week, ncluding the location of each person on that current day. Each member of the team was already recording their weekly movements within their Exchange calendar as all day appointments, configuring the Subject field for each appointment accordingly. For example, In OfficeWorking at Home etc. No other all day appointments were booked in the calendars.
  • Query Dynamics CRM and return data relating to Active/Inactive Case records.
  • To be displayable on a TV/Screen, refresh automatically and be exportable as a .pdf document or similar.

A CRM Dashboard can achieve about 50-60% of the above, but the key requirements of querying Exchange and exporting the dashboard are much more tricky; whilst it is certainly possible to perform web service requests from within CRM to Exchange, the process would be so convoluted to implement that is arguably not worth the effort. Likewise, CRM is not particularly friendly when it comes to printing out Dashboards, as you often left to the mercy of the web browser in question. With all this in mind, we decided that PowerBI was the best option and proceeded to bring all the data together using PowerQuery.

We first used the out of the box Exchange connector to query each person’s mailbox for all Calendar items, performing two transformations on the data. First, we filtered the result to return Calendar items from the current week and, second, we added a column to identify which Calendar the record derives from (as there is no field on each record to determine this). We’ll see why this is required in a few moments:

let
    Source = Exchange.Contents("john.smith@domain.com"),
    Calendar1 = Source{[Name="Calendar"]}[Data],
    #"Filtered Rows" = Table.SelectRows(Calendar1, each Date.IsInCurrentWeek([Start])),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Owner", each "John Smith")
in
    #"Added Custom"

Next, we combined all Calendars together into one table – again using Power Query. Table.Combine is a comma separated list of all tables you want to merge together, so you can add/remove accordingly to suit your requirements. We also take this opportunity to remove unnecessary fields and convert our Start and End field values to their correct type:

let
    Source = Table.Combine({Calendar1, Calendar2, Calendar3}),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Folder Path", "Location", "DisplayTo", "DisplayCc", "RequiredAttendees", "OptionalAttendees", "LegacyFreeBusyStatus", "IsReminderSet", "ReminderMinutesBeforeStart", "Importance", "Categories", "HasAttachments", "Attachments", "Preview", "Attributes", "Body"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Start", type date}, {"End", type date}})
in
    #"Changed Type"

Our CRM data is returned via an adapted version of the query used previously on the blog, taking into account the benefits of using a Saved Query as opposed to FetchXML. No further work is required to manipulate the data once in PowerBI, so this won’t be covered any further. Our issue is now with the Exchange Calendars. Because the appointments in the Calendars to indicate each persons movement are set as All Day appointments spanning multiple days, we have no way of extrapolating the days in between to determine whether it is the current day. So for example, if the all day Appointment starts on Monday and ends on Wednesday, we have Monday and Wednesday’s date, but not Tuesday’s. We, therefore, need to find a solution that determines whether the appointment falls on a specific day of the week – Monday, Tuesday, Wednesday, Thursday or Friday.

Our first step is to generate a date table covering the entire period we are concerned with. Using this very handy query, we can set up a PowerBI function that will allow us to generate just that – in this case, for the whole of 2017:

Why do we need this? Because we need to determine for each date in 2017 what day it falls on. For this reason, we now take off our Power Query hat and jam on our DAX one instead 🙂 Close & Apply your queries in PowerBI and then navigate to your new date table. Add a new Column, using the following DAX formula to populate it:

Day of Week (Number) = WEEKDAY('2017'[Date], 2)

The WEEKDAY function is an incredibly handy function in this regard, enabling us to determine the day of the week for any date value. Nice! We can now go back to our “unified” calendar, and perform the following modifications to it:

  • Add on a column that returns a TRUE/FALSE value for each row on our calendar, which tells us if the Start, End or any date between these values falls on a specific day. So, for our IsMondayAllDay field, our DAX formula is below. This will need to be modified accordingly for each subsequent column, by incrementing 1 on the ‘2017’[Day of Week (Number)], 1 bit by 1 for Tuesday, 2 for Wednesday etc.:

IsMondayAllDay = IF(AND(CONTAINS(CALENDAR([Start], IF([End] = [Start], [End], [End] - 1)), [Date], DATEVALUE(LOOKUPVALUE('2017'[Date], '2017'[Week Number], FORMAT(WEEKNUM(AllCalendars[Start], 2), "General Number"), '2017'[Day of Week (Number)], 1))), AllCalendars[IsAllDayEvent] = TRUE()), "TRUE", "FALSE")

  • A calculated column that tells us whether the current row is today, by referencing each of our fields created in the subsequent step. Similar to above, a TRUE/FALSE is returned for this:

IsToday = IF(([IsMondayAllDay] = "TRUE" && WEEKDAY(TODAY(), 2) = 1) || ([IsTuesdayAllDay] = "TRUE" && WEEKDAY(TODAY(), 2) = 2) || ([IsWednesdayAllDay] = "TRUE" && WEEKDAY(TODAY(), 2) = 3) || ([IsThursdayAllDay] = "TRUE" && WEEKDAY(TODAY(), 2) = 4) || ([IsFridayAllDay] = "True" && WEEKDAY(TODAY(), 2) = 5), "TRUE", "FALSE")

We now have everything we need to configure a Measure that can be used on our Dashboard – the Subject of the calendar appointment and a way of indicating that the appointment is today. So our final DAX formula would be as follows for John Smith:

John's Location (Today) = LOOKUPVALUE(AllCalendars[Subject], AllCalendars[IsToday], "TRUE", AllCalendars[Owner], "John Smith")

Now, it is worth noting, that the above solution is not fool-proof. For example, if a person has multiple All Day Appointments configured in their Calendar, then it is likely that the Measure used above will fall over. Giving that this is unlikely to happen in the above scenario, no proactive steps have been taken to mitigate this, although you can certainly implement a solution to address this (e.g. use the MAX function, only return records which contains “Working”, “Office” or “Home” in the Subject etc.). Nevertheless, I feel the above solution provided an effective “crash course” in a number of fundamental PowerBI concepts, including:

  • PowerQuery data retrieval and manipulation
  • PowerBi Functions
  • DAX Formulas and the difference between Measures and Calculated Columns

As a colleague recently said to me, “I think we will be using PowerBI a lot more in the future”. This is something that I would certainly agree with based on my experience with it so far 🙂

One of the dangers when working as part of a specific role within a technology-focused occupation is that a full 360-degree knowledge of an application, and some its more subtle nuances, can often be lacking. This is particularly true for those who work with Dynamics CRM/Dynamics 365 for Enterprise (CRM/D365E). For example, most people can say with confidence how an entity is created but may be less familiar with the process of removing an entity and the list of things you need to watch out for as part of this process. This can be exacerbated if your role is involved primarily with “build a solution” projects, where often you will build out components and hand it over to a customer; it is unlikely that, after this point, you will have much involvement on how the solution shapes, evolves and also (arguably) some of the challenges that can be faced when working with the application on a daily basis. This is, I would argue, essential experience and an area that should be addressed if you feel it is lacking in some way.

I encountered a strange issue recently when performing a tidy-up of an existing Dynamics CRM 2016 solution. The work involved “breaking up” the solution into more logical groupings, based on business functions. After getting these solution components archived into an unmanaged solution, we then proceeded to delete the components from the primary solution file, one by one.

Deleting solution components can often be a laborious process, thanks to the applications Dependent Components feature, which prevents you from deleting in-use components from the application. Whilst no doubt a highly beneficial feature to have in place, it can prove to be difficult to unwrap in practice. Assuming your team/business is following sound principals relating to change management, as I have argued for previously on the blog, all CRM Administrators/Customisers should have some experience of doing this. For those who have yet to take the plunge, though, it’s important to remember the following:

  • Dependent Components include pretty much everything that you can find with a Solution file, so you won’t need to look further afield in order to track down any components.
  • Relationships can be the trickiest component to remove dependencies for, as the Lookup field on the related entity will need to be removed from all forms and views first.
  • Certain components may need to be deactivated first before they can be safely deleted. For example, Workflows and Business Process Flows.

I definitely prefer CRM/D365E having this feature in place, but it can feel like a double-edged sword at times.

Going back to the task at hand, we were close to getting all the entities that needed deleting completed, but we encountered the following issue when deleting an entity:

The Component Type and Dependency Type fields were the only fields populated with information – Sdk Message Processing Step and Published respectively –  so we were initially left stumped at what the issue could be. We did some digging around within CRM to see what the problem is, by first of all querying Advanced Find to return all of the Sdk Message Processing Steps records for the entity concerned. There were two records that caught our attention:

Both records do not have a Name field populated, just like the Dependent Components highlighted above, and also contain the following useful Description value – Real-time Workflow execution task. This would immediately suggest that the issue relates to a Workflow of some description. But we had already deactivated/deleted all workflows that reference the Entity or its Attributes.

After some further research and a good night sleep, I came back to the issue and remembered some obscure information relating to Business Rules from MSDN and the how they are stored in CRM:

The following table describes relevant process trigger entity attributes.

SchemaName Type Description
ControlName String Name of the attribute that a change event is registered for. For other events this value is null.
ControlType Picklist Type of the control to which this trigger is bound.

The only valid value for this release is 1. This indicates that the control is an attribute. This value only applies when the ControlName is not null.

Event String There are three valid values to indicate the event:

  • load
  • change
  • save
FormId Lookup ID of the form associated with the business rule.

This value is null when the rule applies to all forms for the entity that supports business rules.

IsCustomizable ManagedProperty Information that specifies whether this component can be customized.

You cannot change process trigger records included in a managed solution when the IsCustomizable.Value is false.

PrimaryEntityTypeCode EntityName Logical name for the entity that the business rule is applied on.
ProcessId Lookup ID of the process.
ProcessTriggerId Uniqueidentifier ID of the process trigger record.

From the applications point of view, it appears that Business Rules are treated the same as the more typical Processes. This theory is backed up by the fact that Business Rules have to be explicitly Activated/Deactivated, just like a Workflow, Action or other types of process. After going back to the Entity to double-check, we confirmed that there were indeed two Active Business Rules configured; and, by deleting them and checking Dependent Components again, we were safely able to the delete the Entity.

When attempting to reproduce this issue within a test environment, later on, I was able to clarify that the issue does not occur all of the time. From the looks of it, both of the Entities that we were attempting to delete the above had a relationship and the Business Rules in question were directly referencing the Lookup field. So, when reproducing the issue with a standard Business Rule configured (i.e. not referencing any lookup field), I was able to delete the entity successfully. So it is good to know that it is a rare issue and one that will not be commonplace whenever you need to delete an entity. Nevertheless, this issue demonstrates clearly the importance of familiarising yourself regularly with scenarios with CRM/D365E that you are not generally exposed to, within a testing environment or similar. Doing this will almost certainly throw up a few things that you can learn at the end of it and better equip yourself for any problems you may face in the future.

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

Often, when working with the latest features as part of Dynamics CRM/Dynamics 365 for Enterprise (CRM/D365E), it very much feels like handling a double-edged sword. Whilst the functionality they bring to the table is often impressive, there is typically scant information available when things go wrong – either through official channels or via online forum/blog posts. This can often act as a major barrier to early adoption, particularly when the business benefit of such adoption far outweighs any wasted time or extraordinary effort.

One of these new features is Word Templates, a feature that I have blogged about previously – particularly in how they compare against the more traditional SQL Server Reporting Services (SSRS) Reports that CRM/D365E offer developers. They present CRM customisers a much more familiar and equally powerful means of creating common templates that can be run against specific CRM record types, providing largely similar functionality compared to SSRS Reports. For those who have not been fortunate enough to have previous experience using SSRS, Word Templates present a far more accessible and friendly approach to addressing documentation needs within CRM/D365E.

When configuring access to Word (and indeed Excel) Templates for your users, you generally only need to be concerned with two set of permissions – adequate level Read permissions against the entity that the template is being run against (Lead, Account etc.) and the Document Generation privilege, in the Business Management area on the Security Role window:

When we were recently attempting to setup access to Word Templates for a specific group of users on CRM 2016 Online (8.1), we first verified that all of the above privileges had been granted – but we still encountered a strange issue when attempting to generate the Word Template:

This message should be familiar to those who work with the application frequently, as it generally is one of the error types that is generally thrown back when a database error has occurred – typically a timeout error or similar. What is distinctly not familiar about the above is the fact that we are unable to select the Download Log File button. Doing so would generally present us with a sufficiently detailed error message about the underlying problem. Without this, the issue becomes significantly more tricky to diagnose.

Before escalating the issue further with Microsoft. we were able to diagnose and observe the following:

  • Users that had been assigned the System Administrator security role were able to generate the template without issue
  • Users within the root Business Unit, likewise, had no issue generating the template
  • We were unable to replicate the issue within a separate environment, that had been configured the exact same way (same Business Units, Security Roles etc.)
  • When running a Fiddler trace whilst reproducing the issue, nothing additional error message wise was exposed behind the scenes. Fiddler, for those who are unaware, is one of the best tools to have in your arsenal when diagnosing web service request issues in CRM. As the act of generating a Word Template would (presumably) cause a web service request, it was hoped that something additional clues could be gathered by using Fiddler.

Due to the limitations of CRM 2016 Online compared with On-Premise CRM 2016 (i.e. we had no way in which we could interrogate the SQL database for the instance), we had to escalate the case to Microsoft in order to provide a resolution. And, as is generally the case in these matters, the proposed solution was informative and surprising in equal measure.

The support engineer assigned to the case took a copy of our instance database and deployed into a test environment, to see if the issue could be reproduced. They also have the benefit of being able to access information regarding the instance that I would imagine most CRM Online administrators would give their right hand for 🙂 Because of this, we were able to determine the underlying error database error message that was causing the problem:

The data type image cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable

The error is currently an acknowledged bug in CRM, which is targeted for resolution early in 2017. In the meantime, the engineer pointed me towards a tool that I was previously unaware of – the Dynamics CRM Organization Settings Editor. I was already aware that there are a number of settings relating to a CRM/D365E organisation that can be modified via PowerShell/cmd line executable for On-Premise deployments only. These settings can achieve a number of potentially desirable changes to your CRM Organisation, some of which cannot be achieved via the CRM interface alone – such as changing whether emails are sent synchronously or asynchronously (SendEmailSynchronously) or modifying the number of elements that are displayed in the tablet app, such as fields (TabletClientMaxFields), tabs (TabletClientMaxTabs) and lists (TabletClientMaxLists). In order to make these changes, there is a tool that you can download from Microsoft that enables you to change these settings via a cmd line window – but, arguably, the Dynamics CRM Organization Settings Editor is a far simpler tool to use, given that it is a managed solution that enables you to modify all of the settings from within CRM/D365E. Regardless of which tool you use, the solution suggested by the support engineer (which resolves the problem, incidentally) is outlined below, using the Dynamics CRM Organisation Settings Editor. It is worth noting that, although the KB article above does not specifically reference this, I was advised that using the tool is unsupported by Microsoft. Therefore, any changes – and issues that may be caused as a result – are made at your own risk:

  1. Install the managed solution file into CRM and then open up the Solution. You will be greeted with the Configuration page, which should look similar to the below:
  2. Navigate to the EnableRetrieveMultipleOptimization setting, which should be set to the default of not set.
  3. Click Add to modify the setting. You will be asked to confirm the change before it will take effect:
  4. Once you click OK, the default value of 1 will be applied to this setting. Fortunately, this is the precise setting we need to get things working, so verify that this is indeed set to 1 on your instance and then close out of the solution:

Now, when you refresh CRM for the affected user, you should be able to download the Word Template without issue.

Although we can expect this error to be resolved shortly as part of the next update to D365E, hopefully, the above workaround will help others who come across the same issue and allow you to use Word Templates without issue within your CRM/D365E environment 🙂