Filter Related Lists in Dynamics 365 Marketing Emails

Once again I am back with a solution inspired by my fellow MVP Amey Holden. Here’s the challenge:

In a #MSDyn365 Marketing email, how do we show a list of one or more associated records connected to the recipient Contact AND filter those records to show just those that meet certain criteria? Let’s look at one way to accomplish this.

With Dynamics 365 Marketing it is possible to add associated records in the body of an email using the Handlebars.js markup language. Microsoft has some good examples in their documentation here. For example, if you want to send a list of Appointments that are related to a Contact, you would use this code snippet:

{{#each contact.appointment_contact_regardingobjectid}}
Subject: {{this.subject}} | Start Time: {{this.scheduledstart}} | Status: {{this.statecode}}

Tip: Before you can reference data in expressions like these, you need to make sure the entity is syncing to the Azure services behind Dynamics 365 Marketing. Under the Marketing app’s Settings area, locate the Dataset Configuration list and check the box(es) next to the entities you want to reference and publish your changes.
Also, if you add an entity for this purpose, you will need to update the security role “Marketing Services User” to permit it to at least “Read” the entity.

In my example above, a contact who has two open appointments and one closed appointment would receive an email that looks like this:

Notice that the first appointment is in the status of “Completed”. What if I only want to show the open appointments? What you’ll need to do is add a nested “if” statement inside the list to first check the status, and then, only if it meets the proper criteria, write it out in the body of the email.

One thing I learned is that even though the status gets written into the body of the email as the text value “Completed” or “Open”, the if/else logic is going to compare the index value. For the out-of-the-box activity status fields, 0 is the index value for Open. So here’s the code to list out only the open appointments:

{{#each contact.appointment_contact_regardingobjectid}}
{{#if (eq this.statecode 0)}}
Subject: {{this.subject}} | Start Time: {{this.scheduledstart}} | Status: {{this.statecode}}

The end result looks like this:

Published by Matt Wittemann

Sr. Technology Specialist, Microsoft Former 16-time Microsoft MVP

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: