Dynamics 365 portals: Use liquid to return JSON or XML

For many business requirements to get a desired user experience you may push the limits that the entity list functionality provides. With liquid you can write your own service to return data in various formats using the web template Mime Type property. By returning your own data you can inject logic and specific formatting using liquid functionality, this will allow you to utilize new components or libraries to help you provide the specific experience your requirements demand. This post will look at how liquid can be used with web templates to return JSON or XML so that the data can be consumed and used to build a complex user experience.

With liquid there are many ways to query for data. There is the entities object which can be used to retrieve a single record by ID. To get lists of data you can use the entity list which behind is using one or many entity views and an entity view is a fetchxml query that defines the view. Entity list contains a feature, OData feed, that allows you to take an entity view and make it available as a service. The OData feed is a great way to get a RESTFul JSON return but it has many shortcomings. If your interested in trying out the OData feed functionality then check out the documentation still available on the Adxstudio Community site – Entity List OData Feeds.

If you want to directly write your own queries in web templates, perhaps dynamically constructing them, utilize entity permission relationship based data, then you can use the liquid fetchxml tag. Below is a little outline of the functions of this liquid object.

{% fetchxml my_query %}
  <fetch version="1.0" mapping="logical">
    <!-- Write FetchXML here, use Liquid in here if you want, to build XML dynamically. -->
  </fetch>
{% endfetchxml %}
 
{{ my_query.xml | escape }}
{{ my_query.results.total_record_count }}
{{ my_query.results.more_records }}
{{ my_query.results.paging_cookie | escape }}
{% for result in my_query.results.entities %}
  {{ result.id | escape }}
{% endfor %}

Reference: Adxstudio Community Forums

With Dynamics 365 portals entity permissions is required by default and does not need to be referenced in the liquid tag. This differs from Adxstudio Portals v7.x, so if you are getting blank results using the fetchxml liquid object then ensure to first validate your entity permissions.

The fetchxml liquid tag and web templates Mime Type functionality provide the ability to build a web template that returns custom JSON or XML objects. With this you can build endpoints that intake custom parameters, perform logic while constructing the query, logic in returning the results, formatting the results and doing related record queries, all the while adhering to the entity permissions in place for that entity.

Below is an example of a web template that queries a custom course schedule entity with joins to related entities, course and instructor. The liquid code looks for 2 parameters location and category and if they exist it adds the fetch conditions for those parameters.

{% fetchxml feed %}
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" count="10" 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_accountid" />
    <attribute name="dpx_cost" />
    <attribute name="dpx_coursescheduleid" />
    <order attribute="createdon" descending="true" />
    <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 %}
    </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_level" />
      <attribute name="dpx_lengthunit" />
      <attribute name="dpx_length" />
      <attribute name="dpx_coursecategoryid" />
      {% if request.params['category'] %}
        <filter type="and">
          <condition attribute="dpx_coursecategoryid" operator="eq" value="{{ request.params['category'] | xml_escape }}" />
        </filter>
      {% endif %}
    </link-entity>
  </entity>
</fetch>
{% endfetchxml %}{
  "totalcount": {{ feed.results.total_record_count }},
  "morerecords": {{ feed.results.more_records }},
  "page": {{ request.params['page'] | default: 0 }},
  "results": [
    {% for item in feed.results.entities %}
      {
        "starttime": "{{ item.dpx_starttime | date_to_iso8601 }}",
        "endtime": "{{ item.dpx_endtime | date_to_iso8601 }}",
        "instructorname": "{{ item['instructorlink.dpx_contactid'].name }}",
        "courselevel": "{{ item['courselink.dpx_level'].label }}",
        "location": {
          "id" : "{{ item.dpx_courselocationid.id }}",
          "name": "{{ item.dpx_courselocationid.name }}"
        }
      }{% unless forloop.last %},{% endunless %}
    {% endfor -%}
  ]
}

