Getting at Marketing List Member Details in Dynamics CRM

Its not obvious how to get at Marketing List Member Details in CRM; in fact, there isn’t an out-of-the-box way to get basic details about the Leads or Contacts on your Marketing Lists. Here’s a solution that involves creating a custom FetchXML query, that you can build with the FetchXML Builder tool You can then use the query by running it directly within the FetchXML Builder; or use the query to build a custom FetchXML SSRS Report.

Marketing Lists in Dynamics 365 can be static or dynamic. I’ve only tested this with static marketing lists as of now, so I’ll hold off on commenting whether this will also work for dynamic-type marketing lists. But, to solve the common problem of not being able to get at Marketing List Member Details in Dynamics 365, we can build a Fetch XML query.

Many-to-Many Relationship (a.k.a., N:N Relationship)

There’s a many-to-many relationship between Marketing Lists and Marketing List Members (Leads or Contacts). There is no obvious way to see a view of the details of your marketing list members. For example, say you wanted to know the city and state of all the members of a given marketing list. The city and state data is on the contact record, and the contact record is on the Marketing List, so why can’t you just create a view of the contacts, with the respective city and state using Advanced Find?

The problem is that the relationship between Marketing Lists and Marketing List Members (e.g., Contacts) is a many-to-many relationship, and so the Advanced Find won’t find this relationship by default (out-of-the-box configuration with no customizations).

There are work-around solutions to create this custom N:N relationship, which would require some configuration. But if you want to get at the data without creating a customized Advanced Find view, then the alternative is to create a custom FetchXML query.

FetchXML Builder with the XRMToolbox

The XRMToolbox is a must-have tool for CRM Consultants, and sometimes even CRM Power Users. It’s a free download that includes a tool called the FetchXML Builder.

Once, you get started with the FetchXML Builder, you can build a query that looks like this, and you’ll be able to query any Marketing List to see Lead or Contact details, like city and state of contacts on your marketing list, as we discussed in our example.

[xml]
<fetch>
<entity name=”list”>
<attribute name=”listname” />
<attribute name=”listid” alias=”List” />
<filter type=”and”>
<condition attribute=”listname” operator=”eq” value=”Marketing-List-Name-here” />
</filter>
<link-entity name=”listmember” from=”listid” to=”listid” intersect=”true”>
<attribute name=”entitytype” />
<attribute name=”listmemberid” />
<attribute name=”entityid” />
<link-entity name=”contact” from=”contactid” to=”entityid” alias=”Contact”>
<attribute name=”emailaddress1″ />
<attribute name=”owneridname” />
<attribute name=”owneridtype” />
<attribute name=”ownerid” />
<link-entity name=”team” from=”teamid” to=”owningteam” alias=”Team”>
<attribute name=”name” />
</link-entity>
</link-entity>
</link-entity>
</entity>
</fetch>
[/xml]