@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?
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:
The Automation triggers your script with a few modifications to eliminate calls to “Output”.
Works beautifully! :grinning_face_with_big_eyes:
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?
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.
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.
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 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:
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!
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.
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.
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.
Link to Base: Automated Backlinking Base
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.
Missing Links: Shows all records where “Missing Links” = 1
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!