New Script: Same Table Linked Records Backlinks

[openside]

Explainer Video:

Base:

Airtable

Same Table Backlinks - Airtable Universe

Title: Same Table Backlinks License: MIT Author: Openside (Team behind On2Air products and BuiltOnAir community) Sites: https://openside.com - Openside...

Code:

gist.github.com

https://gist.github.com/on2air/b68450a877ab99fe4a93ef7f9c387568

same-table-backlinks.js

/*****
 * Title: Same Table Backlinks
 * License: MIT
 * Author: Openside (Team behind On2Air products and BuiltOnAir community)
 * Sites:
 * https://openside.com - Openside Consulting Services
 * https://openside.com/#products - On2Air Products
 * https://builtonair.com - All things Airtable Community
 * 
 * Reach out for all your Airtable needs

This file has been truncated. show original

[Justin_Barrett]

Ahhh…that is indeed a conundrum. To avoid a full walk to find orphaned back-links, you’d need a second way to track existing links, like putting the record IDs of all linked records in another field (not via a formula, but via the script that made the links. When link changes are made, the automation script would need to compare those IDs against the actual linked records and address any discrepancies appropriately: either making new reciprocal links, or removing old ones.

[Paul_Warren]

@Theo_Michel,

I would recommend taking a look at my earlier post about automating this script. It might not look simple, but you can use a look-up field and a formula to check if the current record is back-linked properly within the record it is linked to. If my post quoted below isn’t enough detail, let me know and I’ll try to put a fake base together I can send a view link for.

Paul_Warren:

Thank you @openside! This Script has been so integral to our workflow that we have now automated the process!

Below is the process laid out:

1. Look up the “Previous Task” field of the “Next Task” (Field: “nextTasksPreviousTasks”)
2. SEARCH() if the Primary Field, “Task ID”, is found within “nextTasksPreviousTasks”
3. Look up “Next Task” of “Previous Task” (Field: “previousTasksNextTasks”)
4. SEARCH() if the “Task ID” is found within “previousTasksNextTasks”
5. IF() either SEARCH() fails then flag in “missingLinks”
6. Create view filtered on “missingLinks”. (View: “BacklinkingAutomation”
7. Trigger Automation based on “BacklinkingAutomation” view.

The Automation triggers your script with a few modifications to eliminate calls to “Output”.

Works beautifully! :grinning_face_with_big_eyes:

[Theo_Michel]

@Paul_Warren is your script run every time a row is updated, or only periodically or on demand? Also, could you share the full list of relevant fields in your table? I don’t understand the general idea of previousTasksNextTasks and nextTasksPreviousTasks.

@Justin_Barrett’s suggestion seems the cleanest to me. Putting the links in an additional field allows you to do a diff between the fields to figure out what link(s) were added or removed, when running a script triggered by a record update automation. Then you can just process the diff, which should be quick.

My biggest concern at this point is that I don’t understand why AirTable explicitly prevents this / makes it so hard. It may take me a couple hours to write that script (as a relative noob), which means it would take them 30 minutes or less… so why haven’t they!? (and just included it as a feature)

Some possibilities that come to mind:

1. it’s not actually that easy to implement
2. the solution is prone to subtle issues, either with performance or consistency

I wish the airtable team would just weigh in on this and make a recommendation!

[Justin_Barrett]

Theo_Michel:

I wish the airtable team would just weigh in on this and make a recommendation!

The Airtable dev team is notoriously tight-lipped about a lot of things. There’s probably some deeper reason why backlinks aren’t created automatically, and my gut says that one or both of your guesses are involved, but it’s unlikely that the devs would ever chime in. I’d love to be proven wrong, though!

[Paul_Warren]

@Theo_Michel,

It will be my goal to create an example base outside of work hours sometime next week. I think it will help other people as well. It’s not 100% perfect, but it runs automatically whenever a record is changed and knows which records to change based on if they are linked properly. There can be some delay, but it isn’t too bad.

[openside]

If I were to guess why its not implemented, i don’t think its technical - they already do it for across-table links.

I think its because it would cause confusion for the average user - it can be easy to get the 2 fields confused on how they relate to eachother and which one should be used, since they are both linked records that point to the same table.

So, I’m thinking it was to avoid that confusion.

[Jordan_Toplitzk]

any ideas why the script isn’t working for me? I copied it and updated it for my table exactly as the original openside script is written. thanks

let links = [
{
table:“Contacts”,
source:“Service Provider2”,
dest:“SP Clients”
},
]

[Paul_Warren]

Hello @Theo_Michel,

Below is a link to the example base. It is simplified from what I described previously. I will explain the weaknesses after discussing how it works.

Function
Essentially, everytime the linked records are edited, formulas check to see if the records are backlinked properly. This is accomplished by looking up the backlink of the linked record and checking if the current record can be found there. If it is not, the formula flags the record with a “1” and populates a view called “Missing Links”. An automation is then triggered to run the backlinking script.

Limitations

1. The major limitation of this approach is that it only works 100% if records are only linked to a single other record. This approach can be refined for multiple links by editing the formula fields. However, even as is, it works quite well even with multiple linked records.
2. As your number of records increases, the script begins to take longer and longer to run. Eventually, this will cause the automation to fail. The best way to handle this is to have the automation only correct a specific view. If you are confident you only have a small number of “Active Records” that might be the easiest way to narrow them down.
   In my real base, I have my “Tasks” table records linked to a “Projects” table and typically links between tasks stay within a project (not always true… That’s a different topic). So, I have solved this problem by creating a view that shows all tasks for all projects where at least one task’s links have been edited in the last 10 minutes. So far, this has worked beautifully.

Link to Base: Automated Backlinking Base

Fields
Task ID: Primary field using a simple formula to concatenate "Task " with a hidden Autonumber field.
Next Tasks: A Linked Record field for the same table. Source Field for the script.
Previous Tasks: A Linked Record Field for the same table. Destination Field for the script.
Next Tasks’ Previous Tasks: Lookup Field of Linked records in “Next Tasks”. Looks up the “Previous Tasks” of the “Next Tasks”.
Previous Tasks’ Next Tasks: Lookup Field of Linked records in “Previous Tasks”. Looks up the “Next Tasks” of the “Previous Tasks”.
Search Next Tasks’ Previous Tasks: Uses an IF statement and the SEARCH function to check if the current task is in the Next Tasks’ Previous Tasks.
Search Previous Tasks’ Next Tasks: Uses an IF statement and the SEARCH function to check if the current task is in the Previous Tasks’ Next Tasks.
Missing Links: Returns 1 if either of the previous two fields returned 1. This field indicates if backlinks are missing! It isn’t perfect, but it works reasonably well.

Views
Missing Links: Shows all records where “Missing Links” = 1

Automation
Backlinking Automation:

• Trigger: Record entering the “Missing Links” view
• Action: Runs the backlinking script with minor edits to remove calls to the Output API.

Let me know if you have any questions. Make sure to look at the base and the formulas. I know reading even my best descriptions can be very confusing :stuck_out_tongue:

I hope this can help!

[Paul_Warren]

Is it possible that the comma after the
}
needs to be removed? It might be just a syntax issue.

[Jordan_Toplitzk]

i’ve tried removing the comma and still not working. am I using the script correctly?