PowerAutomate Microsoft Dynamics 365 Finance and Operations apps

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 

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.

Power Automate – Get list of entities

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.

Power Automate – Get list of entities – Result

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/.

1	2020-12-12 07:33:59 GET /data/ - 443 ja.tomas@axazure.com

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.

List of entities obtained from the web browser https://cloudax.jatomas.dynamics.com/data

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:

Power Automate – Get a record

• 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

Primary Key of Customer groups entity from Visual Studio

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.

Execution error details.

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

Power Automate – Get a record of customer groups

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.

Power Automate – Get a record – Execution result

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

1 2	2020-12-12 08:02:56 GET /data/CustomerGroups(dataAreaId='10',CustomerGroupId='USMF') ja.tomas@axazure.com - 404 2020-12-12 08:07:58 GET /data/CustomerGroups(dataAreaId='USMF',CustomerGroupId='10') ja.tomas@axazure.com - 200

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:

User default company

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.

Power automate – 404 – Not found

1	2020-12-12 08:15:37 GET /data/CustomerGroups(dataAreaId='USRT',CustomerGroupId='10') ja.tomas@axazure.com - 404

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.

Power Automate – Lists items presents in table

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

Power Automate – Advanced options

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:

Power Automate – 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.

Power Automate

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-companyparameters.

1	2020-12-12 08:25:08 GET /data/CustomerGroups %24filter=dataAreaId+eq+%27USRT%27+and+CustomerGroupId+eq+%2710%27&%24top=1&%24select=CustomerGroupId%2cDescription&cross-company=true ja.tomas@axazure.com - 200

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.

Power automate – Operation executed

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.