How to use the CDS Connector for Microsoft Power Automate
Content
You might not have heard of Microsoft Power Automate, but maybe you have heard of Microsoft Flow. Flow was recently renamed Power Automate. But that’s cosmetics. What you really need to know is: Power Automate/Flow replaces classic Microsoft Dynamics 365 workflows with so-called flows (which is why existing workflows are now triggered via the “Flow” menu).
NB: You will still find Power Automate referred to as Microsoft Flow. For accuracy’s sake, we will refer to it by its proper, current name.
And the great thing about flows is: nearly everything is possible. With Power Automate, these new workflows can be created quickly and they work nicely for every proof of concept. With dozens of connectors functionality seems endless. There is also a huge amount of documentation about the available connectors.
In this article I want to show you how to use the Common Data Service (CDS) connector for Microsoft Power Automate and how you can employ functionality that was non-existent with out-of-the-box workflows.
What is the Common Data Service Connector?
The CDS connector can be used in Power Automate, Canvas Apps and Logic Apps. It provides access to the Common Data Service database of Dynamics 365 environments. In this article I want to focus on some advanced functionality, in particular the aggregate function.
For this we are using the List records function with Dynamics 365 Sample Data to get details about our opportunities.
| Name | Key | Required | Type | Description |
|---|---|---|---|---|
| Environment | dataset | True | string | Select an Environment |
| Entity Name | table | True | string | Select an Entity |
| Aggregation transformation | $apply | string | A sequence of OData aggregation transformations | |
| Filter Query | $filter | string | An OData filter query to restrict the entries returned (e.g. stringColumn eq ‘string’ OR numberColumn lt 123) |
OData aggregate
Since we want to use the aggregate function to get a some of our opportunities by statecode, we need to check the OData aggregation. This documentation is a full overview how the different aggregate methods work. We want to focus on use with Microsoft Dynamics 365 and use the examples from Microsoft regarding the Web API.
Opportunity est. by status reason
One common requirement is an opportunity overview based on status reason. This gives a forecast of the sales pipeline.
For this simple task we could use the aggregate Sum with a Group by to see the Estimated Amount by our sales phase.
Aggregation transformation:
Groupby(
(statecode),
Aggregate(
Estimatedvalue_base with sum as estimatedvalue_base
)
)
Based on the sample data we would expect a result of €116,000 for Open and €188,000 for Won. To compare these values, we using the Dynamics 365 _base field and our system currency is €.
Body:
{
"@odata.context": "https://flow-apim-europe-001-francecentral-01.azure-apim.net/apim/commondataservice/****-****-****/$metadata#datasets('default.cds')/tables('opportunities')/items",
"value": [
{
"@odata.id": "https://promx***.crm4.dynamics.com/api/data/v9.0/opportunities(00000000-0000-0000-0000-000000000000)",
"@odata.etag": "",
"ItemInternalId": "00000000-0000-0000-0000-000000000000",
"statecode": 0,
"_statecode_label": "Open",
"estimatedvalue_base": 116000
},
{
"@odata.id": "https://promx***.crm4.dynamics.com/api/data/v9.0/opportunities(00000000-0000-0000-0000-000000000000)",
"@odata.etag": "",
"ItemInternalId": "00000000-0000-0000-0000-000000000000",
"statecode": 1,
"_statecode_label": "Won",
"estimatedvalue_base": 188000
}
]
}FAQ
What are connectors used for in Power Automate?
A connector allows the underlying service to connect with Microsoft Power Automate. It enables users to connect apps, data and services in the cloud.
What connectors are available in Power Automate?
There are a great many connectors to use with Power Automate. Some of the most popular are: Dropbox, Google Calendar, Salesforce and, of course, other Microsoft apps such as OneDrive or Outlook. Find a list of all connectors here.
