Need automation to crosslink records on the same table when one is linked to another

[Mouser-IB-Admin]

Alright, this is a bit convoluted, so I'm hoping someone out there is much more comfortable with automations than I am because this is driving me absolutely crazy. I have a list of companies that are being tracked as they're evaluated, but I want to be able to cross-reference them on the same table to indicate they're being evaluated for the same need. For instance, if I have records for Coke, Pepsi, and Dr. Pepper, I want to be able to go into the Coke record and have a linked record field that points to the same table, and I want to be able to link Pepsi and Dr. Pepper as also soda options.

That part is easy, I've got it configured. The trick is that when I do this, what I want is for an automation to automatically go to the Pepsi record and update the linked record field to add Coke, showing the two are linked. Same for Dr. Pepper. The dream scenario is that Pepsi and Dr. Pepper would also reference each other automatically, but I bet that's probably pushing it a bit far.

I am at a loss for how to get an automation to do this. From what I've gathered I need to have the trigger as "when a record is updated" and point to the specific field, but then what? Some things I've read say I need both a "find records" and then an "update record" trigger, but nothing I've tried to configure ever works. The one time I got it to fully pass validation, instead of updating any other fields it just cleared the field I had just populated with a value. Trying to figure out which dynamically generated selection to make and reference back to previous steps in the automation is making me go cross-eyed.

It sure would be helpful if when the test fails Airtable actually told you where in the process it failed, instead of just a big "nope". lol Can anyone guide me through this?

[Alyssa_Buchthal]

This would be pretty simple to do with javascript and a quick loop, whenever that field is updated for one, find the records using the ids in the first linked field, and update each's matching field to contain the same id's as the field from the og record.

In simple terms, If you call the field "same need evaluation" and it's a self-linked field to that same table with all other records linked therein, when you update it for one (when a record is updated trigger), it would loop through all records linked to in that field (loop condition: "for each record linked in 'same need evaluation') just duplicate the cell contents from the trigger record into all records linked in the same field.

This will cause an infinite loop, however, so you'll be relying on Airtable thinking that a duplicate update w/ no actual change doesn't trigger the automation again. Otherwise you'd need to build in your own stop condition.

[Mouser-IB-Admin]

Would you be willing to provide any additional guidance? I'm already stumbling with the built-in automation flow, so adding javascript to it is a whole new element of complexity that I'm just not ready for.  🙂  The infinite loop part definitely crossed my mind if the records kept trying to link to each other, but there's no "insert stop" function in the automation builder so that's tricky.

[Alyssa_Buchthal · Accepted solution]

I won't be able to write out code segments for you without seeing the actual base. One thing I will say is that architecture-wise, self-linked records are never an ideal thing. Could you not keep a separate table tracking evaluations, then link it to the table with products, and have each product considered in an evaluation linked appropriately? Then you can filter by evaluation to see all products in consideration, whereas with the self-link you won't be able to do that.

[Mouser-IB-Admin]

This is what I'm doing as a placeholder, so it seems it's going to be my best bet long-term unfortunately. Every time I think of something I'd like to automate, it unfortunately ends up like this where the logic would break Airtable. I appreciate you responding with the suggestion though.

[Alyssa_Buchthal]

I think you'll find that in the long-term, it's a much more robust solution. Also, if this is what you already have in place, you can 100% make a look-up to the linked record field so that you can access all records linked from any single record linked. Quick pic of what I mean below, with table 22 having records 1, 2, and 3 all linked to table 23.

[TheLeadGenerati]

Automating record crosslinking within the same table boosts efficiency. Picture The Lead Generation Agency using this to keep data linked and updated seamlessly. It cuts down manual work and keeps everything accurate and organized.