Automatically Linking Records to Each Other in Same Table

[Gunnell]

I have a table with contact information for people and business. I need to associate contacts (in the same table) with each other where appropriate. When I associate Sally with Joey (see screen shot), I'd like Joey to automatically be added as an associated contact for Sally.

I'm having a hard time wrapping my head around how to automate this. Especially since I don't want to affect the Pizza Shop record in this case, I just need to locate the record that was just linked to (Sally) and enter the record that was updated (Joey) while also retaining any records the "linked to" record (Sally) already contained in the linked field (Pizza Shop).

[ScottWorld · Accepted solution]

Unfortunately, Airtable doesn’t offer automatic backlinking within the same table. Your only 2 options for this would be to:

(1) Write a custom JavaScript to handle this for you. Two scripts that do this are available here and here.

or

(2) For a no-code way of doing this that doesn’t require writing any programming code at all, you can use Make’s advanced Airtable automations, which is a no-code automation tool for Airtable.

There is a small learning curve with Make, which is why I’ve assembled a bunch of Make training resources in this thread. For example, to instantly trigger a Make automation from Airtable, check out this thread on instantly triggering a Make automation from Airtable using a webhook.

You will also need to know how to work with arrays, which I demonstrate on this Airtable podcast episode.

p.s. If you have a budget for your project and you’d like to hire an expert Airtable consultant to help you with any of this, please feel free to contact me through my website: Airtable consultant — ScottWorld 

[Gunnell]

As always, thanks for your fast and helpful response. It sounds like I might have to finally break down and bring scripts and external tools into my Airtable universe. I've been holding out for so long... 🙂 

[ScottWorld]

Haha, I know what you mean! Although that’s where all the fun REALLY begins! 😉

I would highly recommend starting your external app journey with Make, because it can typically handle 95% of any advanced automation needs — with no coding!

[Pjero_Kusijanov]

I have used quite a lot of time to sort this out, and I have finally managed to come to bullet profs solution that really works as it is supposed to.  

It includes Airtable automations and Script.

My table name: Contacts
My linked field name in Contacts table: Linked Records
*Change this values in script to match yours or use same names as I did.

In table where you want to use bi-directional linking (Automatically Linking Records to Each Other);

1. Create field "Link to another record" linking to itself.
     - In my case, {Linked Records}. Make sure to check "Allow linking to multiple records"

2. Create Automation

2.2 Action 1: "When a record is updated". Point it to "Contacts" table, and for fields use {Linked Records}

2.3. Action 2: "Run Script".
       - For input variable; Set name to: recordId, and value Airtable record ID (See photo below)
       - Paste the Script 

// Define the table
let table = base.getTable("Contacts"); // Replace "Contacts" with your actual table name

// Get the record that triggered the automation
let inputConfig = input.config();
let recordId = inputConfig.recordId;
let record = await table.selectRecordAsync(recordId);

// Define the linked records field
let linkedField = "Linked Records";

// Get the current linked records for the trigger record
let currentLinkedRecords = record.getCellValue(linkedField) || [];

// Array to store records that need to be updated
let recordsToUpdate = [];

// Loop through all linked records and ensure bidirectional link
for (let linkedRecord of currentLinkedRecords) {
let linkedRecordId = linkedRecord.id;
let linkedRecordDetails = await table.selectRecordAsync(linkedRecordId);

let linkedRecordLinkedTo = linkedRecordDetails.getCellValue(linkedField) || [];

// Check if the current record is already in the linked record's linked field
if (!linkedRecordLinkedTo.some(rec => rec.id === record.id)) {
// Add this linked record to the update array (only if the link doesn't already exist)
recordsToUpdate.push({
recordId: linkedRecordId,
linkedRecords: [...linkedRecordLinkedTo, { id: record.id }]
});
}
}

// Update all necessary records in a batch to avoid multiple triggers
if (recordsToUpdate.length > 0) {
let updates = recordsToUpdate.map(record => ({
id: record.recordId,
fields: {
[linkedField]: record.linkedRecords
}
}));

// Batch update all records
await table.updateRecordsAsync(updates);
}

// Now handle removing bidirectional links
let allRecords = await table.selectRecordsAsync();
let recordsToRemove = [];

// Loop through all records and check for unlinked records
for (let otherRecord of allRecords.records) {
if (otherRecord.id !== recordId) {
let otherLinkedRecords = otherRecord.getCellValue(linkedField) || [];

// Check if the current record was previously linked but is now unlinked
if (otherLinkedRecords.some(rec => rec.id === recordId) && !currentLinkedRecords.some(rec => rec.id === otherRecord.id)) {
// Add this record to the removal array (only if the link exists and needs removal)
recordsToRemove.push({
recordId: otherRecord.id,
linkedRecords: otherLinkedRecords.filter(rec => rec.id !== recordId)
});
}
}
}

// Batch remove records that no longer need to be linked
if (recordsToRemove.length > 0) {
let removals = recordsToRemove.map(record => ({
id: record.recordId,
fields: {
[linkedField]: record.linkedRecords
}
}));

// Batch update all records to remove unneeded links
await table.updateRecordsAsync(removals);
}


Hope you manage to get it to work. Enjoy 🙂