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);
	}
	}
	}
	}

view raw Plg_ExecuteFetchXml.cs hosted with ❤ by GitHub

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 …