Managing multiple mailing templates with Airtable (Formula Field limitations)

[Pierre-Louis_Fi]

Hi all,

Hope you’re doing well.

I’m asking the community about a strategic choice I’m about to make.

We are handeling Client Missions through Airtable. We did automate most of our process with Zapier, like sending e-mails.
Right now, we only have 1 Client Langage, and 2 differents type of products. Each product and lang will result in a quiet different mail template. For now, I was using Paths in Zapier.

However, we’re going to scale to 3 langages, and maybe up to 4 or 5 products, therefore I may have up to 15 differents mail templates. Zapier Paths seem not appropriate at all, without considering maintenance costs…

I’m about to change the strategy to conditionally format e-mails templates directly in Airtable in a Formula Field.

This formula with look like :

SWITCH ( Product ,
“Classic”,
SWITCH ( Lang ,
“FR”, ‘Mail template…”
“NL”, ‘Mail template…”
“EN”, ‘Mail template…”
),
“Copro”,
SWITCH ( Lang ,
“FR”, ‘Mail template…”
“NL”, ‘Mail template…”
“EN”, ‘Mail template…”
),
“Office”,
SWITCH ( Lang ,
“FR”, ‘Mail template…”
“NL”, ‘Mail template…”
“EN”, ‘Mail template…”
),
)

Of course “Mail template” will be a HTML formatted, including variables, etc…

My questions are :

• Considering the size of the potential formula (I already made a valid test with 9 branchs and 40.000 chars total), should I anticipate issues ?
• Would you recommend an other strategy ?

Thank you very much for you advices,

Regards,
Pierre-Louis

[JonathanBowen]

Hi @Pierre-Louis_Firre - as your set-up is getting more complex, my suggestion would be to move from Zapier to a dedicated email sending service such as SendGrid. There is a SendGrid block within Airtable, but this isn’t as full-featured as using the SendGrid API directly. You would have to invoke the email send from a separate app or script (you can’t send from SendGrid using Airtable scripting block either right now unfortunately) but you would get a lot more flexibility so it might well be worth adding to the level of complexity of your set-up.

By using something like SendGrid you would store the different HTML templates in SendGrid and not in Airtable. For a more complex set-up, this is probably the right place. On the Airtable side you could then end up with a formula like this:

SWITCH (Product ,
  'Classic',
  SWITCH ( Lang ,
    'FR', ‘Template A'
    'NL', ‘Template B'
    'EN', ‘Template C'
  ),
  'Copro',
  SWITCH ( Lang ,
    'FR', ‘Template D'
    'NL', ‘Template E'
    'EN', ‘Template F'
  ),
  etc, etc, 
)

It is possible that, depending upon your requirements, you might only need 3 templates (one for each language) and all of the variables - product, price, description etc, along with the Template name, are passed to the SendGrid API as variables.

There’s nothing wrong with the set-up you have now (Airtable -> Zapier), but as it gets more complex, it feels like there is a good argument for putting the right things in the right places and generally a service like SendGrid would be the place for the email templates.

Hope this helps.

JB

[Pierre-Louis_Fi]

Hi @JonathanBowen, thank you very much for you advice!

As it sounds like the best long-term approach with Sendgrid, it’s a bit out of reach for me considering my inexperience into coding and the fact that sending e-mails is just a part of a far bigger automation through Zapier.

I guess we’ll go into API’s and custom development on a third phase of scaling our infrastructure.

Thanks again!

[JonathanBowen]

Yep, makes sense, I understand.

If you stick with Zapier paths for now, I think you can get up to 18 endpoints:

3 paths, each with 3 nested paths (so 9) and each final path offering a binary outcome, so 18 in total.

Even with this set up, you might find that it makes more sense (and is easier to manage) if the HTML templates are in Zapier rather than Airtable, but this does depend upon your specific case.

Good luck!

[Pierre-Louis_Fi]

Hi again,

Actually, I’m gonna get rid of Zapier Paths, because of mid-term limitations and heavy maintenance costs.

The idea is to have a straightforward Zap with 1 block for sending an e-mail. In this block, I will input the Mail Body in one unique variable that will be generated inside Airtable (Mail1_Body, which is a formula with SWITCH(SWITCH…) that will render the HTML body I need.

This simplifie all my process.
I’m only annoyed by the fact that I concentrate every templates in a Field Formula :

You see that there will be a lot of information in this formula (I already made a working test with 9 endpoints with a total of more than 40.000 chars, which is already a lot and more than necessary)
Content of the formula is obviously generated in a separated developper text edit software, then I copy/paste is in the Airtable Formula when I need to modify something.

It works, but it’s not a very elegant solution (but better than 3 nested paths in Zapier actually).

I’m worried that I’m pushing the Formula Field into its own limits…
May I face unexpected issues working like this?

[Aron]

It seems like you’re using Zapier mail here. There’s an important limitation there of 10 emails per hour. If you’re sending from your own email address you may also hit gmail limits as well.

You may want to look into using Zapier to sync your email list from Airtable with a dedicated email service (mailchimp, sendgrid, etc etc)

If you don’t need instant send (e.g. it can be trigger by a person), Sendgrid block now supports HTML. You can create multiple emails based on language and trigger based on the views.

[Pierre-Louis_Fi]

Hi @Aron, thanks for you intervention.

I’m not using Zapier Mail, neither Gmail due to those limitations. I’ve switch to Mailgun that does the job perfectly!

I’m gonna have a look to SendGrid block, though.
However, sending the e-mail is part of a 5-to-10 Steps Zaps (every step is important for updating records and so on). I fear that I won’t be able to do that with a SendGrid block properly.

Thanks!

[Pierre-Louis_Fi · Accepted solution]

For those who are wondering which solution I chose for managing multiple mail templates : Code by Zapier > Run Javascript with templates included in a code file with a switch() on mapping (to select the correct template.

I use outputs in the next mail block :

I keep all the Template code in a file like this

I then use

• https://jshint.com/ to check if I made any mistake
• http://removelinebreaks.net/ to remove all the breaks in the code before pasting in Zapier (because Javascript in Zapier doesn’t accept it)

Please feel free to ask me if you have any question.
This solution works like a charm and is very flexible.

[Lindsey_Bavaro]

Hi! Pierre-Louis_Firre, did you add the HTML code into Airtable so you could use merge fields from Airtable records before sending the final code to Zapier? I’d like to manipulate the code in Airtable using IF Statements (if possible) then send to Zapier via a button (webhook) which would connect to Outlook and add an email into my drafts folder. But for some reason I can’t get the HTML to save in the formula field even with " " around it. Thoughts?

[Pierre-Louis_Fi]

Hi Lindsey,

I’m still using HTML directly into Airtable field for some mailing business.
It works properly without any issue, like this :

Could you share some screenshots of your issue?