Power Automate & MSDyn365FO: Deep dive (II)

We continue with the series of articles about Power Automate and the different operations that can be performed on Microsoft Dynamics 365 Finance and Operations apps.

As we saw in the previous article, thanks to the MSDyn365FO premium connector for Power Automate, we have a series of triggers and actions to interact with our system. In this new post we are going to start talking about the actions, specifically the different reading actions that exist, which are:

• Get list of entities
• Get a record
• Lists items presents in table

If you want to learn more about the available trigger, take a look at this article where you will see a practical case of how to launch a Power Automate Cloud Flow when a business event occurs: No-Code: Extend MSDyn365FO with the Power Platform – Juan Antonio Tomás (jatomas.com)

Get list of entities

The first action that we are going to see is the Get list of entities. This is a fairly simple action in which we only have to indicate an input parameter, which is the URL of the instance on which it will be executed.

The result that the execution of the same returns us is quite predictable, a list with all the public entities that we have in the system, as we can see in the following screenshot.

If we go to see the IIS log on which we are executing the operations, we can verify that the operation we are launching on the system is nothing more than a GET call to the url https://jatomas.cloudax.dynamics.com/data/.

That is, the result is the same as the one we obtain when we call that URL from the web browser itself, but in JSON format.

Get a record

If what we want is to make a query on a specific record in the database, we can use the Get a record action. To do this, we need to indicate the following parameters:

• Instance: Url of the MSDyn365FO instance we want to act on.
• Entity name: Name of the entity in which we want to make the query (Customers, Customer groups, Vendors, Products …).
• Object id: Unique identifier of the record we want to obtain.

The Object Id, generally, consists of the company code (dataAreaId), followed by the value or values of the fields that make up the primary key of the entity. We can search the values that make up this object id in different ways. For example, let’s say we want to get a specific Customer Group. One option would be to go to Visual Studio, find the DataEntity CustCustomerGroupEntity and see the fields that make up the PK of it

On the other hand, we can use a little trick within Power Automate, executing the action only with the company code in the object Id. Obviously, the execution will be wrong, but in the result of the same it tells us which are the values that we have to indicate.

In this specific case, we must indicate the DataAreaId and the CustomerGroupId.

IMPORTANT: The values inside the object Id must go exactly in the indicated order, separated by commas ( , ), and without spaces between them, as we see in the previous image.

Once the parameters are correctly completed, we can see the result of the execution in which we obtain a JSON with all the data of the group of clients that we have indicated.

Let’s review the IIS log again to check the operations that are performed during execution.

As we can see, the execution of the action results in a GET call on the CustomerGroups entity, passing the company code and the customer group id as parameters. Here we can also check the importance of the order of the parameters. In the first execution, the order is not correct, so we get a 404 error, while in the second, the execution is successful and we get a 200.

Limitations

Well, yes, this specific action has a fairly large limitation: We can only obtain records of the company that we have associated by default in the system through user preferences.

As you all know, all users have a company that is accessed by default, and that can be modified from the user’s preferences:

What happens if we make a query with a company that is not the one we have associated with? Well, as we can see in the following image, as well as in the IIS log, when trying to obtain a record from a different company, the system returns a 404 – Not found error.

This is not really a limitation of Power Automate as such, but rather an issue related to how Dynamics 365 handles OData calls for company changes. If you make the same call directly in the browser, you will see that the result is an empty array, so it does not find the record in question either.

How can we deal with this “problem”? Well, just like we do with X++, in order to query data from different companies, we must use the cross-company statement. If you add cross-company=true to the previous URL, in the browser, it already allows you to search for the different companies in the system and filter by the dataAreaId, but this specific Power Automate action does not allow us to use this parameter, so we are going to see the last of the reading actions to see how to solve this situation.

Lists items present in table

The last of the actions that we are going to see today is much more flexible than the previous one. On the one hand, it allows you to obtain more than one record from a specific table, and on the other, it has many more parameters, which no longer have so much to do with the values necessary to search for a record in Dynamics 365, but with the parameters or options that OData offers us to make the search for them more flexible.

The first thing we are going to see when selecting this action is that we need to indicate the instance on which we are working, as well as the entity on which we are going to search, but also, we can see a series of advanced options that will give us the flexibility that we mentioned

These options are going to allow us:

• Aggregation transformation: It allows us to use OData aggregation transformations like sum, max, average…
• Filter Query: Filter by specific fields using operators for filtering like eq (equal), greater than (gt)…
• Order By: Sort the results we get by a series of fields
• Top Count: Get the first N records that meet the indicated conditions.
• Skip Count: Discard the first N records obtained from the result. (Using this option together with Top Count we can obtain paginated values, taking into account that we have a limitation of 10k records per call).
• Select Query: Indicate the fields we want to obtain.
• Cross Company: Get records from different companies.

Here you can get more information about using OData in Dynamics 365 F&O apps: Open Data Protocol (OData) – Finance & Operations | Dynamics 365 | Microsoft Docs.

As you can see and imagine, we are facing a much more powerful action than the previous one, and by correctly combining the different advanced options that we have, we can make data collection much faster and more efficient. For instance, it is not the same to make a select * from table, than to indicate the list of fields that we want to obtain and that we need.

Following with the previous example, now we are able to obtain the Customer groups of a company that is not the one we have associated by default using the following advanced options:

Unlike with the previous action, in this case we can expect to obtain more than one record, so in the action after this one, Power automate directly includes an Apply to each so that it is executed in each and every one of the elements obtained.

And once we run the flow, we can see how Power Automate “translates” the execution of this action to an http call in the IIS log, using the filter, top, select and cross-company parameters.

And we can also see how this last action is applied to each of the elements obtained, and the result for each one of them.

And with this we leave it for today, I hope you have found it interesting, and any questions you may have, I read you in the comments.

Thanks!!