My spreadsheet has records that include a column “Week Number” which references a table of each week of the year.
Instead of manually duplicating the records from the previous week to create new ones for the new week, I would like to setup an automation to do that.
There are multiple records with different columns set differently. The commonality amongst the records I’d like to duplicate is having “This Week” be the week number field.
The “Week Number” comes from another table. Depending on TODAY(), the actual record for “This Week” changes (and the previous one becomes “Last Week”). When it does, that’s when I’d like the automation to run - this can also be done via a time trigger Mondays at 3am.
I’ve been able to get an automation setup to find the relevant records but it seems like I can only create one that collates all the data instead of duplicating each record separately. Is there a way to loop through and duplicate each of the matching records separately without a script?
Unfortunately, the “find records” action doesn’t work how any of us had hoped it would work, because as you have discovered, you can’t iterate through the records to do what you want to do.
There are 3 options that you can take:
Your first option is to trigger each one of your records individually, instead of thinking of them as one large group of records to iterate through.
You’ve already figured out the proper formula for each record that could serve as half of your trigger, which would be “Week’s Status is Last Week”.
But, you would also need to add another formula field into your table to see if the current date & time is Monday at 3am, which would serve as the other half of your trigger.
This is where it gets a little tricky, because Airtable won’t actually be checking for the results of this formula exactly on Monday at 3am. Airtable will only check it approximately every 1-2 hours, so the duplication might happen at 3am or 3:30am or 4am or 4:15am or some other random time between 3am and 5am.
Your formula could be something like this:
This would result in the number 1 if it is 3am (or later) on Monday, and you would look for that number 1 as the other part of your trigger.
Again, this will not happen precisely on Monday at 3am, but it will happen “around” that time (within 2 hours).
For me personally — and this is just my personal preference — the #1 easiest & quickest & best solution for me personally is to create all of my Airtable automations with Make.com, which is a professional automation platform that is similar to Zapier but way more powerful & way less expensive.
Make.com always iterates through your records after a search, and it has extremely advanced scheduling capabilities so you can schedule something for once a week at a specific time.
I give a few examples of how to use Make (formerly known as Integromat) on this video podcast: