How to Update multiple record values with automation

Sometimes you may want to update multiple record values with automation in Airtable. In this article, I explain how you can achieve that. I will be using an example that you can understand and apply in many different scenarios. For example:

Update/Delete...

• Old ticket's status based on last status
• Project status based on sub-tasks statuses
• Appointment status based on last record
• Previous fields data based on new entered data, etc.

Let's understand the current base I am using to go through an example. I have 3 tables in my base which are:

1. Client's : Client information table.
2. Project's: Projects which are linked to clients.
3. Discussion Logs: Discussions that may happen for each project.

Understanding the problem that we will solve

Let's understand what we are going to do in this example. As you can see above, I have 3 tables. Each table is connected or linked with the other tables. Let's assume I have a client and that client has a project. First, I will create an entry in the client table and then one in the project table as well, linked to the client table. Now during the project development, I might require some information or want to discuss something with the client. At this point in time, I will create an entry in the discussion logs table which will be linked to the project's table. I might possibly contact the client more than once for the same or a new reason. Each time I the contact client, I open a new log in the discussion logs table.

It's difficult to keep track of statuses for discussion logs and also for projects. This is our problem. How we can update a previous discussion log's status and project status based on the last updated status for the same project when a certain condition is met? Basically, how do we update multiple record values with automation when a certain condition is met?

Current tables in airtable base...

Client's Table

The image below is of a Client's table where we store client's information and is linked with a project table.

Project's Table

The image below is from the Project's table where we store Project's information and it is linked with the client's table and discussion logs table.

Discussion Logs Table

The image below is of the Discussion logs table which is connected to the project's table. Here we store discussion logs with clients.

For current scenarios what is our trigger and what do we want to update?

If you have noticed, we have Status as a column in the project and discussion logs tables. Now here we need two answers:

1. What is our trigger?
2. What do we want to update?

The answer to the questions is, the status column is our trigger and we want to update all old statuses and also update the status of the project. For example, in our case, we have two logs for one project [ Airtable consulting project ] open in our discussion logs table. Let's assume that in the last discussion, I changed the status to 'Closed'. Now I would like to update the first entry status to be 'Closed' as well as the Project status to be 'Closed'. That means, whatever the last record status is, should be for all the related discussion logs entries and projects as well. To make the example simpler, I will be keeping two statuses only: open and closed.

Now, let's implement the solution step by step.

1. First click on the automation option on the top right corner of the screen.
2. Then set the trigger to 'When a record is updated, and select the table and status as the fields which we need to monitor for the trigger.
3. Now you just have to copy-paste the script which you can get by clicking here.
4. Turn on the automation and you are operational.

Understanding Script & Notes

So, what exactly is our script doing?

• First we are getting data from the discussion log table.
• Then we are going through a loop and finding the last record's status and using that status for discussion logs and project status
• And finally we are updating records.

Here, note that we are sorting the data of the discussion table based on the date of discussion. Now whenever the status is updated, our script will go through each record and find out the last record's status, and set that status to all related discussion and project records.

You can implement this idea of updating multiple record values with automation in many different ways by customizing the script.

Feel free to contact me for Airtable consulting services by CLICKING HERE.