Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Reset a field in a table once a day for each records

Topic Labels: Automations
3032 8
cancel
Showing results for 
Search instead for 
Did you mean: 
SG1
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi,
I’m trying to do something that seems very simple.
Once a day, I want to reset a checkbox value to unchecked for each of my records.

The table :
image

The automation :
image

Can’t figure out why it doesn’t work.
What do I miss?

8 Replies 8

The update record action only works for a single record. You cannot use it to update the results of a find records action, unless only one record was found.

Some options

  • Use a scripting action. This is what I do.
  • Use a third party automation tool that can loop through found records
  • Trigger off a record meeting conditions, instead of a schedule. Have the trigger be a formula that checks for both the time and the check. Note that it is impossible to get the timing exact with this method because NOW() can take an hour or more to update when the base is closed. You also have to deal with timezone issues.
  • Use a “control record” in a different table that is linked to your main table. Have your scheduled automation use the results of the “find records” action to update the linked record field. Because you are updating only one record, this will work. Then have a second automation that triggers in the original table when there is a value in the linked record field. This second automation clears both the checkbox and its own linked record field.

I think Scott is posting a video that explains the steps in more detail.

Welcome to the community, @SG1!

Ha, I just saw @kuovonne’s posts come through above while I was typing the post below, and we are both saying the same exact thing. We both list the same exact 4 workarounds.

This is what I was typing:

Unfortunately, Airtable’s automations are extremely limited. The “find records” action will never loop through the records after you find them. It will only produce a text array of the records that it found, so it’s not very useful in the vast majority of cases.

There are several workarounds to this poor behavior from Airtable:

  1. You could write a script to loop through the records and update them one at a time.

  2. You could create a formula field that results in a certain value at a certain time of day. Then, you could change the automation to trigger based on that value. Note that Airtable doesn’t update times regularly, so it won’t be accurate timing at all. Airtable will only update the formula approximately every 1-2 hours if you’re not using the base at the time.

  3. Create a “control record” in a “utility table” that will link to all the records in your original table as a result of your automation pasting its text array into the linked record field. Then, create a 2nd automation that triggers from the original records getting updated.

I discuss the step-by-step details on how to do this in this episode of the BuiltOnAir video podcast:

  1. Use a professional automation tool such as Make.com, which ALWAYS loops through your found records, and also offers precise & advanced scheduling. In Make.com, this automation is as simple as the screenshot below — which you can immediately see is so much easier than Airtable’s automations:

image

SG1
5 - Automation Enthusiast
5 - Automation Enthusiast

@kuovonne @ScottWorld
Thanks for your answers ! Exactly what i needed :slightly_smiling_face:
I tried the script way but finally decided to go back on Make.
It’s easier and faster for me for scheduling this kind of task.
I still can’t figure out when Airtable automations should be used when you have a Make pro plan… I guess in some use cases you could save a lot of Make operations using AT. Maybe there are some others advantages. I don’t know.
Do you have any clues?

That’s a totally subjective decision on your part. If you want to save money on Make, then do more of your automations in Airtable. If something can’t be done in Airtable, then do it in Make. Anyways, glad you’re enjoying using Make! I like it a lot too! It’s “relatively” easy & very powerful & very inexpensive.

As Scott said, it is really up to you. I use the following rule of thumb:
if the automation only involves the Airtable base, use an Airtable automation, otherwise use a 3rd party automation tool.

There are a few exceptions. For example, if I need to send a simple email with little formatting, I tend to use an Airtable automation. I also use scripting a lot, which lets me work around the limitations of using the results of the “Find Records” action.

Cady_Smith1
6 - Interface Innovator
6 - Interface Innovator

Here’s how you can do it! It’s a little weird, but it should work and it’s really simple to set up.

Create a new formula field called ‘Today’.
Formula:
TODAY()
Formatting: Turn off time, don’t need it.

Create a new automation:
Trigger: When a record is updated.
Fields to watch: ‘Today’

Create new action:
Action: Update Record
Fields to update: Set ‘OK’ to blank.

SG1
5 - Automation Enthusiast
5 - Automation Enthusiast

@ScottWorld Yes Make is great. N8N seems even greater to me because it’s open source and “free” to use if you can self-host it. Then you become the true master of your data. They only charge you if you want to be hosted on their cloud. And pricing are way more interesting than those of Make because they don’t charge on operations number but on flow execution number.
But from my experience, it’s harder to use because it’s still very new. The documentation is quite thin and not much content online related to N8N. When i got an issue it can be hard to find a solution online by myself. I’ve heard they have a great community though… Have you tried this tool?

@kuovonne Yes i feel like i need to dive deeper into airtable scripting. It’s on my list.
However, a good understanding of how basic AT functionalities work and how data is handled is the first step for me.

@Cady_Smith1 Thanks. Indeed, it’s a little weird to proceed this way for such a basic task. I’ve finally used make for this.