How to auto-update a junction table depends on the types of updates you need.
If you want to automatically delete junction records when one of the linked records is deleted, you can trigger an automation when one of the linked record fields is blank. Then use a scripting action to delete the record. (A scripting action is currently the only way to automatically delete a record without a third party integration. I include a script for deleting a triggering record in my automation helper scripts in my Gumroad store.
Adding records to a junction table is trickier. A lot depends on how many junction records you want to create. It is possible to have a non-scripting method of creating junction records. One method is to use a “find records” action to find the records in the other table, then link the found records to the triggering record. This creates a backlink in the other table. Then a second automation in the other table creates each junction record and deletes the direct link.
What frustrates me is that the Create Junction Table “Script” (see image) does exactly what I want. It creates a many many junction table, and fixes any errors every time it runs. If a record is deleted, it deletes the junction records associate with that also.
It’s absolutely perfect!
The problem is, I can’t run it from an automation, because when I install it it becomes an “App” and not a “Script”. Even though it’s listed as a “Script” in the app store area. And everything I’ve read says you cannot “run” an app through an automation.
This is some confusing terminology on Airtable’s part.
Scripting App is an “app” that lives in a “dashbaord”–the right-side panel that appears when you click “apps”.
A Scripting Action is an action in an automation
Some scripts can run in both Scripting App and an Automation Scripting action, others only work in one location or the other. Someone experienced at writing scripts for Airtable can convert a script that works in one location to the other location.
If you are planning on running the script on a schedule, hiring someone to convert the existing script to an automation script could work.
However, if you want to trigger the automation when a new record is created or deleted in either table, I do not recommend using that script. That script examines all records in all three tables, making it inefficient. If you end up having multiple new records trigger the automation simultaneously, you may also end up with duplicate junction records.
Hey Zion, thanks for providing the temp table! I’ve added two automations to it:
An automation that runs when a record on the Junction table has an empty Bug Name or Home Name field, and then deletes that record
An automation that, on creation of a record in the Bug table, will create an appropriate number of records in the Junction table with the appropriate links
I believe the base in your post is a base on a Pro workspace, and as such you’ll be charged for every collaborator that joins it. I’ve taken the liberty of deleting the Creater invite link to your base so that, even if someone clicks on the link, they will not be invited to your base and you will not be charged.
I have also gave myself Read Only permissions for the base so that you will not be charged anymore.
You should be able to create your own automation for records created in the Human table with what I’ve done for you, but let me know if you need help