Dynamics 365 portals v9 solutions now available and liquid editor change

If you are running on Dynamics 365 v9.x the solutions that accompany the latest portal update which moves the portal version up a major version to v9 are now available in all geos. Solutions for portal updates needs to be installed manually or are customer driven by an administrator instead of like the portal code which is applied by Microsoft. Administrators can access this update in the Dynamics 365 Administration Center and use the solution editor for the instance.

The new solutions with the v9.x are only available for v9.x instances though, you will not have them installed if you are still on v8.2 of Dynamics 365. So be aware there are v9 only changes in the future of portals. You can read more about the documented changes on the Microsoft Support site, instructions for new features like Reset Portal and Change Base URL can be found on the Microsoft Docs site.

But not everything is documented. The big change in these solution updates first pointed out by @readyxrm (Nick Doelman) privately is the inclusion of intellisense for liquid within the Web Template editor. But that is actually just the highly visible change. The major change is the editor for Web Templates and Liquid Templating to the Microsoft Monaco Editor which is the same editor that VS Code uses. This moves away from the ACE Editor that ADXStudio had originally implemented in the product.

You can see some interesting methods in the initialization of the editor that point to a liquid parser being built for the Monaco editor and the autocomplete registration.

registerLiquidLanguage(monaco);
registerAutocompleteProvider(monaco);

Within the registerAutocompleteProvider method they have the starts of the intellisense definition. It is very basic at this point and there is no dynamic build of intellisense based on your existing liquid code and you can’t yet access standard liquid objects (user, page, sitemap, etc.) and their attributes or attributes of queried entities. It is the first implementation though and I would imagine there is more to come with later releases.

function registerAutocompleteProvider(monaco) {
	monaco.languages.registerCompletionItemProvider('liquid', {
		provideCompletionItems: () => {
			var autocompleteProviderItems = [];
			var keywords = ['assign', 'capture', 'endcapture', 'increment', 'decrement',
						'if', 'else', 'elsif', 'endif', 'for', 'endfor', 'break',
						'continue', 'limit', 'offset', 'range', 'reversed', 'cols',
						'case', 'endcase', 'when', 'block', 'endblock', 'true', 'false',
						'in', 'unless', 'endunless', 'cycle', 'tablerow', 'endtablerow',
						'contains', 'startswith', 'endswith', 'comment', 'endcomment',
						'raw', 'endraw', 'editable', 'endentitylist', 'endentityview', 'endinclude',
						'endmarker', 'entitylist', 'entityview', 'forloop', 'image', 'include',
						'marker', 'outputcache', 'plugin', 'style', 'text', 'widget',
						'abs', 'append', 'at_least', 'at_most', 'capitalize', 'ceil', 'compact',
						'concat', 'date', 'default', 'divided_by', 'downcase', 'escape',
						'escape_once', 'first', 'floor', 'join', 'last', 'lstrip', 'map',
						'minus', 'modulo', 'newline_to_br', 'plus', 'prepend', 'remove',
						'remove_first', 'replace', 'replace_first', 'reverse', 'round',
						'rstrip', 'size', 'slice', 'sort', 'sort_natural', 'split', 'strip',
						'strip_html', 'strip_newlines', 'times', 'truncate', 'truncatewords',
						'uniq', 'upcase', 'url_decode', 'url_encode'];

			for (var i = 0; i < keywords.length; i++) {
				autocompleteProviderItems.push({ 'label': keywords[i], kind: monaco.languages.CompletionItemKind.Keyword });
			}

			return autocompleteProviderItems;
		}
	});
}

They have a good list of tags and filters but there is no relationships or understanding of where they work with operators so don’t expect a lot out of this autocomplete.

This is though a big change and hopefully a very prosperous one for Dynamics 365 portals developers. Using the same editor as VS Code and the multitude of features it has with it could really open up the possibilities, both for Microsoft and for customizers to enhance it. Monaco is open source and has a well documented API that can be used to extend it.

Sadly they weren’t able to yet addressed the much loved developer shortcut to save a web template often of CTRL + S and make the code window bigger or full screen function. Maybe soon though with the investment in this new editor :).

Dynamics 365 portal: Use liquid fetchxml with paging cookie

You might be familiar already with a previous post on Use Liquid to Return JSON or XML but what if you want efficient paging included in your scenario. I have had a couple of queries of how to do this with large data sets so that the fetchxml limit of 5000 results can be exceeded or results returned in an efficient manner as possible. Fetchxml has a solution with the paging cookie and the portal natively uses this in all its entity view type queries, but you can use it as well in your custom liquid fetchxml!

Using the same method in the previous post Use Liquid to Return JSON or XML we will setup a web template that makes the fetchxml query and instead of returning HTML we will set the MIME type to application/json.

This is the stubbed in liquid code we are starting with in our web template:

