The Strange Case of The Liquid Template fetxhxml Tag

I have been asked by one of my customers to investigate a problem related to the Liquid Template fetxhxml tag.
If you aren’t familiar with this powerful Liquid tag yet, it allows retrieving Dynamics 365 data using a standard FetchXML query. You can even leverage this tag to retrieve data a-synchronously.

Trying to reproduce the problem, I added a simple FetchXML query within a fetchxml tag in one of the Portal pages via the actual Web Page record, knowing that the front side editor disrupts FetchXML queries syntax.

Testing the query results in the target Portal page, I noticed that the query condition was ignored – the query returned all Contact records.

Returning to the Web Page and refreshing it, I noticed that the FetchXML query mysteriously changed – the closing  slash for each attribute element was replaced with a full closing <attribute> tag.

It seems that before saving the Web Page form, the text editor diligently ‘corrects’ my query to comply to XML hierarchy format. This behavior was reproduced with Chrome/Edge browsers on both UII/old forms UI.
Strangely enough, the changed query syntax is considered valid by Dynamics 365, but any query condition is completely ignored.
I am guessing a recent features update changed the text editor behavior, as this behavior does not exist on earlier Dynamics 365 versions. 

After searching Microsoft Portal documentation for the fetchxml Liquid Template tag finding no results, I started considering the option that this is not a bug, but maybe some kind of feature phase out. Naaah.

So until Microsoft fixes this glitch, how can you use conditional FetchXML queries and fetchxml liquid tags? By using XrmTookBox Portal Code Editor Plug-in which allows Portal content editing (among other features) without disrupting FetchXML queries

Advertisement

Executing Web API Calls from Flow/Logic Apps

While writing my previous post regarding Flow as a scheduling mechanism,  I stumbled across this post demonstrating how to execute Web API calls from Flow.

Why would you want to execute Web API calls from Flow/Logic Apps?
While Microsoft Flow/Logic Apps support basic Dynamics 365 operations, there are many missing functions that can simplify common integration scenarios between these platforms: executing Custom Actions, triggering Processes, handling metadata and issuing complex queries.
Some of these problems can be worked around using the Command Pattern, but using the Web API from within Flow/Logic Apps provides a simple and powerful integration mechanism.  

In this post, I’ll demonstrate how to authenticate and execute Web API functions and actions from Flow (a similar process can be used with Logic Apps):

• Execute a FetchXML query
• Execute a Custom Action

The requirement used to demonstrate is again the automated weekly evaluation of Leads: once a week, any Lead which is older than 5 days and not rated hot is disqualified.
With this implementation approach, Flow is used for scheduling, query for target business records and applying a Dynamics 365 Custom Action to each business record. 

Prerequisite

1. Have access to Microsoft Dynamics 365 online instance and Flow environment
2. Register Microsoft Dynamics 365 online instance in Azure AD and have the Application Id key ready.
   Make sure you set the oauth2AllowImplicitFlow as described here.

Walkthrough

1. Download and import Solutions

   Download and import this Flow solution into your Flow workspace.

   

   

   

   Download, import and publish this unmanaged solution into your Dynamics 365 instance. It contains one Custom Action that will be executed from Flow using Web API. 

2. Set Flow Settings

   Edit the newly imported Flow

   

   Set the following keys with values to match your environment

   

   

   

3. Note the following settings

   This variable holds FetchXML query to retrieve the target Lead records.

   

   This variable holds the target entity name as it is used with Web API

   

   This variable holds the target Custom Action to handle each Lead.
   Note that for unbound Custom Action, the Microsoft.Dynamics.CRM should be removed.

   

   This action requests a token used to authenticate to Web API

   

   This action executes the FetchXML query

   
   This action parses the query resulting records.
   If you change the FetchXML query to support a different entity or attributes, make sure you also change the JSON schema to support it

   

   This action executes the target Custom Action for each Lead record returned by the query

   

4. Test your Flow

   After a successful run, Flow will display the number of affected records.
   In Dynamics 365, these records should appear as disabled now.

   

   If your Flow fails on the RequestOAuth2Token action, try this solution

Implementation Notes 

• I prefer authenticating using an Application Id and Secret via an App user rather than plain user credentials. 

Microsoft Portal – Retrieve Dynamics 365 Data Asynchronously – Part 2

In part 1 of this post I demonstrated building and using a service which receives a FetchXML query and returns Dynamics 365 data to any Portal page in an asynchronous manner as a JSON object. This service is similar to the SDK’s RetrieveMultiple message.
In this part 2, I’ll demonstrate a different service, which like the SDK Retrieve message, receives a record type, record id (GUID) and columns set to return the required data as a JSON object.
This is useful when you already have a specific Dynamics 365 record id at hand and you want to retrieve additional data for this record.

1. Create a Web Template  named RetrieveService

   Set the Mime Type is to application/json.
   

   Paste the following code in to the web template source and save.

   {% comment %} test for required parameters {% endcomment %}
   {% if request.params[‘entityname’] and request.params[‘id’] and request.params[‘columnset’] %}

   {% capture msg_no_data %}No data for specified attribute {% endcapture %}

   {% comment %} extract parameters {% endcomment %}
   {% comment %} extract target entity {% endcomment %}
   {% assign entityname = request.params[‘entityname’] %}
   {% comment %} extract target record id {% endcomment %}
   {% assign id = request.params[‘id’] %}
   {% comment %} parse requested attributes into array {% endcomment %}
   {% assign columnset = request.params[‘columnset’] | split: “,” %}

   {% comment %} query for target entity  {% endcomment %}
   {% assign item = entities[request.params[‘entityname’]][request.params[‘id’]] %} 

   {% comment %} Emit JSON response {% endcomment %}
       {% if item %}{
           {% comment %} Iterate throguh requestd attributes array {% endcomment %}
           {% for att in columnset %}
               {% comment %} Handle optionset attribute {% endcomment %}
               {% if item[att].label %}”{{ att }}”:”{{ item[att].label | default: msg_no_data }}”
               {% comment %} Handle lookup attribute {% endcomment %}
               {% elseif item[att].name %}”{{ att }}”:{“name”:”{{ item[att].name | default: msg_no_data }}”,”id”:”{{ item[att].id | default: msg_no_data }}”}                           
               {% comment %} Handle other attributes {% endcomment %}
               {% else %}”{{ att }}”:”{{ item[att] | default: msg_no_data }}”
               {% endif %}{% unless forloop.last %},{% endunless %}
           {% endfor -%}                   
       }   
       {% endif %}

   {% comment %} handle missing parameters {% endcomment %}
   {% else %}
     { “error”:”Missing one or more required parameter(s): entityname, id, columnset” }
   {% endif %}

2. Create a Page Template named RetrieveService
   Map the Page Template to the Web Template created on step 1 above.
   Uncheck the Header and Footer checkbox.
   
   

3. Create a Portal Page named RetrieveService
   Set Home as parent page.
   Map the page to the Page Template created on step 2 and copy the partial URL which is used in the next step

   

4. Consume Service

   Use the following code sample anywhere you need asynchronous data retrieval of from Dynamics 365. In my example it is located in the Home page JavaScript portion.
   Note the URL address which maps to the Page (created above) URL.
   Replace the entityname, id and columnset parameters.
   Most important: make sure your users have the required Entity Permissions for the entities queried, otherwise they will receive empty results.

   $(function (){
     //define asynchronous request from any portal page
       $.ajax({
           method: “POST”,       
           url: “/retrieveservice/”,
           data: {
               //define entity name, id and columnset 
             entityname: ‘incident‘,
             id: ‘D2A697CD-D3C7-E811-A965-000D3AB0F1D7‘,
             columnset: ‘ticketnumber,title,createdon,incidentid,statecode,caseorigincode,customerid,ownerid‘  
           }
       }).done(function (msg){
           console.log(msg);
       }).fail(function (jqXHR, textStatus, errorThrown){
           console.log(textStatus);
       });
   });

   As the sample code logs the resulting JSON object to the console, open the browser developers tool (F12) to view it.

   

Microsoft Portal – Retrieve Dynamics 365 Data Asynchronously – Part 1

Leveraging Colin Vermander brilliant article on using Liquid Templates to return JSON, I would like to demonstrate creating and using a ‘service’ to asynchronously retrieve Dynamics 365 data into any portal page.

Why is this useful?

Liquid Templates tags are rendered on server side before a response is returned to the browser, so FetchXML tag will return a static result once the page is returned.
But what about responding dynamically to client side events such as option selection, button click or expending an element to view more details?
Sending the page to the server again is no longer an option if you want to maintain a decent UX. Having an endpoint that can be called asynchronously to return data from Dynamics 365 can certainly help here. The next walkthrough explains how to do just that.
In Part 2, I’ll demonstrate a similar service aimed to support Retrieve requests (rather than RetrieveMultiple).

