Retrieving more than 5000 records using Power Automate Flow.

Hello Guys,

In our previous blog we have seen how we can create word document and attach it to CRM entity record as notes and use the same documents which is stored in CRM record as notes and will attach it to email. Today we are going to see how we can retrieve more than 5000 records using Power Automate Flow.

Do follow us to know more about Dynamics and Power Platform. Also if you like our blog then please comment and share this blog with your friends.

In our one of the blog we have seen how we can retrieve more than 5000 records using code(C#). Here we have used the concept of pagination. Same concept we are going to use with power automate flow.

First of all we will create one manual trigger flow and try to retrieve records using List Rows.

As you can see in above flow, we are retrieving Account entity. Here we can retrieve max 5000 records per request.

Consider a scenario where we need to retrieve more than 5000 records then we can not do the same using this step. There should be some mechanism to retrieve these records. We can use the same pagination concept here also. Let's see how to do that.

First of all we will run the flow and will see what we are getting in the output.

Click on click to download. This will open a new tab.

Copy all content and open any online JSON formatter website and format the JSON. You can use Notepad++ also. I have used Json Formattor website to perform this formatting.

As you can see we have formatted our output JSON. Now expand body node.

As you can see here in body node we have some cookie information which we are going to use in our pagination for retrieving more than 5000 reccords.

Here as you can see there is one property named @Microsoft.Dynamics.CRM.morerecords which indicates we have more records which is not retrieved yet.

Also another property named @Microsoft.Dynamics.CRM.fetchxmlpagingcookie which provides the page number and page cookie data in encoded form. we will be using this information in our flow.

First of all we will declare two variables in our flow as shown below:

Now add a new variable which is of type object. This will store all accounts retrieved from fetch query.

Now add do until loop and specify loop condition as shown below:

empty(variables('PagingCookie'))

Now inside the do until loop add a new step which will retrieve Accounts using List Rows. Configure it as shown below :

Now we will be using fetch xml inside this step. Configure it as shown below:

add(iterationIndexes('Do_until'),1)

if(equals(iterationIndexes('Do_until'),0),
'',concat('paging-cookie=''',
 substring(first(skip(split(string(xml(setProperty(variables('Account Json'),

'z',variables('PagingCookieTrim')))),'<'),1)),2),''''))

Now add a new step as shown below. This is set variable step.

if(empty(outputs('Accounts')?['body']?['@Microsoft.Dynamics.CRM.fetchxmlpagingcookie']),'',decodeUriComponent(decodeUriComponent(outputs('Accounts')?['body']?['@Microsoft.Dynamics.CRM.fetchxmlpagingcookie'])))

Now add a new step as shown below. This is also set variable step.

if(empty(variables('PagingCookie')),'',replace(substring(variables('PagingCookie'),add(indexOf(variables('PagingCookie'),'pagingcookie="'),14)),'" istracking="False" />',''))

So our flow is ready. So now we will run the flow and will check whether we are able to retrieve more than 5000 records or not.