New Script: Same Table Linked Records Backlinks

Explainer Video:

Base:

Code:

13 Likes

That was good… excellent idea, @openside!

@openside thanks so much for the code! Do you have any suggestions on how to tweak the code so that the fields (Source and Destination) are fixed, and we don’t need to select them upon running the script?

Read the top of the script code. It gives instructions on how to update the configuration section (labeled in the code) so you don’t have to answer those prompts each time.

2 Likes

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! :smiley:

2 Likes

@openside do you know of any way to do this automatically when a forward link is created, rather than having it be run manually on a large batch?

now with automations you could set it up to listen for changes to the initial link field and then run a modified version of this script that just processes that single record.

2 Likes

I just tried that and it works, but has a major limitation: in the automations you can’t receive the value of the changed field, if the changed field is a linked record field. This means that you don’t know what changed, which means (I think) that you have to do a full walk of the table, basically running your whole script every time a change is made. I tried this on ~180 records and it took seconds to run. I’m very nervous about what will happen when we get to thousands of records.

Am I wrong about the automation not receiving the value of the changed field, or about the implication of that?

Thanks!
Theo

Correct.

Not necessarily. Because you have the record ID of the triggering record, that can be passed to the script via an input variable. Run a query on the table, then use the getRecord method on that query to select the specific record that triggered the automation (using that input variable). That will let you read any of its fields, including the link field, which will return an array of objects for the linked records. Iterate through that array and add links back to the triggering record. No searching needed.

But what if the change removed a record link rather than adding one? Without doing a full walk to find them, how do you avoid orphaned back-links?

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.

@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.

1 Like

@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!

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!

@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.

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.

2 Likes

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”
},
]

script

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!

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

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