SendGrid to multiple related contacts problem with fields in email body

[Dean_Moray]

I’m new to Airtable and SendGrid.

I have a contacts table with a first name, last name & email address fields. I have a linked events table linked to the contact name with multiple select so that one event can be associated with multiple contacts.

I want to send email through SendGrid to every contact related to the event. I did a lookup of the contacts email address in the Events table and it does send separate emails to each person because the lookup field separates the email addresses with commas. However, I want to customize the body of the email with the contacts’ first name and SendGrid seems to take BOTH first names and places them in BOTH emails. Is there a way around this problem so it only includes contact1’s first name in the email to contact 1 and contact2’s first name in the email to contact 2?

I’m thinking there has to be some sort of join table that creates separate records for each combination of event and name but it is beyond my expertise. Can anyone direct me on the best methodology with specifics that a noob can follow?

[Justin_Barrett]

I’ve only used the SendGrid block a few times, but the behavior you’re seeing isn’t surprising. SendGrid bases the design of each email on each record that it finds, not necessarily the design of the field contents. You actually got lucky with it sending multiple emails for a single event (i.e. a single event record) because of how the email addresses came in separated by commas. Normally it sends one email per record, but the commas effectively “tricked” it into sending multiple emails.

To keep the other field fill-ins separated, you’ll need to design a system to send emails from the [Contacts] table, using a view that filters those contacts who are attending a specific event. However, this introduces two problems.

1. You have to tweak the filter every time you want to send emails about a different event, which means manually typing some portion of the event name into the filter parameter. Not horrible, but not ideal either.
2. The bigger problem is with inserting event-specific information into your emails. You could use a lookup to pull that from your [Events] table so that each contact record has data about its linked events, but if a contact is linked to multiple events, it will pull info from all of those events, not just the one you’re sending details about.

Here’s my suggestion that will solve both somewhat easily. Add a checkbox field to [Events] that’s named something like {Current}, and a relevant number of formula fields that echo key event details only for the event that’s marked as current.

Here’s an example formula from the {Current Location} field:

IF(Current, Location)

The rest are nearly identical with the relevant field inserted into the formula. For {Current Date}, you’ll have to format the date so that later steps don’t leave you with an unreadable mess:

IF(Current, DATETIME_FORMAT(Date, "L"))

In the [Contacts] table, add rollup fields to pull in these various “current” data fields for the contacts’ linked events. Here’s an example of one such field setup:

Once all the rollups are added, you should have something like this:

Make a new grid view for [Contacts], name it something obvious like “Specific Event,” and add the following filter:

This will give you a pared-down list of contacts who are attending the event you checked in the [Events] table.

Use this view to send your emails via SendGrid. Change the current event in the [Events] table, and this filtered view will update automatically:

[Dean_Moray]

Justin, thanks for the thorough and thoughtful reply.

Yeah, I didn’t think what I experienced was unexpected, just not usable. I am inexperienced in airtable but will try to follow your instructions and see if I can make it happen.

One question though, rather than having to do a checkbox to set something as current, is it possible to do it by formula so that the filtered view is something like “event date is in the next 7 days”? My use case is to send reminders to related contacts of each upcoming events each week. if I could use a formula, I wouldn’t have to go in manually and set the events.

[Dean_Moray]

Justin,

I THINK I followed your steps but I ran into another issue. If there are two events that are “Current” for the same contact, it seems to still roll them both events in the rollup field so a SendGrid merge doesn’t work properly. Did I miss a step?

[Justin_Barrett]

Dean_Moray:

One question though, rather than having to do a checkbox to set something as current, is it possible to do it by formula so that the filtered view is something like “event date is in the next 7 days”? My use case is to send reminders to related contacts of each upcoming events each week.

Thanks for the clarification. Technically this could be done, using a formula to mark events that will be in the next seven days. However, there’s an issue with this which relates to your other comment:

Dean_Moray:

If there are two events that are “Current” for the same contact, it seems to still roll them both events in the rollup field so a SendGrid merge doesn’t work properly. Did I miss a step?

No. I should have explained this in my setup above, but my vision behind this design is that you would manually mark only a single event as “current” before sending emails about that one event. Then you would unmark that one, mark another one, and send those emails. Perhaps “Current” was a poor word choice for that checkbox field. :frowning:

From your comments, it sounds like a) you have enough events going on that even this manual marking and unmarking of events might become cumbersome, and b) you’re looking for a more automated, hands-off solution. In that light, I can’t think of a way to do this strictly within Airtable using the SendGrid block because of the restrictions we’ve already discussed earlier. SendGrid is great for relatively simple use cases, but yours is pushing just enough beyond “simple” to the point that it’s wise to consider other alternatives.

Specifically, I suggest looking into using a third-party integration tool like Zapier or Integromat. Both have the ability to search Airtable records based on certain criteria, and perform operations based on the results. I’ve got more experience with Integromat because of its lower price point and higher operation count, and have setup some scheduled email automations not too different from what you’re describing. The setup is too involved to elaborate on here, but if you’d like some one-on-one help with it, message me and we’ll set something up.

[Dean_Moray]

Thanks again. I’ll look into Zapier and/or Integromat to see what is involved.

AirTable support suggested I could do it with a Junction table but the labor involved in picking the Contact & Event is more work than I can do unless there is some way to automate the creation of records with every combination of Event & Related Contacts (or Contact & Related Events) without having to do it manually.

[Justin_Barrett]

Dean_Moray:

…unless there is some way to automate the creation of records with every combination of Event & Related Contacts (or Contact & Related Events) without having to do it manually.

Unfortunately (or perhaps fortunately, depending on how you look at it) Airtable doesn’t have any mechanism for automatically creating records. This is also something typically addressed via tools like Zapier or Integromat.

[JonathanBowen]

Hi @Dean_Moray - I think the idea of a junction table is the correct way to do this, although I appreciate that with many events and contacts is could be a lot of work to set up. The benefit is you get one record per event attendee, which give you what you need for a personalised Sendgrid email:

You could also have a filter on this view to show events in the next X days.

JB