The fetchxml result is then formatted into a JSON object using the forloop liquid object to iterate through each entity record. Linked entity attributes are easily accessed via the linked entity alias {{ entityRecord['alias.attribute'] }}. With the return being JSON you will want to set the Mime Type property to application/json.

Another example using Case (incident) where we also use a N:N relationship of all child cases in the custom data object returned with the referenced attribute to load the entity relationship.

{% fetchxml feed %}
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" count="10" returntotalrecordcount="true" {% if request.params['page'] %} page="{{request.params['page']}}" {% else %} page="1" {% endif %}>
  <entity name="incident">
    <attribute name="ticketnumber" />
    <attribute name="prioritycode" />
    <attribute name="title" />
    <attribute name="createdon" />
    <attribute name="customerid" />
    <attribute name="ownerid" />
    <attribute name="statecode" />
    <attribute name="incidentid" />
    <attribute name="caseorigincode" />
    <order attribute="title" descending="false" />
  </entity>
</fetch>
{% endfetchxml %}{
  "totalcount": {{ feed.results.total_record_count }},
  "morerecords": {{ feed.results.more_records }},
  "page": {{ request.params['page'] | default: 0 }},
  "results": [
    {% for item in feed.results.entities %}
      {
        "ticketnumber": "{{ item.ticketnumber }}",
        "title": "{{ item.title }}",
        "customer":  {
          "id" : "{{ item.customerid.id }}",
          "name": "{{ item.customerid.name }}"
        },
        "incident_parent_incident": [
            {% for parent in item.incident_parent_incident.referenced %}
              {
                "parentticketnumber": "{{ parent.ticketnumber }}"
              }{% unless forloop.last %},{% endunless %}
            {% endfor %}
          ]
      }{% unless forloop.last %},{% endunless %}
    {% endfor -%}
  ]
}

If you instead wanted to return XML then it is just a matter of updating the Mime Type of the web template to application/xml and the code to output XML instead of the JSON format. Below is a sample of the first example but returning XML.

<!--FETCHXML query -->
{% endfetchxml %}<?xml version="1.0" encoding="UTF-8" ?>
<fetchxmlquery>
  <totalcount>{{ feed.results.total_record_count }}</totalcount>
  <morerecords>{{ feed.results.more_records }}</morerecords>
  <page>{{ request.params['page'] | default: 0 }}</page>
  <results>
    {% for item in feed.results.entities %}
      <item>
        <starttime>{{ item.dpx_starttime | date_to_iso8601 }}</starttime>
        <endtime>{{ item.dpx_endtime | date_to_iso8601 }}</endtime>
        <instructorname>{{ item['instructorlink.dpx_contactid'].name }}</instructorname>
        <courselevel>{{ item['courselink.dpx_level'].label }}</courselevel>
        <location>
          <id>{{ item.dpx_courselocationid.id }}</id>
          <name>{{ item.dpx_courselocationid.name }}</name>
        </location>
      </item>
    {% endfor %}
  </results>
</fetchxmlquery>

Once you have setup your web template with your liquid logic and Mime Type you need to get a URL for it. Create a page template of the Type, Web Template, and the previous Web Template referenced. As well ensure that Use Website Header and Footer is unchecked so that all that is returned is the data formed by the web template.

Now using the portals front-end editor, create a new web page using the new page template that references the web template. This will give your web template a URL and you can now refer to this endpoint within other JavaScript on the site. Here is a small jQuery sample calling a JSON endpoint URL and logging the result to the browser console. You can also do testing of your endpoints with Postman.

<script>
  $(function(){
    $.ajax({
      method: "GET",
      url: "/cases-json/"
    })
    .done(function( msg ) {
      console.log(JSON.parse(msg));
    });
  });
</script>

Note for entity permissions to function beyond anonymous a cookie for authentication must be attached to the request. JavaScript on the site already making requests will include the necessary cookie by default.

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.

