I am creating a Master Calendar & Team Calendar system in Airtable, where each calendar update automatically updates/creates based on an update in the other calendar.
I put together a system that created a “Unique Event ID”, which is a shared ID for the same event across both Bases. Everything is working great so far, except that I would like to delete records automatically in one Base if the corresponding record is not found in the other.
There’s no Delete trigger in Integromat, so my workaround is:
-The trigger is an existing “Update Record” automation
After that automation completes, the automation will do a scan on both Airtable Bases.
Step 1: Search all existing records on Master Calendar Base (up to 10,000)
Step 2: Search all existing records on Team Calendar (up to 10,000)
Step 3: Filter, then Delete - If the Event ID is found on the Master Calendar Version but does not exist on the Team Calendar Version, Delete the record on the Team Calendar (up to 10,000)
I’ve tried a few different versions of this scenario with no luck. The “Delete Record” function only seems to delete the first record found.
Has anybody attempted to do something like this before? Am I going about it the wrong way? Very much appreciated…
Accepted Solutions
I was able to solve this problem with the help of @M_k!
Here is the solution:
(note: the Router might not be necessary so I’ll test the scenario without it)
First, the automation searches the unfiltered Master Calendar Records…
Then, it looks up all the Team Calendar records that have a Unique Event ID that matches…
Then it filters the data to delete. I think this is where I was mainly going wrong…Since the first Module (the Master Calendar) is the unfiltered data where there will be more records, I needed to search that Base as the first condition of the filter. The filter then checks that data against the smaller set of data, which is the matching records found in the Team Calendar that I searched for in Module 2.
If there is no match found on the Team Calendar, then it can be concluded that the Team Calendar version was deleted, and as a result, the Master Calendar version will be deleted.
I would need to create a duplicate of this scenario for records deleted from the Master Calendar as well.
Thanks again for all the help.
John
I had created a scenario to delete records, for my use case. I will attach screenshots and maybe it will help you. Note the formula in the third image, you would use your key field for the formula.and the filter (in your case you will need to change it for your use case.)
Hope it helps., if it does, please type SOLVED in the subject line.
Thank you,
Mary Kay
Hi Mary,
Thank you for your reply.
I think that set me in the right direction, but I’m still having some issues.
As you’ll see in my screenshot, that’s the 2nd step of the automation. The first step performs a search on the Master Calendar for all records. On the second step, I’m trying to use the formula to search all of the records on the Team Calendar that don’t have a matching record on the Master Calendar. In the third step, I would delete those records…
I’ve tried a couple other formula variations, including:
IF({MasterCal ID} != “{{1.MasterCal ID}}”, TRUE(),FALSE())
IF({MasterCal ID} = “{{1.MasterCal ID}}”, “0”,“1”)
I’ve copied your automation exactly, but the difference is that mine is a negative scenario. In the first 2 steps, I need to match up the records across both Bases, and then in the 3rd step, select all the records that didn’t find a match in the first 2 steps and delete them. It doesn’t seem to work the same way…
You may need to use a router and have one branch with Airtable app and the same for the other branch.
Something like the image below, without the first Delete function. Then create a filter just before the last two Airtable apps (click the wrench image.) This is where you would setup your filter for:
“Step 3: Filter, then Delete - If the Event ID is found on the Master Calendar Version but does not exist on the Team Calendar Version, Delete the record on the Team Calendar (up to 10,000)”
For the filter, in the Airtable app, only keep what’s in both the curly and straight, but keep the brackets and quotes.
Give this a try.
Mary K
P.S. If you need to sent a reply to me directly use an ampersand in front of my name: @M_k
I was able to solve this problem with the help of @M_k!
Here is the solution:
(note: the Router might not be necessary so I’ll test the scenario without it)
First, the automation searches the unfiltered Master Calendar Records…
Then, it looks up all the Team Calendar records that have a Unique Event ID that matches…
Then it filters the data to delete. I think this is where I was mainly going wrong…Since the first Module (the Master Calendar) is the unfiltered data where there will be more records, I needed to search that Base as the first condition of the filter. The filter then checks that data against the smaller set of data, which is the matching records found in the Team Calendar that I searched for in Module 2.
If there is no match found on the Team Calendar, then it can be concluded that the Team Calendar version was deleted, and as a result, the Master Calendar version will be deleted.
I would need to create a duplicate of this scenario for records deleted from the Master Calendar as well.
Thanks again for all the help.
John
