Functional consultants or administrators who have been using Dynamics CRM / Dynamics 365 Customer Engagement (D365CE) for any considerable length of time will likely have built up a solid collection of FetchXML queries, that are usable for a variety of different scenarios. Such privileged individuals are in the fortunate position of being able to leverage them in the following ways:

In other words, you have a range of useful queries that can potentially meet any needs within the application from a reporting standpoint. This is all well and good if you find yourself working solely within CRM/D365CE all the time, but when you start to bring in separate tools, such as Power BI, there can be some difficulty in migrating these across straightforwardly. Typically, you may find yourself staring down the barrel of a complicated and costly redevelopment exercise, where you have to invest a lot of time within Power Query to replicate your existing FetchXML queries as efficiently as possible; this puts potentially a lot of hard work and investment made into FetchXML query development down the drain almost immediately.

Fortunately, there is a way in which we can leverage our FetchXML queries using Power BI. I did a post on this very subject a few years ago, where I talked through an example from start to finish. The main limitations with this were, however, 1) the inability to return more than 5000 records at a time, given that paging was not correctly incorporated and 2) the fact that you had to manually define code for every query that you wished to utilise, which would take a lot of time to do and increase the risk of human error occurring.

As usual in these situations, the wonderful CRM/D365CE community has delivered a solution to address the first issue raised above. The Power Query (M) Builder tool is a handy plugin within the XrmToolBox that allows you to generate M query code snippets that you can use within Power BI Desktop. Most importantly, the tool incorporates a solution from Keith Mescha and the former Sonoma Partners Power BI Accelerator to get around the paging issue and allow you to return unlimited data from the application. You can find out more about the tool by checking out Ulrik “The CRM Chart Guy” Carlsson’s blog post dedicated to this very subject.

The tool is undoubtedly great, but if you have numerous FetchXML queries in a raw format that you wish to process within Power BI, it could take you some time to get these moved across into Power BI – particularly given that the tool does not currently support the ability to “bring your own” FetchXML queries. By using the example code provided by the tool, and carrying out some further work to address the second concern, it is possible to use the following M query function that will allow you to compartmentalise all of the above functionality in an easy to call Power Query function. Simply open a new blank query within Power Query and copy & paste the below into the window:

/*
    Generate FetchXML Query Results M Function
    Required Parameters:
        crmURL = The URL of your CRM/D365CE instance e.g. https://mycrm.crm11.dynamics.com
        entityName = The OData entity name that you are querying.
        query = The FetchXML query to execute. This should NOT include the top level <fetch> node, but only all subsequent nodes with double quotes escaped e.g. <entity name=""incident""><all-attributes /></entity>
    Credits: Big thanks to the Power Query Builder tool (https://crmchartguy.com/power-query-builder/) and Keith Mescha/Sonoma Partners Power BI Accelerator
             for figuring out the paging issue. Portions of the auto-generated code from the above tool is utilised within this function.
*/



let
    Func = (crmURL as text,entityName as text,query as text) =>
