Synced Calendar Record Archival Automation

[NickL]

Hi folks! 

I'm trying to create a Base that tracks all the calendar entries in a shared Google Calendar, and then every week (or so), archives all the new entries in the Calendar Sync table into a separate "Archive Table". 

The first part is easy, I have our AT Base syncing with the correct Google Calendar we need and pulling all the info down into AT. The second part is where I'm running into problems. 

Because our Google Calendar policy deletes everything older than several (I believe 3) years - I need to find a way to archive that information separate from the sync table (since when things delete in Google Calendar, they'll also delete in the synced table).

I also don't want to have it automatically pull new entries anytime a change is made to the Synced Calendar table - as we're likely to adjust the entries multiple times and/or delete and add new versions based on our needs - so if I have it automate pulling every entry over when it's created, I'll end up with a ton of duplicate entries for when a project had a calendar event, got deleted, and then added an updated version. 

My bright idea for the solution was to have AT do an auto-archive, where (at a set, recurring interval - say a week or month) it takes all the entries from the past week or month and copies them each into a separate Archive table. That way we only copy over the events that actually happened, after the fact, once they're finalized. 

But - I'm having a hard time finding a way to build an automation that copy/pastes multiple records based on a date range criteria in bulk. Can anyone help?

Zapier might be the solve, but I'd love to do it with AirTable alone if it's possible. 

[TheTimeSavingCo · Accepted solution]

Hmm, instead of doing them in batches what if you did them per record? 

You could then create a formula field to check whether the event was completed yesterday and create an automation that triggers off of that field, and create the backups the X days after the event

For doing it as a batch, if you wanted them to be per record, then you're probably going to have to use Zapier / Make, or hire someone to write you some script I reckon

I'm assuming you're not okay with just grabbing the data and dumping it all in a single long text field per month? 

[NickL]

That's a great call - I think your idea to create a date based field on the Synced Calendar table and trigger the automation off of that should work well. I'll do some testing! 

And yup, you're correct in assuming it wouldn't be great to dump the data into a single text field - ideally trying to keep the formatting the same/similar across both tables. 

Thanks for the help!