Auto-Updating Junction Table

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.

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?

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

sure thing.

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

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.

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.

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:

Hey Adam,

I would love to see the second automation, can you share a quick screenshot of how you are updating the Junction table with the appropriate number of records?

Thanks,

Montier