🌎 Overview
🛠 The Build - 7 Steps
Core Functionality
1. Create an Airtable Base
2. Create a Recurrences Table
3. Create a To Generate View
4. Add an Automation to Generate Recurrences
5. Test your Automation
6. Link Submissions to Recurrences
7. Create your Submission Form
💪 Additional Functionality
📚 More Resources

🌎 Overview

The first tasks to automate are those you do over and over again
Often times there isn't an automated way to collect data from another person
Utilize a recurring request form to capture data in a standardized format, and automate reminders
Create a single template, and duplicate it for handling other recurring requests
Integrate the results into your other tools to eliminate requests, follow-ups, and data management

The build is a single form accessed via public link:

Difficulty: Intermediate

Cost: Free plan

Tools Used: Airtable

Template: Link

🛠 The Build - 7 Steps

Core Functionality

Allows submissions for specific dates set up as recurrences
Does not allow duplicate submissions to a date, or outside the set of dates expected
Auto-reminders if the form hasn't been submitted by a certain time
A form for the user to remove submissions

In this example we will create an "End-of-Day" form to collect total payments by payment method each day. The specifics of what we are collecting is not as important as how we're collecting it. This same method can be followed for collecting any set of data and/or documents.

1. Create an Airtable Base

From scratch, create an Airtable base with a field for the items or files you need in each request.

2. Create a `Recurrences` Table

Set up the following fields:

Date
Next - Calculates the date the next recurrence should be created
Created - Whether the next recurrence has been created

3. Create a `To Generate` View

Create a new grid view with two filters:

The Next field must be on or before today and
The Created box has not been checked

4. Add an Automation to Generate Recurrences

The automation is triggered each time a record enters the To Generate view
It triggers two actions

Create a Record - A new record is created, and the Date field is set to the value of the Next field in the trigger record
Update a Record - The Created box is checked in the trigger record

5. Test your Automation

To ensure your automation is working correctly, while looking at the default grid view (not the To Generate view), create a new record in the past. The next recurrence should populate automatically to current.

⚠️

When creating a record use the date picker to set the date. If you type the date in, Airtable will trigger the automation as soon as you begin typing the date.

6. Link Submissions to Recurrences

Create a field under Submissions to link each record to a specific recurrence
Create a formula field under Recurrences to indicate whether the recurrence has been linked
Create a view named Unlinked in the Recurrences table, filtering to exclude linked records
Limit the link between the Submissions and Recurrences table to only allow selections that are in the Unlinked view.

7. Create your Submission Form

In the Submissions table create a new form view
Add narrative explanations to assist the user
Mark fields required
New submissions will now appear in your Submissions table
Since each submission is associated with a recurrence, and the form only shows unlinked recurrences, only the outstanding submission dates will be displayed by the form

💪 Additional Functionality

‣

Send email reminders with Airtable

Add two fields to the recurrences table:

Today - A formula field simply containing TODAY()
Reminded - A date field

Create a grid view called Remind with three filters:

Linked = 0 and
Date is on or before today
Reminded is not today

Create an automation to send an email each day for any overdue recurrences

Trigger when a record enters the Remind view.
Send an email identifying which recurrence is late. You could also add an email address to each recurrence, to direct different recurrences to different people.
Update the record's Reminded field with today's date. This pushes it out of the Remind view until the next day.

‣

Allow a read-only user to remove a past submission with Airtable

Change the first field in the Submissions table to be a formula field referencing the Recurrence field

Create a new Removals table, with a single field to link to the Submissions table

In the Removals table, create a form to allow the user to select a submission to remove

Create an automation with the following steps:

Trigger is when a record is created in the Removals table
You then update the Submissions record identified in the trigger record
Set the Recurrence field of the submission to be empty - This removes the association of the submission with a recurrence. The main form will once again show this date as an option for the user.

Currently, the removal form will show unassociated submissions that were previously deleted:

To fix this

Create a grid view in the Submissions table that is filtered to exclude records whose Recurrence field is empty
Modify the Submission field in the Removals table to limit record selection to your new view

‣

Set up multiple recurrences with Airtable

Any number of distinct recurrences can be running in parallel in your Recurrences table. A few examples:

Multiple people - A separate recurrence for each member of the team

Multiple locations or classes - A separate recurrence for each location or class

Different recurrence frequencies - In the same table, you can set up recurrence at different frequencies, with a new automation for each frequency

‣

Post as deposits to QuickBooks Online with Zapier

Add a QBO checkbox field to Airtable

Create a zap with the following steps:

Triggered when there is a new record in the Submissions table
Create a sales receipt for each amount, in my case I have Cash, Card and Finance
Update the original record in Submissions to check the QBO box

‣

Post as deposits to Xero with Zapier

Add a Xero checkbox field to Airtable

Create a zap with the following steps:

Triggered when there is a new record in the Submissions table
Create a bank transaction (receive money) for each amount, in my case I have Cash, Card and Finance
Update the original record in Submissions to check the Xero box

‣

Calculate the total of all amounts provided with Airtable

Add a new Total formula field in Airtable
Enter a formula to add the three fields together
Use the total in subsequent automations, such as notifications, or post to the accounting file

‣

Upload a file to Google Drive with Zapier

💡

Google Drive is interchangeable here with other file management systems like Dropbox, OneDrive or Sharefile

Add fields for each file you'd like to collect to the Submissions table, and to the form
Add a Google Drive checkbox field

Create a zap with the following steps:

Triggered when there is a new record in the Submissions table
Upload the file attachment from each field to Google Drive. Here I have File 1 and File 2.
Update the original record in Submissions to check the Google Drive box

‣

Notify someone of a submission via Slack with Airtable

Create an Automation in Airtable with the following steps:

Triggered by a new record in the Submissions table
Use the "Send a Slack Message" action in Airtable to post a custom message to a channel

‣

Notify someone of a submission via Email with Airtable

Create an Automation in Airtable with the following steps:

Triggered by a new record in the Submissions table
Use the "Send an Email" action in Airtable to send a custom email

‣

Set up branching logic with Zapier

Create a zap with the following steps:

Triggered when there is a new record in the Submissions table
Action is "Paths by Zapier"

Using paths in Zapier allows you to conditionally set logic based on the form response. For example, if there were $0 in deposits, send the owner a notification.

📚 More Resources

A 12 minute video walkthrough of this build is available in the rlz.io community
If you haven't used Airtable before, signing up via this link gets me a $10 Airtable credit 🙏

#001 A recurring request form