Skip to main content
Solved

Scheduled Automated Emails when training attendance is marked as complete

  • August 2, 2023
  • 7 replies
  • 82 views
A
Forum|alt.badge.img+3

Hi there,

I'm trying automation for the first time and the ask is very specific. We run different training programs. For now, there are three training events we conduct for which we want to send automated emails once attendance has been updated. For eg: Main event is 'Clarity in Communication' . Under this topic we have multiple sessions carried out through the year. Once the training is completed, we want an email to go out to the participants after 7 days, then 30 days and then one specific date in April. All these messages are different. I'm not sure how to proceed with this and set up these conditions. I've tried various triggers but maybe I need some additional data in my records? Any insight would be helpful. Would be happy to chat in more detail if required.

Best answer by TheTimeSavingCo

Hello, thank you so much for getting back to me. Incidentally, I had already started to create the date fields. I'm good till step 4 but getting the formula wrong for step 5. Many options are coming up in the examples box and I'm not getting it right. I generally struggle with sheets and formulas so I might need some additional help with the formulas. Thank you in advance and thankyou for your patience:)

 


No worries!  Try this:

 

IF( {Completion Date}, IF( IS_SAME( TODAY(), {Specific date to send email}, 'days' ), "Send specific date email", IF( IS_SAME( TODAY(), DATEADD({Completion Date}, 7, 'days'), 'days' ), '7 days email', IF( IS_SAME( TODAY(), DATEADD({Completion Date}, 30, 'days'), 'days' ), '30 days email' ) ) ) )

 

Link to base

7 replies

TheTimeSavingCo
Forum|alt.badge.img+31

Should be doable!

Try the following:
1. Create a field to indicate the date the program was completed called "Program Completion Date" or something
2. Create a formula field to calculate the date 7 days after the "Program Completion Date" called "7 days after completion" using 'DATEADD()'
3. Create a formula field to calculate the date 30 days after the "Program Completion Date" called "30 days after completion"
4. Create a date field called "Specific date to send email"
5. Create a formula field that will check whether today's date is after "7 days after completion", "30 days after completion" or "Specific date to send email", using 'TODAY()' and "IS_SAME()", and it'll output different text based on these conditions.  Call this field "Send email?" or something
6. Create an automation that will trigger whenever "Send email?" is updated, and give it conditional logic so that if it outputs the text that indicates it's 7 days past the completion date, it'll send the appropriate email

Should do what you're looking for!

Adam, Airtable Consultant - thetimesaving.company
A
Forum|alt.badge.img+3
  • amykalra
  • Author
  • Participating Frequently
  • August 3, 2023

Should be doable!

Try the following:
1. Create a field to indicate the date the program was completed called "Program Completion Date" or something
2. Create a formula field to calculate the date 7 days after the "Program Completion Date" called "7 days after completion" using 'DATEADD()'
3. Create a formula field to calculate the date 30 days after the "Program Completion Date" called "30 days after completion"
4. Create a date field called "Specific date to send email"
5. Create a formula field that will check whether today's date is after "7 days after completion", "30 days after completion" or "Specific date to send email", using 'TODAY()' and "IS_SAME()", and it'll output different text based on these conditions.  Call this field "Send email?" or something
6. Create an automation that will trigger whenever "Send email?" is updated, and give it conditional logic so that if it outputs the text that indicates it's 7 days past the completion date, it'll send the appropriate email

Should do what you're looking for!


Hello, thank you so much for getting back to me. Incidentally, I had already started to create the date fields. I'm good till step 4 but getting the formula wrong for step 5. Many options are coming up in the examples box and I'm not getting it right. I generally struggle with sheets and formulas so I might need some additional help with the formulas. Thank you in advance and thankyou for your patience:)

 

TheTimeSavingCo
Forum|alt.badge.img+31

Hello, thank you so much for getting back to me. Incidentally, I had already started to create the date fields. I'm good till step 4 but getting the formula wrong for step 5. Many options are coming up in the examples box and I'm not getting it right. I generally struggle with sheets and formulas so I might need some additional help with the formulas. Thank you in advance and thankyou for your patience:)

 


No worries!  Try this:

 

IF( {Completion Date}, IF( IS_SAME( TODAY(), {Specific date to send email}, 'days' ), "Send specific date email", IF( IS_SAME( TODAY(), DATEADD({Completion Date}, 7, 'days'), 'days' ), '7 days email', IF( IS_SAME( TODAY(), DATEADD({Completion Date}, 30, 'days'), 'days' ), '30 days email' ) ) ) )

 

Link to base

Adam, Airtable Consultant - thetimesaving.company
A
A
Forum|alt.badge.img+3
  • amykalra
  • Author
  • Participating Frequently
  • August 16, 2023

No worries!  Try this:

 

IF( {Completion Date}, IF( IS_SAME( TODAY(), {Specific date to send email}, 'days' ), "Send specific date email", IF( IS_SAME( TODAY(), DATEADD({Completion Date}, 7, 'days'), 'days' ), '7 days email', IF( IS_SAME( TODAY(), DATEADD({Completion Date}, 30, 'days'), 'days' ), '30 days email' ) ) ) )

 

Link to base


Hello,

Apologies for the delay. I'm trying to use this formula but I'm getting it wrong. Is it possible to get edit access to the link you shared? Maybe I can input an example of the data I'm using and the formula I'm trying out. Would that work?

TheTimeSavingCo
A
Forum|alt.badge.img+3
  • amykalra
  • Author
  • Participating Frequently
  • August 17, 2023

No worries!  Try this:

 

IF( {Completion Date}, IF( IS_SAME( TODAY(), {Specific date to send email}, 'days' ), "Send specific date email", IF( IS_SAME( TODAY(), DATEADD({Completion Date}, 7, 'days'), 'days' ), '7 days email', IF( IS_SAME( TODAY(), DATEADD({Completion Date}, 30, 'days'), 'days' ), '30 days email' ) ) ) )

 

Link to base


Thank you, I actually managed to get the formula right and used this to set up automation with conditional logic as you described in one of your steps previously. Seems to be working well so far. Thank you so much. This was super helpful!!!

TheTimeSavingCo
TheTimeSavingCo
Forum|alt.badge.img+31

Thank you, I actually managed to get the formula right and used this to set up automation with conditional logic as you described in one of your steps previously. Seems to be working well so far. Thank you so much. This was super helpful!!!


Glad you figured it out and sorry for the delay in my reply!

For future reference, you can duplicate a read only base to your own workspaces, and you will then have access to the entire setup with creator permissions, formulas and all!

Adam, Airtable Consultant - thetimesaving.company
A
Forum|alt.badge.img+3
  • amykalra
  • Author
  • Participating Frequently
  • September 6, 2023

Glad you figured it out and sorry for the delay in my reply!

For future reference, you can duplicate a read only base to your own workspaces, and you will then have access to the entire setup with creator permissions, formulas and all!


Hello again,

So I've set up the formula and it works great. I've also set up the automation using conditional logic. The test steps are all successful but when I switch on the automation, the emails don't get sent out. Not sure if I'm missing a step somewhere. Any insights into what could be happening?

2 Attachments
Terms & ConditionsAccessibility statement