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.

2 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?

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *