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?