[Power Automate – Dataverse] How to execute a fetchXml query that includes options not supported by the List rows action?

Dataverse list rows connector allows to retrieve rows from a Dataverse table. This connector can filter rows using OData expressions or fetchXml queries. Unfortunately, the connector does not support all fetchXml requests. This blog will discuss an approach to execute this kind of unsupported queries.

Let’s look at what the documentation says:


The distinct operator and aggregation queries are not currently supported in FetchXML queries from the List rows connector.

Let’s try a fetchXml query with the option distinct=”true” !!

<fetch distinct="true" >
  <entity name="contact" >
    <attribute name="address1_country" />
  </entity>
</fetch>

Here is the output of this query with the list rows connector:

Key property 'contactid' of type 'Microsoft.Dynamics.CRM.contact' is null. Key properties cannot have null values.

Indeed, the List row connector needs to know the primary key of the table for its correct use

Suggested solution:

My first approach was to remove the distinct option and calculate the unique values on Power Automate. It’s true that this will work fine, but it will require downloading all the data, and it may take time if the data is large. So, the approach is to delegate this operation to Dataverse.

To delegate the calculation to Dataverse, the server’s extension capabilities can be used. Indeed, this approach will execute the fetchXml through a Custom Api, and call it from Cloud Flow with the Dataverse connector action ‘Perfom unbound action’.

Below is the definition of the Custom Api:

For inputs, only one is needed, which is the fetchXml:

Finally, for output, the Custom Api will return an EntityCollection:

Now for the implementation:

// <copyright file="Plg_ExecuteFetchXml.cs" company="">
// Copyright (c) 2022 All Rights Reserved
// </copyright>
// <author></author>
// <date>3/14/2022 2:38:36 PM</date>
// <summary>Implements the Plg_ExecuteFetchXml Plugin.</summary>
// <auto-generated>
// This code was generated by a tool.
// Runtime Version:4.0.30319.1
// </auto-generated>
using System;
using System.ServiceModel;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
namespace MEA.POC.Plugins
{
/// <summary>
/// Plg_ExecuteFetchXml Plugin.
/// </summary>
public class Plg_ExecuteFetchXml: PluginBase
{
/// <summary>
/// Initializes a new instance of the <see cref="Plg_ExecuteFetchXml"/> class.
/// </summary>
/// <param name="unsecure">Contains public (unsecured) configuration information.</param>
/// <param name="secure">Contains non-public (secured) configuration information.
/// When using Microsoft Dynamics 365 for Outlook with Offline Access,
/// the secure string is not passed to a plug-in that executes while the client is offline.</param>
public Plg_ExecuteFetchXml(string unsecure, string secure)
: base(typeof(Plg_ExecuteFetchXml))
{
// TODO: Implement your custom configuration handling.
}
/// <summary>
/// Main entry point for he business logic that the plug-in is to execute.
/// </summary>
/// <param name="localContext">The <see cref="LocalPluginContext"/> which contains the
/// <see cref="IPluginExecutionContext"/>,
/// <see cref="IOrganizationService"/>
/// and <see cref="ITracingService"/>
/// </param>
/// <remarks>
/// For improved performance, Microsoft Dynamics 365 caches plug-in instances.
/// The plug-in's Execute method should be written to be stateless as the constructor
/// is not called for every invocation of the plug-in. Also, multiple system threads
/// could execute the plug-in at the same time. All per invocation state information
/// is stored in the context. This means that you should not use global variables in plug-ins.
/// </remarks>
protected override void ExecuteCdsPlugin(ILocalPluginContext localContext)
{
if (localContext == null)
{
throw new InvalidPluginExecutionException(nameof(localContext));
}
// Obtain the tracing service
ITracingService tracingService = localContext.TracingService;
try
{
IPluginExecutionContext context = (IPluginExecutionContext)localContext.PluginExecutionContext;
IOrganizationService currentUserService = localContext.CurrentUserService;
var fetchXml = (string) context.InputParameters["mea_fetchxml"];
var result = currentUserService.RetrieveMultiple(new FetchExpression(fetchXml));
context.OutputParameters["mea_fetchxmlresponse"] = result;
}
catch (Exception ex)
{
tracingService?.Trace("An error occurred executing Plugin MEA.POC.Plugins.Plg_ExecuteFetchXml : {0}", ex.ToString());
throw new InvalidPluginExecutionException("An error occurred executing Plugin MEA.POC.Plugins.Plg_ExecuteFetchXml .", ex);
}
}
}
}

For example, this custom api can be called from a Power Apps. Below is the flow used:

In a Power App, the Custom Api response can be displayed in a gallery:

Let’s take a look at how it works on the Power Apps. First, when changing the input fetchXml:

UpdateContext({result: ExecuteFetchXmlFromPowerApps.Run('fetchXml Input'.Text).result});
ClearCollect(
    countriesCollection,
    MatchAll(
        result,
        "\{""@odata.type"":""(?<odataType>[^""]*)"",""address1_country"":""(?<address1_country>[^""]*)"",""address1_composite"":""(?<address1_composite>[^""]*)""\}"
    )
);

The bottom part is equal to the response of the Cloud Flow ‘Execute FetchXml From PowerApps’ . Indeed, it is an array of objects:

Finally, the content of the gallery is the collection ‘countriesCollection’

Hope it helps …

3 thoughts on “[Power Automate – Dataverse] How to execute a fetchXml query that includes options not supported by the List rows action?

  1. Good stuff. Sad to say it’s needed. What PowerFX really needs right now is PowerQuery. Your elegant solution is convoluted due to these seemingly related teams not solving common problems together – such as the persistent need for Dataverse to include some level of analytics, because that’s how our business flows work. It requires infrastructure and secondary environment management devops engineering that’s just simply not the promise of the platform. This problem you are solving really limits the utility of Power Apps for my team.

    Liked by 1 person

    1. Thanks for your feedback, I hope we can have soon the possibility to extend powerFx with our implementations. I think this is already in the roadmap of microsoft. Thanks again for reading and sharing some words on my blog.

      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 )

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