Migrating Marketing List Members in Microsoft Dynamics 365 CRM

The Requirement:

We have recently had a requirement from a Customer to migrate members of Marketing Lists.

Some background:

We have been doing an Environment to Environment migration (in the same tenant) due to conflicts in Base Currencies causing issues with integration with Business Central, but that’s a whole other article in itself!

So, using a tool called KingswaySoft, we have migrated their base data; Account, Contacts and Leads as well as the Marketing List Header records.

They are going to be rolling out Dynamics 365 Marketing, but due to the way these new segments are stored, getting the current (legacy) marketing lists into segments wasn’t quite so straightforward. So the solution was to take the existing marketing lists (Of which there are many, with thousands of people assigned to each) so that they could then use these to build their new static segments using the existing data.

So, we migrated the Marketing Lists. No Problem, a relatively straightforward job, or as straightforward as any data migration is at least.

Then came the list members.

For those of you that don’t already know this, list members are stored in what is called an intersect table. Dataverse uses these for various points throughout the system to create links from one table to another. The prime example being Teams and Users, there is an underlying table called Team Memberships that creates the N:N relationship between the Many Users and the Many Teams they can be members of. 

Attempt 1

Thinking that the migration of marketing lists would be in a similar vein, I set about getting my Fetch XML query set up to get the list members out of the source system. (We are using the Fetch XML as we are only taking a subset of the marketing lists that are still relevant).

Then I come to map them over. For those you that have never used KingswaySoft to transfer data in Dataverse before, we use it primarily because in most tools you can’t access some of the fundamental system tables, such as Team Memberships, but in KWS, you can.

So, I look through the list (probably around 10 times!) and the List Members table isn’t there.

It turns out that List Members is a particularly special type of table, and Entity Reference Table. Never heard of them? I hadn’t either. It was a new one for me, and after 7 years of working with CRM, there isn’t much about the underlying data structures that surprises me now.

But, in this world of Dataverse architecture, I figured, where this is a will, there is a way.

I checked the API, and there it was, in all its glory:

So, I knew we could get to it, the question was just how.  

Attempt 2

I first tried sticking with KWS. Set up an OData Destination and eventually managed to find the List Members Table. 

I thought happy days but unfortunately, it was not.

Due to the complexity of the List Members table, having links to Leads, Contacts and Accounts and the Entity Type being defined on the row, it makes it a little more complicated.

There are three main fields on the List Members Table.

1. List ID – The Marketing List
2. Entity ID – The ID of the Linked Record
3. Entity Type – Defines which entity the link it to.

Or in the OData Format:

But if we look at the options in KWS for this table, this is what we get:

Note the absence of two out of the three main fields we are looking for!

OK, so this meant even the OData Destination wasn’t going to work. 

Attempt 3

On to plan C – Power Automate.

One of the main restrictions in using Power Automate to do cross environment data transfers is the fact that when you are creating Flows, they live in an environment and the Dataverse connector limits you to that environment. So how do we get over that obstacle to get the data out of our source environment and into our Target Environment.

We use HTTP request:

To do this, you will need an APP Registration, and to add the App User in Dataverse with a security role assigned that has permissions on the table you are accessing:

As I have already migrated the List data, I have the corresponding ID that matches in the source system as I made sure to maintain the GUID’s during the data transfer. This means I can use the legacy connector of when a record is selected to choose the lists I want to get the members for.

This only works if the List ID’s have been maintained, as you will need it to filter the OData Query you are passing to the List members table to retrieve the data.

This returned me a lovely little (Ok, quite a large!), JSON Array. So, I now have my data from my source, I just need to get it into the target.

My first thought was to just do a create action, put list members as a custom value in the table name and it will bring through the fields I need right? Again, this didn’t go as hoped. It brought through the List ID field as well as the Entity Type field (So some progress!), but alas no Entity ID field to populate.

The next port of call was to repeat the OData action, but with my Target Environment. I diligently created my Schema and replaced this with the dynamic values required, ran it and encountered this error:

Bad Grammar aside, this was actually a pretty useful error as it pointed me towards the solution!

The Solution:

Combining the HTTP request on the Source we did in Attempt 3, I then created a Switch, so I could determine whether it was a Contact, Account or Lead that needed to be linked, and then created some actions in each case.

This is the Contact Case

You first need to do a “Get a Row by ID” Action on the Contact record (Or Account/Lead in those cases) as you need the OData ID field to create the link. But then you use the Relate Rows Dataverse action, give it the ID of the list you want to relate, select the appropriate relationship and give it the OData ID from your Get Action.

Note: You can do this use either the “When a record is selected” Action or from the HTTP Request action (You might note, for the PA aficionado’s that I have in fact had to parse the HTTP request to get the data out using the Parse JSON action, but I can’t do all the work for you!)

Et voilà, you now have your Marketing List Members migrated.