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.