let
    FullURL = Text.Combine({crmURL, "/api/data/v9.1/", entityName},""),
    QueryAll = (z as text, x as number) =>
    let
                Source = Json.Document(Web.Contents(FullURL,
                        [
                            Headers=
                                [
                               #"Prefer"="odata.include-annotations=Microsoft.Dynamics.CRM.fetchxmlpagingcookie"
                                ],
                            Query=
                                [
                                fetchXml="<fetch distinct=""True"" page=""" & Text.From(x) & """ paging-cookie=""" & z & """>" & query & "</fetch>"
                                ]
                        ]
                    )
                ),
    Paging = try Xml.Document(Source[#"@Microsoft.Dynamics.CRM.fetchxmlpagingcookie"]) otherwise null,
    Retrieve = if Paging <> null 
                then List.Combine({Source[value],@QueryAll(Text.Replace(Text.Replace(Text.Replace(Uri.Parts("http://a.b?d=" & Uri.Parts("http://a.b?d=" & Paging{0}[Attributes]{1}[Value])[Query][d])[Query][d], ">", "&gt;"), "<", "&lt;"), """", "&quot;"), x + 1)})
                else Source[value]
    in 
        Retrieve,
    GenerateEmptyTable = (query as text) =>
    let
            XML = Xml.Document(query),
            #"Expanded Value" = Table.ExpandTableColumn(XML, "Value", {"Name", "Namespace", "Value", "Attributes"}, {"Value.Name", "Value.Namespace", "Value.Value", "Value.Attributes"}),
            #"Expanded Value.Value" = Table.ExpandTableColumn(#"Expanded Value", "Value.Value", {"Name", "Namespace", "Value", "Attributes"}, {"Value.Value.Name", "Value.Value.Namespace", "Value.Value.Value", "Value.Value.Attributes"}),
            #"Expanded Value.Attributes" = Table.ExpandTableColumn(#"Expanded Value.Value", "Value.Attributes", {"Name", "Namespace", "Value"}, {"Value.Attributes.Name", "Value.Attributes.Namespace", "Value.Attributes.Value"}),
            #"Filtered Rows" = Table.SelectRows(#"Expanded Value.Attributes", each ([Value.Attributes.Name] = "name")),
            #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Name", "Namespace", "Value.Name", "Value.Namespace", "Value.Value.Name", "Value.Value.Namespace", "Value.Value.Value", "Value.Value.Attributes", "Value.Attributes.Name", "Value.Attributes.Namespace", "Attributes"}),
            #"Transposed Table" = Table.Transpose(#"Removed Columns"),
            #"Promote Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
            #"Added Custom" = Table.AddColumn(#"Promote Headers", "@odata.etag", each ""),
            #"Reordered Columns" = Table.ReorderColumns(#"Added Custom", List.Sort(Table.ColumnNames(#"Added Custom"), Order.Ascending))
    in
        #"Reordered Columns",
    List = QueryAll("",1),
    Table = if List.IsEmpty(List)
        then GenerateEmptyTable(query)
        else #"D365CEData",
    #"D365CEData" = Table.FromList(List, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expand = Table.ExpandRecordColumn( #"D365CEData", "Column1", Record.FieldNames(Table.Column(#"D365CEData", "Column1"){0})),
    D365CE = Table.ReorderColumns(Expand, List.Sort(Table.ColumnNames(Expand), Order.Ascending)),
    Results = if List.IsEmpty(List)
        then Table
        else D365CE
    in
        Results
    in 
        Func

When saved, Power BI will then generate a function that should resemble the below screenshot:

From here, you can then populate each of the required parameters as follows:

And then you are good to go! As an example, the following FetchXML query:

  <entity name=""incident"">
    <attribute name=""title"" />
    <attribute name=""ticketnumber"" />
    <attribute name=""createdon"" />
    <attribute name=""incidentid"" />
    <attribute name=""caseorigincode"" />
    <attribute name=""casetypecode"" />
    <order descending=""false"" attribute=""title"" />
    <filter type=""and"">
      <condition attribute=""createdon"" operator=""this-year"" />
      <condition attribute=""casetypecode"" operator=""in"">
        <value>2</value>
        <value>1</value>
      </condition>
    </filter>
  </entity>
  

Would return results similar to the below via the above function:

One limitation with this function, at present, is that I haven’t yet found a way to ensure formatted values return correctly, even when there are no results available. I’ll report back if I figure out a way to do this šŸ™‚ A huge thanks to Keith, Ulrik and Sonoma Partners for kindly supplying the paging code snippet into the community and in helping me to build out the above function.

Earlier this month, a colleague escalated an issue to me involving Dynamics CRM/365 Customer Engagement (CRM/D365CE), specifically relating to email tracking. This feature is by far one of the most useful and unwieldy within the application, if not configured correctly. In days of yore, the setup steps involved could be tedious to implement, mainly if you were operating within the confines of a hybrid environment (for example, CRM 2015 on-premises and Exchange Server Online). Or, you could have been one of the handful of unfortunate individuals on the planet today that had to rely on the abomination that is the Email Router. We can be thankful today that Server-Side Synchronization is the sole method for pulling in emails from any manner of SMTP or POP3 mail servers; although note that only Exchange mailboxes support Appointment, Contact & Task synchronisation. Lucky though we are to be living in more enlightened times, careful attention and management of Server-Side Synchronization deployments is still an ongoing requirement. This is primarily to ensure all mailboxes operate as intended and – most critically – to ensure that only the most relevant emails are tagged back into the application, and not instead a deluge of unrelated correspondence.

Going back to the issue mentioned at the start of this post – a user in question was having a problem with certain emails not synchronising automatically back into the application, even though the emails in question had a corresponding Contact record within CRM/D365CE. We were also able to observe that other emails sentĀ fromĀ the user to theĀ Contact record(s) in question were being tagged back without issue. When first diagnosing problems like this, you can forgive yourself for not straight away making a beeline to the user’s Mailbox record within the application to verify that:

  • The Mailbox is enabled for Server-Side Synchronization for Incoming/Outgoing Email.
  • No processing errors are occurring that could be preventing emails from being successfully handled by the application.

These options can be accessed from the System Settings area of the application, on the Email tab, and define the default settings for all newly created users.

Likewise, these details can are accessible from the Mailbox record for the user concerned.

Although not likely (more often than not) to be the cause of any mail flow issues, it is worthwhile not to potentially overcomplicate a technical issue at the first juncture by overlooking anything obvious. šŸ™‚

As we can see in this example, there are no problems with the over-arching Server-Side Synchronization configuration, nor are there any problems with the individual mailbox. It is at this point that we must refer to the following screen that all users in the application have access to via the gear icon at the top of the screen – theĀ User Options screen:

TheĀ Track option allows users to specify how CRM/D365CE handles automatic email tracking, based on four options:

  • All Email Messages: Does exactly what it says on the tin, and is not recommended to leave on as default, for the reasons I alluded to earlier.
  • Email messages in response to Dynamics 365 Email: Only emails sent from within Dynamics 365 (or tracked accordingly via Outlook) will be stored in the application, alongside any replies that are received.
  • Email messages from Dynamics 365 Leads, Contacts and Accounts: Only emails which match back to the record types listed, based on email address, will be stored within the application.
  • Email messages from Dynamics 365 records that are email enabled: The same as the previous option, but expanded out to include all record types that are configured with the Sending email… option on the Entity configuration page.

For the user who was having email tracking issues, the default setting specified was Email messages in response to Dynamics 365 Email. So, to resolve the issue, it is necessary for the user to update their settings to either the 3rd or 4th option.

Any situation that involves detailed, technical configuration by end-users are generally ones that I like to avoid – for a few simple, business-relevant reasons:

  • IT/Technical teams should be the ones making configuration changes to applications, not end users who have not had training or experience on the steps they are being asked to follow.
  • End-users are busy, and it is always essential that we are conscious of their time and in making any interaction short and positive as opposed to long and arduous.
  • If the above instructions are relayed over the telephone, as opposed to in-person, then the propensity for mistakes to occur rises significantly.

However, from what we have seen so far, it will be necessary to access the application as the user to make the change – either by taking control of their session or by (perish the thought) relaying user credentials to enable someone in IT support to make the configuration change. Don’t EVER do this option by the way! Fortunately, there is a better way of updating user profile settings, using a tool whose importance has been highlighted in no uncertain terms previously on the blogthe XrmToolbox. One of the handiest out of the box tools that this provides is theĀ User Settings Utility which…well…see for yourself:

As a consequence, application administrators can “magically” modify any of the settings contained within the User Options page, including – as we can see below – theĀ Track email messagesĀ setting:

With a few clicks, the appropriate changes can be applied not just to a single user, but toĀ everyone within the application – avoiding any potential end-user confusion and making our jobs easier. This simple fact is another reason why you should immediately launch the XrmToolBox whenever you find yourself with a CRM/D365CE issue that stumps you and why the community tools available for the application are top-notch.

I’ve gone on record previously saying how highly I rate the Dynamics CRM/Dynamics 365 Customer Engagement (CRM/D365CE) community. Out of all the groups I have been a part of in the past, you couldn’t ask for a more diverse, highly passionate and – most importantly of all – helpful community. There are a lot of talented individuals out there which put a metric tonne of effort into providing the necessary tools, know-how and support to make our daily journey with CRM/D365CE that much easier to traverse.

An excellent case in point comes from the CRM DevOps extraordinaire himself, Ben Walker, who reached out me regarding my recent post on default SiteMap areas vanishing mysteriously. Now, when you are working with tools like XrmToolbox, day in, day out, the propensity towards generating facepalm moments for not noticing apparent things can increase exponentially over time. With this in mind, Ben has very kindly demonstrated a much more simplistic way of restoring missing SiteMap areas and, as he very rightly points out, the amount of hassle and time-saving the XrmToolbox can provide when you fully understand its capabilities. With this in mind, let’s revisit the scenario discussed in the previous post and go through the insanely better approach to solving this issue:

  1. Download and run XrmToolbox and select the SiteMap Editor app, logging into your CRM/D365CE instance when prompted:

After logging in, you should see a screen similar to the below:

  1. Click on theĀ Load SiteMapĀ button to load the SiteMap definition for the instance you are connected to. It should bear some resemblance to the below when loaded:

  1. Expand theĀ Area (Settings) node. It should resemble the below (i.e. no Group for Process Center):

  1. Right click on theĀ Area (Settings) node and select Add Default SiteMap Area button. Clicking this will launch theĀ SiteMap Component Picker window, which lists all of the sitemap components included by default in the application. Scroll down, select theĀ ProcessCenter option. Then, after ticking theĀ Add child components tooĀ checkbox, press OK. The SiteMap Editor will then add on the entire group node for the ProcessCenter, including all child nodes:

  1. When you are ready, click on the Update SiteMap button and wait until the changes upload/publish into the application. You can then log onto CRM/D365CE to verify that the new area has appeared successfully.

I love this alternative solution for a number of reasons. There are fewer steps involved, there is no requirement to resort to messing around with the SiteMap XML files (which has its own set of potential pitfalls, if done incorrectly) and the solution very much looks and feels like a “factory reset”, without any risk of removing other custom SiteMap areas that you may have added for alternate requirements. A huge thanks to Ben for reaching out and sharing this nifty solution and for rightly demonstrating how fantastic the CRM/D365CE community is šŸ™‚

UPDATE 02/09/2018: It turns out that there is a far better way of fixing this problem. Please click here to find out more.

I thought I was losing my mind the other day. This feeling can be a general occurrence in the world of IT, when something completely random and unexplainable happens – emphasised even more so when you have a vivid recollection of something behaving in a particular way. In this specific case, a colleague was asking why they could no longer access the list of Workflows setup within a version 8.2 Dynamics 365 Customer Engagement (D365CE) Online instance via the Settings area of the system. Longstanding CRM or D365CE professionals will recall that this has been a mainstay of the application since Dynamics CRM 2015, accessible via the Settings ->Ā Processes group Sitemap area:

Suffice to say, when I logged on to the affected instance, I was thoroughly stumped, as this area had indeed vanished entirely:

I asked around the relatively small pool of colleagues who a) had access to this instance and b) had knowledge of modifying the sitemap area (more on this shortly). The short answer, I discovered, was that no one had any clue as to why this area had suddenly vanished. It was then that I came upon the following Dynamics 365 Community forum post, which seemed to confirm my initial suspicions; namely, that something must have happened behind the scenes with Microsoft or as part of an update that removed the Processes area from the SiteMap. Based on the timings of the posts, this would appear to be a relatively recent phenomenon and one that can be straightforwardly fixed…if you know how to. šŸ˜‰

For those who are unfamiliar with how SiteMaps work within the application, these are effectively XML files that sit behind the scenes, defining how the navigation components in CRM/ D365CE operate. They tell the application which of the various Entities, Settings, Dashboards and other custom solution elements that need to be displayed to end users. The great thing is that this XML can be readily exported from the application and modified to suit a wide range of business scenarios, such as:

  • Only make a specific SiteMap area available to users who are part of theĀ Sales ManagerĀ Security Role.
  • Override the default label for theĀ Leads SiteMap area to readĀ Sales Prospect instead.
  • Link to external applications, websites or custom developed Web Resources.

What this all means is that there is a way to fix the issue described earlier in the post and, even better, the steps involved are very straightforward. This is all helped by quite possibly the best application that all D365CE professionals should have within their arsenal – the XrmToolBox. With the help of a specific component that this solution provides, alongside a reliable text editor program, the potentially laborious process of fiddling around with XML files and the whole export/import process can become streamlined so that anybody can achieve wizard-like ability in tailoring the applications SiteMap. With all this in mind, let’s take a look on how to fix the above issue, step by step:

  1. Download and run XrmToolbox and select the SiteMap Editor app, logging into your CRM/D365CE instance when prompted:

After logging in, you should be greeted with a screen similar to the below:

  1. Click on theĀ Load SiteMapĀ button to load the SiteMap definition for the instance you are connected to. Once loaded, click on theĀ Save SiteMap button, saving the file with an appropriate name on an accessible location on your local computer.
  2. Open the file using your chosen text editor, applying any relevant formatting settings to assist you in the steps that follow. Use the Find function (CTRL + F) to find the Group with the node value ofĀ Customizations. It should look similar to the image below, with the GroupĀ System_Setting specified as the next one after it:

  1. Copy and paste the following text just after theĀ </Group> node (i.e. Line 415):
<Group Id="ProcessCenter" IsProfile="false">
    <Titles>
        <Title LCID="1033" Title="Processes" />
    </Titles>
    <SubArea Entity="workflow" GetStartedPanePath="Workflows_Web_User_Visor.html" GetStartedPanePathAdmin="Workflows_Web_Admin_Visor.html" GetStartedPanePathAdminOutlook="Workflows_Outlook_Admin_Visor.html" GetStartedPanePathOutlook="Workflows_Outlook_User_Visor.html" Id="nav_workflow" AvailableOffline="false" PassParams="false">
        <Titles>
          <Title LCID="1033" Title="Workflows" />
        </Titles>
    </SubArea>
</Group>

It should resemble the below if done correctly:

  1. Save a copy of your updated Sitemap XML file and go back to the XrmToolbox, selecting theĀ Open SiteMap button. This will let you import the modified, copied XML file back into the Toolbox, ready for uploading back onto CRM/D365CE. At this stage, you can verify the SiteMap structure of the node by expanding the appropriate area within the main SiteMap window:

When you are ready, click on theĀ Update SiteMap button and wait until the changes are uploaded/published into the application. You can then log onto CRM/D365CE to verify that the new area has appeared successfully. Remember when I said to save a copy of the SiteMap XML? At this stage, if the application throws an error, then you can follow the steps above to reimport the original SiteMap to how it was before the change, thereby allowing you to diagnose any issues with the XML safely.

It is still a bit of mystery precisely what caused the original SiteMap area for Processes to go walkies. The evidence would suggest that some change by Microsoft forced its removal and that this occurred not necessarily as part of a major version update (the instance in our scenario has not been updated to a major release for 18 months at least, and this area was definitely there at some stage last year). One of the accepted truths with any cloud CRM system is that you at the mercy of the solution vendor, ultimately, if they decide to modify things in the background with little or no notice. The great benefit in respect to this situation is that, when you consider the vast array of customisation and development options afforded to us, CRM/D365CE can be very quickly tweaked to resolve cases like this, and you do not find yourself at the mercy of operating a business system where your bespoke development options are severely curtailed.

One of the great things about developing bespoke solutions for CRM is the ability to make changes to the sitemap navigation.Ā For the uninitiated, the Sitemap is this area within CRM:

Sitemap

The areas and individual buttons can be modifiedĀ to suit most requirements for organizations, to include links to custom entities, external applications or to an internal HTML/Silverlight web resource. As a result, CRM can be made to look highly bespoke and unique, as if it is a completely different CRM system altogether from the default setup.

We recently had a requirement to create a sitemap area button that would open a specific record. The record in question is one that will be updated frequently, so colleagues within the business require quick and easy access to it from the Sitemap area. We already know that this possible for opening specific entity views, as we have used this a number of times previously (for example, change the default view that opens when you click the ‘Accounts’ button to “Accounts I Follow”). MSDN provides a great outline of how you go about doing this:

To display a list of entity records within the application for a SubArea set the Entity attribute value. This displays the default view for that entity and provides the correct title and icon.

However, if you want to have a SubArea element that uses a specific initial default view, use the following Url pattern.

Url=”/_root/homepage.aspx?etn=<entity logical name >&amp;viewid=%7b<GUID value of view id>%7d”

Source:Ā https://msdn.microsoft.com/en-gb/library/gg328483.aspx#BKMK_DisplayViewInApplicationUsingSiteMap

The question at this stage then is can we adapt the above method in order to open an entity record instead? Let’s give at a go, using our trusty XrmToolbox Sitemap Editor tool. The steps below assume that you already know how to use this tool and to make amends to the sitemap area.

On the above article page, we are told that in order to open an entity record via a URL, we need to provide the following query parameters:

  • etn: The logical name of the entity
  • pagetype: In this instance, should be set to “entityrecord”
  • id: The GUID for the CRM record to open. The best way to obtain this is to export the record to Excel and unhide all the columns; the GUID is then the value in the A column; you will need to change this to Upper Case via an Excel =UPPER function:

ExcelGUID

Then, in order to ensure that the GUID is accepted correctly in the URL, we need to surround it with curly braces. As these character types are not accepted as part of a URL string, we need to provide the following substitute character strings:

{ =Ā %7B

} =Ā %7D

e.g. {E16EE6D6-56B4-E511-80E2-2C59E541BD38} ->Ā %7BE16EE6D6-56B4-E511-80E2-2C59E541BD38%7D

So let the trial and error begin! The most simple way of getting this to work would be to change the SubArea URL value to the full CRM instance URL. So, for example, our CRM Online URL would look something like this:

https://mycrminstance.crm.dynamics.com/main.aspx?etn=test_mycustomentity&pagetype=entityrecord&id=%7BE16EE6D6-56B4-E511-80E2-2C59E541BD38%7D

But if we have separate development/production environments, then this is impractical as the link will not work when moving our solution between environments. Our preferred setup therefore is to look at using a relative URL path that works across different environments.

What happens if we try adapting the URL example for views instead? So, in which case, our URL would be:

/_root/homepage.aspx?etn=test_mycustomentity&pagetype=entityrecord&id=%7BE16EE6D6-56B4-E511-80E2-2C59E541BD38%7D

SitemapURLError

That’s a no then! The next step then is to take a closer look at the example URL’s provided and making some guesswork in regards to how relative URL’s function. If we assume then that our full URL is:

https://mycrminstance.crm.dynamics.com/main.aspx?etn=test_mycustomentity&pagetype=entityrecord&id=%7BE16EE6D6-56B4-E511-80E2-2C59E541BD38%7D

Then our relative URL would be:

/main.aspx?etn=test_mycustomentity&pagetype=entityrecord&id=%7BE16EE6D6-56B4-E511-80E2-2C59E541BD38%7D

And guess what? It works! One comment to make on this though is that the record opens in a brand new window within Internet Explorer & Google Chrome, so I would therefore presume that this is the case across all browsers. There are some additional query string parameters that can be specified in the URL to make this look more like a quick-edit “pop out” window:

  • cmdbar: Setting this to “false” will hide the ribbon on the form
  • navbar: Setting this to “off” will hide the sitemap navigation bar

Our URL string and record window would therefore look this:

/main.aspx?etn=test_mycustomentity&pagetype=entityrecord&id=%7BE16EE6D6-56B4-E511-80E2-2C59E541BD38%7D&cmdbar=false&navbar=off

URLExampleWindow

The user can then make their changes to records, save and then close the window. Suffice it to say, it is good to know that it is possible to do this within CRM and that the trial and error steps involved were fairly minimal.