Using FetchXML with Liquid Templates for CRM Data Queries

Dynamics CRM Portals are new and excellent ways to tie customer service and Dynamics 365 CRM together.

If you have a Dynamics 365 Enterprise subscription, then it’s very likely you have portal license as part of this package. Don’t let it go to waste! You can use it to create a customer service portal from CRM without the cost and effort of creating a new website framework from scratch.

If you’re using Dynamics 365 CRM then you’ve probably heard about the advantages in having a Dynamics 365 Partner like KTL Solutions. One of which is helping you get your customer service portal designed and running quickly and efficiently. No expensive staff training or worry of knowledge loss from employee turnover. Dynamics Partners bring expert knowledge as and when you need it.

Here I’ll show you an example of how the process by which you get CRM Portal data using Liquid Templates can make or break your portal in terms of performance.

Liquid is an open source scripting language for loading static and dynamic content into web pages. The language itself isn’t tied to any platform but rather is designed to be adaptable to many platforms and products. In the case of CRM portals, Liquid Templates are an easy and powerful way to get data from CRM to your portal pages, however it’s also easy to get into a situation where performance suffers.

Let’s start with a basic example. Liquid provides several predefined objects, one of which is “user”, which contains information about the currently logged on user. In the portal pages, one can display the user’s name using Liquid’s tilde bracket syntax in the HTML as follows:

<div>Hello {{ user.fullname }}</div>

 

Now this by itself isn’t that useful. The user needs to be able to see information such their account and orders. The user is tied so an account and from the basic Liquid documentation, we can get that through Liquid using the “entities” object.

{% assign account = entities.account[user.parentcustomerid.id] %}

 

This set a variable named “account” to the with the user’s parent customer association. While this looks innocent, it’s the beginning of a problem and it won’t be noticeable just yet. Now, let’s say we need to show the user things like orders and invoices. Here’s a simple example that just displays the dates of all the orders on the account.

{% for order in account.order_customer_accounts %}

<div>Date: {{ order.createdOn }}</div>

{% endfor %}

 

At this point things will start slowing down. The account doesn’t need thousands of orders for this, a couple hundred is enough to cause a lengthy page delay, even if change the above to no display all the data in the for loop at once. In an actual case with a client, Liquid was pulling 300 objects and the portal web page was taking over 60 seconds to appear.

So why is this happening when the only data we used was the date?

In all comes down to the way Liquid works. When an entity object is loaded by Liquid through objects it will retrieve all the attributes from that object. It doesn’t matter if you only used a few as Liquid doesn’t analyze the code ahead to see what you used. This is an easy trap to fall into and fortunately there is a better way using FetchXML.

Those who have done development work against the CRM SDK will be quite familiar with FetchXML. What isn’t so well known is that FetchXML can be done with Liquid Template. At the date this blog was written, going to the Adxstudio site and doing a search for FetchXML yields only six matches. The top of five of which are not Portal related, instead pointing to the web control libraries. The last response is a vague mention on the term in “Release Notes (7.0)” of Adxstudio portals. Given there is no other mention, and being “Release Notes”, it’s easy to automatically dismiss that last match. Searching the internet carefully for Liquid Templates and FetchXML will eventually land you on a blog about it like this one, and not long ago you had search deep and hard to find them.

So, what are the benefits of FetchXML? And how does one use it?

FetchXML is a XML structure that defines an explicit data query that specifies exactly what CRM entities and attribute data should be extracted, what relations connect these entities, and any search condition logic to be used. FetchXML could be described almost like an SQL query. The CRM attributes are the columns of data, the joined tables are CRM entities, and the logical conditions are a WHERE block.

The only special thing to be aware of with using FetchXML in Liquid is that it can only be done inside a Portal Web Template object. This may requires splitting out some pages in smaller logical portions but shouldn’t be obstacle in any way. If you haven’t used FetchXML before, this will be an easy introduction.

Now let’s finally look at a starting example of FetchXML with Liquid. Remember that this is going into a Portal Web Template, not a Web Page object. This below returns the parent account for the current Portal user, retrieving only the name and accountid attributes.

The first and last lines are Liquid statements, and not part of the FetchXML structure itself. These specify that the text between the {% fetchxml accountQuery %} and { % endfetchxml %} contains a FetchXML structure. The name accountQuery in the first statement is the name of Liquid variable to be created that will contain the results of the FetchXML statement.

The main fetch element of the FetchXML block contains some defining attributes. Inside that is an entity element for primary entity type being queried. In this case, an account entity. Inside there are two attribute elements with names. These should always be specified and state exactly what account attribute data should be included.  To keep performance at optimal, avoid pulling attributes you don’t need.

Next is a link-entity element which specified that the search should link the account entities to contact entities using specified to and from reference attributes. In this case, we are linking the contact entity of the user by the parentcustomerid attribute of the contact to the accountid attribute of account entity.

Inside the link-entity element is a filter element which contains condition elements. In this case we have a single condition element stating that the contactId must be equal the portal user id.  Since the user id is a Liquid variable, it must be wrapped by double curly brackets so that Liquid will recognize it as a state and not just text.

The xml_escape tells Liquid to parse the variable for XML. While this isn’t necessary for ids, it’s a good habit to follow for two important reasons. First, if you’re doing a query based off text from a parameter in the URL, or form user input, then it’s possible for characters to exist that will confuse the XML parsing and cause an error to occur. Second, not escaping test poses a security risk in that a hacker could enter text with characters that are interpreted as being part of the FetchXML to manipulate the search.

Next, I’ll add some code to display the results of the query. This isn’t restricted to the Web Template and so can be in there or in the page HTML.

Since there is only one contact queried, it will only join to one account and return one result looking something like this:

 

Fetch XML Results

Name: XYZ Company ID: 5fceb958-8962-e711-8123-c4346bac2a0c

 

Now let’s expand that to what was done earlier…a list of orders for the user’s account.

This time it will only be pulling two attributes of data, name and totalamount, along with the ones needed for the link-entity which will return much faster. We can then do the same loop and put these into a table with something like this.

Even if we pulled attributes of the sales order the process is stuff fast because it doesn’t all other attribute and references. To give an idea of how much less, just on attributes alone, a sales order entity has over 90 attributes, and account entity over 150 attributes. After this change it can pull hundreds of records in a few seconds, though normally you won’t want to pull that many at once, and to deal with such a case FetchXML has paging features to break down large query result sets.

More information, and documentation, about FetchXML, is available from Microsoft.

One other tip if you’re not familiar with FetchXML. In Dynamics CRM you can click the Advanced Find button at the top and construct a query there which you can then be download as a FetchXML structure using the download button at the top.

With this knowledge in hand, you can now go forward with making CRM data queries on your Portal that are fast and efficient. This is just some of the technical knowledge you can have available to you any time with a Dynamics 365 Partner like KTL Solutions. To find out more about what we can do for you, please calls contact us by phone or email.

Share this post

Related Posts