Jan 29, 2020

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.

Parameters

NameKeyRequiredTypeDescription
EnvironmentdatasetTruestringSelect an Environment
Entity NametableTruestringSelect an Entity
Aggregation transformation$applystringA sequence of OData aggregation transformations
Filter Query$filterstringAn 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
	)
)

Sample data:

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
    }
  ]
}

Answering