Hey @localemagazine!
If you're not already doing it, I just wanted to quickly call out that you should also definitely reach out to Airtable through your dedicated enterprise support channels for quick help and support!
Now, regarding your use case, I would recommend that you pair a really clean formula field that reads a date/time value of when you'd like the email to fire off.
It would look something like this:
Here's what the formula looks like:
IF(
AND(
{Email Status}, {Date To Send}
),
SWITCH(
{Email Status},
"Ready To Send",
IF(
OR(
{Date To Send} = SET_TIMEZONE(NOW(), "America/Los_Angeles"),
{Date To Send} < SET_TIMEZONE(NOW(), "America/Los_Angeles")
),
"⚙ Clear To Send",
IF(
{Date To Send} > SET_TIMEZONE(NOW(), "America/Los_Angeles"),
"💬 Queued"
)
),
"Not Ready",
"🛠 Not Ready",
"Sent",
"✔ Email Sent"
)
)Since you're working off of pacific time, you'll have an easier time.
You'd want to build your automation to fire off the email when a record meets the condition of the formula field returning a value of "💬 Queued", as shown in the screenshot.
When the email is sent, you'd have your automation update the record's Email Status single-select field to reflect "Sent" which would then have the formula return "✔ Email Sent."
The level of granular control that this gives you is both a pro and a con.
If you don't want your team to have to manage when the emails will be sent during the day, you can use something like this formula instead:
IF(
AND(
{Email Status}, {Date}
),
SWITCH(
{Email Status},
"Sent",
"✔ Email Sent",
"Not Ready",
"❌ Not Ready",
"Ready To Send",
IF(
DATEADD(SET_TIMEZONE({Date}, "America/Los_Angeles"), 14, "hours") > SET_TIMEZONE(NOW(), "America/Los_Angeles"),
"💬 Queued To Send",
IF(
OR(
DATEADD(SET_TIMEZONE({Date}, "America/Los_Angeles"), 14, "hours") = SET_TIMEZONE(NOW(), "America/Los_Angeles"),
DATEADD(SET_TIMEZONE({Date}, "America/Los_Angeles"), 14, "hours") < SET_TIMEZONE(NOW(), "America/Los_Angeles")
),
"⚙ Pending Send"
)
)
)
)This formula assumes a target email send time of 8:00 AM PT.
Again, you'd just have your automation fire off of the value returned from the formula field here and have the automation complete its actions by updating the email status field to "Sent."
There's tons of interesting and unique ways to approach the problem, really just depends on what your requirements are.
