Help

Auto Generating Daily Huddle Email to Engineers

Topic Labels: Integrations
2014 10
cancel
Showing results for 
Search instead for 
Did you mean: 
Josh_Armenta1
4 - Data Explorer
4 - Data Explorer

Hello,

What’s the best way to auto-generate (at 9am PST) an email that goes out to members of our Engineering workspace, something along the lines of:

"Good morning Engineer:

Today is [date].

Yesterday you were scheduled to work on
[populated from tasks].

Today you are scheduled to work on
[populated from tasks].

Tomorrow you are scheduled to work on
[populated from tasks].

You have the following overdue tasks:
[populated from tasks]

Have a great day!"

10 Replies 10

Integromat is my personal top choice for this sort of thing.

Integromat lets you schedule email automations — or any sort of automation that you’d like — on whatever time schedule you’d like, including every day at 9am!

If you don’t mind having the email come from Airtable and not your own email account, this could also be done using Airtable’s built-in automation system. Setting up the specifically-timed trigger is a little tricky, but it’s doable. If you’d like help with this setup, let me know. However, for user-friendliness with this specific type of setup, and also to have the email come from virtually any address you want, I agree with @ScottWorld’s suggestion of using Integromat.

@Justin_Barrett I’m curious about your solution for setting up a timed trigger with Airtable’s automations! Can you please share your wisdom? :slightly_smiling_face:

I’ll probably make a video for my channel about this one of these days, but here’s the basic setup that I worked out after some testing.

In my task tracking base, I run an update every night at midnight that refreshes certain things on certain records. I originally had this running in Integromat, but wanted to see if the new Airtable automation could be set up to run on a schedule. It can, though it won’t run precisely at midnight. Since I’ve been using this new setup, it tends to run 6-8 minutes after midnight most of the time. Only three times has it run precisely at midnight, but I’m not picky, so the delay is acceptable in my situation.

The first time I set it up, it was running once on every record that needed an update. However, my once-a-day update also needs to reset certain things in a couple of other tables, so I changed it so that only one record actually triggers the automation, and the others are found and updated within the automation script itself.

Anyway, here’s the setup. I have a formula field with this formula, comparing the task’s due date against NOW(), and only on that single triggering record:

IF(Name = "Task Name", {Due Value} < {Now Value})

I use {Due Value} and {Now Value} in some other calculations as well, which is why they get their own fields. If those were expanded, the full formula would be this:

IF(Name = "Task Name", VALUE(DATETIME_FORMAT(Due, "YYYYMMDD")) < VALUE(DATETIME_FORMAT(SET_TIMEZONE(NOW(), "America/Los_Angeles"), "YYYYMMDD")))

In short, I’m formatting both the task’s due date and NOW() in the same way, converting them to numbers, and checking to see if the NOW() value is larger (i.e. later). By looking for the specific task name, this formula will only output a 1 or 0 on that single record: 1 if the record’s due date is earlier than NOW(), and a 0 otherwise.

I then have a view with a filter that will only show records where that field contains a 1. Around midnight-ish, the formula is recalculated behind the scenes, spits out a 1 because NOW() is later than the due date, and that one record pops into that view, which triggers the automation. The automation script does its thing, including updating the due date of that trigger record (it’s a repeating daily task, so it’s a good choice for a repeating daily trigger), which takes it out of that view until the next day.

With a little tweaking, this setup could be modified to trigger at other times. Want it to run hourly? Add “HH” to the end of both formatting strings. Want it to run hourly but only on weekdays? Add logic to the formula so that it only compares the values on weekdays. Ultimately, the frequency is set by the formula logic when comparing the date/time in a field against NOW(), and the automation script must update that field date/time when it runs in order to properly set up the next interval. However, as I pointed out above, it’s not going to be super precise because there’s no telling how frequently the formula will refresh. If you want a 9am trigger, it might not kick off until 9:07. Best to set the trigger time a little earlier.

Ahh, thanks so much, @Justin_Barrett! This is brilliant! :slightly_smiling_face:

I didn’t realize that Airtable continually refreshes any formulas based on NOW() — and presumably TODAY() as well — behind the scenes at all times, regardless of whether somebody is actively using the system or not!

I was under the impression that these functions only updated when needed or called upon by other triggers, but not the other way around — that these functions can become the TRIGGERS THEMSELVES.

This is a huge breakthrough for me, so thank you! :slightly_smiling_face:

I was basing my assumption on my experience with Airtable where I’ve been using the refresh button to update NOW() and TODAY() formulas.

And I’ve also been basing my assumption on my database experience with FileMaker, which never refreshes the current date & time in the background except when needed for some functionality (or until someone starts using the system, or until someone hits the refresh button). The current date & time always updates instantly whenever it is currently needed, but otherwise the date & time remains idle to prevent unnecessary slowdowns in the system. FileMaker has an entirely separate scheduling component (like Integromat’s scheduling component) where you can setup automations to happen on any sort of a recurring schedule or non-recurring schedule.

Anyways, thanks for explaining this breakthrough for me! :slightly_smiling_face:

This breakthrough could also eliminate the need for Integromat in many circumstances, although as you mentioned, Airtable won’t trigger PRECISELY at the expected times, whereas Integromat will always fire on schedule.

p.s. @Justin_Barrett, does this also mean that someone could be staring idly at their Airtable database (without actively interacting with their Airtable database), and watch these formulas update in real time? Or would they need to refresh their screen to see these formulas update? I’ve always been refreshing the Airtable screen to get updated results based on NOW() and TODAY(), but then again, I haven’t waited 7+ minutes idly for the screen to update on its own.

Very possibly. When I was first testing this trigger system, I just set things up the way I hoped they would work, and checked the results the next morning. For a mid-day test, I’d probably do something similar, and just move on to some other task while waiting.

Earlier today, I ran a test while preparing a response for a related thread, and did pretty much what I mentioned above: set up the trigger, and then stepped away to wait. It ran anyway while I was away. I can’t guarantee that the UI updated dynamically, but the data under the hood did update, which is the most important part.

Very, very interesting! Thank you for testing this, @Justin_Barrett, and thanks for the results!! :slightly_smiling_face:

In essence, this means that an Airtable base is never really “idle”… it’s always churning away in the background.

Jarvis
7 - App Architect
7 - App Architect

This is great @Justin_Barrett ! I’ve been looking for a clever trick for this exact situation, and your detailed explanation is going to help me a lot :grinning_face_with_big_eyes: