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

Colin Maitland, 22 February 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 penultimate article, I will begin to demonstrate how my ‘Marketing List App’ creates new or replaces existing static Marketing Lists with many thousands of Marketing List Members in only a few seconds.

Here is an outline of the complete end-to-end process. The user …

1. uses the Slicers in the Power BI report to filter the Supporters

2. optionally also uses Report Level Filters in the Power BI report to filter the Supporters

3. exports the selected Supporters from the Power BI report to a Microsoft Excel file named ‘Supporters (Marketing List App).xlsx’

4. saves the Microsoft Excel file to specified Document location in Microsoft SharePoint

5. starts a process that imports the contents of the Microsoft Excel file into a Common Data Services (CDS) entity

6. uses the ‘Marketing List App’ to create new or replace existing Marketing Lists from the selected list of Supporters.

In order to complete the final step, after the User clicks Refresh in the Marketing List App to obtain a count of the number of Supporters, selects a Marketing List option, enters the Name for new Marketing Lists or selects existing Marketing Lists and then clicks Create new Marketing Lists or Replace existing Marketing Lists, the Marketing List App must then create new or replace existing static Marketing Lists.

The creation of new Marketing Lists requires these steps to be completed:

1. Create new static Marketing List for Individuals

2. Add Supporters that are Individuals to the Marketing List for Individuals

3. Create new static Marketing List for Organisations

4. Add Supporters that are Organisations to the Marketing List for Organisations

The replacement of existing Marketing Lists requires these additional steps to be completed prior to then completing the same steps as those for the creation of new Marketing Lists:

1. Delete existing Marketing List for Individuals

2. Delete existing Marketing List for Organisations

The times required to delete and create Marketing Lists that contain many thousands of Marketing List Members requires many minutes and even up to an hour or more to complete. However, the following approach used by my Marketing List App and related components reduces this time to a few seconds:

When deleting the existing Marketing Lists, the Marketing List App, invokes this formula to concurrently rename and deactivate the existing Marketing Lists. This causes those Marketing Lists to be hidden from the User.

This formula also sets the value of a field named ‘Marketing List App (Delete Marketing List)’ to Yes. A scheduled daily recurring Dynamics 365 Bulk Record Deletion Job then performs the actual deletion of those static Marketing Lists where the value of the ‘Marketing List App (Delete Marketing List)’ field has been set to Yes.

Resultingly, from within the Marketing List App the deletion step appears to the user to only take one or two seconds to complete.

When creating the new Marketing Lists, the Marketing List App invokes this formula to start a Power Automate Flow. The Power Automate Flow invokes an action that results in the new Marketing Lists being created and populated with many thousands of Supporters in only a few seconds. This process will be demonstrated in my next article.

Notice the references to ‘MarketingListApp(CopyDynamicListToStatic)’, ‘varDynamicMarketingListIndividualListId’ and ‘varDynamicMarketingListOrganisationListId’. These are clues to what will be explained in my next and final article in this series.

The ListName, Purpose and Description values passed to the Power Automate Flow are obtained from those provided by the user if creating new Marketing Lists or from the existing Marketing Lists if replacing existing Marketing Lists.

In my next and final article in this series, I will demonstrate how the Power Automate Flow creates and populates the new or replacement Marketing Lists and populates them with many thousands of Supporters in only a few seconds.