DEV Community: Ulrich Feindt

Building a chore tracker with Airtable automations: Part 2

Ulrich Feindt — Sun, 13 Aug 2023 10:20:52 +0000

This is part 2 of a guide to building a chore tracker in Airtable. Make sure to check out part 1 if you haven't already.

At the end of part 1, we had a working chore tacker that lists the chores and highlights the ones that are due. Furthermore it automatically schedules a chore again once it is marked done. We will now add email notifications as a final feature. That way, you won't need to check the tracker everyday but will just receive a reminder email if any chores are due.

Adding chore assignees

Before we can send the emails we need to decide where to store the email it should be send to. If you are using the tracker just by yourself, you can hardcode the email in the automation. However, if you are sharing the household responsibilities with someone, you may want to assign the chores to specific people. This can be done with a new table.

Return to the chore tracker that we built in part 1. At the top click Add or import (next to the name of your Chores table) and then Create blank table. Call the table Users and set the records to be called users as well.

In the new table, delete the Notes and Status fields. Then rename the Assignee field to User. You can now enter a name and the assign user accounts. If you wish, you could also add an email field for the users' email addresses. However, all users should create Airtable accounts to use the tracker, so you can just sent the email to the accounts' adresses. Note I am using my own account for both users in the example below. You should assign a different account to each user.

In order to assign a chore to a user, we can link our two tables. In the Chores table, create a new field of type Link to another record. Select the Users table and name the field Assignee. You can skip adding additional lookup fields as we will not need them. Now you can click on the new field in one of the rows to add a link.

Return to the Users table. You should notice that Airtable has automatically added a link field to it called Chores and that it shows the chores that we have assigned to the users.

In order to list the chores in the email, we need to format the list a little bit. I like to see one chore per line. This can be achieved with a rollup field that applies a function to the array of values in a specific field of all linked records. Add a field of type Rollup naming it Chores to do. Select the Chores table as the source and the Description field as the field to roll up. We are only interested in chores that are due, so enable the option to only include linked records that meet certain conditions and set the condition to the Status being Due. Lastly, enter the following aggregation formula:

ARRAYJOIN(values, "\n")

This formula adds a linebreak between each chore.

The content of the rollup field will not show the linebreaks. To confirm that each chore is listed on its own line, you can expand the field.

Sending the emails

