Sending Notifications from Aitable

Whilst Airtable offers a revolutionary new approach to relational databases, there are some things that are not immediately obvious and others that need external tools like Zapier to achieve. In this article I am going to look at how to send task reminders (the basic technique could be used for other scenarios) - in this case by email. However, they could be sent via Slack, etc. just as easily.

There are a couple of potential solutions to this with Zapier - the simplest is to base a Zap on a View filtered by "{Reminder Date} is today". This is very straightforward to implement with just a date field and a single view with the filter. You then have a Zap triggered by a New Record in View. The only problem with this approach is that Zapier will only send a reminder once for each record (even when the Reminder Date is changed).

There is, however, an alternative, and that is to use a Scheduled Zap - i.e. one triggered by the Schedule trigger included with Zapier. These can be set to Monthly, Weekly, Daily or Hourly - and because you aren't then reliant on Zapier determining whether it has already been triggered by a record, and you can essentially do whatever you want in the Zap after that.

The trick here is that you need to be able to find all the tasks for which a reminder is due in a Single Zap - and then 'loop' through them sending emails relating to each and also updating them so the reminders aren't sent again.

But how do I loop in Zapier? We'll come back to this question a little later…

The Basic Setup in Airtable

If you recall, in this example I'm looking at how to send reminders for a task - so let's see how the Task table is configured:

📷

So we have a typical task table with Task, Notes, Type, Reminder Date etc. Oddly, the keys to this process are actually the Reminder Type field and the TaskID (If Today) Fields.

The TaskID (If Today) field is a formula field which is going to effectively limit this process to only those tasks with a reminder due today - the formula is:

📷

Now let's see the other side of this configuration in the linked Reminder Types table:

📷

As you can see, as the Task is linked to the Reminder Type, the Reminder Types records contain a list of ALL of the reminders of that type, including those not scheduled today. Finally, the TaskIDs Rollup field contains a comma separated list of only those tasks with a reminder date of Today by picking up and concatenating the TaskID (If Today) field. This is how the rollup is configured:

📷

So, now we have access to a comma separated list of the IDs of tasks which are due for a reminder today! Now let's see how this is processed in Zapier…

Building the Zap

The first step is to create a new Zap with a Schedule trigger:

📷

In this case I have set the Zap to run hourly.

Next, we need to get hold of the Reminder Type record containing the list of reminders due today:

📷

You can see the entire configuration of the step in this screenshot - simply finding the record for Email in the Reminder Type table.

I promised I would come back to the question of looping in Zapier - and now is the moment!

There is no Loop option in Zapier, but it turns out that if you create a code step which outputs an ARRAY of OBJECTS then the steps that follow will be performed for each Object in the Array. To build this requires a little knowledge of Javascript - and I'm sure there are those more familiar with it than I - but here is how I've done this:

📷

The screen above shows the configuration of the Zap - including assigning the TaskIds field to a variable called varReminderList - and the code follows. The code essentially splits the string containing all the IDs of the tasks into an array and then loops through the array and builds it into an array of objects. I would be interested to hear if there are simpler ways to achieve this!

Here's the Code:

//this code is required to turn the string containing the Email reminders into //an array of objects. This returns an array with one or more tasks and, //because it is an array of objects the following steps will run for each //client.

if (inputData.varReminderList == null) { var reminderListArray = []; } else { var reminderListArray = inputData.varReminderList.split(","); } var output = []; var reminderNos = reminderListArray.length; var i=0; do { var thisReminder = new String(reminderListArray[i]); var thisReminderObj = {}; thisReminderObj.record = thisReminder; output.push({thisReminderObj}); i++; } while (i < reminderNos);

So, that's the hard part over - now let's complete the process by updating the Task record (so the reminder isn't sent next time) and also send the email:

Updating the Task

To do this we use an Airtable Update record Action:

📷

The thing to note here is that we are using the Reminder Object from the Code step to search for the record by ID. Further down the page, I've then put an update to the Reminder Type field:

📷

Here replacing the value "Email" with "None" - next time this runs the email will NOT be repeated!

Send the email

The final step is to send the email (or any other means of notification you may prefer):

📷

So, there we have the complete process where, every hour, Zapier will send email alerts to me re any outstanding tasks I may have!

Final Note…

There is a limitation in this - Zapier will only Loop around 25 records in an array like this and so if there are more than this number it could fail. However, there is a way around this - and it just adds one more code step to the Zap.

I hope you've found this article useful and please feel free to make any comments or suggestions!