Dynamics 365 Business Central: Power Automate Find Records action demistified.

When talking about Power Automate or Azure Logic Apps workflows for Dynamics 365 Business Central, I often see that the Find records action is sometimes abused and generates lots of confusion.

The Find records action always returns a collection or list of records that matches the applied filters, even if there’s only one record. The current action also requires that Power Automate or Logic Apps workflow’s creators understand the syntax of the OData query in order to apply filters.

But despite that, there’s a real limit that generates confusion.

To explain that, I’ve created a simple extension that generates records in a custom table. In this table I’ve created 20100 records (random number but not too much random ):

In my demo extension, I’ve also published an API on top of this custom table.

What happens if I called the API by sending a GET request in order to retrieve all records?

Business Central APIs applies paging to the OData response. Paging ensures that the quantity of data that is returned by an OData URI doesn’t overwhelm Business Central Server or client program that you use to capture data, while optimizing performance.

Business Central online uses a maximum of 20,000 entities per page by default and you cannot change this parameter. With Business Central on-premises, page size is determined by a configuration setting on the Business Central Server (ODataServicesMaxPageSize), that you can change, but that’s another story.

The response of the GET API call is the following:

The API gives the first 20000 records (records with entryNo from 1 to 20000) and it gives an @odata.nextLink url for retrieving the next page of records.

If I call the @odata.nextLink url, the next page of data (100 records in my demo data, with entryNo from 20001 to 20100) is returned:

Now let’s do that with the Find records action in Power Automate.

What happens when using the Find Records action in a Power Automate or Logic Apps flow?

Let’s create a very simple workflow like the following:

Here I’m using the Find records (V3) action in order to retrieve all records that I have in my custom table (20100 records). When I execute the workflow, this is the response from the Find records (V3) action:

The Find records (V3) action returns 1000 records and gives you an @odata.nextLink url for retrieving the next page of 1000 records. The 1000 record limit is an hard limit imposed by design in this action, simply because this action was not borned for retrieving a massive amount of records.

The @odata.NextLink url can be retrieved in a variable by using the following formula:

body('Find_records_(V3)')?['@odata.nextLink']

but this is not a BC API url.

In my opinion, the Find records (V3) action is not the best choice to use when you need to retrieve a lots of Business Central records with Power Automate or Azure Logic Apps. For these scenarios it’s better to directly use the HTTP action.

When using the HTTP action, you need to first acquire the token (via OAuth 2) and then perform a GET operation to your API endpoint (some more steps to do, I know). Bu then the result is the following:

As you can see, now you have 20000 records as output of your call and you have also the @odata.nextLink that you can recall (in a DO UNTIL loop) in order to have the next OData page and retrieve all the records from Dynamics 365 Business Central.

Much more performant…

Original Post https://demiliani.com/2024/11/15/dynamics-365-business-central-power-automate-find-records-action-demistified/