Now we have everything we need to send reminder email. Navigate to the Automation page and create a new automation called Send reminder emails. Choose the trigger Add schedule time and select a convenient time (I'm using 10 AM for my tracker):

Interval type: Daily
Timing: Every 1 day at 10:00am

Now add an action to Find records:

Table: Users
Find records based on: Condition
Conditions: Where Chores to do is not empty

Lastly, add a repating group that will loop over the found records:

Click Select input list and select the Find records action with the option Use as list.

Inside the repeating group, add an action to Send email with the following configuration:

To: Click the plus sign, then the Current item followed by User and Email.
Click Show more options and under From name enter: Chore Tracker
Subject: You have chores to do!
Message: Write any message you would like but remember to include the chores by clicking the plus button and selecting the Chores to do field of the Current item. Similarly, you can include the user's name if you like.

Now everything is in place for sending daily reminder email. Before, you switch the automation on, you should test it using the Test step button for the Send an email action. After confirming a couple of dialog, you should get a preview of the email. Before you leave the automation page, don't forget to activate the new automation.

Conclusion and next steps

This concludes this short series of posts in which we build a chore tracker that:

Lists your chores and highlights the ones that are due.
Schedules chores again when they are marked done, based on when they should be repeated.
Sends daily reminder emails to the chores' assignees if they have chores to do.

If you like to dive deeper into what Airtable can do, consider trying the following improvements to the tracker:

Add nicer formatting to the email using HTML or markdown.
Add a table to log when a chore was completed and by whom.

Building a chore tracker with Airtable automations: Part 1

Ulrich Feindt — Sun, 06 Aug 2023 10:23:10 +0000

When trying to take care of recurring chores, I often find that simple scheduled reminders, like a repeating calendar event, just don't work for me. Some chores don't have a fixed schedule but just a rough time scale, after which they should be done.

For instance, my partner and I have two cats and we have an automatic food dispenser for the dry food that they mostly eat. The dispenser holds enough food for a little under two weeks but we never know whether it will be ten days or twelve because how much they eat depends on their activity and how many treats and other food we give them. If I just set a reminder for every ten days, the dispenser might still have a few days worth of food and I decide to wait. However, now my scheduled reminders are off by a few days and I would have to manually postpone them all.

As a solution, I have set up an Airtable base that does the following:

It lists our recurring chores with a description, a number of days after which it may need to be repeated, and a list of assignees.
When a chore is due, it is marked as such and a daily reminder is sent to the assignees. (I'll cover the email in another post.)
When the chore is marked as done by a user, the due date is updated by the defined number of days and the task is moved back to the future chores.

Why Airtable?

At first my plans for this chore tracker were to build a small web app, maybe in Django or maybe I'd go through some tutorials for a FARM stack app (FastAPI, React, MongoDB). The thing, however, is that this often felt like it would take me too much time to get started, so I never did start the project.

Then I realized that I can set it up in Airtable very quickly and just went for it. At work, we are using Airtable as a product information manager (PIM) and I have been adding automations where necessary, so I was already familiar with its capabilities and limitations.

Setting up the base and the chores table

To get started, you'll need to sign up at Airtable. A free account will provide all the features that you need (as of writing this).

Once you are signed up, click Create a base.

Give it a name, e.g. Chore Tracker, by clicking the current name. You can also change the color and icon.

Now, let's set up the chores table:

You'll start with an empty table named Table 1, see figure 1. Rename it by clicking on the table name and then Rename table. Change the name to Chores. You can also change what the records/rows will be called. Tasks is the most fitting term, I think.
The Table started with a few fields/columns that we won't need. For the fields Notes, Assignee, and Status, click the header and then Delete field.
You need to keep the first field, currently called Name. This field will contain the descriptions of the chores, so rename it to Description. You probably have figured out how to rename a field by now. If not, click the header and then Edit field.

You'll need three additional fields in the chores table for starters. For each of them, click the plus sign next in the table header, enter the field name, and select its type:

Done (Checkbox)
Due date (Date, selecting your preferred format)
Repeat after [days] (Number, set the precision to Integer)

Now you can enter your chores, giving them an initial due date and a number of days, after which they should be repeated. Note that there are currently three empty records in the table. If you need more, just click one of the the plus signs below the records. If you only need one or two records, you can right click empty (and other) records and then Delete task.

You can also sort the chores by due date but right now, you will have to check the dates to see which chore is due. Let's make this clearer.

Separating chores that are due

In order to distinguish chores that are due from those that are pending, you can assign a status dynamically using a formula field. Create a new field named Status and of type Formula. As the formula, enter:

IF(TODAY() >= {Due date}, "Due", "Pending")

This field will thus say Pending when the due date is in the future and otherwise it will say Due. In order to test the formula, set one chore to a past date and another to a future date.

Lastly, to separate pending chores from those that are due, click Group at the top of the table and select the Status field. You can then also hide the Status field to keep the table tidy by right click Status in the header. Additionally, feel free to sort the table by the due date. This will sort the chores inside each grouping.

Now it is obvious which chores are due at a glance. Next we will set up the automation that reschedules completed tasks.

Rescheduling completed tasks automatically

Airtable provides a simple no-code system for automation. This can be great if you're not a software developer and just want to automate some simply but for developers, limitations can be frustrating (as we'll see in a bit). These limitation can be overcome by adding a script to an automation but that is a only possible on a paid Airtable plan. Fortunately, in this case we can work around it.

First, the automation needs a trigger. At the top of the page click Automations to navigate to the automations page. Select the empty automation that is already there and give it a name. Then click Add trigger and select the following properties:

Trigger type: When record matches conditions
Table: Chores
Conditions: When Done is checked

Now you just need to add an action that updates the record but trying this will reveal the aforementioned limitation: you cannot calculate new values inside an automation without using a script, i.e. we cannot determine the new date the task should be scheduled at here. Luckily, we can circumvent this by adding another formula field to the table.

Return to the table view by clicking Data at the top of the page. In the Chores table, add a new field of type Formula named Next due date with the following formula:

DATEADD(TODAY(), {Repeat after [days]}, "days")

This will always show a date in the future by the defined number of days. Feel free to adjust the formatting of the field. By default it will show the time but we don't need that level of precision here. You can also hide this field if you like. I will leave it visible for now for testing.

Now return to the automations page, click Add advanced logic or action, and select Update record. In the properties, select the following:

Table: Chores
Record ID: Click the plus sign and selec the Record ID of the record from the trigger (this should be your only option)
Fields: Click Choose field to add two field to update
- Done: unchecked
- Due date: Click the gear icon to the right to switch the field to Dynamic, then click the plus sign and select the Next due date field of the record from the trigger.

Now the automation is ready to be tested. Before you return to the table view, don't forget to switch the automation on.

To test the automation, return to the table view and check the checkbox in the Done field of one of the task. (Note that this will work whether a task is due or not, i.e. you can use it if you took care of a chore early.) It may take a few second but you should see the task update to the new due date and Done should be unchecked. If the task was due, it will now be grouped under Pending again.

Next steps

Now that we have a working automated tracker for your chores and tasks, we could just check it every day to see what needs to be done. But if you are as prone to forgetting this as me, you will want to add daily email reminders if there are chores. These reminders will be implemented in my next post.