{% fetchxml feed %}
  <fetch version="1.0" mapping="logical">
    <entity name="contact">
      <attribute name="firstname" />
      <attribute name="lastname" />
      <attribute name="contactid" />
      <order attribute="lastname" descending="false" />
    </entity>
  </fetch>
{% endfetchxml %}{
  "results": [
    {% for item in feed.results.entities %}
      {
        "firstname": "{{ item.firstname }}",
        "lastname": "{{ item.lastname }}",
        "contactid": "{{ item.contactid }}"
      }{% unless forloop.last %},{% endunless %}
    {% endfor %}
  ]
}

Here we are just making a simple query using the liquid fetchxml tag and returning a list of all contacts (up to 5000 with the fetchxml limit).

The problem using this just like this is that it is not getting back a limited number of results and there is no paging of records involved. To make paging efficient on large date sets Microsoft has included what is called a paging cookie in fetchxml so that you can get faster application performance. Read more about the fetchxml paging cookie on the Microsoft Docs site – Page Large Result Sets with FetchXML.

The paging_cookie property should be used with the more_results boolean property both available the results object of a fetchxml query. The code below now has updated to include both of those properties in the highlighted lines in JSON returned by the endpoint.

{% fetchxml feed %}
  <fetch version="1.0" mapping="logical">
    <entity name="contact">
      <attribute name="firstname" />
      <attribute name="lastname" />
      <attribute name="contactid" />
      <order attribute="lastname" descending="false" />
    </entity>
  </fetch>
{% endfetchxml %}{
  "morerecords": {{ feed.results.more_records }},
  "paging-cookie": "{{ feed.results.paging_cookie }}",
  "results": [
    {% for item in feed.results.entities %}
      {
        "firstname": "{{ item.firstname }}",
        "lastname": "{{ item.lastname }}",
        "contactid": "{{ item.contactid }}"
      }{% unless forloop.last %},{% endunless %}
    {% endfor %}
  ]
}

Now you can make logic decisions if to get more records based on the value of more_records and use the value of paging_cookie to provide to the fetchxml.

At this point we are going to want to include a page size or returned record count so that we aren’t getting all the records at once (to a max of 5000). To do this you want to add the count attribute to the opening fetch with a integer value as to the number of records in the page of results.

<fetch version="1.0" mapping="logical" count="10">

Now we have setup the returned JSON with all the necessary details for the UI to make choices to get more data. Now we need further enhance the liquid logic to allow the UI to pass the endpoint parameters for the page and paging cookie so you can include those in the liquid fetchxml query. To do this we need to collect both of those as query string parameters and then add them to the fetchxml query if they exist.

For the paging cookie we want some logic so that the cookie is only included when it is passed as a parameter to the endpoint. Adding the following code to the top of your web template will check the request parameters for the key 'paging-cookie' and if it has a value then setup the XML statement attribute with the value of the query string parameter.

{% assign pagingCookie = request.params['paging-cookie'] %}
{% if pagingCookie %}
  {% assign pagingCookie = ' paging-cookie="{{ pagingCookie }}"' | liquid %}
{% endif %}

Note we also have a filter at the end of the paging cookie variable assignment, the liquid filter so that liquid is executed in the assignment of the variable.

With the paging cookie and the page parameter we want to add those to the opening fetch xml tag.

<fetch version="1.0" mapping="logical"{{ pagingCookie }} page="{{ request.params['page'] | default:1 }}" count="10">

We have also applied the default filter on the request.params['page'] so that when it isn’t included as a query string parameter that it assumes you want the first page. The first page won’t also require a paging cookie.

Everything looks pretty good at this point with the exception of one gotcha. The paging cookie value is going to be XML. XML is not going to go well into a query string parameter because it includes illegal URL characters. We could solve this with the UI layer itself and translate or encode the XML as URL safe but that would require logic at the UI. We can actually encode the XML on the liquid end so that the data we pass to whatever the UI is doesn’t need to worry about any translating, just passing that same data back.

We are going to add another property to the return JSON that is the encoded version of the paging cookie. With this we need to use a liquid filter called url_escape to encode all the XML to URL friendly characters.

"paging-cookie-encoded": "{{ feed.results.paging_cookie | escape | url_escape }}",

For example the original XML paging cookie of:

<cookie page="1"><lastname last="Vermander" first="Administrator" /><contactid last="{D77E163F-4B77-E811-A960-000D3A1CA7D6}" first="{7469FD95-C0BD-4236-90BF-1D1100291DF5}" /></cookie>

Becomes:

%26lt%3Bcookie+page%3D%26quot%3B1%26quot%3B%26gt%3B%26lt%3Blastname+last%3D%26quot%3BVermander%26quot%3B+first%3D%26quot%3BAdministrator%26quot%3B+%2F%26gt%3B%26lt%3Bcontactid+last%3D%26quot%3B%7BD77E163F-4B77-E811-A960-000D3A1CA7D6%7D%26quot%3B+first%3D%26quot%3B%7B7469FD95-C0BD-4236-90BF-1D1100291DF5%7D%26quot%3B+%2F%26gt%3B%26lt%3B%2Fcookie%26gt%3B

