Paging while using FetchXML in Dataverse Connector

If you want to retrieve more than 5,000 records in a Flow using List Rows action from Dataverse, you need to page through the records. Flow does not automatically do this for you. This is not a new topic. It is already been explored by Linn and Debajit. You can read their posts below:

  1. Linn – https://linnzawwin.blogspot.com/2021/01/retrieve-more-than-100000-dataverse.html
  2. Debajit – https://debajmecrm.com/how-to-query-more-than-5k-cds-records-using-fetchxml-in-powerautomate-microsoft-flow/

When I was looking into this same problem, I used two things differently:

  1. Using interationIndexes for paging
  2. Using xml for encoding paging cookie, instead of manually encoding “<” to “&lt;”, “>” to “&gt;” etc.

Here is the Flow

I use iterationIndexes like this for the page for each iteration.

The paging cookie returned in the first page looks like this

<cookie page="1"><systemuserid last="{7A44238F-9894-EB11-B1AC-002248153EDE}" first="{5C4D8EE8-62FF-E911-A811-000D3A799417}" /></cookie>

I then use the expression below to sanitise the characters so that I can use it in the subsequent page. It looks a bit clunky and verbose, but the key thing here is that when xml function to encode the XML, it also sanitises it. encodingJSON is a temporary object used to store the paging cookie XML. This JSON is what gets converted to XML and cleaned up with split and substring.

if(equals(iterationIndexes('Do_until'),0),
	'',
	concat(
		'paging-cookie=''', 
		substring(
			first(
				skip(
					split(
						string(xml(setProperty(variables('encodingJSON'),'x',variables('pagingCookieCleansed'))))
					,'<')
				,1)
			)
		,2),
	'''')
)

Here is the Flow running through all pages.

You can download this sample Flow from https://1drv.ms/u/s!AvzjERKFC6gOx3ZbdK8YOBYat0CV?e=M41ONd

Using Custom API as a trigger for Flow

Dropping new goodies straight to Microsoft Docs, without any formal announcement, has now been normalised. Couple of Virtual Table features have been “announced” without much fanfare this way. The ability to trigger Flows from Custom API is one such unannounced feature.

Custom API is a feature in Dataverse that is very similar to Custom Process Actions (formerly known as Actions). You can refer Compare Custom Process Action and Custom API doc to understand the differences. There are many useful tools to help you work with Custom API in XrmToolBox. They are

  1. Custom API Manager by David Rivard
  2. Custom API Tester by Jonas Rapp
  3. Custom Action to Custom API Convertor by Mark Carrington

The UI to create a new custom API is a bit to many clicks. So, we will use Custom API Manager to create our API and Custom API Tester to trigger it. You can easily create a new Custom API using Custom API Manager.

XrmToolBox Custom API Manager Tool

The important points to note when you create a Custom API that can be used as trigger are:

  1. You cannot have the IsFunction set to true
  2. You cannot have IsPrivate set to true
  3. You cannot have Allowed Custom Processing Step Type set to None

After you have created your Custom API, you need to create

  1. Root Catalog
  2. Atleast one Child Catalog
  3. One Catalog Assignment record for each Custom API or Table
  4. You cannot add Catalog Assignment records straight to the root catalog

You can do these right inside the solution. Here is how my root catalog record looks like.

Root Catalog

The unique name of the catalog needs to have a publisher prefix, otherwise you will get this error.

Root Catalog no prefix exception

After creating the root catalog, you can create the child catalog from the related records area in the form.

Child Catalog list

This is how my child catalog looks like

Child Catalog Record

I am going to add all my Custom APIs to this Custom APIs sub-catalog as Catalog Assignment. You can add both Custom APIs and Tables/Entities (if the Custom API is bound to an Table) from this screen.

Catalog Assignment

Here is how my solution looks like after creating Catalog, Custom APIs and Catalog Assignment records.

Solution with Custom API and Catalog records

Next step is to create the Flow with the “When an action is performed” trigger. In this trigger you need to choose the Root catalog, the sub-catalog and the custom API in that sub-catalog.

If the Custom API is bound to an Table, you need to choose the Table and then the Custom API, as it filters down the Custom API by Table.

Custom API bound to Table trigger

Here is how my trigger looks like. Since my Custom API is not bound to any entity, I choose none for the Table name.

Unbound Custom API Trigger

My Flow will now run when I trigger the Custom API. I can do this using Custom API tester.

Custom API Tester tool

I can also trigger the Custom API from Flow itself.

Trigger Custom API from Flow

This will cause trigger my Flow that was waiting for that Custom API call.

Flow executed on Custom API call

One thing to note is that you created the custom API with Is Private set to true, you will still see that custom API in the Flow trigger, but when you save the Flow, you will get this exception.

Private Custom API error

One word of warning: This is a preview feature. So, don’t use it in production yet. This is a welcome feature, and it opens up Flow to more integration scenarios.

References:

  1. Trigger flows when a Microsoft Dataverse action is called
  2. Catalog and Catalog Assignment Tables (Thanks to Jim Daly for sharing this docs link)