1. Create a Web Template named FetchXMLServiceMake sure that the Mime Type is set to application/json

   

   Paste the following code in to the web template source and save

   {% comment %} test for required FetchXML query {% endcomment %}

   {% if request.params[‘query’] %}

   {% capture msg_no_data %}No data or attribute was not included in query{% endcapture %}

   {% comment %} get FetchXML query from request params, decode query XML and execute query {% endcomment %}

   {% fetchxml query %}

   {{ request.params[‘query’] | replace: ‘%3C’, ‘<‘ | replace: ‘%20’, ‘ ‘ | replace: ‘%3D’, ‘=’ | replace: ‘%3E’, ‘>’ | replace: ‘%2F’, ‘/’ }}

   {% endfetchxml %}

   {% comment %} parse requested attributes into array {% endcomment %}

   {% assign columnset = request.params[‘columnset’] | split: “,” %}

   {% comment %} Emit JSON response {% endcomment %}

   {

   “totalcount”: {{ query.results.total_record_count }},

   “morerecords”: {{ query.results.more_records }},

   “results”: [

   {% for item in query.results.entities %}

   {

   {% comment %} Iterate throguh requestd attributes array {% endcomment %}

   {% for att in columnset %}

   {% comment %} Handle optionset attribute {% endcomment %}

   {% if item[att].label %}”{{ att }}”:”{{ item[att].label | default: msg_no_data }}”

   {% comment %} Handle lookup attribute {% endcomment %}

   {% elseif item[att].name %}”{{ att }}”:{“name”:”{{ item[att].name | default: msg_no_data }}”,”id”:”{{ item[att].id | default: msg_no_data }}”}

   {% comment %} Handle other attributes {% endcomment %}

   {% else %}”{{ att }}”:”{{ item[att] | default: msg_no_data }}”

   {% endif %}{% unless forloop.last %},{% endunless %}

   {% endfor -%}

   }

   {% unless forloop.last %},{% endunless %}{% endfor -%}

   ]

   }

   {% comment %} handle no FetchXML query {% endcomment %}

   {% else %}

   { “error”:”No FetchXML query” }

   {% endif %}
2. Create a Page Template named FetchXMLService 

   Map the Page Template to the Web Template created on step 1 above.
   Uncheck the Header and Footer checkbox.

3. Create a Portal Page named FetchXMLService 

   Set Home as parent page.
   Map the page to the FetchXMLService Page Template and copy the partial URL which is used in the next step

4. Consume Service 

   Use the following code sample anywhere you need asynchronously retrieval of data from Dynamics 365. In my example it is located in the Home page JavaScript portion.
   Note the URL address which maps to the Page (created above) URL.
   Replace the FetchXML query with your own query and specify the attributes you want to get values.
   Most important: make sure your users have the required Entity Permissions for the entities queried, otherwise they will receive empty results.

   $(function (){
   //define asynchronous request from any portal page
   $.ajax({
   method: “POST”,
   url: “/fetchxmlservice/“,
   data: {
   //define FetchXML query
   query: encodeURIComponent(
   “<fetch count=’10’ returntotalrecordcount=’true’>” +
   “<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>”),
   //define attributes collection to return values from query
   columnset: “ticketnumber,title,createdon,incidentid,statecode,caseorigincode,customerid,ownerid“
   }
   }).done(function (msg){
   console.log(msg);
   }).fail(function (jqXHR, textStatus, errorThrown){
   console.log(textStatus);
   });
   });
   
   As the sample code logs the resulting JSON object to the console, open the browser developers tool (F12) to view it.

Implementation Notes

• The service is useful for simple FetchXML queries. For more complex queries including linked entities, you’ll need to upgrade the Web Template code to handle more complex query results
• Liquid Templates language includes a url_decode function but it seem unavailable in the Portal language variation. Hence the explicit decoding in the Web Template

Dude, Where’s My Workflow?

There is no built view in Microsoft Dynamics CRM that shows the different solutions to which a component (such as Workflow Rule) is related. If you have many solutions, iterating through all of them can be exhausting and time consuming.

The following query retrieve all solution which contain a Workflow Rule named testwf:

<fetch version=”1.0″ mapping=”logical”>
    <entity name=”solution”>
        <attribute name=”friendlyname”></attribute>
        <link-entity name=”solutioncomponent” from=”solutionid” to=”solutionid” >         
            <link-entity name=”workflow” from=”workflowid” to=”objectid”>
                <filter>
                    <condition attribute=”name” operator=”eq” value=”testwf“></condition>      
                </filter>                       
            </link-entity>
        </link-entity>    
    </entity>
</fetch>

The solutioncomponent entity binds elements such as Workflow Rule to Solutions. The solutionid attribute maps each solutioncomponent record to a Solution record and the objectid attrbiute maps to the Workflow Rule. Off course, you can find any solution element with a similar query, just change the relationship attribute.

Here is the query result which includes two solutions:

[
  {
    "formattedValues": {},
    "friendlyname": "Default Solution",
    "solutionid": "fd140aaf-4df4-11dd-bd17-0019b9312238"
  },
  {
    "formattedValues": {},
    "friendlyname": "Web API Samples",
    "solutionid": "1c35faf2-ee8c-4a1c-a838-d49692d0d941"
  }
]

You can use various tools to run FetchXML queries, I am using XRMToolBox.

Asynchronous Batch Process Solution Revisited – part 3

In the previous post, I walked through the ABP Target Records Scenario. In this post, I’ll go through the Aggregative Query Scenario.

Prerequisites

1. Download the Asynchronous Batch Process Solution, import into Microsoft Dynamics CRM 2015 on-premise/Online organization
2. Go to Settings – > Solutions and Open the ABP solution. Go to the Batch Process entity definition and check the Settings checkbox in the ‘Areas that display this entity’ section
3. Save and publish the solution

As always, I advise against publishing any external solution on your production environment without testing it first.

Aggregative Query Scenario

This scenario describes a scheduled aggregative calculation on Microsoft Dynamics CRM records, followed by performing some business logic operation with the calculation result. Due to the aggregative calculation result nature, the business logic operation is usually an email notification or record creation/update.
Note that grouping is unsupported by this version of the ABP.

Some sample business requirements of this type:

– Send daily email message to Service Manager with total number of daily resolved cases
– Each month, create a Note for each Sales Person user, recording total estimated revenue of opened Opportunities
– Send monthly report to an external application with sum of total closed Orders

Unfortunately, it is not yet possible to define aggregative queries using the Advanced Find editor, but there are useful tools such as FetchXMLBuilder (part of the XRMToolBox  suite) that can help you define FetchXML syntax for such queries.
Also, the Microsoft Dynamics CRM SDK contains useful samples for aggregative queries which can be easily tweaked to suit your purpose.

Following are the steps to implement the daily resolved cases requirement:

1. Create a new Action targeting ‘None (global)’ and name it ‘Send daily resolved cases total notification’
2. Uncheck all ‘Start when’ trigger checkboxes to prevent automatic triggering of the process
3. Add an input parameter of type string named aggregationResult. This specific name is critical for for the ABP operation.
4. Add a step to send an email notification to the Service Manager, including the required text embedding the aggregationResult parameter value
5. Save and activate the process

6. Define aggregative query. I used the FetchXMLBuilder too available in the XRMToolBox  suite and copy the query text to the clipboard

7. Create a new Batch Process record (Settings –> Batch Processes)
8. Paste the aggregative FetchXML query into the Target Records field. Note that the alias is used as as part of the aggregation result, so a meaningful name is preferred
9. Name the Batch Process: ‘Daily resolved cases total notification’ (or other meaningful name)
10. Set the Activation Frequency to ‘Daily’
11. Set the Process field value to ‘Send daily resolved cases total notification’ process
12. Set the Next Activation to any future date and time. I suggest selecting the application’s least busy time
13. Set the Status Reason to ‘Scheduled’
14. Click Save to schedule the Batch Process

You are done! The Batch Process is set and waiting for the next activation date and time. If all goes well, an email notification will be sent as defined and the Batch Process will reschedule to the same time next day.

If you would like to deactivate the Batch Process scheduling, change the Status Reason value to ‘Suspended’ and save. Since there may already be a Workflow instance scheduled and waiting, go to the Background Processes for the Batch Process record and cancel waiting instances.
This also applies if you change the Next Activation date/time once the Batch Process record is already created and scheduled.

In the next post, I’ll walkthrough the External Action scenario. Stay tuned.