With this you now have a JSON endpoint that supports paging with the fetchxml paging cookie and can now efficiently return any number of records with various page sizes in your portal implementations. I do always recommend you keep your page sizes reasonable for performance considerations.

Below is the completed web template example with paging cookie included in the input of the endpoint and output of JSON.

{% assign pagingCookie = request.params['paging-cookie'] %}
{% if pagingCookie %}
  {% assign pagingCookie = ' paging-cookie="{{ pagingCookie }}"' | liquid %}
{% endif %}
{% fetchxml feed %}
  <fetch version="1.0" mapping="logical"{{ pagingCookie }} page="{{ request.params['page'] | default:1 }}" count="10">
    <entity name="contact">
      <attribute name="firstname" />
      <attribute name="lastname" />
      <attribute name="contactid" />
      <order attribute="lastname" descending="false" />
    </entity>
  </fetch>
{% endfetchxml %}{
  "morerecords": {{ feed.results.more_records }},
  "paging-cookie": "{{ feed.results.paging_cookie }}",
  "paging-cookie-encoded": "{{ feed.results.paging_cookie | escape | url_escape }}",
  "page": {{ request.params['page'] | default: 0 }},
  "results": [
    {% for item in feed.results.entities %}
      {
        "firstname": "{{ item.firstname }}",
        "lastname": "{{ item.lastname }}",
        "contactid": "{{ item.contactid }}"
      }{% unless forloop.last %},{% endunless %}
    {% endfor %}
  ]
}

You can test your new service now without parameters and with parameters. Your query for the first page would follow this format, the page parameter being optional.

https://[portalname].microsoftcrmportals.com/[json-endpoint]/?page=1

Queries for next or previous pages should include the encoded version of the paging cookie.

https://[portalname].microsoftcrmportals.com/[json-endpoint]/?page=2&paging-cookie=%26lt%3Bcookie+page%3D%26quot%3B1%26quot%3B%26gt%3B%26lt%3Blastname+last%3D%26quot%3BVermander%26quot%3B+first%3D%26quot%3BAdministrator%26quot%3B+%2F%26gt%3B%26lt%3Bcontactid+last%3D%26quot%3B%7BD77E163F-4B77-E811-A960-000D3A1CA7D6%7D%26quot%3B+first%3D%26quot%3B%7B7469FD95-C0BD-4236-90BF-1D1100291DF5%7D%26quot%3B+%2F%26gt%3B%26lt%3B%2Fcookie%26gt%3B

Dynamics 365 portals: Liquid with a JavaScript framework – vue.js

If you are a new developer to Dynamics 365 portals but you are familiar with modern web development techniques then you probably know a JavaScript framework like react.js, angular or vue.js.  With the Dynamics 365 portal and liquid templates you can utilize the development approaches with those frameworks within your liquid templates to create Single Page Applications (SPAs).  Let’s take a look at a simple example to understand how you can start implementing these yourself with more complex requirements.

There are a multitude of frameworks out there, they have a lot of similarities to each of them and the concepts very similar so you should be able to apply these techniques to any of the frameworks.  For this example I will be using the popular new vue.js framework.

The benefits of a lot of JavaScript frameworks is the ability to separate the view and model so you can easily create data bound templates.  At this your point you might be like but liquid/web templates already do this for me.  They do, but they do not provide a solution beyond that, like creating events, transitions, routing, security or other convince functions/features that are within a lot of the JavaScript frameworks.  Liquid does a great job of the templates but leaves the rest for you to figure out with most likely a library like jQuery or other JavaScript libraries out there, which can get a little messy, and unstructured.

With the ability to create a REST based GET service in liquid you have the basis for creating a template with all the frameworks functionality using the development knowledge you already have.  You don’t even need to know Dynamics, or the portals, or liquid, just that you are consuming a JSON based model and rendering it with your framework of choice.  If you are not familiar with creating REST based GET service that returns JSON or XML then checkout the previous post, Dynamics 365 portals: Use liquid to return JSON or XML.

For this example we are re-using the JSON based service in FullCalendar liquid post we did a couple of months ago with a couple of additional fields included in the JSON return.  The new JSON includes the course schedule ID so we can use it to uniquely identify the records in our presentation so they can easily have events wired up to them.

[
  {% assign urlMarker = sitemarker['Event Details'] %}
  {% for item in feed.results.entities %}
    {
      "id": "{{ item.dpx_coursescheduleid }}",
      "courseid": "{{ item.dpx_courseid.id }}",
      "title": "{{ item.dpx_courseid.name }} - {{ item['instructorlink.dpx_contactid'].name }}",
      "start": "{{ item.dpx_starttime | date_to_iso8601 }}",
      "end": "{{ item.dpx_endtime | date_to_iso8601 }}",
      "color": "{{ item['instructorlink.new_colorcode'] }}",
      "textColor": "{{ item['instructorlink.new_textcolor'] }}",
      "url": "/{{ urlMarker.Url }}?id={{ item.id }}"
    }{% unless forloop.last %},{% endunless %}
  {% endfor -%}
]

