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:
Option #1:
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:
AND(HOUR(NOW())>=3,WEEKDAY(NOW())=1)
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).
Option #2:
You can keep your “scheduled time” trigger & your “find records” action, and then you would write your own custom JavaScript script to follow the “Find Records” action, which would then iterate through your records and duplicate them.
This requires knowledge of JavaScript code, or hiring somebody who has knowledge of JavaScript code.
There are many JavaScript experts in these forums.
Option #3:
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.
it also doesn’t require any JavaScript coding, because it is a low-code/no-code platform. It has none of the limitations of Airtable’s automations (of which there are many), so it is a great long-term planning decision to outsource al of your automations to a professional platform like Make.
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:
p.s. If you have a budget for your project and would like to hire a consultant to help you with any of this, I am an expert Airtable consultant & Make consultant, so you can always feel free to reach out to me through my website at scottworld.com:
