Dynamics 365 CE: Looping through Marketing List Members in a Power Automate Flow
By now, most reading this article understand Microsoft's Power Automate (formerly Microsoft Flow) to be the latest smash hit out from the reigning champion of enterprise software in the industry. So just how can we use this tool in conjunction with Dynamics 365 Customer Engagement's Marketing List Entity? The secrets of the elusive solution will be revealed shortly.
First, let's set the stage. A commonly used action in Flow is that of sending email, which is not surprising when you consider just how many options you have to do this: Notifications, Office 365 Outlook, Outlook.com, Gmail, Mail, SMTP, the list goes on.
Recently, I was building a Flow to export and send data from Dynamics 365 as an email attachment to a group of end users. After achieving core functionality, we decided to get a little bit fancy on how to handle the recipient list. We wanted a way for end users to add or remove themselves and others from the recipient list in the easiest and most user-friendly way possible. Enter: Marketing Lists.
Dynamics 365 Experts often forget that that the logical name for the Marketing List entity is just, well, 'list'. And when you consider the design of this entity, it's clear that this is what the developers intended it to be.
Marketing Lists can be used for anything, not just Marketing
So, in this case, I decided to use the "Marketing" List entity as a recipient list record for this email being sent via the Flow. The only challenge was actually doing that. You see, Flow doesn't always go out of its way to make our lives easy. Sometimes you have to use an OData query when you'd really like to use FetchXML instead.
That's the exact situation I found myself in after accessing the new Recipient List in my Flow. The next logical step would be to get the Contacts associated with the list, so I tried just that. I don't know much about OData queries, but I know that Jonas Rapp's incredible FetchXML Builder plugin for XrmToolBox has great buttons, so I headed straight there with FetchXML in clipboard.
Upon attempting to turn my Fetch into an OData query, I was presented with this message:
OData queries do not support filter on link entities
This was at first appalling - certainly there had to be a way to get my Marketing List members with OData, but it didn't become clear to me until I stumbled upon a post from CRM Innovation focused on a completely unrelated topic: Running Microsoft Flow from within Dynamics 365 CE
As if to shame me, this post casually employed logic to loop through List Members on a Marketing List like it was no big deal. I have no shame, however, and in fact, I embraced it all as a learning moment to be shared with the rest of the world, hence this blog post.
With the fluff out of the way, let's get on to the solution.
We'll start by assuming that you're creating your Flow within the Dynamics 365 Environment containing the data you're working with. With this in mind, you should be able to select "(Current)" in the Environment selector on CDS Actions, or use the newer "Common Data Service (Current Environment)" actions.
Step 1: Define your Marketing List Id. This is best done in a Compose action near the top of your Flow where it can be easily updated in the future if needed. Compose actions are the most efficient way to establish Constants in a Flow. As always, be sure to relabel your action to succinctly explain its purpose.
Step 2: Collect the List Members. The step is the key, whereby rather than listing a set of Contacts, we list a set of records from the listmember table. This table holds the relationship between Accounts, Contacts, or Leads, and (Static) Marketing Lists they are related to.
To do this, use the Common Data Service "List records" action. You'll notice that the listmember table is not available in the dropdown, but don’t let that stop you! Simply enter "listmembers" as a custom string value for the Entity Name argument, and use the following Odata Filter Query: "_listid_value eq [Recipient List Id]" where [Recipient List Id] is the Output of your Compose action from Step 1.
Step 3: Check member Type (Optional). If you'd like the flexibility to choose Marketing Lists of multiple types (Account, Contact, Lead) then you can branch your logic for each supported type inside a Condition or Switch action. Check the Marketing List Type (logical name: createdfromcode) property of the Marketing List to find the related entity name.
If you want to do this, you'll first need to retrieve this property with a CDS "Get record" action, passing the Marketing List Id from Step1.
Step 4: Loop through List Members. Finally, with your listmembers acquired, loop through each to get whatever data you may need from the related Account/Contact/Lead record. In my case, I pulled out the Email Address to be appended to a string of recipients separated by semicolons.
Also note, you may need to manually extract the item identifier from the action returned in Step 2. You can use an Expression like I did in this example: items('List_Members_associated_with_Recipient_List')?['_entityid_value']
This pattern could likely be improved in many ways, like by using the "Expand Query" argument in Step 2 to include the desired data value(s) from the related member entity, for example. That said, this simpler approach works just fine for reasonably sized datasets. You should probably consider a more performant pattern if you'll regularly be looping through over 500 Marketing List Members.
Now that you have power to use Marketing List Members in your Flows, let's consider some other use cases. I'll start, and readers can finish in the comments below!
- Check a field value across all Members
- Change a field value for all Members
- Integrate Members with an external system or process
If you need any help leveraging Power Automate to digitally transform your business operations, Arbela Technologies is here to assist you! Contact us today.