Now we can build of our vue.js templates and logic in a new web template or various templates that consume this service. If you are not familiar with vue.js then you can read more about its functionality on the documentation site – https://vuejs.org/v2/guide/.

Add a new web template, named whatever you like, mine is called “Liquid – vuejs”. Within it the first item you will need to include is a reference to vue.js. For this example I am using the CDN version of vue.js which will return the latest version automatically.

<script src="https://cdn.jsdelivr.net/npm/vue"></script>

Next I am going to open the raw tag, {% raw %} which tells the liquid parser to basically ignore the tags it would normally look for when parsing and rendering liquid. This is necessary because liquid uses the same mustache brackets that the JavaScript frameworks would use as well. You can start and end the raw segments as much as you like so that you can mix your liquid functionality with the framework at the same time. The first element we put in is the script template which makes up the vue.js view.

{% raw %}
<script type="text/x-template" id="cs-default-template">
    <div v-bind:id="'cs-data'">
        <div class="alert alert-danger" v-for="error in errors" v-bind:key="error.id">
            {{ error.description }}
        </div>
        <transition name="fade">
          <div v-if="itemsLoaded && hasItems">
            <div class="col-md-4" v-for="item in items" v-bind:key="item.id">
              <div v-bind:style="{ background: item.color, color: item.textColor }">
                <p style="background: #ccc; padding: 5px; color:#000;margin:0;">
                  <span class="fa fa-calendar"></span>
                  <a href="#" v-bind:id="item.id" v-on:click.prevent="details">
                      {{ item.title }}
                  </a>
                </p>
                <p style="padding: 5px;">
                  Start: <abbr class="timeago" v-bind:title="item.start"></abbr></br>
                  End: <abbr class="timeago" v-bind:title="item.end"></abbr>
                </p>
                <div class="download-indicator pull-right">
                    <i class="fa fa-circle-o-notch fa-spin fa-fw"></i>
                    <span class="sr-only">Loading...</span>
                </div>
              </div>
            </div>
          </div>
        </transition>
        <transition name="fade">
            <div v-if="itemsLoaded && !hasItems" class="alert alert-info">
                <span class="fa fa-info-circle"></span>
                There are no course schedules available for the current record.
            </div>
        </transition>
        <transition name="fade">
            <div v-if="!itemsLoaded && !isError" class="loading text-center">
                Loading items...<br />
                <i class="fa fa-circle-o-notch fa-spin fa-fw"></i>
            </div>
        </transition>
    </div>
</script>
{% endraw %}

Within this code you will notice a lot of HTML and binding of data elements from a array collection called items. Items as you will see from our vue component will be the results of the REST JSON service. Other items to note are the v-for, v-if, and v-bind, these are all vue.js directives. Any vue.js directive will be prefixed with v-, and can apply a reactive behaviour to the DOM. There are a lot of different directives in vue.js so be sure to checkout the documentation for more.

Next we need to create some component logic. We have our template that has its view display but now we need to provide it with function and events. To do this we create a vue component. You can compose your own components with view so you can then inject them anywhere within your application, repeatedly without re-writing logic.

<script>
  Vue.component('cs-items', {
    props: {
    },
    data: function () {
        return {
            items: [],
            errors: [],
            itemsLoaded: false
        }
    },
    computed: {
        hasItems: function () {
            return this.items.length > 0;
        },
        isError: function () {
            return this.errors.length > 0;
        }
    },
    template: '#cs-default-template',
    created: function () {
        var elId = this.$root.$el.id;
        console.log('cs container -- created : ' + elId);

        $.ajax({
          method: "GET",
          url: "/liquid/calendar-json/"
        }).done(function (responseData) {
            console.log(responseData);
            this.items = responseData;
            this.itemsLoaded = true;
        }.bind(this)).fail(function (jqXHR, textStatus, errorThrown) {
            console.log("Ajax error: " + errorThrown);
            if (jqXHR.xhr.status == 401) {
                this.errors.push({ id: "401", description: "User is not authorized for the requested CS." });
            } else {
                this.errors.push({ id: "500", description: "Error retrieving CS.  Details: " + errorThrown });
            }                
        }.bind(this));
    },
    updated: function () {
        var elId = this.$root.$el.id;
        console.log('cs container -- updated : ' + elId);
        $('#' + elId + ' abbr.timeago').timeago();
    },
    methods: {
        details: function (event) {
            var elId = this.$root.$el.id;
            console.log('cs container -- details : ' + event.target.id);
        }
      }
  });
</script>

With this code we are basically registering the tag cs-items so that when we include it in HTML (<cs-items></cs-items>) the vue component engine will execute its logic against it. This vue component is figured with the template property to use our previously created template with the ID: #cs-default-template. Basically where ever you have the custom component HTML it will replace it with the template code while processing it with the vue.js engine. That engine includes events like created, updated and several more. For our purposes on create we want to populate the template with the data from our REST JSON service. The beauty of vue.js is you can determine how you want to make this call and the library you want to accomplish that with. For these purposes I have used jQuery but you could use our favorite library here.

