Auto-Updating Junction Table

[Zion_Brock]

Has anyone found a solution to have a junction table automatically updated, when new records are created or existing ones deleted?

I love the Junction App that is supported by Airtable, but need a solution that triggers it to run. And from what I understand there’s no way to run an app through an automation.

So I tried to take the script used in the App and use it in an automation, but can’t get it to work.

I’d even hire a programmer to do this, but when I tried to find someone on Fiverr they knew less than I did about scripting … LOL.

[kuovonne]

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.

On the other hand, I am a fan of scripting and I would probably do this with scripting to avoid having the extra direct linked record field. However, I recommend only hiring a script writer who is familiar with writing scripts for Airtable. It is important to have a developer who understands the Airtable architecture as well as JavaScript.

[Zion_Brock]

Thank you for your response.

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.

I have tried to just copy the javascript code from it and make my own script inside an automation, but it always throws an error. And my limitations in this area prevent me from seeing why it isn’t working, even after creating input variables.

Does this make better sense now?

[TheTimeSavingCo]

Hm, that looks simple enough to modify. Could you take a screenshot of how you’ve got it set up right now? i.e.:

With a script in an automation action, all the fields and tables will have to be hardcoded, and so whoever assists you would need that information

[Zion_Brock]

sure thing.

[Zion_Brock]

I’m trying to get it working in a little temporary table, so then I can modify it from there. Here’s my temp table for you. Sign up - Airtable

[kuovonne]

Zion_Brock:

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.

• A Script is some JavaScript code.
• 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.

[kuovonne]

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.

[TheTimeSavingCo · Accepted solution]

Hey Zion, thanks for providing the temp table! I’ve added two automations to it:

1. 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
2. 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

---

Thanks to @kuovonne for the pointers!

[Zion_Brock]

Thank you so much for all your contributions, guys. This was super helpful! Adam, I ended up using the scripts you added to my example table, and it worked great!

You guys are awesome. :slightly_smiling_face: