Using Liquid Templates and FetchXML to Retrieve Data in a Dynamics 365 Online Portal

by | Jul 25, 2017 | Dynamics CRM

The Dynamics 365 (CRM) portal has many features that are extremely valuable that fly a little (or completely) under the radar. One of those valuable features that I have been working with recently is a feature called liquid templates, which is used to write FetchXML to reach back into my CRM online instance and obtain additional data when I am working on a portal form.

This approach opens up a completely new world of possibilities for many custom solutions that our clients may need to implement on the Dynamics 365 portal. I am going to demonstrate a quick liquid FetchXML example that I hope will help you get started.

Some knowledge of FetchXML and JSON will make this article easier to follow but I will try to demonstrate this example in an approachable way that will point you in the direction of where you would need to go for more information. I have included additional information about the technologies that I will be leveraging below.

What is Liquid?

If you don’t know what liquid is don’t feel too bad, I was in the same boat a few weeks ago and had to do some quick learning. Liquid is an open-source template language created by Shopify in Ruby. Liquid was ported over to .net in 2010 via the open-source license. This version of liquid is known as dotliquid. The syntax between the original liquid and dotliquid are very similar.

The important thing to note is that liquid is supported within the CRM portal and we will be using it from the web template to retrieve data from CRM via FetchXML. I will try to explain the little bit of liquid used for our queries; check out the links below for additional information:

1. The original Liquid (Ruby)
2. Dotliquid

What is FetchXML?

I am going to assume everyone reading this article has some familiarity with FetchXML. The high-level definition is as follows: FetchXML is a proprietary query language that is used in Microsoft Dynamics 365 (online & on-premises). If you are new to FetchXML please refer to the following link for more information: Build queries with FetchXML.

What is JSON?

JSON is the format in which our FetchXML results will return. It stands for JavaScript Object Notation and it is a way to store and transmit data objects in an efficient and readable manner. I won’t go into too much detail on this either but here is a pretty good explanation with additional links: What is JSON and why would I use it?

Let’s Get Down to Business and Fetch Some Data

In our example, we will be pulling the main phone, address and primary contact information associated with an account name. We will be searching for the account by the account name and returning the associated active contacts.

Step 1: Create the FetchXML query.

If you are new to FetchXML (or even if you are seasoned), it does not hurt to start creating your query using the Advanced Find in Dynamics 365. You can see my Advanced Find Query and the FetchXML that is generated from it below:

Download FetchXML

Download FetchXML

Step 2: Create the Web Template

Under the Portals tab in Dynamics 365, select Web Templates.

Under the Portals tab in Dynamics 365, select Web Templates.

Click create new.

Click create new to create a new web template.

Enter in your Liquid code into the Web Template with your revised XML and save.

Enter in your Liquid code into the Web Template with your revised XML and save.

Name: This is the name of the Web Template. I try to make mine as descriptive as I can and start each one with Fetch so I know it is a FetchXML template. I also add a “_WT” but this really isn’t necessary.

Website: This pull-down has the list of websites that are active in the portal. Select the website that will be using this FetchXML query.

MIME Type: Enter “JSON” here (no quotes).

Source: There is a lot here but it’s not as bad as it looks. I will explain each line of code below:

Code

Line 1-2: These are the parameters that are being passed into the Web Template. I will be passing in the parameters cacheString and accountname via the URL when I call this query. You will note that by using Liquid we are assigning these values to variables that we will be using the FetchXML later.

NOTE: cacheString is a random number that I generate and pass to my FetchXML query because Dynamics likes to cache previously-used queries instead of grabbing the most recent data if it sees that the same query is being run again. Dynamics does this for speed reasons but I prefer to make sure I am getting the most current data so I change the query by passing in this random number. You will notice that this number is used in my query condition but in a way that will not affect the results.

Line 4: This is a Liquid statement that names the FetchXML query. You will note that I named this FetchXML query ‘accounts’. This line is stating that FetchXML starts here.

Line 5-22: This is a slightly altered version of the FetchXML. Note that I have updated the FetchXML query that we generated earlier to contain single quotes instead of double quotes. I converted (“) to (‘).

Lines 17-18: These lines were altered to use the variables we assigned in lines 1 and 2. Note that to use a variable in Liquid we use {{ and }}.

Line 23: Close the FetchXML code that we started in line 4.
• Note after that we have an open square bracket ( [ ). We are going to start getting our data from the FetchXML and put it in the JSON format. This open square bracket is the start of that. It will allow us to return an array of objects. In this example, we are only returning one account but the way the code is written above will work for one object and multiple so I always write it this way.
• After the square bracket, we are starting a “for” loop with liquid to get everything returned from the FetchXML query. Notice that we are cycling through the FetchXML query name that we created on line 4 (accounts) and returning each object as an entity named “item”.
o (% for item in accounts.results.entities %} – accounts came from line 4

Code

Line 25-33 is assigning the data that was returned into a JSON object that we create. The format is name:value so on line 29 the name is “mainphone” and the value is the telephone1 returned from our FetchXML and contained in the item entity. Note the quotes and again the liquid double brackets.

Lines 27-28 has something worth pointing out. Because the primarycontactid is a lookup in Dynamics 365, the value returned from the FetchXML needs to define whether we want to have the id or the name returned. In my example, I showed how to retrieve both.

Line 34: Here we are adding a little Liquid logic to add a comma to our JSON format if there is more than one entity (in this case accounts) returned. Again, this is to facilitate a query that will return more than one entity if needed.

Line 35: Ending the Liquid for loop. Also finally closing our square bracket (]).

Step 3: Create a Page Template

Under the Portals tab in Dynamics 365, select the Page Templates option.

Under the Portals tab in Dynamics 365, select the Page Templates option.

Click create new.

Click create new.

 

Enter in the following and save.

Enter in the the name and save.

Name: I use the same name as the web template only I add a _PT at the end. You can make it whatever you want.

Website: This pulldown has the list of websites that are active in the portal. Select the website that will be using this FetchXML query.

Type: In this pulldown list, select the Web Template option. (Doing this will make the Web Template field show that we are filling in next.)

Web Template: Select the web template that we created in step 2.

Use Website Header and Footer: Uncheck this if it is selected. It will add the header and footer to the data returned but we only want the JSON data returned.

Description: Add description.

Step 4: Create the Web Page

Under the Portals tab in Dynamics 365 select the Web Pages option.

Under the Portals tab in Dynamics 365 select the Web Pages option.

Click create new.

Click create new.

Enter in the following data and save.

Enter in the data and save.

Name: I use the same name as the web template and page template without anything at the end. It can be whatever you want.

Website: This pull-down has the list of websites that are active in the portal. Select the website that will be using this FetchXML query.

Parent Page: Normally I just use “Home” since most of the sites I develop for are in flux. This sets the parent page and affects the URL address used to call the fetch XML page.

Partial URL: I always make this the same as the name to avoid confusion.

Page Template: Select the page template that we created in step 3.

Publishing State: Select “Published” from this pull down to activate this page on the portal.

Mark "yes" next to "Hidden From Sitemap."

Hidden From Sitemap: This is further down the page. Usually, I hide my Fetch query pages from the sitemap.

Step 5: Test Your Query

Important – To test your query we need to log into the portal. Login and then go to the URL you created and enter the parameters. My example is the following:

Works (sample)

The results can be seen below. It worked, the JSON is returned.

The JSON is returned.

IMPORTANT: If you only see two empty square brackets “ [ ] “ nothing got returned from your query. Double check that the entity you are trying to return is enabled on the portal through security. Check that the person logged in has access to that account. Also, double check your query.

In future articles we will explore:

1. Using this example, I will demonstrate how to call this FetchXML query via a JavaScript ajax call on a portal Entity Form.
2. I will also show some tips and tricks to use FetchXML and Liquid for related entities joins to reduce the number of calls needed.
3. How to expose an entity to the portal to allow it be called by liquid and FetchXML.
4. Leverage this technology to call Plugins and Workflows from the Portal.
5. Much more…

8 Comments

  1. Akbar

    How to check, entity you are trying to return is enabled on the portal through security. plz tell me about this one. how to enabled entity on the portal through security.

  2. Vani

    How to pass multiple parameters to web template. Is it possible to push all the params into an object and to pass that object as a parameter. Please help me with an example.

  3. Malind Mohan

    Actually, Portal User are able to get the result from web page without logging into CRM Portal.
    https:/// is returning the result.
    How can we enforce the portal user to redirect the login page instead of result if user does not login into portal.

  4. Taylor Valnes

    Hello Vani,

    I’ve spoken with the writer of this blog and he did some research on it. His response was:
    After doing some research it appears that Liquid does not have a JSON.Parse method available. https://github.com/Shopify/liquid/issues/432
    Every time I have used this approach I have passed individual parameters in the URL. I also looked at some of the documentation for dot.liquid and I didn’t see anything there either.
    Depending upon what your requirements are there is a snippet of Liquid code that may help you. If you pass an entity ID as a parameter in the URL you can get that CRM entity through Liquid and assign it to a Liquid Object.

    If your URL is as follows: https://XYZcompany.microsoftcrmportals.com/FetchLeadTimes/?cacheString=200021&id=0e29be85-f32b-4bd0-a169-872ade4a7874

    You would assign that id to a variable in your liquid using the following: (Let’s assume we are trying to find a specific contact entity. I have underlined the portion of the call that is the entity name that would have to be changed depending on the entity queried.)
    {% assign contactObject = entities.contact[id] %}

    At this point the contactObject is a liquid object of the contact entity that was retrieved using the id passed in the URL.

    Now to refer to the field in that entity you would use the following syntax:
    {{contactObject.fullname}}, {{contactObject.address1_city}} {{contactObject.address1_stateorprovince}}

    Which on you page might look like:
    John Dow, Springfield NY

    Without knowing your specific need for passing an object this may or may not be of use to you. I hope it helps.

    NOTE: To pass multiple parameters just add them one after the other using the ampersand symbol (&) as seen below. In this example I am passing two ids. ID1 and ID2 along with the cacheString.
    https://XYZcompany.microsoftcrmportals.com/FetchLeadTimes/?cacheString=200021&id1=0e29be85-f32b-4bd0-a169-872ade4a7874&id2=4d53ac14-1d61-427b-92c2-45e49ed79bc0

    Regards,
    Taylor

  5. Taylor Valnes

    Hello Malind,

    I’ve spoken with the writer of blog and he responded with the following:

    Check the entity permissions on the entity you are returning. Make sure that you are not allowing anonymous (or public) access to that entity. To secure the entity you would want to make that entity only available to authenticated users. Remember that CRM security is additive meaning if you have both anonymous and authenticated user access settings on one entity, anonymous wins out.

    I never had the requirement to get the URL to go back to the login page so I am unsure on that one. I have had the requirement to lock down the fetch query so that only authenticated users retrieve results. If the user is not authenticated they will get a blank JSON object. “[ ]“

    Regards,
    Taylor

  6. Kevin Rulloda

    What a great article!, I am new in Customer Portal, and i rarely use the Page and Web Template, i always use the available template and “Full Page” for new entities, How every we have some requirement that upon changing a value from, for example “LookUpField1” i will retrieve some “FieldValue” from that lookup and insert that to another field for example “Field2”. Is this possible for Client Side Scripting? I mean can i use this to combine with JQUERY? or do you have any suggestion on how to retrieve values from a Lookup field.

  7. Taylor Valnes

    Hello Kevin,

    I spoke with Steve, the blog writer, and he had the following to say:
    “Thanks for the kudos on the article! If I understand your requirements correctly, you have a lookup field visible on the form and when the value changes you will retrieve that value and insert it into another field on this same form. If that is the case, straight up jQuery should work. The FetchXML approach that I demonstrated is only needed when you need to retrieve a data from within CRM that is not currently available on the form.

    Here is an untested general idea of what you are looking for using jQuery:
    $(document).ready(function () {

    $(‘# LookUpField1’).change(function() {
    var yourLookupValue = $(‘#LookUpField1’).val();
    $(‘# Field2’).val(yourLookupValue);
    }
    }

    This code could be added to the Custom JavaScript section of the form in question.”

    Regards,
    Taylor

Submit a Comment

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

Upcoming Events

april

08apr10:00 am10:30 amLicensing Preparedness for Dynamics 365 Customer Engagement

08apr12:00 pm1:00 pmMaking Project Information Management (PIM) a Priority

08apr2:00 pm2:30 pmFeatures in the Spring Release of Dynamics 365 Customer Engagement Users Can Take Advantage of Immediately

09apr11:00 am12:00 pmConfab With Stoneridge - Livestream - Portals

13apr11:30 am12:30 pmStoneridge Connect Online Keynote: Business Transformations Throughout History

14apr8:00 am5:00 pmStoneridge Connect Online - Day 1

15apr8:00 am5:00 pmStoneridge Connect Online - Day 2

15apr1:15 pm5:00 pmWhat’s New for Developers in Dynamics 365 Finance and Supply Chain Management – Online Workshop

16apr8:00 am5:00 pmStoneridge Connect Online - Day 3

22apr11:00 am12:00 pmPower BI and Reporting with Dynamics 365 Business Central

22apr2:00 pm2:30 pmNew Features for Power Apps Users

23apr11:00 am12:00 pmConfab With Stoneridge - Livestream - Internet of Things (IoT)

29apr10:00 am11:00 amStreamlining Customer Service and Enabling Your Sales Team with a Self-Service Portal

29apr12:00 pm12:30 pmUpdates to the Dynamics 365 Customer Engagement User Experience - What Technical Resources Need to Know

may

06may12:00 pm12:30 pmPower Apps Telemetry and AI Builder - Power Platform Updates

06may2:00 pm2:30 pmImprove Customer Experience with a Mobile Workforce Management Solution

07may11:00 am12:00 pmConfab With Stoneridge - Livestream - Manufacturing

13may12:00 pm1:00 pm3 Simple Sets Your Business Can Take to Embrace the Future of B2B E-Commerce

21may11:00 am12:00 pmEnterprise Asset Management and Manufacturing

About Stoneridge
Stoneridge Software is a unique Microsoft Gold Partner, with emphasis on partner. With specialties in Microsoft Dynamics 365, Microsoft Dynamics AX, Microsoft Dynamics NAV, Microsoft Dynamics GP and Microsoft Dynamics CRM, we focus on attracting the most knowledgeable experts in the field to our team, and prioritize delivering stellar solutions with maximum impact for your business. At Stoneridge, we are deeply committed to your results. Each engagement is met with a dedicated team, ready to provide thorough, tailored, and expert service. Based in Minnesota, we intentionally “step into your shoes,” wherever you are. We focus on what you care about, and develop trusting, long-term relationships with our clients.

Subscribe To Our Blog

Sign up to get periodic updates on the latest posts.

Thank you for subscribing!

X