Finally we have our component HTML that we can include anywhere in our page now and have the vue engine render it.

<div id="cs-items-01" class="cs-container">
  <cs-items></cs-items>
</div>

Vue has a number of functions that you can explore as part of creating components, props (parameters to your component), computed props, events (created, updated, etc.), methods that you can then include in your vue template and much more. I won’t go through all the vue component functionality here as it can all be found on their documentation site.

The end result of this example is we display a listing of course schedules using the REST JSON service and have an event wired up to click the details of an item. The onclick here for this sample will just get the ID of the item clicked and log it to the console but here you could execute your own functionality and call additional data services that render more content or render the view differently.

Here is the final result and the complete code of the example.

<script src="https://cdn.jsdelivr.net/npm/vue"></script>
<script src="https://use.fontawesome.com/f90e38839e.js"></script>

<div id="cs-items-01" class="cs-container">
  <cs-items></cs-items>
</div>

{% raw %}
<script type="text/x-template" id="cs-default-template">
    <div v-bind:id="'cs-data'">
        <div class="alert alert-danger" v-for="error in errors" v-bind:key="error.id">
            {{ error.description }}
        </div>
        <transition name="fade">
          <div v-if="itemsLoaded && hasItems">
            <div class="col-md-4" v-for="item in items" v-bind:key="item.id">
              <div v-bind:style="{ background: item.color, color: item.textColor }">
                <p style="background: #ccc; padding: 5px; color:#000;margin:0;">
                  <span class="fa fa-calendar"></span>
                  <a href="#" v-bind:id="item.id" v-on:click.prevent="details">
                      {{ item.title }}
                  </a>
                </p>
                <p style="padding: 5px;">
                  Start: <abbr class="timeago" v-bind:title="item.start"></abbr></br>
                  End: <abbr class="timeago" v-bind:title="item.end"></abbr>
                </p>
                <div class="download-indicator pull-right">
                    <i class="fa fa-circle-o-notch fa-spin fa-fw"></i>
                    <span class="sr-only">Loading...</span>
                </div>
              </div>
            </div>
          </div>
        </transition>
        <transition name="fade">
            <div v-if="itemsLoaded && !hasItems" class="alert alert-info">
                <span class="fa fa-info-circle"></span>
                There are no course schedules available for the current record.
            </div>
        </transition>
        <transition name="fade">
            <div v-if="!itemsLoaded && !isError" class="loading text-center">
                Loading items...<br />
                <i class="fa fa-circle-o-notch fa-spin fa-fw"></i>
            </div>
        </transition>
    </div>
</script>

<script>
  Vue.component('cs-items', {
    props: {
    },
    data: function () {
        return {
            items: [],
            errors: [],
            itemsLoaded: false
        }
    },
    computed: {
        hasItems: function () {
            return this.items.length > 0;
        },
        isError: function () {
            return this.errors.length > 0;
        }
    },
    template: '#cs-default-template',
    created: function () {
        var elId = this.$root.$el.id;
        console.log('cs container -- created : ' + elId);

        $.ajax({
          method: "GET",
          url: "/liquid/calendar-json/"
        }).done(function (responseData) {
            console.log(responseData);
            this.items = responseData;
            this.itemsLoaded = true;
        }.bind(this)).fail(function (jqXHR, textStatus, errorThrown) {
            console.log("Ajax error: " + errorThrown);
            if (jqXHR.xhr.status == 401) {
                this.errors.push({ id: "401", description: "User is not authorized for the requested CS." });
            } else {
                this.errors.push({ id: "500", description: "Error retrieving CS.  Details: " + errorThrown });
            }                
        }.bind(this));
    },
    updated: function () {
        var elId = this.$root.$el.id;
        console.log('cs container -- updated : ' + elId);
        $('#' + elId + ' abbr.timeago').timeago();
    },
    methods: {
        deleteFile: function (event) {
        },
        details: function (event) {
            var elId = this.$root.$el.id;
            console.log('cs container -- details : ' + event.target.id);

            var item = $('#' + event.target.id);
            
        }
      }
  });
  
  var vm = new Vue({
      el: '#cs-items-01'
  });

</script>
{% endraw %}

This was really meant as an introduction to using a JavaScript framework within the Dynamics 365 portal. There is a lot more you can do and future examples I will document and post including methods you can use to actually post back data with a companion app to the Dynamics 365 instance creating a complete application with the framework of your choice.

Happy Halloween! 🙂

Dynamics 365 portals: Use aggregate FetchXML queries and Chart.js

A frequently requested feature of all types of portals is to display Dynamics 365 data in different displays like charts. The Dynamics 365 portal has a pre-built chart function that utilizes the out of box CRM charting functionality so that you can take the charts you create in CRM and display then easily on the portal. This functionality is somewhat limited but using the techniques discussed in Using liquid to return JSON or XML we can easily build a services that uses FetchXML aggregate queries to build data returns to summarize or roll-up data. With the aggregated query results you can quickly work with many of the JavaScript charting libraries like d3.js, chart.js, flot.js or many others to display a rich interactive view of the data that the default chart liquid component can’t do. In this post is a simple implementation of Chart.js which is a powerful, but simple library that has a very small footprint to build some charts with the FetchXML aggregate data.

Let’s start by creating the web template that is going to return the JSON data for the chart to display. As in the previous examples we will use the fetchxml liquid tag to retrieve the data but using the aggregate function of FetchXML to summarize data. Our example is going to continue using the course schedules and instructors, for this chart we would like to get a count of schedules by instructor.

{% fetchxml feed %}
<fetch version="1.0" output-format="xml-platform" mapping="logical" aggregate="true">
  <entity name="dpx_courseschedule">
    <attribute name="dpx_coursescheduleid" alias="schedule_count" aggregate="countcolumn" />
    <attribute name="dpx_instructorid" alias="dpx_instructorid" groupby="true" />   
  </entity>
</fetch>
{% endfetchxml %}[
  {% for item in feed.results.entities %}
    {
      "count": "{{ item.schedule_count }}",
      "instructor": "{{ item.dpx_instructorid.name }}",
      "instructorid": "{{ item.dpx_instructorid.id }}"
    }{% unless forloop.last %},{% endunless %}
  {% endfor %}
]

You’ll notice this FetchXML is different from previous examples. Firstly in the opening fetch tag it removes the distinct property and adds aggregate="true". Within the entity tags the attributes are limited to one to count by with the aggregate="countcolumn" for the dpx_coursescheduleid and then because we want the count by instructor the attribute for dpx_instructorid has the property groupby="true". As in previous examples we take the results, iterate through them and create a JSON array. Also don’t forget to set the web template Mime Type to application/json and ensure that you give the return JSON template a URL using the instructions in Use liquid to return JSON or XML.

With aggregate FetchXML queries you can include filter criteria so if you want to dynamically filter the data in the aggregation you can easily add parameters that are then added as filters. You could do some cool combinations with other components that use the same technique like the previous post on FullCalendar so that as you change months or filter down your calendar data it also is filtering the chart with the same parameters.

Now that we have a JSON data return template we can build the web template that will process it with the chart.js library. Below is the basic scaffolding for our template which is a canvas HTML5 element, the chart.js Javascript library and then a document ready with the get of the canvas element.

<canvas id="myChart"></canvas>

<script src="//cdnjs.cloudflare.com/ajax/libs/Chart.js/2.5.0/Chart.min.js"></script>
<script>
  $(function(){  // document ready    
      var ctx = $("#myChart");
  });
</script>

Firstly within document ready we need to make a call to retrieve the data using a jQuery AJAX GET method to the JSON return template URL. With the returned JSON we will transform it into a chart.js data object so that it can be added easily to any chart type. To do so we are going to create a couple of arrays, one for labels and one for data values, then populate them by iterating through the resulting JSON array from the AJAX call.

$(function(){ // document ready
    $.ajax({
      method: "GET",
      url: "/courses-aggregate-json/"
    })
    .done(function( results ) {
      
      var labels = [], datavalues = [];
      
      for (i = 0; i < results.length; i++) { 
        labels.push(results[i].instructor);
        datavalues.push(parseFloat(results[i].count));
      }

      var dataobj = {
          labels: labels,
          datasets: [{
              data: countdata
          }]
      };

      var ctx = $("#myChart");

      // bind data to chart object
    });
});

With the returned data now formatted for chart.js data object we can initialize the chart element with a chart type and the data object. The chart.js options for the pie type are also set to animate its display and have the legend displayed at the bottom of the chart.

// inserted within the done of the ajax get
// after the transform of return JSON and creation of dataobj

var ctx = $("#myChart");

var myChart = new Chart(ctx, {
  type: 'pie',
  data: dataobj,
  options: {
    animation:{
        animateScale:true
    }, 
    legend: {
      display: true,
      position: "bottom"
    }
  }
});

After getting this web template a URL with a page template and web page you should end up with your pie chart. Although its probably looking a little gray as we didn’t assign any colors.

If you read the previous post on FullCalendar then we know that our instructors entity actually contains an attribute that holds a hex color code value which we could use to color the chart. With following code which should be located at the top of the script tag prior to the document ready method, we can get those color codes, as well as create a function that will find the color for a specific instructor.

{% fetchxml feed %}
  <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
    <entity name="dpx_instructor">
      <attribute name="dpx_instructorid" />
      <attribute name="dpx_colorcode" />
    </entity>
  </fetch>
{% endfetchxml %}

var instructorData = [{% for item in feed.results.entities %} {
      "instructorid": "{{ item.id }}",
      "color": "{{ item.dpx_colorcode | default:"#ffff00" }}"
    }{% unless forloop.last %},{% endunless %}
  {% endfor %}];

function findInstructorColor(lookupId) {
  for(var a = 0; a < instructorData.length; a++) {
    if(instructorData[a].instructorid == lookupId)
    {
      return instructorData[a].color;
    }
  }
}

With this code we are doing similar to the previous JSON return template but within the JavaScript so on render of the template it dynamically renders an object called instructorData which is a JSON array with the instructorid and color.

In the transform into the data object section update it with a new array called color and populate the array in the result iteration (line 6) which uses the findInstructorColor method, then include that new array in the dataobj (line 13).

var labels = [], countdata = [], color = [];

for (i = 0; i < results.length; i++) { 
  labels.push(results[i].instructor);
  countdata.push(parseFloat(results[i].count));
  color.push(findInstructorColor(results[i].instructorid));
}

var dataobj = {
  labels: labels,
  datasets: [{
    data: countdata,
    backgroundColor: color
  }]
};

With these changes you should now have a much nicer looking pie chart with color!

Using JavaScript charting libraries in combination with the FetchXML aggregate queries can allow you to start creating some really cool looking dashboards that go beyond the out of box functionality of portals. You can even use some Bootstrap components to help you do some nice formatting in combination with charts.

If your interested in learning more about liquid techniques I will be giving a webinar with xRMVirtual on April 26th at 12pm EST – Advanced Liquid Templates for Dynamics 365 portals.

Dynamics 365 portals: Implement FullCalendar with Liquid

Using the techniques discussed in Using liquid to return JSON or XML we can easily build a services that work with various with JavaScript libraries. FullCalendar is one of the most popular libraries for displaying events in various views on the web and can easily be used in the portal and creating a JSON return template for the event data. With FullCalendar you can fully customize a calendar output in the portal to really get that user experience you might be looking for. In this post is a simple implementation of FullCalendar with a custom entity JSON return template that will help outline how you can use this library in your portal projects.

First we can start with creating the web template that will be the return JSON data. Within this template a fetchxml query that takes at least the parameters of start and end so that it will filter for the date range being displayed on the calendar by adding the necessary conditions in the fetch.

{% fetchxml feed %}
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" count="100" returntotalrecordcount="true" {% if request.params['page'] %} page="{{request.params['page']}}" {% else %}
page="1"
{% endif %}>
  <entity name="dpx_courseschedule">
    <attribute name="dpx_number" />
    <attribute name="createdon" />
    <attribute name="dpx_starttime" />
    <attribute name="dpx_endtime" />
    <attribute name="dpx_courselocationid" />
    <attribute name="dpx_courseid" />
    <attribute name="dpx_coursescheduleid" />
    <filter type="and">
      <condition attribute="statecode" operator="eq" value="0" />
      {% if request.params['location'] %}
        <condition attribute="dpx_courselocationid" operator="eq" value="{{ request.params['location'] | xml_escape }}" />
      {% endif %}
      {% if request.params['start'] %}
        <condition attribute="dpx_starttime" operator="ge" value="{{ request.params['start'] | xml_escape }}" />
      {% endif %}
      {% if request.params['end'] %}
        <condition attribute="dpx_endtime" operator="le" value="{{ request.params['end'] | xml_escape }}" />
      {% endif %}
    </filter>
    <link-entity name="dpx_instructor" from="dpx_instructorid" to="dpx_instructorid" visible="false" link-type="outer" alias="instructorlink">
      <attribute name="dpx_contactid" />
      <attribute name="dpx_number" />
    </link-entity>
    <link-entity name="dpx_course" from="dpx_courseid" to="dpx_courseid" alias="courselink">
      <attribute name="dpx_coursecategoryid" />
      {% if request.params['category'] %}
        <filter type="and">
          <condition attribute="dpx_coursecategoryid" operator="eq" uiname="Category 1" uitype="dpx_coursecategory" value="{{ request.params['category'] | xml_escape }}" />
        </filter>
      {% endif %}
    </link-entity>
  </entity>
</fetch>
{% endfetchxml %}[
  {% for item in feed.results.entities %}
    {
      "title": "{{ item.dpx_courseid.name }} - {{ item['instructorlink.dpx_contactid'].name }}",
      "start": "{{ item.dpx_starttime | date_to_iso8601 }}",
      "end": "{{ item.dpx_endtime | date_to_iso8601 }}"
    }{% unless forloop.last %},{% endunless %}
  {% endfor -%}
]

The JSON is formatted to follow the FullCalendar Event Object and return an Event Source Object. Providing the return format directly in the service allows us to now easily hook up this service to the FullCalendar event configuration. The sample above only includes a number of simple properties but you can add any number of properties from the Event Object and map them to data in the query results.

Ensure that you now give the return JSON template a URL using the instructions in Use liquid to return JSON or XML and the Mime Type is set to application/json.

Now create a new web template that is going to implement the FullCalendar library and consuming the event return JSON template.

The first thing that needs to be added is references to both the CSS as well as JavaScript for FullCalendar as well as a reference to Moment.js. FullCalendar utilizes Moment.js for all date functions and therefore the Moment.js library needs to be referenced prior to the FullCalendar JavaScript library. Below is a simple template that uses the CDN’s for FullCalendar as well as Moment.js.

