Automation to Archive "Done" Records (Kanban)

[Sahib_Singh]

I am using a Kanban for my to do list (see screenshot). Throughout each day, I will move items to the “Done” column (status) as they are completed. I have also created an automation that adds a checkmark to the “Done” field of that record when its status is changed to “Done.” At the end of each day (e.g., midnight), I’d like to have an automation that changes all the records with a “Done” status to the “Archive” status as a way to refresh the “Done” column of my Kanban category for the next day’s tasks. What would this automation look like? So far, I’ve tried using the trigger “At a scheduled time” and the action “Find records” that have the condition “Done” as the status. When I try adding another “Update record” action, I get stuck.

Sorry if a post like this has already been created! Any help is appreciated.

[Julian_Kirkness]

Hi @Sahib_Singh

It sounds like you just need to add a conditional action to the automation testing for the condition Status = Done and then have the action update the status to Archive:

Come back to me if you need more help.

Julian

[Sahib_Singh]

Hi @Julian_Kirkness,

Thank you so much for your quick response. I attempted to recreate the automation as you described. Unfortunately, I’m still having trouble figuring out what to put in the “Record ID” for the Conditional Action:

I definitely need to do some more research and homework on this part of automations. The Record ID step is consistently tripping me up.

I appreciate your additional help!

Sahib

[Justin_Barrett]

The “Record ID” value is supposed to be set to the ID of the record that you wish to update.

With that in mind, I don’t believe that this setup is going to work as you expect. If I’m reading your original message correctly, you’re going to have several records each day in the “Done” column, all needing to be archived. The problem is that the “Find records” action is going to return a collection of records, but the “Update record” action only accepts one record ID in the “Record ID” configuration field.

If your base is in a Pro-plan workspace or higher, you can use a “Run a script” action to take all of the records found by the “Find records” action—or the script could do the finding as well—and then batch-update all found records.

If that won’t work in your circumstance, another option is to use a formula field to set a specific value on records that are marked as “Done,” but only after a certain time of day. Use that to drive an automation that triggers off of that specific value, which will mean that each record will run separately in the automation. The downside is that it will use up more automation runs because it’s triggering once per record, but it will work if you can’t use scripts in your automations.

[Julian_Kirkness]

Hi @Sahib_Singh

My Bad!

I had my Integromat / Make thinking hat on when I responded to your question I think…

@Justin_Barrett is correct that you will need a script to do this natively in Airtable - so I thought I would create one for you.

The first step is to set up a view in your table filtered for Status = Done - and then set up an automation to run at say 1am and have the action as the following script:

// this code reads from a view which is filtered for Task Status = Done and then loops through
//the resulting records and updates the Task Status field to Archive...
let table = base.getTable("Table 4");
let view = table.getView("Done records");
let query = await view.selectRecordsAsync({fields: ["Task Status"]});
let status = "Archive";
for (let doneRecord of query.records) {
    await table.updateRecordAsync(doneRecord, {'Task Status': {'name': status},});
}

You may need to alter table, view and field names as appropriate.

Your Automation should then look like this:

I hope this helps.

Julian

[kuovonne]

Julian_Kirkness:

so I thought I would create one for you.

If there is a large number of records to be updated this script could exceed the limits on automation runs.

Instead of updating the records one at a time, you should update them in batches.

Since you are using a script, you may also want to put the logic of identifying which records to update in the script itself. This way if someone messes with the view filters, you won’t accidentally update the wrong records. You will also have fewer views cluttering the user interface.

If you want the flexibility of identifying the records to be updated outside of the script, you can adjust the script to use the result of the “Find Records” action. Note that this will only work if there are 100 or less records to be updated, as the “Find records” action returns a maximum of 100 records.

[Russell_Findlay]

Whenever you feel like you need to write scripts and pull lots of records through - think of views and triggering actions based on entering views… I think the below should work for your use case:

1: Create a last modified field which is only related to the status field

2: create a view in your table “items” that is filtered to status = done and status last modified = yesterday

3: create an automation that when a record enters this view, change status to archive

[Julian_Kirkness]

@kuovonne I have experimented with the code I showed above and it can deal with about 180 records to update in one run - the limit is not the 100 records but rather the 30 second run time limit.

In principle I agree with you that it would be better to select the records in the code with a filter rather than using a view (for the reason you suggest) - but this way is simpler.

@Russell_Findlay - Of course your solution is much simpler and really more elegant!

[kuovonne]

Julian_Kirkness:

the limit is not the 100 records but rather the 30 second run time limit.

I think we might be talking about different limits.
The 100 record limit is for the “find records” action.

[Justin_Barrett]

Russell_Findlay:

Whenever you feel like you need to write scripts and pull lots of records through - think of views and triggering actions based on entering views

While this will work (and is similar to the suggestion that I made above), the downside is that it will burn through the user’s monthly automation allotment much more quickly. An automation that uses a script to perform updates on 1000 records will run once. A similar automation that triggers once for each of those 1000 records will use 1000 automation runs. Obviously not all users have the ability to run scripts in automations, but they can be a huge benefit when large record counts need to be processed.