13 thoughts on “Dynamics 365 portals: Use liquid to return JSON or XML

  1. This looks like it would do exactly what I need, but I can’t get it to work. I built a web template, page template, and web page as described but always get a blank result set no matter what fetchxml I try to use. Here’s my full web template code:
    {% fetchxml portalQuery %}

    {% endfetchxml %}
    [
    {% for item in portalQuery.results.entities %}
    { “contactid”:”{{ item.contactid }}”,
    “firstname”:”{{ item.firstname }}”,
    “lastname”:”{{ item.lastname }}”
    }
    {% endfor %}
    ]
    I’ve trimmed it down to a simple static query and eliminated paging, etc, to rule anything out, but I always get a response of an empty array “[]”. I also built a plugin and attached it to the retrievemultiple event on contact to determine whether or not the query was actually executing. Te plugin did execute, and in the plugin context InputParameters list, I had a Query object. What’s interesting here is that the object was a QueryExpression type, not FetchExpression as I would expect, and the selected attributes and conditions did not match my original fetchxml. What am I doing wrong?

    Like

    1. Check that you have created entity permissions and assigned the web roles to the entity permission. If your service is meant to be public (anonymous) as well as authenticated then ensure you add both roles, not just anonymous.

      Like

    2. It just give me the page filled with Json as below, however I am unable to retrieve it elsewere using the URL + GET method..
      [
      {
      “count”: “7”,
      “instructor”: “Sprint”,
      “instructorid”: “c5b1d45e-074a-e211-b30c-78e3b508f827”
      }
      ]

      In another web template I am trying to get it using below.. but not getting anything

      Hi22

      $(function(){ // document ready
      $.ajax({
      method: “GET”,
      url: “/reports/financials/testpage2/”
      })
      .done(function( results ) {
      alert(“test alert”);

      var labels = [], datavalues = [];

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

      alert("total labels retrieved. " + labels.length);

      });
      });

      Note: testpage2 is the web page which I created inheriting from page template linked with web template returning json.

      Like

    3. Hi Colin Vermander,
      Thanks for sharing this post. This post is very useful.
      However, i was trying to trigger the plugin when i query data from CRM using WebTemplate(Portal).
      Plugin is not triggering.
      Please advise if this can be achieved.
      Note:- I’ve registered plugin on RetrieveMultiple message

      Like

      1. Prem, not sure why a retrieve plugin would cause an issue, but I would suggest first testing your plugin with standard Dynamics UI then move to incorporating the portal.

        Colin

        Like

    1. Hi Chris, yes in the example code in this post you can see some conditions being added to the fetch based on parameters in the URL. You can do whatever conditional logic you want based on data within the liquid template.

      Like

  2. Hey Colin, thanks for this article. Really what I need for my project. It would be extremely helpful if you could consider posting some debugging strategies for a page like this. It’s certainly not your typical ASP.NET debugging. Are we stuck with the basic “print” technique? Or in this case outputting some JSON var that flags what happened in the code? What we need to see most is the dynamically created fetchxml that results from the parameters sent to the page, for example.

    Like

    1. I have a feed that was initially returning zero records. I created a READ entity permission on Incident, then assigned that permission to the webrole for authenticated users. Now when I access the feed page it runs for a long while and I get a timeout errror. The liquid code is patterned after your course schedule example, except it’s for incidents, and there are a few more filters. I am not sure how to address a timeout. Do I need to limit the number of records per page or something?

      Like

      1. hi John, yes perhaps there is a timeout with the fetchquery. What you can do is use the fetchxml tester in the XrmToolbox and see if it also experiences the same issue.

        Colin

        Like

  3. Hi Colin,
    I need to show more than 5000 record to my Portal website. I am using FetchXML query (end point) to get records from my Dynamics 365 instance to Dynamics 365 Portal website. but it can’t fetch more than 5000 records in single FetchXML query. I have updated fetchxml query with paging-cookie to retrieve more than 5000 records. However, I am not sure way to pass paging-cookie from UI page to FetchXML query end point.

    Do we have an example to handle more than 5K records using fetchxml (JSON) endpoint and UI page?

    Thanks,
    Veera

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s