<link href="//cdnjs.cloudflare.com/ajax/libs/fullcalendar/3.3.1/fullcalendar.min.css" rel="stylesheet" />

<div id='calendar'></div>

<script src="//cdnjs.cloudflare.com/ajax/libs/moment.js/2.18.1/moment.min.js"></script>
<script src="//cdnjs.cloudflare.com/ajax/libs/fullcalendar/3.3.1/fullcalendar.min.js"></script>

<script>
  $(document).ready(function() {
      $('#calendar').fullCalendar({
          // FullCalendar configuration properties
      });
  });
</script>

The JavaScript in the code above initializes FullCalendar on the HTML element with the id of calendar and takes a series of parameters. You can review all the parameters available for configuration on the FullCalendar documentation. There are configuration properties for just about every function and include event callbacks that can be configured for certain events which could be utilized to make further calls to return JSON based templates.

To display event data on the calendar there are a couple of parameters that can be used to inject event data. For the following configuration we will be using the previously created web template that returns the FullCalendar Event Object based on our custom entity, but you can retrieve the data in various ways with the options that FullCalendar provides including multiple event sources.

<script>
  $(document).ready(function() {  
      $('#calendar').fullCalendar({
        events: {
          url: '/fullcalendar-json/',
          type: 'GET',
          error: function() {
            alert('there was an error while fetching events!');
          },
          color: 'yellow',
          textColor: 'black'
        }
      });
  });
</script>

Here we have replaced the blank configuration properties with the Events property which uses a url to retrieve event data. The URL is configured to our previous templates URL and the GET HTTP method is configured as the type. The event properties color and textColor are also configured for the default display of events on the calendar. After hooking up this new web template to a page template and web page you can view it in the portal and should have a result similar to the following:

Now we can take this further and enhance the display of events as well as add event filtering options. For my custom entities, I extended the Instructor with 2 additional fields, color and text color which I put in HEX color code values into for each record. I extended the fetchxml query to include these fields as well as the JSON output to add these fields as Event Object properties, below is the new JSON output including as well a URL that will link to the event details using a site marker.

{% fetchxml feed %}
<!-- fetchxml statement removed for length -->
{% endfetchxml %}[
  {% assign urlMarker = sitemarker['Event Details'] %}
  {% for item in feed.results.entities %}
    {
      "title": "{{ item.dpx_courseid.name }} - {{ item['instructorlink.dpx_contactid'].name }}",
      "start": "{{ item.dpx_starttime | date_to_iso8601 }}",
      "end": "{{ item.dpx_endtime | date_to_iso8601 }}",
      "color": "{{ item['instructorlink.dpx_colorcode'] }}",
      "textColor": "{{ item['instructorlink.dpx_textcolor'] }}",
      "url": "/{{ urlMarker.Url }}?id={{ item.id }}"
    }{% unless forloop.last %},{% endunless %}
  {% endfor -%}
]

In my FullCalendar liquid template I have add a fetchxml query so I can get a list of locations which are displayed in a drop down so users can then filter events in the calendar by this property.

{% fetchxml locationfeed %}
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="dpx_courselocation">
    <attribute name="dpx_name" />
  </entity>
</fetch>
{% endfetchxml %}

<p>
  <div class="input-group">
    <select id="course-location" class="form-control">
        <option value="" selected disabled>Select a location...</option>
      {% for item in locationfeed.results.entities %}
        <option value="{{ item.id }}">{{ item.dpx_name }}</option>
      {% endfor %}
    </select>
    <span class="input-group-btn">
      <button id="refresh-events" class="btn btn-primary">Refresh Events</button>
    </span>
    <span class="input-group-btn">
      <button id="clear-events" class="btn btn-danger">Reset/Clear</button>
    </span>
  </div>
</p>

Then the JavaScript I have added 2 events for the new refresh and clear buttons, as well I have added a data property to the events call that will inject the location value if it is selected.

$('#calendar').fullCalendar({
  events: {
    url: '/fullcalendar-json/',
    type: 'GET',
    data: function() {
      var locationId = $('#course-location option:selected').val();            
      if (locationId) {
        return {
          location: locationId
        };
      }            
      return null;
    },
    error: function() {
      alert('there was an error while fetching events!');
    },
    color: 'yellow',   // a non-ajax option
    textColor: 'black' // a non-ajax option
  }
});

$('#refresh-events').on('click', function(){
  $('#calendar').fullCalendar('refetchEvents');
});

$('#clear-events').on('click', function(){
  $('#course-location').prop('selectedIndex',0);
  $('#calendar').fullCalendar('refetchEvents');
});

The display and function of the calendar should now be enhanced with colors for each instructor and the ability to filter the course schedules by location!

Hopefully this has helped show an example of the power you can get out of liquid templates by creating your own JSON based service and utilizing a JavaScript library like FullCalendar.

If your interested in learning more about liquid techniques I will be giving a webinar with xRMVirtual on April 26th at 12pm EST – Advanced Liquid Templates for Dynamics 365 portals.