Our customer team often works with clients who want to send an email containing information dynamically pulled from the recipients’ lead, contact, or account’s child records. Specifically, the customer wants to pull information from multiple child records of the same entity type into the email. While it is a common request, this is actually pretty tricky to pull off.
The major roadblock here is that dynamic content pulls data from the recipient’s lead, contact, or account record and any related record that these have an N:1 relationship with. However, it does not pull data from any related record that the lead, contact, or account with an 1:N or N:N relationship. Essentially, in the latter case the dynamic content cannot decide which of the related records of the specified entity type to pull the data from.
One way to get around this is to use connections. By using connections, you can associate a set of records with an email send and then pull data from these items into an email template by using a freemarker list. While this is helpful if you want each recipient to receive an email containing data from the same set of records, it is not very useful if you want each recipient to receive an email containing data pulled from a set of child records that are unique that to recipient.
For example, contacts have a 1:N relationship with appointments so a contact can be associated with multiple appointment records. On the other hand, a given appointment record may only be associated with a single contact record. I want to send an email to my contacts reminding them of their next two appointments—for the sake of this example, we will assume that it is our company’s policy to never have more than two upcoming appointments scheduled for a contact at any given time.
In the above scenario, dynamic content is unable to pull from records that the recipient’s contact has a 1:N relationship with, which means that we cannot pull directly from the appointment records. Additionally, each contact having its own unique set of related appointment records rules out connections as a viable option. The solution here is to add custom fields to the contact entity, and then pull data from the contact’s appointment records into these custom fields on the related contact record. This enables data to be accessed with dynamic content and placed into an email sent to that contact.
Here is an example of what those fields could look like on a contact record:
Now, all we need to do is create a workflow that will automatically fill in these fields whenever a new appointment is created. The workflow should run on the creation of an appointment record; check if the contact’s appointment 1 location and appointment 2 location fields do not contain data.
If so, update the contact’s appointment 1 fields with data pulled from the appointment record.
The rest of the workflow contains a series of checks and updates to ensure that the proper fields are updated:
- If the contact’s appointment 1 location field contains data and the appointment 2 location field does not contain data, update the appointment 2 fields with information from the appointment record.
- If the contact’s appointment 1 location and appointment 2 location fields both contain data, check if the appointment 1 start time is before appointment 2 start time.
- If so, update the appointment 1 fields with information from the appointment record. Otherwise, update the appointment 2 fields with information from the appointment record.
Here is what it looks like when completed:
Once you have set this up, you can use standard dynamic content to pull data from the contact’s appointment 1 and appointment 2 fields into emails sent to your contacts.