Creating Checklists with Airtable and Zapier

Checklists are a common requirement within many business applications - from Project Management to HR, etc.

So, in this Article, we are going to go through the process of automatically creating task lists in Airtable - with the automation handled through Zapier. In order to copy this you will need an Airtable account and a Paid Zapier account (all the features required are not available in a Free account).

First Let's Create the Base in Airtable

In this example, we're going to work on the basis of a project app. When a new project is created we are going to enter a checklist (from a lookup table) and Zapier will then create the tasks from the entered checklist.

The Project Table

Here's how the project table is setup:

Screen Shot 2017-10-07 at 13.12.09

This is all quite straightforward - with the only unusual item being the lookup field called Checklist Items - this is a field from the Checklist table listing the items which the checklist will generate.


Screen Shot 2017-10-07 at 13.15.48

This table simply contains a Name field and then shows which Checklist Items are linked to it and also which Projects. Generally I prefer to create these links in the Many (or child) side of a one to many relationship.

Checklist Items

Screen Shot 2017-10-07 at 13.18.32

This table contains a Name field, who to assign the task to (a Collaborator field), a number of days after today that the task is due and which Checklist it forms a part of. The ItemID field will be used later to enable Zapier to search for this record so that we can get other information from it.

With all this information, we are now in a position where Airtable has all the information it needs to generate a task and assign it to a project and collaborator.

The Tasks Table

Screen Shot 2017-10-07 at 13.22.26

Here we see the Tasks table containing 4 tasks (which were generated by Zapier from the Checklist). I've implemented this table so that manually entered tasks can also be input (not from checklist items. If you enter a value in the Brief description field rather than the Checklist Item, then the Task field (Name field) will show the description rather than the Checklist item - the formula is:

Screen Shot 2017-10-07 at 13.25.47

So, that's the Airtable side of things. Now let's look at how Zapier takes this and generates the tasks automatically…

Building the Zap

Screen Shot 2017-10-07 at 13.28.11

Firstly, the Zap is going to be triggered by a New Record in the Project table - as you can see above. One of the fields returned by this step is going to contain a comma separated list of the Checklist Items associated with the Checklist (from the Project's [Checklist Items] field. On screen, this looks as though it contains the names of the items - however in Zapier we get a list of the Checklist Items' IDs - which is much more useful.

In the previous Blog post I wrote about sending reminders from Airtable, we discussed how Zapier doesn't appear to be able to loop through things (like a list of Checklist Items) - but that it is possible to get Zapier to do this by using some code to turn the list into an Array of Objects. What happens when you do this is that Zapier will perform any subsequent step for each Object…

Here's how it's done in Zapier (using Javascript):

Screen Shot 2017-10-07 at 13.35.25

I don't intend to explain the code in detail - but it is pretty generic and will convert any comma separated list (as you get from an Airtable field of this sort) into an Array of Objects. If you want to try it yourself, here's the code in a copyable format:

//this code is required to turn the string containing record IDs into
//an array of objects. Because the output is an array of objects the following
//steps will run for each record found.

if (inputData.csString == null) {
var listArray = [];
} else {
var listArray = inputData.csString.split(",");
var output = [];
var arrayNos = listArray.length;
var i=0;
do {
var thisItem = new String(listArray[i]);
var thisItemObj = {};
thisItemObj.record = thisItem;
while (i < arrayNos);

So, the following steps will now run for each Item in the Checklist:

First we're going to lookup the Checklist Item from the database (because we need several fields from its record):

Screen Shot 2017-10-07 at 13.39.55

Note that we're searching by ItemID - which is a field we created for this purpose when we created the Checklist Items table.

I decided to use the following step (using Zapier's Formatter 'app') to calculate the Due date of the task list item. It's more flexible than trying to do this in the final step where the Task itself is created.

Screen Shot 2017-10-07 at 13.45.03

What's happening here is that the Created time of the project record (from Zap step 1) is going to have the Due After (Days) field from Checklist Steps added to it (in Days) - and then the result will be formatted as shown. The latter is often useful as it avoids ambiguities between US and European date formats.

So, there we have the complete process where, for every new project, Zapier will create tasks based on the Checklist selected and assign them to the project and user!

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!
blog comments powered by Disqus