Creating/Replacing Marketing Lists in Dynamics 365 from Power BI - Part 8

Colin Maitland, 02 March 2021

In my first article of this series, I introduced a demonstration of a custom Power BI embedded Canvas App, named ‘Marketing List App’, that is used to create new or replace existing static Marketing Lists in Dynamics 365 populated with a list of Supporters selected in a Power BI report.

In this final article, I will finish demonstrating how my ‘Marketing List App’ creates and populates static Marketing Lists with many thousands of Marketing List Members in only a few seconds. I have provided clues to this in my first and previous articles of this series; i.e. in my first article I referred to the standard ‘Copy to Static’ functionality for dynamic Marketing Lists and in my previous article I referred to a custom Power Automate Flow named ‘MarketingListApp(CopyDynamicListToStatic)’.

In this article, I will demonstrate how my custom ‘Marketing List App (CopyDynamicListToStatic)’ Flow invokes the standard Dynamics 365 ‘Copy to Static’ functionality for dynamic Marketing Lists to create and populate the new static Marketing Lists.

Firstly, the following two dynamic Marketing Lists have been added to Dynamics 365.

The first one is configured using Manage Members to select those Contacts, i.e. Individuals, that are linked to Supporters in the custom ‘Supporters (Marketing List App)’ entity using the Contact lookup field in that entity. The Contact lookup field is populated by a custom Power Automate Flow as each new Supporter is added by the ‘Refresh Supporters (Marketing List App)’ Data Flow.

The second one is configured using Manage Members, to select those Accounts, i.e. Organisations, that are linked to Supporters in the custom ‘Supporters (Marketing List App)’ entity using the Account lookup field in that entity. The Account lookup field is populated by a custom Power Automate Flow as each new Supporter record is added by the ‘Refresh Supporters (Marketing List App)’ Data Flow.

A Dynamics 365 Business Rule is used to lock the fields on these Marketing Lists so their Name, Purpose, Source and Description cannot be changed.

Before invoking the ‘Marketing List App (CopyDynamicListToStatic)’ Flow the Marketing List App concurrently retrieves the IDs of these two dynamic Marketing Lists. These are identified their Type, Name, List Member Type, Source, Purpose and Status. This is done only once, when the Marketing List App is opened using the following formula:

Whenever the Marketing List App invokes the ‘Marketing List App (CopyDynamicListToStatic)’ Flow, it passes the value of the varDynamicMarketingListIndividualListId or the varDynamicMarketingListOrganisationListId variable to the flow, along with other values such as Name, Purpose, Description and OwnerId. The formula used is demonstrated in my previous article.

The ‘Marketing List App (CopyDynamicListToStatic)’ Flow has these steps. The varSourceDynamicMarketingListId, varName, varSource, varPurpose, varDescription and varOwner variables are initialised from using paramater values provided by the Marketing List App.

The ‘Invoke CopyDynamicListToStatic’ step is a ‘Perform an unbound action’ step and is configured to invoke the ‘CopyDynamicListToStatic’ action for the Marketing Lists entity. The Item ID is populated from the previously initialised varSourceDynamicMarketingListId variable. When invoked, this step only takes a few seconds to create a new Marketing List and populate it with many thousands of Marketing List Members from the dynamic Marketing List identified by the varSourceDynamicMarketingListId.

When completed, this action, returns the new Marketing List including its Id.

The Check New Marketing List step confirms that the new static Marketing List exists.

If the new Marketing List exists then the steps that follow set a variable named varNewMarketingListId to the Id of the new Marketing List and then updates the Name, Description, Owner, Purpose and Source of the new Marketing List to those provided by the Marketing List App. A result of success and the Id of the new Marketing List are then returned to the Marketing List App.

Here is an example of two newly created Marketing Lists using this approach:

In conclusion, the entire end-to-end process was implemented using these components: Power BI report, Power BI Export to Excel, SharePoint Document Location, a custom Common Data Service entity, a Power Automate Data Flow, two Power Automate Flows, a Canvas App and a Dynamics 365 Security Role.