Help

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

Re: Automation - change fields after a certain date

2937 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Live_Klassisk
4 - Data Explorer
4 - Data Explorer

Hi all

I've been trying to make a specific automation for a while, but with no success:

I want a (single select) field to automatically change after a certain time period. I have success when making one instance and testing on just one record, but I can't seem to make it work for more than one record at a time and to make it activate by itself.

How can I apply the automation to a whole table? I've tried the new "repeating group" but with no luck.

Hope someone can help, would be much appreciated!

Best,
Erik

 

 

 

3 Replies 3
Tyler_Thorson
6 - Interface Innovator
6 - Interface Innovator

There are a few ways to do this, but I have found this way works reliably for me:

Here is an example of a base where this works perfectly.

Here is how I have the example set up:

FIELDS:

Tyler_Thorson_0-1684084183359.png

Status   -   A single select field that triggers the changes:
Ready, Active, Done

--- Time to Expire   -    A formula field that calculates what time the Item should change: 

DATEADD(NOW(),5,'Minutes')

Be sure to set the format to be the same for all collaborators, and set to GMT timezone.

Time to Expire
   -   A date field that will have {--- Time to Expire} copied into it by an automation:
Be sure enable time, set the format to be the same for all collaborators, and set to GMT timezone.

Is Expired?   -    A formula field that calculates if the Time to Expire is past.

IF({Status} = "Active",
IF(DATETIME_DIFF(NOW(),{Time to Expire},'m') >= 0,
"EXPIRED",
"ACTIVE"),
"INACTIVE")

This looks at the time in {Time to Expire} and checks if it is before or after NOW(). It also checks if the status is Active, so that it will not return "EXPIRED" after it has be switched back to "Ready".

Timer Triggered   -   
A checkbox field that will be checked while the timer is active.
I recommend locking this field to everyone except automations.

AUTOMATIONS:

Set Expiry   -   Sets the Time of Expiry and Checks the Timer Trigger Checkbox
      Trigger:   When a Record is Updated   -   Items Table   -   {Status} Field
      Condition:
            IF {Status} = "Active"
            AND {Timer Trigger} = [Not Checked]
      Action: Update Record   -   ID of Trigger Record
            SET {Time to Expire} = {--- Time to Expire}
            SET {Timer Triggered} = [Checked]

Reset Expired Items   -   Sets the Status of an Active Item back to Ready when the timer expires.
      Trigger:   When a Record is Updated   -   Items Table   -   {Is Expired?} Field
      Condition:
            IF {Is Expired?} = "EXPIRED"
            AND {Timer Trigger} = [Checked]
      Action: Update Record   -   ID of Trigger Record
            SET {Status} = "Ready"
            SET {Time to Expire} = [Leave Blank]
            SET {Timer Triggered} = [Not Checked]

OPTIONS:
To change the amount of time before expiry, change the {--- Time to Expire} formula to the amount and unit of time you wish it to last.

The way this is set up, it will not reliably update records that aren't being observed in some way.
I.E. If you don't interact with a record whose timer has expired, it may not update until it is interacted with.
This is fine in most cases, but if you have a large number of records that expired since you last logged in, it may take a minute for all the automations to update all the records.

You can fix this by adding another automation that triggers every day at night and updates another field:

Add a Number field called {Updates}, add an automation:

Update Expiry  -   Forces expiry timers to update.
      Trigger:   At a Scheduled Time   -   Daily at 2AM
      Action: Find Records   -   Items Table
            Condition:
                  IF{Timer Trigger} = [Checked]
      Repeating Group   -   List from Find Records
            Action   -   Update Record   -   Current Record
            SET {Updates} = {Updates} + 1

This will force all the records that need to have their status updated to evaluate the formula.
If you need it update more frequently, you can set the automation trigger to a more frequent time period.

Just note that you shouldn't need to do this unless you are having issues without it as it will eat up automation runs for your Workspace, especially if you have it set to run more frequently than once per day.

Hope that helps!

What type of time period are you talking about? Minutes? Hours? Days?

Note that automations that are triggered by a record's conditions only run for records that do NOT meet the condition when the automation is turned on, but then switch to meeting the condition while the automation is turned on.

 

Depending on your needs, a different option is to have a scheduled automation that runs every evening. Do a "find records" action to find records that meet the conditions. Then have a repeating group that uses the list generated by the "find records" action. Inside the repeating group, use an "Update record" action to update the {single select} for the current list item.

If you still need more help, I suggest posting screen captures.

ScottWorld
18 - Pluto
18 - Pluto

@Live_Klassisk If you go down the “find records” path, note that Airtable can only find up to 100 records unless you use a script. If you’re anticipating more than 100 records, you can use Make. There can be a bit of a learning curve with Make, which is why I created this basic navigation video for Make, along with providing the link to Make’s free training courses. There are also many Make experts hanging out there who can answer other Make questions.