Power Automate have come a long way indeed. And with all the advancements in Power Automate, tasks which used to take weeks now take few days or even less.
But sometimes the simplest of requirement which we take almost as granted come across as a challenge. And one of this was to retrieve more than 5000 records using FetchXml queries in Power Automate. In this blog I am going to discuss the following.
- How to use paging when using FetchXml in Power Automate?
- How to retrieve large record set of CDS using FetchXml in Power Automate?
Let’s explore the challenges we faced here. The first and foremost thing – As of the current version of Power Automate, you can use FetchXml only with Common Data Service (Current Environment) connector. For this example, I will use accounts entity. I have around 12000 account records in the system.
And I am using the List Records action of the specified connector execute in FetchXml. Below is the screenshot of my fetchxml. Nothing fancy in there except the PageNumber variable.
Below is the PageNumber variable declaration.
So far so good! And quite easy too. When we use the FetchXml, by default it return 5000 records in the first query. No surprise in this. If we have worked with Dynamics 365 or CDS SDK, we are already aware of this.
Now the next question – How do we get the next set of records? And here comes the hard part. If we observe, the List Record step for CDS Current environment connector return a property Next link. The property obviously look like to contain some information to fetch the next set of records.
Unfortunately this property is empty when you use Fetch Xml with this connector. If you would have used OData then this property return a URL to retrieve the next set of records.
This is where things can get really complex. It’s quite obvious we need some kind of cookie or skip token to fetch the next set of results. But how do we get the paging cookie information?
The paging information is actually in the response body. Below is a screenshot of sample response containing the paging cookie information.
As you can see we have couple of properties returned in the body.
- @Microsoft.Dynamics.CRM.fetchxmlpagingcookie
- @Microsoft.Dynamics.CRM.morerecords
While there is a hope there is a problem too. The next problem is to get the paging cookie out of the body and use it to fetch the next set of records. Trust me, it’s quite complex to do the same especially with expressions in Power Automate. Hence I have split this into multiple steps.
First thing is to take out the paging cookie from the response body. I have declared a variable and below is the expression to get the same. As best practice I have specified the expression in the comment of the action.
outputs('List_records')?['body']['@Microsoft.Dynamics.CRM.fetchxmlpagingcookie']
Below is the sample output of this operation
"<cookie page="1"><name last="Account 5997" first="A. Datum Corporation (sample)" /><accountid last="{61500940-AC3B-EB11-A813-000D3A56957F}" first="{A78DC821-CB36-EB11-A813-000D3A56902E}" /></cookie>"The paging cookie come with double quotes at the start and end. So we need to trim the double quotes from the start and end. And while that is fairly easy to do in some languages, in Power Automate unfortunately the trim function only trim spaces. So below is the expression I wrote to do the same.
substring(variables('PagingCookie'),1,sub(length(variables('PagingCookie')),2))
I then increase the page number by 1 and then use the incremented page number and cookie in the next query.
But wait, what is the replace expression doing there in the paging-cookie attribute value. If you observe the value of the paging cookie in our first step, it is basically on HTML format. However when we pass the paging cookie information to fetch xml, we should perform HTML encoding of the paging cookie information. And again here Power Automate disappointed me. There is no function for HTML encoding in Power Automate. So I wrote the below expression to do the same.
replace(replace(replace(variables('TrimmedToken'),'<', '<'), '>', '>'), '"','"')“TrimmedToken” is the variable I used in the earlier step.
And that’s it. When I run this the next set of records are retrieved. In real implementations, you would ideally like to implement this entire procedure using do-until action until the paging cookie information is no longer present in the body of the response.
Was quite an intriguing experience for me and I am pretty sure would be for you as well.
You may also like the below posts
Hope this helped!
Debajit Dutta
Business Solutions MVP
Discover more from Debajit's Power Apps & Dynamics 365 Blog
Subscribe to get the latest posts sent to your email.